Shulou(Shulou.com)06/01 Report--

0 mysqldump: Error: Query execution was interrupted, maximum statement execution time exceeded when trying to dump tablespaces

0 mysqldump: Error 3024: Query execution was interrupted, maximum statement execution time exceeded when dumping table `$ tb_ name` at row: xxxx

Version:

MySQL 5.7.8 +

Reason:

Max_execution_time is too small

How to deal with it:

① increases the N value through hints (the document says that in hints usage, changing N to 0 is unlimited, but my test does not take effect, and can be set to a larger value such as 999999.)

SELECT / * + MAX_EXECUTION_TIME (N) * / * FROM T1 LIMIT 100000

② modifies the max_execution_ time value to set it to a larger value, or to 0 (unlimited)

Appendix:

The parameter 5.7.8 is added in ms, and the dynamic parameter defaults to 0. Setting it to 0 means that the SELECT timeout is not set (there is no limit on the timeout). Does not work on SELECT statements in stored procedures, and only works on read-only SELECT, such as INSERT. SELECT... It doesn't work.

For more information:

Http://blog.itpub.net/29773961/viewspace-2150443/

0 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)

Reason:

The view refers to an invalid table, column, function, or definer.

How to deal with it:

According to the error message, you can enter db, execute SHOW CREATE VIEW $view_name\ G, view the definition of the view, and check whether the base table, column, or related function of the view has relevant permissions. Consider rebuilding or deleting the view.

0 mysqldump: Couldnt execute show create table `$ view_ name`: Illegal mix of collations for operation UNION (1271)

Reason:

There is an illegal collation combination when using UNION when creating a view.

How to deal with it:

Check the view definition, check the character set, and consider rebuilding or deleting the view.

0 mysqldump: Couldnt execute SHOW FIELDS FROM `$ view_ name`: The user specified as a definer ($user@$host) does not exist (1449)

0 mysqldump: Couldnt execute show table status like $view_name: SELECT command denied to user @% for column $col_name in table $tb_name (1143)

Reason:

The definer of the view $user@$host does not exist.

How to deal with it:

Check the mysql.user table to confirm that the user exists, and consider rebuilding or deleting the view.

0 Error: Couldnt read status information for table Income_config () mysqldump: Couldnt execute show create table `Tser_ Table`: Table $db_name.test_table doesnt exist (1146)

0 mysqldump: Got error: 1049: Unknown database $db_name when selecting the database

Reason 1:

Set from 0 to 1 in lower_case_table_names, causing some library tables that originally contained uppercase letters to be "not found".

How to deal with it:

Set lower_case_table_names back to 0.

If you must set lower_case_table_names to 1, first set it to 0, and change the library table containing uppercase letters to lowercase, and then set it to 1.

Reason 2 (MySQL 5.5 and below may appear):

Table corruption caused the table not to be found (InnoDB). Both frm and ibd files are there, but cannot SHOW CREATE TABLE xxx\ G

One item of 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. How to deal with it:

Restore from full backup + binlog, or through physical backup for instances with a master or slave.

0 mysqldump: Error 2020: Got packet bigger than max_allowed_packet bytes when dumping table `$ tb_ name` at row: xxxx

Reason:

The default max_allowed_packet is too small

How to deal with it:

Increase the size of max_allowed_packet in mysqldump, such as mysqldump-- max-allowed-packet=268435456

0 mysqldump: Error 1412: Table definition has changed, please retry transaction when dumping table `$ tb_ name` at row: 0

Reason:

When the table is backed up, the table definition is modified. FLUSH TABLE WITH READ LOCK only guarantees data consistency and does not guarantee that schema will not be modified.

How to deal with it:

No DDL operation is performed during the backup.

Repeat one:

① session1 > CREATE TABLE a (id int) ENGINE=InnoDB

② session2 > START TRANSACTION WITH CONSISTENT SNAPSHOT

③ session1 > ALTER TABLE an ADD COLUMN name varchar (32)

④ session2 > SELECT * FROM a

ERROR 1412 (HY000): Table definition has changed, please retry transactionp.s. If ③ and ④ swap the order, then ALTER TABLE cannot succeed and waits for MDL.

Repeat II:

① 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

0 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)

Reason:

When the table engine is MERGE, there is a problem with the table definition when backing up to the table. Maybe the table for merge does not exist, or the base table of the table merge contains tables that are not MyISAM engines.

How to deal with it:

Delete or rebuild the MERGE table.

Repeat one (the definition of the merge table contains a non-MyISAM table):

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 repeat II (table does not 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)-returns normally

DROP TABLE t1

SELECT * FROM merge_t

ERROR 1168 (HY000): appendix to Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist:

Through check table merge_t, you can check which table has a problem, as here is 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) through the cat MGR definition structure file, you can check the base table of the MERGE table:

[root@host test] # pwd

/ data/mysql-data/mysql57/data/test

[root@host test] # cat merge_t.MRG

T1

T2

0 mysqldump: Couldnt execute show create table `$ tb_ name`: Table. / $db_name/$tb_name is marked as crashed and last (automatic?) Repair failed (144)

0 mysqldump: Couldnt execute show create table `$ tb_ name`: Table. / $db_name/$tb_name is marked as crashed and should be repaired

0 mysqldump: Error 1194: Table throne_tower is marked as crashed and should be repaired when dumping table `$ tb_ name` at row: xxxxx

Reason:

Mysqldump in the pull table definition Times error, the table is damaged.

How to deal with it:

The corruption occurs in a non-transactional table such as MyISAM and can be repaired through mysqlcheck or repair table.

0 mysqldump: Couldnt execute SHOW FUNCTION STATUS WHERE Db = $db_name: Cannot load from mysql.$tb_name. The table is probably corrupted (1728)

Reason:

The dictionary table is incorrect, either because the table itself is damaged, or because another version of mysql schema is imported to cover the dictionary table.

How to deal with it:

Repair table repair, if it is still useless, you can try mysql_upgrade to repair, or find the corresponding version of mysql_system_tables_fix.sql to import.

0 mysqldump: Couldnt execute show events: Cannot proceed because system tables used by Event Scheduler were found damaged at server start (1577)

Reason:

The dictionary table is incorrect, and it is most likely that the dictionary table was covered by importing other versions of mysql schema.

How to deal with it:

Try mysql_upgrade to fix, or find the corresponding version of mysql_system_tables_fix.sql to import. This error may restart the instance after the upgrade operation.

0 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)

Reason:

@ @ tmpdir is full.

How to deal with it:

Clear @ @ tmpdir and check the specific directory through SELECT @ @ tmpdir;.

0 mysqldump: Lost connection to MySQL server during query (2013)

ERROR 2002 (HY000): Can't connect to local MySQL server through socket' @ @ socket'

Reason:

Mysqld is closed during mysqldump execution.

How to deal with it:

Check the reason why mysqld is shut down. The common reason is the occurrence of OOM.

0 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)

Reason:

Mysqldump adds the-dump-slave parameter and lacks SUPER or REPLICATION CLIENT to execute SHOW SLAVE STATUS.

How to deal with it:

Check the user rights of mysqldump.

0 mysqldump: Couldn't execute 'STOP SLAVE SQL_THREAD': Access denied for user' dump'@'localhost' (using password: YES) (1045)

Reason:

Mysqldump adds the-- dump-slave parameter, and STOP SLAVE SQL_THREAD is used for lack of SUPER permission.

How to deal with it:

Check the user rights of mysqldump.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.