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.

Have you tried creating a virtualenvironment and installing mysqlclient there? — Athena, Oct 10 '18 at 00:12
What version of mysql are you using? It seems highly likely that the version you have is no longer supported by Django. — solarissmoke, Oct 10 '18 at 04:08
Server version: 5.5.61-0ubuntu0.14.04.1 mysql --version: mysql Ver 14.14 Distrib 5.5.61, for debian-linux-gnu (x86_64) using readline 6.3 — McLovin, Oct 10 '18 at 11:44

score 16 · Answer 1 · answered Dec 10 '18 at 10:43

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

score 4 · Answer 2 · answered Nov 14 '18 at 08:47

4

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.

answered Nov 14 '18 at 08:47

tread

10,133
17
95
170

`pip install django==2.0.*` – tread Jan 08 '19 at 12:50
This answer just solved my problem: https://stackoverflow.com/questions/54931065/django-mysql-error-when-running-tests-in-a-virtualenv?noredirect=1#comment96626819_54931065 – Nikolay Shindarov Feb 28 '19 at 18:32

score 0 · Answer 3 · edited Nov 22 '19 at 15:18

0

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

Solved my problem!

Thanks Luka Zakrajšek

edited Nov 22 '19 at 15:18

lczapski

4,026
3
16
32

answered Nov 22 '19 at 14:09

Rogério Martins

1

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

3 Answers3

Linked