Migrating MySQL DB version 5.6 to 5.5 (Django) structure dump import fails

Question

Locally I was working on Django project with MySQL version 5.6 I am trying to host a DB on Azure and unfortunately ClearDB support 5.5 and not yet 5.6.

I am migrating the data using the export/import functionality on MySQL Workbench.

I dump the structure of the database first and that fails in syntax not sure why.

02:09:05 Restoring C:\Users\Saher\Documents\dumps\Dumpauthmodels.sql
Running: mysql.exe --defaults-file="c:\users\saher\appdata\local\temp\tmpb3d1gg.cnf"  --protocol=tcp --host=us-cdbr-azure-west-b.cleardb.com --user=b42d1da1703a84 --port=3306 --default-character-set=utf8 --comments --database=mytravelsdb < "C:\\Users\\Saher\\Documents\\dumps\\Dumpauthmodels.sql"
ERROR 1064 (42000) at line 79: 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),
  `is_superuser` tinyint(1) NOT NULL,
  `username` varchar(30) COLLATE utf8' at line 4

Operation failed with exitcode 1
02:09:07 Import of C:\Users\Saher\Documents\dumps\Dumpauthmodels.sql has finished with 1 errors

This is simply part of the Django table model.

I know I can simply ignore these tables and just recreate with Django migrata/syncdb commands, but I am curious what Create table syntax changed between 5.5 and 5.6 causing syntax error.

Here is the table structure in the dump from the localhost MySQL 5.6

--
-- Table structure for table `auth_user`
--

DROP TABLE IF EXISTS `auth_user`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `auth_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `password` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  `last_login` datetime(6),
  `is_superuser` tinyint(1) NOT NULL,
  `username` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `first_name` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `last_name` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(254) COLLATE utf8_unicode_ci,
  `is_staff` tinyint(1) NOT NULL,
  `is_active` tinyint(1) NOT NULL,
  `date_joined` datetime(6) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

score 0 · Answer 1 · answered Oct 21 '16 at 09:36

The error message is deceiving I had to post the query in the editor to view the error. It seems the MySQL 5.6 has Automatic Initialization and Updating for TIMESTAMP and DATETIME which is supported if you use such syntax

CREATE TABLE t1 (
  ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
);

This is not supported in 5.5 so one needs to get rid of the parentheses after each datetime and timestamp.

Import worked now.

Migrating MySQL DB version 5.6 to 5.5 (Django) structure dump import fails

1 Answers1