Django migrate error _mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax

Question

TL;DR: python versions conflicts, i think that the python i downloaded and compiled (3.6) can't use this package (libmysqlclient-dev) to make migrations to mysql. only the system's default python (3.4) can.

my ubuntu server came with python 3.4, all of my django work and other work depend on 3.6. i have learned that upgrading system python is a bad idea, so i compiled python 3.6 (with altinstall).

when i ran python3.6 manage.py migrate it gave me this 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 '(6) NOT NULL)' at line 1")

i tried virtual environment and normal python 3.6, both gave the same error, and i made sure that libmysqlclient-dev and mysqlclient are installed.

as this answer suggests, the problem is with libmysqlclient-dev because it's installed via apt-get not pip so i guess it's only compatible with the default python (3.4 that came with the system) or my compiled python 3.6 isn't allowed to use it, because when i made a dummy django project with python3.4 (system's default) and attempted python3.6 manage.py migrate on the same mysql database with the same user, it worked!

AGAIN: my problem is that the manually compiled python 3.6 can't use libmysqlclient-dev that has been installed by apt-get, only 3.4 can

reference: Django MySQL error on migrate

UPDATE i came up with a work around but it's not efficient. i downgraded Django to 2.0.9 and it (python manage.py migrate) worked. but this problem could appear again with a different package.

Answer 1

Django 2.1.* requires MySQL 5.6 or higher. It broke the compatibility by mapping DateTimeField to datetime(6).

You can patch Django MySQL data type mapper. Put this to the top of your settings.py

from django.db.backends.mysql.base import DatabaseWrapper
DatabaseWrapper.data_types['DateTimeField'] = 'datetime' # fix for MySQL 5.5

Answer 2

There is a major difference in the support for django 2.0.* vs django 2.1.*

AS per Django 2.0.* MySQL Notes

Django supports MySQL 5.5 and higher.

As per Django 2.1.* MySQL Notes

Django supports MySQL 5.6 and higher.

Check you MySQL version with: mysql -V and use the correct django version.