navicat導(dǎo)入數(shù)據(jù)庫腳本文件報錯集合

環(huán)境：
數(shù)據(jù)是由用戶test1導(dǎo)出，對應(yīng)的表空間是test1
導(dǎo)入時是用戶test2, 對應(yīng)的表空間是test2
操作：
導(dǎo)入時如果目標(biāo)環(huán)境中有test1表空間就會導(dǎo)入到test1表空間中
而如果目標(biāo)環(huán)境沒有test1表空間，就會導(dǎo)入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', '鄂爾多斯市文化和旅游局經(jīng)辦', '2019-08-13 10:04:30', 'ff8080816b094713016b0bbe0a3e0076', '鄂爾多斯市文化和旅游局經(jīng)辦', '2019-08-13 10:04:30', NULL);
[Msg] Finished - Unsuccessfully
方案：語句錯誤，檢查報錯位置的語法
1.通常是格式，例如是',;等的原因
2.可能是oracle和mysql之間的數(shù)據(jù)相互導(dǎo)，導(dǎo)致字段類型不支持
/-------------==

[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, '鄂爾多斯市智達電子有限責(zé)任公司', '鄂爾多斯銀行股份有限公司東勝支行', '047701012000012706');
INSERT INTO t_contractsupplier VALU
[Msg] Finished - Unsuccessfully
方案：導(dǎo)入和導(dǎo)出的用戶不同，導(dǎo)致函數(shù)調(diào)取時出錯
1.該錯出現(xiàn)的原因是不通版本之間相互導(dǎo)入數(shù)據(jù)文件導(dǎo)致的
/-------------------------------==-

[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', '鄂爾多斯市人民代表大會常務(wù)委員會辦公室經(jīng)辦', '2019-08-05 16:50:28', 'ff8080816ac9ec56016b031357010243', '鄂爾多斯市人民代表大會常務(wù)委員會辦公室經(jīng)辦', '2019-08-05 16:50:28', NULL);
[Msg] Finished - Unsuccessfully
方案：外鍵約束導(dǎo)致刪除不了數(shù)據(jù)
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');
方案：訪問拒絕，更改為導(dǎo)出的用戶

導(dǎo)入數(shù)據(jù)庫時報錯：
[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以上的版本都沒有這個錯誤
可以考慮升級版本，沒有好的解決辦法，如果有請告訴我謝謝謝

使用腳本自動備份數(shù)據(jù)庫時出現(xiàn)錯誤3167
mysql數(shù)據(jù)庫使用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'
解決辦法：
需要設(shè)置開啟狀態(tài)：
用管理員執(zhí)行如下命令：
set global show_compatibility_56=on;
查看是否開啟：
show variables like '%show_compatibility_56%';
再次查看：
select * from information_schema.global_status limit 3;

ORA開頭的是oracle數(shù)據(jù)庫報錯
-------==
[Err] ORA-00942: table or view does not exist
[Err] DROP TABLE "PC_CORE"."B_FB_E_BLOCK_RECORD"
[Msg] Finished - Unsuccessfully
缺少判斷語句出現(xiàn)表不存在的情況就會報錯
用DROP TABLE IF EXISTS 替換 DROP TABLE

[Err] ORA-00955: name is already used by an existing object
說明表已經(jīng)有了。。。

更多相關(guān)內(nèi)容：

Navicat如何導(dǎo)入sql腳本文件

如何解決navicat導(dǎo)入sql文件出錯的問題