Mysqldump failure cases and solutions:
1.mysqldump: Error 2020: Got packet bigger than ‘max_allowed_packet’ bytes when dumping table `blt_Bulletinannex ` at row: 626
error reporting condition: generally, there are blob, text and other fields, and a single record exceeds the default 24m
solution: increase mysqldump by max_allow_Packet parameter. It is invalid to modify this parameter on the server
2. Mysqldump: couldn’t execute ‘show table status like’ members\_ban\_user\_view”: SELECT command denied to user ”@’%’ for column ‘user_id’ in table ‘members_ban_Log ‘(1143)
error reporting condition: the permission given to the account of the corresponding view is insufficient; Or the user does not exist
solution: you need to define the create of the account in the view_view_Priv and show_view_Priv authority; Or add corresponding users and permissions; Delete the view
3. Mysqldump: couldn’t execute ‘show create table ` InnoDB_index_stats`’: Table ‘MySQL.innodb_index_Stats’ doesn’t exist (1146)
error condition: mysql5.6, the system table is damaged, and the table is an InnoDB engine
solution: physically delete the frm file and IBD file of the table, find the definition SQL of the system table, and rebuild the system table
4. Mysqldump: couldn’t execute ‘show create table ` view_all_packages`’: View ‘locker.view_all_Packages’ references invalid table (s) or column (s) or function (s) or definer/invoker of view pack rights to use them (1356)
error condition: illegal view definition
solution: delete or modify the problematic view definition statement
5. Mysqldump: got error: 1045: access denied for user ‘ucloudbackup’ @ ‘10.10.1.242’ (using password: Yes) when t Rying to connect
error conditions: unable to connect, problems with password, account, host and port
solution: first ensure that MySQL can connect normally
6. Mysqldump: couldn’t execute ‘show create table ` userarena log’: table ‘./tank_11/userarena log ‘is marked as crashed and should be repaired (145)
error reporting condition: MyISAM table is damaged
solution: repair table XXX repair the damaged table. You’d better mysqlcheck all tables
7. Mysqldump: couldn’t execute’ show fields from ` TB_CROWDFUNDING_PROJECT`’: Incorrect key file for table ‘ql-5.5/14310da6-644a-472a-b170-0e7e75cfda87/tmp/#sql_32606_0.MYI’; Try to repair it (126)
error reporting condition: the TMP space is insufficient during the use of the temporary table, Cause damage to MyISAM temporary table
solution: increase disk space
8. Mysqldump: couldn’t execute ‘show function status where DB =’ analysis’ ‘: cannot load from mysql.proc. The table is potentially corrupted (1548)
error condition: upgrade causes
solution: run mysql_Upgrade update dB, or update the mysql.proc table structure of the corresponding version
5.1 execute
alter table mysql.proc modify column ` comment` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL AFTER `sql_mode`;
5.5 execute
alter table mysql.proc modify column ` comment` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL AFTER `sql_mode`;
9. Mysqldump: couldn’t execute ‘show events’: cannot proceed because system tables used by event scheduler were found damaged at server start (1577)
error reason: unreasonable upgrade of MySQL version leads to
solution: MySQL first_Upgrade, no, restart DB again (not sure)
10. Mysqldump: couldn’t execute ‘show function status where DB =’ MySQL ”: you have an error in your SQL syntax; Check the manual that responses to your MySQL server version for the right syntax to use near ” at line 1 (1064)
error reason: select * from information_Schema.routes limit 1 reports the same error
looking at the mysql.proc table, it is found that there is a problem with the definition of a function or stored procedure. For example, the nonexistent dB or user appears in the definition. It is speculated that the – R parameter is not added during backup,
solution: try drop syntax to delete the function or stored procedure records with problems defined in mysql.proc. If not, directly delete from
11. Mysqldump: error 2013: lost connection to MySQL server during query when dumping table ` vitality_Flow ` at row: 31961089
error reason: 1 this table is a partition table 2 the table is InnoDB, and there are a large number of blob text and other fields. 3 upload NFS or compress while backup
solution: for the reasons of 1 and 3, the net should be increased_write_Timeout parameter; For the reason of 2, it is necessary to increase max_allow_packet；
12.mysqldump: Couldn’t execute ‘SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = ‘DATAFILE’ AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN (‘15616156′,’mysql’,’test’,’wx00′,’wx01′,’wx02′,’wx03′,’wxid’)) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_Name ‘: lost connection to MySQL server during query (2013)
error reason: oom due to insufficient memory during backup
solution: increase memory
13.mysqldump: Couldnt execute show create table `shop_his_9`: Deadlock found when trying to get lock; try restarting transaction (1213) fails
Error reporting reason: deadlock occurred during mysqldump
Solution: try again
14.mysqldump: Got error: 1049: Unknown database cfcara when selecting the database fails
Reason for error reporting: it is caused by case sensitivity problem of arbitrary modification
Solution: solve the case problem first
mysqldump: Couldn’t execute ‘STOP SLAVE SQL_THREAD’: Access denied for user ‘root’@’172.19.%.%’ (using password: NO) (1045)
Reason for error reporting: backup from the database. The backup account has insufficient permissions and cannot log in
| mysqldump: Couldnt execute show create table `sk_order_38`: Unable to open underlying table which is differently defined or of non-MyISAM type or doesnt exist (1168) fails |
Error reason: it is caused by an error in the definition of MRG table
Solution: delete this table
Original blog:
http://blog.csdn.net/cug_jiang126com/article/details/49359699
