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

Question

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)

You have --password= "vocabulator\$database" ', but in your django settings this is the db name? — Burhan Khalid
– Burhan Khalid, Commented Aug 31, 2015 at 5:34
I had used < and > symbols which were interpreted as a HTML tag, which created a typo in my post. I have fixed it now. As you said, the last part is the DB name. I have obviously not quoted the actual password in my post. — Terry Rozmus
– Terry Rozmus, Commented Aug 31, 2015 at 6:16
Your question led me to the page I had followed to install connectors (I called them bindings in the post): pythonanywhere.com/wiki/UsingMySQL The drivers I installed are the ones listed on that page, which appear to be compatible with both MySQL 5.5 and Python 3.4. However, I now see that I deviated slightly from these instructions, but when I change my settings file so they match exactly, new errors appear. When I change the 'ENGINE' to 'mysql.connector.django' as in the instructions, the error changes to, "ImportError: cannot import name 'BaseDatabaseFeatures'". — Terry Rozmus
– Terry Rozmus, Commented Aug 31, 2015 at 6:48
In fact it appears someone else has encountered my exact situation and posted in on Stackoverflow three months ago. stackoverflow.com/questions/30426512/… I see someone's response on this at the bottom, so I will look into that one. — Terry Rozmus
– Terry Rozmus, Commented Aug 31, 2015 at 6:53

Terry Rozmus · Accepted Answer · 2015-08-31 07:31:41Z

1

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 Aug 31, 2015 at 7:31

Terry Rozmus

3963 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Giles Thomas Over a year ago

PythonAnywhere dev here: thanks for the heads-up! We've fixed the help page.

user3282758 Over a year ago

@Terry Rozmus I am facing the same error with python 3.4 django 1.8 windows 8 mysql database. I have tried a lot of things. Also installed mysql-connector-python-2.1.3.tar.gz. Please help

Terry Rozmus Over a year ago

It's hard to know what issues you are facing unless you post the errors you are getting. In addition I am a Django beginner. I'm pretty good at trouble-shooting, but you probably need the help of someone more experienced. I suggest you post your query as a new stackoverflow question together with a detailed outline of your errors and what you have tried so you can get this help.

Ihor Konovalenko · Accepted Answer · 2021-11-18 10:13:58Z

0

i have try this,and works...

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

edited Nov 18, 2021 at 10:13

Ihor Konovalenko

1,4172 gold badges19 silver badges24 bronze badges

answered Nov 18, 2021 at 4:26

Sarmin Ikoni

1

1 Comment

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related