Recreate all tables in mysql after dropping the database

[Joel Mathew]

I tried to fix mysql problems in my database by dropping tables. When that didnt work, I dropped the database.

I then recreated the database, and tried to run makemigrations. But I am getting errors about missing tables:

joel@hp:~/myappointments$ python3 manage.py makemigrations

Traceback (most recent call last):

  File "/home/joel/.local/lib/python3.6/site-packages/django/db/backends/utils.py", line 85, in _execute

    return self.cursor.execute(sql, params)

  File "/home/joel/.local/lib/python3.6/site-packages/django/db/backends/mysql/base.py", line 71, in execute

    return self.cursor.execute(query, args)

  File "/home/joel/.local/lib/python3.6/site-packages/MySQLdb/cursors.py", line 250, in execute

    self.errorhandler(self, exc, value)

  File "/home/joel/.local/lib/python3.6/site-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler

    raise errorvalue

  File "/home/joel/.local/lib/python3.6/site-packages/MySQLdb/cursors.py", line 247, in execute

    res = self._query(query)

  File "/home/joel/.local/lib/python3.6/site-packages/MySQLdb/cursors.py", line 412, in _query

    rowcount = self._do_query(q)

  File "/home/joel/.local/lib/python3.6/site-packages/MySQLdb/cursors.py", line 375, in _do_query

    db.query(q)

  File "/home/joel/.local/lib/python3.6/site-packages/MySQLdb/connections.py", line 276, in query

    _mysql.connection.query(self, query)

_mysql_exceptions.ProgrammingError: (1146, "Table 'appointmentsdj.appointments_doctor' doesn't exist")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):

  File "manage.py", line 15, in <module>

    execute_from_command_line(sys.argv)

  File "/home/joel/.local/lib/python3.6/site-packages/django/core/management/__init__.py", line 381, in execute_from_command_line

    utility.execute()

  File "/home/joel/.local/lib/python3.6/site-packages/django/core/management/__init__.py", line 375, in execute

    self.fetch_command(subcommand).run_from_argv(self.argv)

  File "/home/joel/.local/lib/python3.6/site-packages/django/core/management/base.py", line 316, in run_from_argv

    self.execute(*args, **cmd_options)

  File "/home/joel/.local/lib/python3.6/site-packages/django/core/management/base.py", line 350, in execute

    self.check()

  File "/home/joel/.local/lib/python3.6/site-packages/django/core/management/base.py", line 379, in check

    include_deployment_checks=include_deployment_checks,

  File "/home/joel/.local/lib/python3.6/site-packages/django/core/management/base.py", line 366, in _run_checks

    return checks.run_checks(**kwargs)

  File "/home/joel/.local/lib/python3.6/site-packages/django/core/checks/registry.py", line 71, in run_checks

    new_errors = check(app_configs=app_configs)

  File "/home/joel/.local/lib/python3.6/site-packages/django/core/checks/urls.py", line 40, in check_url_namespaces_unique

    all_namespaces = _load_all_namespaces(resolver)

  File "/home/joel/.local/lib/python3.6/site-packages/django/core/checks/urls.py", line 57, in _load_all_namespaces

    url_patterns = getattr(resolver, 'url_patterns', [])

  File "/home/joel/.local/lib/python3.6/site-packages/django/utils/functional.py", line 37, in __get__

    res = instance.__dict__[self.name] = self.func(instance)

  File "/home/joel/.local/lib/python3.6/site-packages/django/urls/resolvers.py", line 533, in url_patterns

    patterns = getattr(self.urlconf_module, "urlpatterns", self.urlconf_module)

  File "/home/joel/.local/lib/python3.6/site-packages/django/utils/functional.py", line 37, in __get__

    res = instance.__dict__[self.name] = self.func(instance)

  File "/home/joel/.local/lib/python3.6/site-packages/django/urls/resolvers.py", line 526, in urlconf_module

    return import_module(self.urlconf_name)

  File "/usr/lib/python3.6/importlib/__init__.py", line 126, in import_module

    return _bootstrap._gcd_import(name[level:], package, level)

  File "<frozen importlib._bootstrap>", line 994, in _gcd_import

  File "<frozen importlib._bootstrap>", line 971, in _find_and_load

  File "<frozen importlib._bootstrap>", line 955, in _find_and_load_unlocked

  File "<frozen importlib._bootstrap>", line 665, in _load_unlocked

  File "<frozen importlib._bootstrap_external>", line 678, in exec_module

  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed

  File "/home/joel/myappointments/myappointments/urls.py", line 21, in <module>

    path('appointments/', include('appointments.urls')),

  File "/home/joel/.local/lib/python3.6/site-packages/django/urls/conf.py", line 34, in include

    urlconf_module = import_module(urlconf_module)

  File "/usr/lib/python3.6/importlib/__init__.py", line 126, in import_module

    return _bootstrap._gcd_import(name[level:], package, level)

  File "<frozen importlib._bootstrap>", line 994, in _gcd_import

  File "<frozen importlib._bootstrap>", line 971, in _find_and_load

  File "<frozen importlib._bootstrap>", line 955, in _find_and_load_unlocked

  File "<frozen importlib._bootstrap>", line 665, in _load_unlocked

  File "<frozen importlib._bootstrap_external>", line 678, in exec_module

  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed

  File "/home/joel/myappointments/appointments/urls.py", line 3, in <module>

    from . import views

  File "/home/joel/myappointments/appointments/views.py", line 6, in <module>

    from .forms import PatientForm, doctorform, AddAppointmentForm, PatientFormReadOnly, EditAppointmentForm, BillItem, PatientMetaForm, UnconfirmedApptMetaForm

  File "/home/joel/myappointments/appointments/forms.py", line 68, in <module>

    class AddAppointmentFormReadOnly(forms.Form):

  File "/home/joel/myappointments/appointments/forms.py", line 70, in AddAppointmentFormReadOnly

    for doc in doctor.objects.all():

  File "/home/joel/.local/lib/python3.6/site-packages/django/db/models/query.py", line 268, in __iter__

    self._fetch_all()

  File "/home/joel/.local/lib/python3.6/site-packages/django/db/models/query.py", line 1183, in _fetch_all

    self._result_cache = list(self._iterable_class(self))

  File "/home/joel/.local/lib/python3.6/site-packages/django/db/models/query.py", line 54, in __iter__

    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)

  File "/home/joel/.local/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1061, in execute_sql

    cursor.execute(sql, params)

  File "/home/joel/.local/lib/python3.6/site-packages/django/db/backends/utils.py", line 100, in execute

    return super().execute(sql, params)

  File "/home/joel/.local/lib/python3.6/site-packages/django/db/backends/utils.py", line 68, in execute

    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)

  File "/home/joel/.local/lib/python3.6/site-packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers

    return executor(sql, params, many, context)

  File "/home/joel/.local/lib/python3.6/site-packages/django/db/backends/utils.py", line 85, in _execute

    return self.cursor.execute(sql, params)

  File "/home/joel/.local/lib/python3.6/site-packages/django/db/utils.py", line 89, in __exit__

    raise dj_exc_value.with_traceback(traceback) from exc_value

  File "/home/joel/.local/lib/python3.6/site-packages/django/db/backends/utils.py", line 85, in _execute

    return self.cursor.execute(sql, params)

  File "/home/joel/.local/lib/python3.6/site-packages/django/db/backends/mysql/base.py", line 71, in execute

    return self.cursor.execute(query, args)

  File "/home/joel/.local/lib/python3.6/site-packages/MySQLdb/cursors.py", line 250, in execute

    self.errorhandler(self, exc, value)

  File "/home/joel/.local/lib/python3.6/site-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler

    raise errorvalue

  File "/home/joel/.local/lib/python3.6/site-packages/MySQLdb/cursors.py", line 247, in execute

    res = self._query(query)

  File "/home/joel/.local/lib/python3.6/site-packages/MySQLdb/cursors.py", line 412, in _query

    rowcount = self._do_query(q)

  File "/home/joel/.local/lib/python3.6/site-packages/MySQLdb/cursors.py", line 375, in _do_query

    db.query(q)

  File "/home/joel/.local/lib/python3.6/site-packages/MySQLdb/connections.py", line 276, in query

    _mysql.connection.query(self, query)

django.db.utils.ProgrammingError: (1146, "Table 'appointmentsdj.appointments_doctor' doesn't exist")

How can I remove all database data and revert as if to a fresh database.

I have tried a rm -rf myappointments/appointments/migrations/*

On django 2.1, ubuntu

[Mark Phillips]

This is how I do it:

1. drop the database

2. create the database. 

4. make sure the user identified in your settings.py can still access the database

5. delete the migrations

6. run all migrations

Your new db should be populated with all the initial tables.

Take a look at this, https://docs.djangoproject.com/en/2.1/howto/initial-data/, if you want to now add some initial data to your tables.

Mark

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAA%3Diw__Pw9pLMFOPZ7ov8JHWGx5EFNoCJnYF3J0j5iEOXXsHfQ%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

[Michal Petrucha]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA512

Hi Joel,

On Sun, Sep 16, 2018 at 10:16:42PM +0530, Joel Mathew wrote:
> I tried to fix mysql problems in my database by dropping tables. When that
> didnt work, I dropped the database.
> I then recreated the database, and tried to run makemigrations. But I am
> getting errors about missing tables:
>
> joel@hp:~/myappointments$ python3 manage.py makemigrations

[…]

> File "/home/joel/myappointments/appointments/forms.py", line 68, in
> <module>
> class AddAppointmentFormReadOnly(forms.Form):
> File "/home/joel/myappointments/appointments/forms.py", line 70, in
> AddAppointmentFormReadOnly
> for doc in doctor.objects.all():

[…]

>
> How can I remove all database data and revert as if to a fresh database.
> I have tried a rm -rf myappointments/appointments/migrations/*
>
> On django 2.1, ubuntu

The immediate problem that you're facing here is that your application
is making database queries while it is being imported – see line 70 in
/home/joel/myappointments/appointments/forms.py. You will need to
rewrite your forms (and any other classes) in such a way that they do
not query the database while they are being imported, for example by
making use of ModelChoiceFields, or generating any dynamic choices in
your views, and/or forms' __init__. Otherwise, if you keep hitting the
database on import, it will be pretty much impossible for you to set
up your application in an environment where the database schema does
not already match your model definitions.

Second, you should treat your migrations as any other code, because
that's what they are. Treat makemigrations as just a helper to give
you a starting point when writing migrations, rather than as a magic
silver bullet to fix all database problems, and once you have a
migration that does what you want it to do, commit it into the
revision history of your code base.

Good luck,

Michal
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJbn1kHAAoJEHA7T/IPM/klAxQQAIRjUj2R5XFOO0Q457AX0mWA
BM05wwLHEqxR0KxGkkV6M/6IN2qkhD0Sg9AJLTjQBvmKB+0OH85RrOHem85JaoCW
3H0vhgukM5yDIcZYNUDephAj6pvjR0gAFmVWPW9PY8+5hHBTkFLLwEAMvL96uNdO
TVerrRNcDw7QeiihyHa23JPEP9sYd3g9GEH8gtwJQ1q68oGOaNkEbESK8kQIv5hq
Zu1Dlf01h+dJzZNaKjvMlZJlNsuQNkPxpiuus5mKv/EFWLyIbwqS9r/Uy15NLTo9
Lg5dLi9mSPr6yDiNI24u/WFQI//YyRIOKc7w6d0DRKPBR7NP3Xw8h7DYOGCpwJns
xkgw0wqsdRw7Wjblao3TLCcgaYRe11B6YkyxwYMFo0rL/foT9NYD5OCxNwZjmgs/
9paC8Ye8SDzamza78WEL9EIBkGjzSUSqgHvBwlMjiHXAEXRDMaFM/vbz+Cb3YFAR
jEa5LLgBXrfjcVxE+pROBsB+jfgONTo+M9Y/4Xw3WiuDiYxF8FQGBqr3QLcMfIyp
4KoHqAN04/yvFtXbZNs7097fNLfi0vhYjRLPqp/HDqYOvS/LIlnHkJ/EJBHK0CqQ
RRRpS42Sin6Mqoz3XyGOxQFfU3fxUl/lBnwdKAB3IrrOPZQvf7Uw03+CCflOeYFZ
8Ksm4yD3EHXidQCYkeDN
=Uc5m
-----END PGP SIGNATURE-----

[Joel]

Thank you. That is how I fixed it yesterday late night after spending hours on it.

For anyone else that may find it useful, 

I had dropped the tables to fix this. But that ended complaining about missing tables. Then I dropped the database, since all I had was development data. 

It wouldn't do the makemigrations, still complaining about missing columns. I deleted migration files, pycache.. Nothing helped.

Finally, I commented imports of models and forms in my code, and then ran

python3 manage.py app makemigrations

And then it worked.

--
You received this message because you are subscribed to the Google Groups "Django users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.

To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/20180917073432.GX1181%40koniiiik.org.

[Daniel Roseman]

On Monday, 17 September 2018 08:52:32 UTC+1, Joel wrote:

Thank you. That is how I fixed it yesterday late night after spending hours on it.

For anyone else that may find it useful, 

I had dropped the tables to fix this. But that ended complaining about missing tables. Then I dropped the database, since all I had was development data. 

It wouldn't do the makemigrations, still complaining about missing columns. I deleted migration files, pycache.. Nothing helped.

Finally, I commented imports of models and forms in my code, and then ran

python3 manage.py app makemigrations

And then it worked.

Note that although this may have fixed the immediate problem, you still have another issue, which as Michal pointed out is that you are doing database queries at class level in your forms. This will hurt you in several ways: you'll get the same problem when you try to deploy to production, and more seriously you are causing the form to only get the list of doctors on first import. If you add a new doctor you won't see it until you restart the web server process.

If you must generate choices like this, do it in the form's `__init__` method, but generally it is better to use ModelChoiceField with a queryset rather than a ChoiceField with a manually-generated list of choices.

--

DR.