Forums

Hi PythonAnywhere,

The performance of our website is slow and after reading https://help.pythonanywhere.com/pages/MySiteIsSlow/ we think we should switch from SQLite to MySQL for a speedup :) Based on some other forum posts we think we should do the tasks below:

In our virtual environment: - pip install mysqlclient (as we are using Python 3.6)

In the mysql tab: - Create new password

Changes in settings.py: DATABASES = { 'default': { 'ENGINE': 'django.db.backends.mysql', 'NAME': '<your_username>$<your_database_name>', 'USER': '<your_username>', 'PASSWORD': '<your_mysql_password>', 'HOST': '<your_mysql_hostname>', } }

In the console:

python manage.py dumpdata > datadump.json

python manage.py migrate --run-syncdb or python manage.py migrate? (do you know which one?)

However, we are wondering whether it is really that simple :) Is this all to switch databases? And don't we need to make some changes to our Python code as well?

Thanks in advance for answering our questions!

Regards, Karsten

That should be it. The Django ORM deals with the differences between databases for you and whether you use migrate or syncdb will depend on the version of Django that you're using, so check the Django documentation for the version of Django that you are using.

Thank you for your answer! We are using Django version 2.1.1 (installed on our virtual environment). Do you know based on this version number which command (migrate or syncdb) we have to execute? :)

We executed the "python manage.py dumpdata > datadump.json" command before changing database settings, is that correct? And do we have to execute the migrate/syncdb command before or after changing datatabase settings? Thanks!

Yes, you should do the dumpdata before changing the database settings. and then after the database settings change you should migrate/syncdb and probably use loaddata to reload the data.

We created the datadump, changed database settings (to MySQL) and started the sync. Unfortunately, it resulted in an operational error stating "too big precision 100 specified for column"... so we changed database settings back, fixed this in models.py and ran migrate.

Now we want to create a new datadump, but this gives the following error: "sqlite3.ProgrammingError: Cannot operate on a closed database." Do you have any idea what to do next? :)

"and probably use loaddata to reload the data" - could you also explain in detail how to reload the data after syncing databases?

Once the database is synced, you can load everything up just by running

python manage.py loaddata datadump.json

...but it's probably a good idea to work out what the problem is with creating the new data dump first.

Is there more of a traceback that appears before the "sqlite3.ProgrammingError: Cannot operate on a closed database" message?

Thanks for all the help so far! :)

Yes, we agree we should work out that data dump problem first. The exact error message is:

(myevn) 12:06 ~/ontwikkelomgeving $ python3.6 manage.py dumpdata Evelien > datadump.json CommandError: Unable to serialize database: [<class 'decimal.InvalidOperation'>] Exception ignored in: <generator object cursor_iter at 0x7fd037e54410> Traceback (most recent call last): File "/home/ziekenhuisroosters/.virtualenvs/myevn/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1469, in cursor_iter cursor.close() sqlite3.ProgrammingError: Cannot operate on a closed database.

Before this error the data dump gave us this error: OperationalError: (1426, "Too big precision 100 specified for column 'plusminuren_def'. Maximum is 65.") We changed the precision from 100 to 5 which lead to the error mentioned above.

Do you have any idea? Please feel free to look in our files if necessary. It's the app ziekenhuisroosters.pythonanywhere.com with /home/ziekenhuisroosters/ontwikkelomgeving/ as it's working directory.

Thanks again!

i dont think you can change the precision in your scheme. because your database would still have the old precision and instead when you read it it would just completely break.

i'm a bit confused how your db was working originally if it was getting that precision error. Was your sqlite db ever operational?

Yes, it's still operational... working but a bit slow, the reason we wanted to change to MySQL :)

We changed the precision from 5 to 10 and were able to make the datadump. However, we still got the 100 precision error during the database sync as stated below:

`(myevn) 15:53 ~/ontwikkelomgeving $ python3.6 manage.py migrate --run-syncdb System check identified some issues:

WARNINGS: ?: (mysql.W002) MySQL Strict Mode is not set for database connection 'default' HINT: MySQL's Strict Mode fixes many data integrity problems in MySQL, such as data truncation upon insertion, by escalating warnings into errors. It is strongly recommended you activate it. See: https://docs.djangoproject.com/en/2.1/ref/databases/#mysql-sql-mode Operations to perform: Synchronize unmigrated apps: messages, staticfiles, ziekenhuisroosters Apply all migrations: Evelien, admin, auth, contenttypes, kinderdagverblijf, sessions Synchronizing apps without migrations: Creating tables... Running deferred SQL... Running migrations: Applying contenttypes.0001_initial... OK Applying contenttypes.0002_remove_content_type_name... OK Applying auth.0001_initial... OK Applying auth.0002_alter_permission_name_max_length... OK Applying auth.0003_alter_user_email_max_length... OK Applying auth.0004_alter_user_username_opts... OK Applying auth.0005_alter_user_last_login_null... OK Applying auth.0006_require_contenttypes_0002... OK Applying auth.0007_alter_validators_add_error_messages... OK Applying auth.0008_alter_user_username_max_length... OK Applying auth.0009_alter_user_last_name_max_length... OK Applying Evelien.0001_initial...Traceback (most recent call last): File "/home/ziekenhuisroosters/.virtualenvs/myevn/lib/python3.6/site-packages/django/db/backends/utils.py", line 83, in _execute return self.cursor.execute(sql) File "/home/ziekenhuisroosters/.virtualenvs/myevn/lib/python3.6/site-packages/django/db/backends/mysql/base.py", line 71, in execute return self.cursor.execute(query, args) File "/home/ziekenhuisroosters/.virtualenvs/myevn/lib/python3.6/site-packages/MySQLdb/cursors.py", line 198, in execute res = self._query(query) File "/home/ziekenhuisroosters/.virtualenvs/myevn/lib/python3.6/site-packages/MySQLdb/cursors.py", line 304, in _query db.query(q) File "/home/ziekenhuisroosters/.virtualenvs/myevn/lib/python3.6/site-packages/MySQLdb/connections.py", line 217, in query _mysql.connection.query(self, query) MySQLdb._exceptions.OperationalError: (1426, "Too big precision 100 specified for column 'plusminuren_def'. Maximum is 65.")

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

We think you are right that the database still has the 100 precision... do you have any idea what to do next? Can we delete this database table and create it again with the right precisions?

In addition to the above, in the error log we see (even tough we execuded pip install mysqlclient succesfully in the virtual environment) the following: 2019-02-05 17:10:58,110: Error running WSGI application 2019-02-05 17:10:58,111: django.core.exceptions.ImproperlyConfigured: Error loading MySQLdb module. 2019-02-05 17:10:58,111: Did you install mysqlclient? 2019-02-05 17:10:58,111: File "/var/www/ziekenhuisroosters_pythonanywhere_com_wsgi.py", line 26, in <module> 2019-02-05 17:10:58,111: application = get_wsgi_application()

Perhaps see this?

Thank you so much!! We fixed all the errors and were able to make a data dump, change database settings, sync databases and load the data dump to the new MySQL database! :)

However, we still getting a "Something went wrong :-(" error when visiting our website. When looking at the error log we see the following:

2019-02-06 16:38:45,097: Error running WSGI application 2019-02-06 16:38:45,098: django.core.exceptions.ImproperlyConfigured: Error loading MySQLdb module. 2019-02-06 16:38:45,098: Did you install mysqlclient? 2019-02-06 16:38:45,098: File "/var/www/ziekenhuisroosters_pythonanywhere_com_wsgi.py", line 26, in <module> 2019-02-06 16:38:45,098: application = get_wsgi_application()

In our virtual environment we did install mysqlclient executing pip install mysqlclient. Did we forget something or do you know how we can load the MySQLdb module?

Thanks!

Ps. we executed pip install mysqlclient again and it looks like everything is working!

Great! I was just about to suggest that :-)

Hi,

I'm about to execute the same procedure, I currently have an sqlite database and want to switch to mysql. Would it be possible if you created the step-by-step procedures you did to successfully migrate?

Thanks!!!

The first post in this thread is a step-by-step of the process. Beyond that it's specific issues that you may have with your database.

Thank you for this great thread! It guided me smoothly through the transition to MYSQL.

It now also runs much faster for me. best regards Martin