Forums

Hello,

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 quieries 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)

Someone's question on my post to Stackoverflow led me to the page which explains how to install connectors. It was here:

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

It turns out though that there was a bug in the the connector version listed on this page which meant it could not work with Django 1.8. This has been corrected in a newer version. Here is the the line for installing it on PythonAnywhere with a virtualenv for Django 1.8 on Python 3.4:

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

Also, in my original post, I had made a mistake with the 'ENGINE'. It should have been 'mysql.connector.django' as in the Wiki page above.

Hi there, glad you figured it out, and thanks for letting us know about that update to mysql-connector! I've updated the wiki page. Much appreciated! :)