故障案例--mysql5.5分区表的一个坑
来源:互联网 发布:淘宝店铺刷单 编辑:程序博客网 时间:2024/06/05 16:45
故障现象
db每隔一段时间就异常重启,查看DB错误日志的错误日志Database was not shut down normally相关的信息,而查看/var/log/message并没有发现什么异常,没有发生OOM。由于每次异常重启的间隔都比较相近,所以怀疑是业务的某个sql引起的,后来经过业务层排查,发现每隔一段时间都会执行一条如下SQL语句
select sid as sid,source as source,sum(valid) as valid,sum(error) as error from playstats where startTime>="2016-07-08 10:00:00" and endTime<="2016-07-08 12:00:00" group by sid,source;
查看表结构如下
mysql> show create table playstats\G
*************************** 1. row ***************************
Table: playstats
Create Table: CREATE TABLE `playstats` (
`id` int(255) NOT NULL AUTO_INCREMENT,
`year` int(4) NOT NULL,
`month` int(2) NOT NULL,
`day` int(2) NOT NULL,
`startTime` datetime NOT NULL,
`endTime` datetime NOT NULL,
`version` varchar(12) NOT NULL DEFAULT '',
`source` varchar(12) NOT NULL DEFAULT '',
`sid` varchar(12) NOT NULL,
`valid` int(8) NOT NULL,
`error` int(8) NOT NULL,
`total` int(8) NOT NULL,
PRIMARY KEY (`id`,`startTime`,`version`,`source`,`sid`),
KEY `playstats_index_startTime` (`startTime`),
KEY `playstats_index_endTime` (`endTime`),
KEY `playstats_muti_index` (`year`,`month`,`source`),
KEY `playstats_index_source` (`source`),
KEY `month_index` (`month`),
KEY `year_index` (`year`)
) ENGINE=InnoDB AUTO_INCREMENT=1267666446 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (to_days(startTime))
(PARTITION p20160405 VALUES LESS THAN (736425) ENGINE = InnoDB,
PARTITION p20160620 VALUES LESS THAN (736501) ENGINE = InnoDB,
PARTITION p20160706 VALUES LESS THAN (736517) ENGINE = InnoDB) */
1 row in set (0.00 sec)
现象模拟:
mysql> select * from playstats;
Empty set (0.00 sec)
mysql> select sid as sid,source as source,sum(valid) as valid,sum(error) as error from playstats where startTime>="2016-07-03 10:00:00" and endTime<="2016-07-03 12:00:00" group by sid,source;
Empty set (0.00 sec)
mysql> select sid as sid,source as source,sum(valid) as valid,sum(error) as error from playstats where startTime>="2016-07-08 10:00:00" and endTime<="2016-07-08 12:00:00" group by sid,source;
ERROR 2013 (HY000): Lost connection to MySQL server during query
原因分析:
mysql> select to_days("2016-07-03 12:00:00");
+--------------------------------+
| to_days("2016-07-03 12:00:00") |
+--------------------------------+
| 736513 |
+--------------------------------+
1 row in set (0.00 sec)
mysql> select to_days("2016-07-08 10:00:00");
+--------------------------------+
| to_days("2016-07-08 10:00:00") |
+--------------------------------+
| 736518 |
+--------------------------------+
1 row in set (0.00 sec)
直接查没问题,时间范围<736517也没问题,应该是大于736517以后没有定义,于是直接db崩溃,属于bug
解决措施
添加一个maxvalue,即将表结构改为
alter table playstats /*!50100 PARTITION BY RANGE (to_days(startTime)) (PARTITION p20160405 VALUES LESS THAN (736425) ENGINE = InnoDB, PARTITION p20160620 VALUES LESS THAN (736501) ENGINE = InnoDB, PARTITION p20160706 VALUES LESS THAN maxvalue ENGINE = InnoDB) */;
mysql> select sid as sid,source as source,sum(valid) as valid,sum(error) as error from playstats where startTime>="2016-07-08 10:00:00" and endTime<="2016-07-08 12:00:00" group by sid,source;
Empty set (0.00 sec)
这时再执行上面的sql就不会崩溃了
- 故障案例--mysql5.5分区表的一个坑
- 故障案例--mariadb 10.0向mysql5.6官方版本迁移的一个坑
- 一个load过高的故障排查案例
- 故障案例--在线ddl的一个bug
- 故障案例--mysql5.6启动失败
- 一个MySQL 5.7 分区表性能下降的案例分析
- 故障案例:mysql5.6下,mysqlbinlog版本不对可能导致的问题
- 故障案例--binlog_format不为row模式下关于时区设置的一个坑
- 故障案例--mongodb副本集write concern为majority的一个坑
- 故障案例--mysql5.5 myisam引擎出现Waiting for table metadata lock
- 处理MySQL复制环境Slave故障的一个案例
- 对大表创建分区表的案例
- 一个由多线程而引发内存溢出故障的案例分析
- 一个奇怪的故障
- MySQL分区表及MySQL5.7对其的改进
- 故障案例:phpadmin点击打开一个数据库卡死
- 故障案例:一个子查询导致服务崩溃
- 分区表索引实践案例
- 日历控件的封装
- html5-contentEditable在线编辑
- HTTP Header 详解
- 数组作为形参
- html中文字或图片的简单动态展示
- 故障案例--mysql5.5分区表的一个坑
- uva 10361
- GDAL编译错误记录
- [GPIO]MT2601平台L1.MP9版本DWS配置方法
- 解决VC++在WIN7下使用ADO方式连接ACCESS数据库到XP不能运行的问题
- 关于安装WIN7和Ubuntu14.04双系统后,启动直接引导到Ubuntu系统
- 全局变量
- 异常和数组
- 基于Python的参考文献生成器1.0