【问题】
具体问题:新建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"))
开发环境:
mysql5.5(版本: 5.5.24-CDB-2.0.0-log) django=='2.1' pymysql=='0.9.2'
【原因分析】
测试:更换mysql服务器,使用mysql(版本: 5.7.17-log),其他django,pymysql版本不变。正常同步a.无错 b.直接查表,表结构正确
从测试结果推测原因可能是跟mysql版本有关,因此需要知道具体执行的sql语句
1、从文档中
django官方原地址,了解
sqlmigrate auth 0001_initial 命令可以显示
auth下的
auth 0001_initial的原始sql语句。运行后
View Code
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 File tracking disabled 2 BEGIN; 3 -- 4 -- Create model Permission 5 -- 6 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); 7 -- 8 -- Create model Group 9 -- 10 CREATE TABLE `auth_group` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `name` varchar(80) NOT NULL UNIQUE); 11 CREATE TABLE `auth_group_permissions` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `group_id` integer NOT NULL, `permission_id` integer NOT NULL); 12 -- 13 -- Create model User 14 -- 15 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); 16 CREATE TABLE `auth_user_groups` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `user_id` integer NOT NULL, `group_id` integer NOT NULL); 17 CREATE TABLE `auth_user_user_permissions` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `user_id` integer NOT NULL, `permission_id` integer NOT NULL); 18 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`); 19 ALTER TABLE `auth_permission` ADD CONSTRAINT auth_permission_content_type_id_codename_01ab375a_uniq UNIQUE (`content_type_id`, `codename`); 20 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`); 21 ALTER TABLE `auth_group_permissions` ADD CONSTRAINT `auth_group_permissio_permission_id_84c5c92e_fk_auth_perm` FOREIGN KEY (`permission_id`) REFERENCES `auth_permission` (`id`); 22 ALTER TABLE `auth_group_permissions` ADD CONSTRAINT auth_group_permissions_group_id_permission_id_0cd325b0_uniq UNIQUE (`group_id`, `permission_id`); 23 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`); 24 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`); 25 ALTER TABLE `auth_user_groups` ADD CONSTRAINT auth_user_groups_user_id_group_id_94350c0c_uniq UNIQUE (`user_id`, `group_id`); 26 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`); 27 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`); 28 ALTER TABLE `auth_user_user_permissions` ADD CONSTRAINT auth_user_user_permissions_user_id_permission_id_14a6b632_uniq UNIQUE (`user_id`, `permission_id`); 29 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字段的格式也没有问题
看来得看django如何配置了
3、 搜索之后,这个区别发生在mysql5.6版本之后,
django在文档中也说明了这种问题。
django2.1的ChangeLog也说明了不在支持mysql5.5
【
方案】
指定django版本为2.0,migrate成功。
https://www.techiediaries.com/how-to-reset-migrations-in-django-17-18-19-and-110/https://www.kawabangga.com/posts/3013