mysqldump: Error: Query execution was interrupted, maximum statement execution time exceeded when trying to dump tablespaces mysqldump: Error 3024: Query execution was interrupted, maximum statement execution time exceeded when dumping table `$tb_name` at row: xxxx
版本： MySQL 5.7.8+ 原因： max_execution_time过小 处理思路： ① 通过hints，增大N值（文档说，在hints用法中，将N改为0为无限制，但我测下来不生效，可设置成一个较大值如999999解决） SELECT /*+ MAX_EXECUTION_TIME(N) */ * FROM t1 LIMIT 100000; ② 修改max_execution_time值，将该值设置为较大一个值，或设置为0（不限制） 附录： 该参数5.7.8被添加，单位为ms，动态参数，默认为0，设置为0时意味着SELECT超时不被设置（不限制超时时间）。不作用于存储过程中的SELECT语句，并且只作用于只读的SELECT，如INSERT ... SELECT ... 是不被作用的。 for more information:
mysqldump: Couldnt execute SHOW FIELDS FROM `$view_name`: View $db_name.$view_name references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356)
mysqldump: Couldnt execute SHOW FIELDS FROM `$view_name`: The user specified as a definer ($user@$host) does not exist (1449) mysqldump: Couldnt execute show table status like $view_name: SELECT command denied to user @% for column $col_name in table $tb_name (1143)
原因二（MySQL 5.5及以下版本可能出现）： 表损坏导致该表找不到（InnoDB）。frm和ibd文件都在，但无法SHOW CREATE TABLE xxx/G error log一则： 170820 17:43:17 [Note] Event Scheduler: scheduler thread started with id 1 170820 17:44:48 InnoDB: error: space object of table '$db_name/$tb_name', InnoDB: space id 4335 did not exist in memory. Retrying an open. 170820 17:44:48 InnoDB: Error: tablespace id and flags in file './$db_name/$tb_name.ibd' are 0 and 0, but in the InnoDB InnoDB: data dictionary they are 4335 and 0. InnoDB: Have you moved InnoDB .ibd files around without using the InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE? InnoDB: Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html InnoDB: for how to resolve the issue. 170820 17:44:48 InnoDB: cannot calculate statistics for table $db_name/$tb_name InnoDB: because the .ibd file is missing. For help, please refer to InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html 170820 17:44:48 [ERROR] MySQL is trying to open a table handle but the .ibd file for table $db_name/$tb_name does not exist. Have you deleted the .ibd file from the database directory under the MySQL datadir, or have you used DISCARD TABLESPACE? See http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html how you can resolve the problem. 处理思路： 从完整备份+binlog还原，对于有主或从的实例，可通过物理备份还原。
mysqldump: Error 2020: Got packet bigger than max_allowed_packet bytes when dumping table `$tb_name` at row: xxxx
mysqldump: Error 1412: Table definition has changed, please retry transaction when dumping table `$tb_name` at row: 0
原因： 在备份该表时，表定义被修改。FLUSH TABLE WITH READ LOCK只保证数据一致性，并不保证schema不被修改。 处理思路： 备份时期不做DDL操作。 复现一： ① session1> CREATE TABLE a (id int) ENGINE=InnoDB; ② session2> START TRANSACTION WITH CONSISTENT SNAPSHOT; ③ session1> ALTER TABLE a ADD COLUMN name varchar(32); ④ session2> SELECT * FROM a; ERROR 1412 (HY000): Table definition has changed, please retry transaction p.s. 如果③和④调换顺序，则ALTER TABLE无法成功，则会等待MDL。 复现二： ① session1> START TRANSACTION WITH CONSISTENT SNAPSHOT; ② session2> CREATE TABLE b (id int) ENGINE=InnoDB; ③ session1> SELECT * FROM b; ERROR 1412 (HY000): Table definition has changed, please retry transaction
mysqldump: Couldnt execute show create table `$tb_name`: Unable to open underlying table which is differently defined or of non-MyISAM type or doesnt exist (1168)
原因： 出现在表引擎为MERGE时，备份到该表时，发现该表定义存在问题。可能merge的表不存在，或者该表合并的基表包含非MyISAM引擎的表。 处理思路： 删除或者重建该MERGE表。 复现一（merge表中定义包含了非MyISAM表）： CREATE TABLE t1(id int) ENGINE=InnoDB; CREATE TABLE t2(id int) ENGINE=MyISAM; CREATE TABLE merge_t(id int)ENGINE=MERGE UNION=(t1, t2); SELECT * FROM merge_t; ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 复现二（表不存在）： CREATE TABLE t1(id int) ENGINE=MyISAM; CREATE TABLE t2(id int) ENGINE=MyISAM; CREATE TABLE merge_t(id int)ENGINE=MERGE UNION=(t1, t2); SELECT * FROM merge_t; Empty set (0.00 sec) -- 正常返回 DROP TABLE t1; SELECT * FROM merge_t; ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 附录： 通过check table merge_t可以检查是哪张表有问题，如此处是t1： [15:20:12] root@localhost [test]> check table merge_t/G *************************** 1. row *************************** Table: test.merge_t Op: check Msg_type: Error Msg_text: Table 'test.t1' is differently defined or of non-MyISAM type or doesn't exist *************************** 2. row *************************** Table: test.merge_t Op: check Msg_type: Error Msg_text: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist *************************** 3. row *************************** Table: test.merge_t Op: check Msg_type: error Msg_text: Corrupt 3 rows in set (0.00 sec) 通过cat表MGR定义结构文件可以检查MERGE表的基表： [root@host test]# pwd /data/mysql-data/mysql57/data/test [root@host test]# cat merge_t.MRG t1 t2
mysqldump: Couldnt execute show create table `$tb_name`: Table ./$db_name/$tb_name is marked as crashed and last (automatic?) repair failed (144) mysqldump: Couldnt execute show create table `$tb_name`: Table ./$db_name/$tb_name is marked as crashed and should be repaired (145) mysqldump: Error 1194: Table throne_tower is marked as crashed and should be repaired when dumping table `$tb_name` at row: xxxxx
mysqldump: Error: Got error 28 from storage engine when trying to dump tablespaces mysqldump: Couldnt execute show fields from `$tb_name`: Got error 28 from storage engine (1030)
mysqldump: Couldn't execute 'SHOW SLAVE STATUS': Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation (1227)
