django无法同步mysql数据库 Error:1064

迷南。 2022-04-25 08:32 290阅读 0赞

### 具体问题 ###

新建django工程，使用django的manage.py的 migrate命令进行更改。在初始化数据库表时，失败，错误信息为

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"))

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbGxva2FuZHk_size_16_color_FFFFFF_t_70][]

### 开发环境： ###

mysql5.5 
    django=='2.2.1' 
    mysqlclient=='1.4.2'

### 分析原因 ###

错误提示中 check the manual that corresponds to your MySQL server version，

意思是：检查与您的mysql服务器版本相对应的手册。

### 测试 ###

更换mysql服务器，使用mysql（5.6以上的版本），其他django，pymysql版本不变。正常同步a.无错 b.直接查表,表结构正确

从测试结果推测原因可能是跟mysql版本有关，因此需要知道具体执行的sql语句

File tracking disabled
    BEGIN;
    --
    -- Create model Permission
    --
    CREATE TABLE `auth_permission` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `name` varchar(50) NOT NULL, `content_type_id` integer NOT NULL, `codename` varchar(100) NOT NULL);
    --
    -- Create model Group
    --
    CREATE TABLE `auth_group` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `name` varchar(80) NOT NULL UNIQUE);
    CREATE TABLE `auth_group_permissions` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `group_id` integer NOT NULL, `permission_id` integer NOT NULL);
    --
    -- Create model User
    --
    CREATE TABLE `auth_user` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `password` varchar(128) NOT NULL, `last_login` datetime(6) NOT NULL, `is_superuser` bool NOT NULL, `username` varchar(30) NOT NULL UNIQUE, `first_name` varchar(30) NOT NULL, `last_name` varchar(30) NOT NULL, `email` varchar(75) NOT NULL, `is_staff` bool NOT NULL, `is_active` bool NOT NULL, `date_joined` datetime(6) NOT NULL);
    CREATE TABLE `auth_user_groups` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `user_id` integer NOT NULL, `group_id` integer NOT NULL);
    CREATE TABLE `auth_user_user_permissions` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `user_id` integer NOT NULL, `permission_id` integer NOT NULL);
    ALTER TABLE `auth_permission` ADD CONSTRAINT `auth_permission_content_type_id_2f476e4b_fk_django_co` FOREIGN KEY (`content_type_id`) REFERENCES `django_content_type` (`id`);
    ALTER TABLE `auth_permission` ADD CONSTRAINT auth_permission_content_type_id_codename_01ab375a_uniq UNIQUE (`content_type_id`, `codename`);
    ALTER TABLE `auth_group_permissions` ADD CONSTRAINT `auth_group_permissions_group_id_b120cbf9_fk_auth_group_id` FOREIGN KEY (`group_id`) REFERENCES `auth_group` (`id`);
    ALTER TABLE `auth_group_permissions` ADD CONSTRAINT `auth_group_permissio_permission_id_84c5c92e_fk_auth_perm` FOREIGN KEY (`permission_id`) REFERENCES `auth_permission` (`id`);
    ALTER TABLE `auth_group_permissions` ADD CONSTRAINT auth_group_permissions_group_id_permission_id_0cd325b0_uniq UNIQUE (`group_id`, `permission_id`);
    ALTER TABLE `auth_user_groups` ADD CONSTRAINT `auth_user_groups_user_id_6a12ed8b_fk_auth_user_id` FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`id`);
    ALTER TABLE `auth_user_groups` ADD CONSTRAINT `auth_user_groups_group_id_97559544_fk_auth_group_id` FOREIGN KEY (`group_id`) REFERENCES `auth_group` (`id`);
    ALTER TABLE `auth_user_groups` ADD CONSTRAINT auth_user_groups_user_id_group_id_94350c0c_uniq UNIQUE (`user_id`, `group_id`);
    ALTER TABLE `auth_user_user_permissions` ADD CONSTRAINT `auth_user_user_permissions_user_id_a95ead1b_fk_auth_user_id` FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`id`);
    ALTER TABLE `auth_user_user_permissions` ADD CONSTRAINT `auth_user_user_permi_permission_id_1fbb5f2c_fk_auth_perm` FOREIGN KEY (`permission_id`) REFERENCES `auth_permission` (`id`);
    ALTER TABLE `auth_user_user_permissions` ADD CONSTRAINT auth_user_user_permissions_user_id_permission_id_14a6b632_uniq UNIQUE (`user_id`, `permission_id`);
    COMMIT;

使用mysql命令行 逐条测试，发现下行指令报错，具体错误

1 mysql> CREATE TABLE `auth_user` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `password` varchar(128) NOT NULL, `last_login` datetime(6) NOT NULL, `is_superuser` bool NOT NULL, `username` varchar(30) NOT NULL UNIQUE, `first_name` varchar(30) NOT NULL, `last_name` varchar(30) NOT NULL, `email` varchar(75) NOT NULL, `is_staff` bool NOT NULL, `is_active` bool NOT NULL, `date_joined` datetime(6) NOT NULL);
    2 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, `is_superuser` bool NOT NULL, `username` varchar(30) NOT NULL UNIQ' at line 1

对比django的报错信息，还有有区别的。但基本上可以获知是 **(6) NOT NULL**这部分指令的问题。

2、修改**datetime(6) NOT NULL**为**datetime NOT NULL**后，运行

1 mysql> CREATE TABLE `auth_user` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `password` varchar(128) NOT NULL, `last_login` datetime NOT NULL, `is_superuser` bool NOT NULL, `username` varchar(30) NOT NULL UNIQUE, `first_name` varchar(30) NOT NULL, `last_name` varchar(30) NOT NULL, `email` varchar(75) NOT NULL, `is_staff` bool NOT NULL, `is_active` bool NOT NULL, `date_joined` datetime NOT NULL);
    2 Query OK, 0 rows affected

OK，没问题，查看数据库，表也存在了。last_login和date_joined字段的格式也没有问题

3、 搜索之后，这个区别发生在mysql5.6版本之后，[django在文档][django]中也说明了这种问题。[django2.1的ChangeLog][django2.1_ChangeLog]也说明了不在支持mysql5.5

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbGxva2FuZHk_size_16_color_FFFFFF_t_70]: /images/20220125/3c242e5c1cba405bae29f06da571c2df.png
[django]: https://docs.djangoproject.com/en/2.1/ref/databases/#fractional-seconds-support-for-time-and-datetime-fields
[django2.1_ChangeLog]: https://docs.djangoproject.com/en/2.1/releases/2.1/#dropped-support-for-mysql-5-5