navicat导入数据库脚本文件报错集合
环境：
数据是由用户test1导出，对应的表空间是test1
导入时是用户test2, 对应的表空间是test2
操作：
导入时如果目标环境中有test1表空间就会导入到test1表空间中
而如果目标环境没有test1表空间，就会导入test2用户的默认表空间
默认表空间查看方法：select username,default_tablespace from dba_users where username = 'SCOTT';
[Err] 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 'default
-- Table structure for t_contractsuppl' at line 1
[Err] INSERT INTO t_contractmoney VALUES ('ff8080816c854db2016c88b838db0455', NULL, 'ff8080816c854db2016c8896b2c00375', 'ff8080816c000b87016c0302634904cb', NULL, 1125.00, 0, 2019, '0', 'ff8080816b094713016b0bbe0a3e0076', '鄂尔多斯市文化和旅游局经办', '2019-08-13 10:04:30', 'ff8080816b094713016b0bbe0a3e0076', '鄂尔多斯市文化和旅游局经办', '2019-08-13 10:04:30', NULL);
[Msg] Finished - Unsuccessfully
方案：语句错误，检查报错位置的语法
1.通常是格式，例如是',;等的原因
2.可能是oracle和mysql之间的数据相互导，导致字段类型不支持
/-------------==
[Err] 1171 - All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead
[Err] INSERT INTO t_contractsupplier VALUES ('ff8080816c4fd3c5016c65c064c6506d', 'ff8080816c4fd3c5016c65c014c35061', NULL, '鄂尔多斯市智达电子有限责任公司', '鄂尔多斯银行股份有限公司东胜支行', '047701012000012706');
INSERT INTO t_contractsupplier VALU
[Msg] Finished - Unsuccessfully
方案：导入和导出的用户不同，导致函数调取时出错
1.该错出现的原因是不通版本之间相互导入数据文件导致的
/-------------------------------==-
[Err] 1217 - Cannot delete or update a parent row: a foreign key constraint fails
[Err] INSERT INTO t_contractmoney VALUES
--('ff8080816c4fd3c5016c60f902d91a35', NULL, 'ff8080816c4fd3c5016c60f8e1821a2d', 'ff8080816c4b766e016c4c64f44a5938', NULL, 49750.00, 0, 2019, '0', 'ff8080816ac9ec56016b031357010243', '鄂尔多斯市人民代表大会常务委员会办公室经办', '2019-08-05 16:50:28', 'ff8080816ac9ec56016b031357010243', '鄂尔多斯市人民代表大会常务委员会办公室经办', '2019-08-05 16:50:28', NULL);
[Msg] Finished - Unsuccessfully
方案：外键约束导致删除不了数据
1.脚本文件第一行
SET FOREIGN_KEY_CHECKS = 0;
[Err] 1062 - Duplicate entry 'ff8080816b8acbdf016b91b8dbae1b9e' for key 'PRIMARY'
[Err]INSERT INTO t_contractmoney VALUES ('ff8080816b8acbdf016b91b8dbae1b9e');
[Msg] Finished - Unsuccessfully--==
/----==-----------------------------
[Err] 1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation
[Err] INSERT INTO t_userpost VALUES ('12', 'wsd_gly');
方案：访问拒绝，更改为导出的用户
导入数据库时报错：
[ERR] 1071 - Specified key was too long; max key length is 767 bytes
解决方案为：
show variables like '%innodb_large_prefix%';
show variables like '%innodb_file_format%';
set global innodb_large_prefix=on;
set global innodb_file_format=Barracuda;
/----------=
[ERR] 1349 - View's SELECT contains a subquery in the FROM clause
mysql5.6的版本有这个报错，mysql5.0和5.7以上的版本都没有这个错误
可以考虑升级版本，没有好的解决办法，如果有请告诉我谢谢谢
使用脚本自动备份数据库时出现错误3167
mysql数据库使用mysqldump报错3167
The database to be backed up is named: information_schema
mysqldump: Couldn't execute 'SELECT /!40001 SQL_NO_CACHE / FROM GLOBAL_STATUS': The 'INFORMATION_SCHEMA.GLOBAL_STATUS' feature is disabled; see the documentation for 'show_compatibility_56' (3167)
The database to be backed up is named: performance_schema
select from information_schema.global_status ;
3167 - The 'INFORMATION_SCHEMA.GLOBAL_STATUS' feature is disabled; see the documentation for 'show_compatibility_56'
解决办法：
需要设置开启状态：
用管理员执行如下命令：
set global show_compatibility_56=on;
查看是否开启：
show variables like '%show_compatibility_56%';
再次查看：
select * from information_schema.global_status limit 3;
ORA开头的是oracle数据库报错
-------==
[Err] ORA-00942: table or view does not exist
[Err] DROP TABLE "PC_CORE"."B_FB_E_BLOCK_RECORD"
[Msg] Finished - Unsuccessfully
缺少判断语句出现表不存在的情况就会报错
用DROP TABLE IF EXISTS 替换 DROP TABLE
[Err] ORA-00955: name is already used by an existing object
说明表已经有了。。。
更多相关内容：
Navicat如何导入sql脚本文件
如何解决navicat导入sql文件出错的问题
