《那些年,我在乙方的日子 -- 神谕篇NO1》

某个夏日的午后,窗外知了在大声鸣叫。而我却在睡梦中跟基友一起吃鸡,正准备抢空投时 。手机突然铃声响起,惊醒后一看是领导电话,一下子回到了现实中。心想又得去公司吃 "机" 了。

领导:“神谕,工单积压很多了,你过来帮忙处理下。午睡啊?打车过来,公司全额报销”。

1、max_execution_time 过小导致

mysqldump: Error: Query execution was interrupted, maximum statement execution time exceeded when trying to dump tablespaces OR 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过小

处理思路:

通过hint,增大N值(文档说,在hint用法中,将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: http://blog.itpub.net/29773961/viewspace−2150443/

2、关于view引用了无效的表,列,函数或者定义。

mysqldump: Couldnt execute SHOW FIELDS FROM view_name: View db_name.view_name references invalid table(s) or column(s) or function(s) or definerinvoker of view lack rights to use them (1356)

原因:该view引用了无效的表,列,函数或者定义者。

处理思路:可以根据报错信息,进入db,执行SHOW CREATE VIEW view_name\G,查看该view的定义,逐一检查该view的基表,列,或相关函数与用户是否具有相关权限。考虑重建或删除视图。

mysqldump: Couldnt execute show create table view_name: Illegal mix of collations for operation UNION (1271)

原因:创建view时,使用UNION时存在非法的排序规则组合。

处理思路:检查该视图定义,检查字符集,考虑重建或删除视图。

3、关于MySQL字符串大小写

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) OR mysqldump: Got error: 1049: Unknown database $db_name when selecting the database

原因一:lower_case_table_names的0设置成1,导致部分原来含有大写字母的库表“找不到”。

处理思路:lower_case_table_names设置回0。如若有需要将lower_case_table_names设置为1,需先设置为0,并将含有大写字母的库表改成小写,再设置为1。

原因二(MySQL 5.5及以下版本可能出现):

表损坏导致该表找不到(InnoDB)。frm和ibd文件都在,但无法SHOW CREATE TABLE xxx\G error log一则:

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还原,对于有主或从的实例,可考虑通过物理备份还原。

该故障出现的原因可能很多,此处只列出两种。排错思路比较简单,找到这张表或库,并确认能否手动正常访问。

4、max_allowed_packet设置过小

mysqldump: Error 2020: Got packet bigger than max_allowed_packet bytes when dumping table tb_name at row: xxxx

原因:默认的max_allowed_packet过小

处理思路:在mysqldump时增加max_allowed_packet的大小,如mysqldump --max-allowed-packet=268435456

5、备份期间执行DDL

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

6、备份MERGE存储引擎或者MyISAM损坏

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=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

附录:

通过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) OR mysqldump: Couldnt execute show create table $tb_name: Table ./db_name/tb_name is marked as crashed and should be repaired (145) ORmysqldump: Error 1194: Table tb_name is marked as crashed and should be repaired when dumping table tb_name at row: xxxxx

原因:mysqldump在拉取表定义时报错,表损坏。

处理思路:该损坏发生在非事务表如MyISAM,通过mysqlcheck或者repair table修复即可。

7、数据字典不存在或损坏

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

原因:字典表不正确,极大可能是导入了其他版本的mysql schema盖掉了字典表。

处理思路:尝试mysql_upgrade来修复,或找到对应版本的mysql_system_tables_fix.sql来导入。可能有的情况,需要在upgrade操作之后重启实例。

8、tmpdir空间满

mysqldump: Error: Got error 28 from storage engine when trying to dump tablespaces OR mysqldump: Couldnt execute show fields from $tb_name: Got error 28 from storage engine (1030)

原因:@@tmpdir满。

处理思路:清除@@tmpdir,可以通过SELECT @@tmpdir;检查具体目录。

9、执行mysqlump期间mysqld被关闭

mysqldump: Lost connection to MySQL server during query (2013) OR ERROR 2002 (HY000): Can't connect to local MySQL server through socket '@@socket' (111)

原因:mysqldump执行过程中mysqld被关闭。

处理思路:检查mysqld被关闭的原因,一般常见原因是发生OOM。

10.mysqldump执行用户缺少权限

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) OR mysqldump: Couldn't execute 'STOP SLAVE SQL_THREAD': Access denied for user 'dump'@'localhost' (using password: YES) (1045)

原因:mysqldump加了--dump-slave参数,缺少SUPERREPLICATION CLIENT来执行SHOW SLAVE STATUS。或缺少SUPER权限使用STOP SLAVE SQL_THREAD

处理思路:检查使用mysqldump的用户权限。

结尾

月黑风高,漆黑的夜晚,城市里的人们早已入梦,路边两个撸串的年轻男子。

正把酒言欢,酒过三巡,突然两位年轻男子拥抱痛哭。

此时的吵闹声与这个城市的宁静格格不入。

为何俩男子会情绪崩溃?

为何俩男子会相拥?

难道是...

且听下回:神谕为何深夜痛哭~

关于「3306π」社区

围绕 MySQL 核心技术,将互联网行业中最重要的数据化解决方案带到传统行业中;囊括其他开源技术Redis、MongoDB、Hbase、Hadoop、ElasticSearch、Storm、Spark等;分享干货知识,即便是赞助商,也要求如此,拒绝放水。

原文发布于微信公众号 - 3306pai(pai3306)

原文发表时间:2018-08-01

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

merge语句导致的CPU使用率过高的优化(r7笔记第4天)

今天有一个数据库有点反常,早上的时候报出了CPU使用率的警告。 警告内容如下: ZABBIX-监控系统: -----------------------...

34350
来自专栏idba

死锁案例之四

一 前言 死锁,其实是一个很有意思也很有挑战的技术问题,大概每个DBA和部分开发同学都会在工作过程中遇见 。关于死锁我会持续写一个系列的案例分析,希望能够对想...

9730
来自专栏杨建荣的学习笔记

使用awk来解析dump文件 (73天)

dump文件是平时工作中经常碰见的,有时候得到一个dump,但是没有提供一些更多的信息,导入的时候就很可能会有问题。 如果某个用户默认表空间是user,但是du...

45880
来自专栏数据和云

为什么预估执行计划与真实执行计划会有差异?

一 问题概要 对同一个 SQL 语句的 ExplainPlan 里显示的预估执行计划与通过 V$SQL_PLAN 视图获取的 Runtime Plan 真实执行...

47970
来自专栏杨建荣的学习笔记

insert中启用错误日志的问题及分析(r2第10天)

在平时的工作中,有时候需要insert一批数据,这些数据可能是临时表,外部表,普通表,子查询等形式,类似下面的格式 insert into xxxx (sele...

33590
来自专栏乐沙弥的世界

PL/SQL -->隐式游标(SQL%FOUND)

在PL/SQL中,游标的使用分为两种,一种是显示游标,一种是隐式游标,显示游标的使用需要事先使用declare来进行声明,其过程包括

11330
来自专栏数据和云

触类旁通:那些关于 TBL$OR$IDX$PART$NUM 的诡异案例和知识

你是否留意过数据库中有一个奇怪的函数 TBL$OR$IDX$PART$NUM ,你是否留意过很多场景下都出现过它的身影?

15340
来自专栏沃趣科技

应用示例荟萃 | performance_schema全方位介绍(上)

经过前面6个篇幅的学习,相信大家对什么是performance_schema,已经初步形成了一个整体认识,但我想很多同行看完之前的文章之后可能还是一脸懵逼,今天...

26630
来自专栏杨建荣的学习笔记

关于ORA-01555的问题分析(r5笔记第87天)

今天开发的同事发给我一个问题,在运行某一个Job的时候抛出了ORA错误,希望我们看看从数据库层面能不能发现什么。 错误日志如下: Function: Entit...

31660
来自专栏杨建荣的学习笔记

ORA-01427问题的分析和解决(r6笔记第51天)

前几天开发的同事反馈一个问题,说前台系统报出了ORA错误,希望我们能看看是什么原因。 java.sql.SQLException: ORA-01427: sin...

28840

扫码关注云+社区

领取腾讯云代金券