Unable to create the django tables with DateTime Field with size

Question

I was migrating my Django app from local system to production server.

Configurations on local server are as below:

Django2.2  
MySQL 5.7.27-0ubuntu0.16.04.1
mysqlclient-1.4.2.post1 
sqlparse-0.3.0

On production server, configurations are as below:

Django2.2  
MySQL 5.5.53
mysqlclient-1.4.2.post1 
sqlparse-0.3.0

Only difference between local and production configurations is of MySQL server version.

There is a model with below code:

class Tracking404Model(models.Model):
    url = models.CharField(max_length=512, null=False, blank=False)
    timestamp = models.DateTimeField(null=False, blank=False)

After running migration, I ran the commnd python manage.py sqlmigrate app-name migration-file-number to check MySQL syntax generated, which is as below:

CREATE TABLE `404_tracking` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, 
`url` varchar(512) NOT NULL, `timestamp` datetime(6) NOT NULL);

Show create table statement output on Local:

mysql> show create table 404_tracking\G
*************************** 1. row ***************************
       Table: 404_tracking
Create Table: CREATE TABLE `404_tracking` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `url` varchar(512) NOT NULL,
  `timestamp` datetime(6) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Migrations ran just fine on local, tables were created and I was able to perform all mysql operations. Basically everything was just fine.

When running migrations on production, I am getting below error:

raise MigrationSchemaMissing("Unable to create the django_migrations table (%s)" % exc)
django.db.migrations.exceptions.MigrationSchemaMissing: Unable to create the django_migrations table ((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"))

Which means Django 2.2 and mysqlclient 1.4 are trying to create DateTime field with size 6 which is causing an issue on production.

When I tried to execute the SQL query directly in terminal, it shows syntax error:

Although error reported is about migration table, but this looks like it is about DateTime field. There might be DateTime field in migration table as well and it is causing an issue when creating migration table (before app tables) on production.

Also, I created same model on production server few months ago in another Django app, with below configurations:

Django 2.0.6  
MySQL 5.5.53
mysqlclient-1.3.12
No sqlparse module

and it was created just fine. Show create table statement is:

mysql> show create table blog_404_tracking\G
*************************** 1. row ***************************
       Table: blog_404_tracking
Create Table: CREATE TABLE `blog_404_tracking` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `url` varchar(512) NOT NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=68040 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

which means with Django 2.0 and mysqlclient 1.3.12 datetime field was created without length/size.

How can I solve this issue?

Answer 1

Update your production mysql server version :

5.5 to 5.7