[FIXED] Getting a SQL error when running "python manage.py migrate" using a MySQL database

Issue

I've been through the introductory tutorial for Django successfully using SQLite as per the instructions. However, for my real project I want to use MySQL, so I deleted the tutorial project files and started with a fresh setup. The trouble is that when I run "python manage.py migrate" I get the following MySQL error:

_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%s' at line 1").

I have the following in the site settings database section:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'vocabulator$database',
        'USER': 'vocabulator',
        'PASSWORD': '<password>',
        'HOST': 'mysql.server', 
    }
} 

I connected to this database successfully in Bash using, 'mysql --user=vocabulator --host=mysql.server --password=<password> "vocabulator\$database" ', so it appears I have entered these setting details correctly.

I also followed an instruction on setting up database bindings for Python 3, which also appeared to work successfully. It was on either the Django documentation pages or the PythonAnywhere equivalent, but unfortunately I cannot locate the page reference again.

I haven't written any MySQL queries myself yet, so any incorrect ones that are being made must be coming from manage.py, so what could be the cause? The complete error trace is quoted below:

Operations to perform:   Synchronize unmigrated apps: staticfiles, messages   Apply all migrations: contenttypes, sessions, admin, auth Synchronizing apps without migrations:   Creating tables...
    Running deferred SQL...   Installing custom SQL... Running migrations:   Rendering model states... DONE   Applying contenttypes.0001_initial...Traceback (most recent call last):   File "/home/vocabulator/.virtualenvs/django18/lib/python3.4/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)   File "/home/vocabulator/.virtualenvs/django18/lib/python3.4/site-packages/django/db/backends/mysql/base.py", line 124, in execute
    return self.cursor.execute(query, args)   File "/home/vocabulator/.virtualenvs/django18/lib/python3.4/site-packages/MySQLdb/cursors.py", line 184, in execute
    self.errorhandler(self, exc, value)   File "/home/vocabulator/.virtualenvs/django18/lib/python3.4/site-packages/MySQLdb/connections.py", line 37, in defaulterrorhandler
    raise errorvalue   File "/home/vocabulator/.virtualenvs/django18/lib/python3.4/site-packages/MySQLdb/cursors.py", line 171, in execute
    r = self._query(query)   File "/home/vocabulator/.virtualenvs/django18/lib/python3.4/site-packages/MySQLdb/cursors.py", line 330, in _query
    rowcount = self._do_query(q)   File "/home/vocabulator/.virtualenvs/django18/lib/python3.4/site-packages/MySQLdb/cursors.py", line 294, in _do_query
    db.query(q)
_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%s' at line 1")

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

Traceback (most recent call last):   File "manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)   File "/home/vocabulator/.virtualenvs/django18/lib/python3.4/site-packages/django/core/management/__init__.py", line 338, in execute_from_command_line
    utility.execute()   File "/home/vocabulator/.virtualenvs/django18/lib/python3.4/site-packages/django/core/management/__init__.py", line 330, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)   File "/home/vocabulator/.virtualenvs/django18/lib/python3.4/site-packages/django/core/management/base.py", line 393, in run_from_argv
    self.execute(*args, **cmd_options)   File "/home/vocabulator/.virtualenvs/django18/lib/python3.4/site-packages/django/core/management/base.py", line 444, in execute
    output = self.handle(*args, **options)   File "/home/vocabulator/.virtualenvs/django18/lib/python3.4/site-packages/django/core/management/commands/migrate.py", line 222, in handle
    executor.migrate(targets, plan, fake=fake, fake_initial=fake_initial)   File "/home/vocabulator/.virtualenvs/django18/lib/python3.4/site-packages/django/db/migrations/executor.py", line 110, in migrate
    self.apply_migration(states[migration], migration, fake=fake, fake_initial=fake_initial)   File "/home/vocabulator/.virtualenvs/django18/lib/python3.4/site-packages/django/db/migrations/executor.py", line 148, in apply_migration
    state = migration.apply(state, schema_editor)   File "/home/vocabulator/.virtualenvs/django18/lib/python3.4/site-packages/django/db/migrations/migration.py", line 115, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)   File "/home/vocabulator/.virtualenvs/django18/lib/python3.4/site-packages/django/db/migrations/operations/models.py", line 59, in database_forwards
    schema_editor.create_model(model)   File "/home/vocabulator/.virtualenvs/django18/lib/python3.4/site-packages/django/db/backends/base/schema.py", line 289, in create_model
    self.deferred_sql.extend(self._model_indexes_sql(model))   File "/home/vocabulator/.virtualenvs/django18/lib/python3.4/site-packages/django/db/backends/mysql/schema.py", line 55, in _model_indexes_sql
    self.connection.cursor(), model._meta.db_table   File "/home/vocabulator/.virtualenvs/django18/lib/python3.4/site-packages/django/db/backends/mysql/introspection.py", line 142, in get_storage_engine
    "WHERE table_name = %s", [table_name])   File "/home/vocabulator/.virtualenvs/django18/lib/python3.4/site-packages/django/db/backends/utils.py", line 79, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)   File "/home/vocabulator/.virtualenvs/django18/lib/python3.4/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)   File "/home/vocabulator/.virtualenvs/django18/lib/python3.4/site-packages/django/db/utils.py", line 97, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)   File "/home/vocabulator/.virtualenvs/django18/lib/python3.4/site-packages/django/utils/six.py", line 658, in reraise
    raise value.with_traceback(tb)   File "/home/vocabulator/.virtualenvs/django18/lib/python3.4/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)   File "/home/vocabulator/.virtualenvs/django18/lib/python3.4/site-packages/django/db/backends/mysql/base.py", line 124, in execute
    return self.cursor.execute(query, args)   File "/home/vocabulator/.virtualenvs/django18/lib/python3.4/site-packages/MySQLdb/cursors.py", line 184, in execute
    self.errorhandler(self, exc, value)   File "/home/vocabulator/.virtualenvs/django18/lib/python3.4/site-packages/MySQLdb/connections.py", line 37, in defaulterrorhandler
    raise errorvalue   File "/home/vocabulator/.virtualenvs/django18/lib/python3.4/site-packages/MySQLdb/cursors.py", line 171, in execute
    r = self._query(query)   File "/home/vocabulator/.virtualenvs/django18/lib/python3.4/site-packages/MySQLdb/cursors.py", line 330, in _query
    rowcount = self._do_query(q)   File "/home/vocabulator/.virtualenvs/django18/lib/python3.4/site-packages/MySQLdb/cursors.py", line 294, in _do_query
    db.query(q) django.db.utils.ProgrammingError: (1064, "You have an error in your  SQL syntax; check the manual that corresponds to your MySQL server v ersion for the right syntax to use near '%s' at line 1") Exception ignored in: <bound method Cursor.__del__ of <MySQLdb.curso rs.Cursor object at 0x7feec394b940>> Traceback (most recent call last):   File "/home/vocabulator/.virtualenvs/django18/lib/python3.4/site-p ackages/MySQLdb/cursors.py", line 67, in __del__   File "/home/vocabulator/.virtualenvs/django18/lib/python3.4/site-p ackages/MySQLdb/cursors.py", line 73, in close ReferenceError: weakly-referenced object no longer exists (django18)03:29 ~/mysite $ mysql --user=vocabulator --host=mysql.server --p mysql: ambiguous option '--p' (pager, plugin_dir)

Solution

As I said in my comment above, I found the page which had instructed me on how to install drivers. It is this one:

https://www.pythonanywhere.com/wiki/UsingMySQL

However, this Wiki page is a little dated now and it instructed me to install the MySQL connector version 2.0.1. The current version is now 2.0.4. The old version had a bug which meant it would not operate with Django 1.8, as stated here:

http://bugs.mysql.com/bug.php?id=76752

By version 2.0.4 this bug appears to have been corrected.

The line in the PythonAnywhere Wiki page referenced above should change to:

pip3.4 install https://dev.mysql.com/get/Downloads/Connector-Python/mysql-connector-python-2.0.4.tar.gz

Once I ran this, the migration appears to have worked successfully on my Django installation.

Answered By - Terry Rozmus