mysqld诡异crash

突然收到告警短信，提示有一组服务器MHA已经切换，登录服务器后查看错误日志如下（其中相关insert语句已经处理）：

mysql版本：5.5.24

151221 16:54:26  InnoDB: Assertion failure in thread 139867452008192 in file ha_innodb.cc line 1476 InnoDB: Failing assertion: current <= max_value InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. 08:54:26 UTC - mysqld got signal 6 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=134217728 read_buffer_size=131072 max_used_connections=2 max_threads=1000 thread_count=1 connection_count=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 4366462 K bytes of memory Hope that's ok; if not, decrease some variables in the equation.  Thread pointer: 0x6ba32000 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 7f356dcade20 thread_stack 0x40000 /usr/local/mysql/bin/mysqld(my_print_stacktrace+0x2e)[0x76cf7e] /usr/local/mysql/bin/mysqld(handle_fatal_signal+0x386)[0x6566d6] /lib64/libpthread.so.0[0x305b20f790] /lib64/libc.so.6(gsignal+0x35)[0x305ae32625] /lib64/libc.so.6(abort+0x175)[0x305ae33e05] /usr/local/mysql/bin/mysqld[0x7ceb5a] /usr/local/mysql/bin/mysqld[0x7da5e0] /usr/local/mysql/bin/mysqld(_ZN7handler21update_auto_incrementEv+0x209)[0x659909] /usr/local/mysql/bin/mysqld[0x7d818f] /usr/local/mysql/bin/mysqld(_ZN7handler12ha_write_rowEPh+0x51)[0x65b961] /usr/local/mysql/bin/mysqld(_Z12write_recordP3THDP5TABLEP12st_copy_info+0x5d)[0x54a88d] /usr/local/mysql/bin/mysqld(_Z12mysql_insertP3THDP10TABLE_LISTR4ListI4ItemERS3_IS5_ES6_S6_15enum_duplicatesb+0xa4a)[0x5508ba] /usr/local/mysql/bin/mysqld(_Z21mysql_execute_commandP3THD+0x24d6)[0x5601f6] /usr/local/mysql/bin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0xfe)[0x563bee] /usr/local/mysql/bin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x1356)[0x564fc6] /usr/local/mysql/bin/mysqld(_Z10do_commandP3THD+0xc2)[0x5652f2] /usr/local/mysql/bin/mysqld(_Z24do_handle_one_connectionP3THD+0xf2)[0x5f0b02] /usr/local/mysql/bin/mysqld(handle_one_connection+0x4a)[0x5f0bda] /lib64/libpthread.so.0[0x305b207a51] /lib64/libc.so.6(clone+0x6d)[0x305aee89ad] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (8e8bcc10): INSERT INTO t1 (name) VALUES ('yayun') Connection ID (thread ID): 130 Status: NOT_KILLED

提示INSERT INTO t1 (name) VALUES ('yayun');导致mysqld crash
这么简单的语句也会导致mysqld crash？

我们看看表结构：

mysql> show create table t1\G
*************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` tinyint(4) NOT NULL AUTO_INCREMENT, `name` char(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=128 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)

如果不仔细看还发现不出猫腻，那么仔细看看呢？没错，就是id字段，是tinyint类型，tinyint的取值范围是：带符号的范围是-128到127。无符号的范围是0到255
在建表的时候就指定了AUTO_INCREMENT=128，已经到临界点了。当再次插入就会导致mysqld进程重启。再次插入则改值就被重新设置了。我们来看看。

mysql> CREATE TABLE t1 (id  TINYINT not null AUTO_INCREMENT PRIMARY KEY,name char(20)) ENGINE=InnoDB AUTO_INCREMENT=128; Query OK, 0 rows affected (0.00 sec) mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` tinyint(4) NOT NULL AUTO_INCREMENT, `name` char(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=128 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)

插入一条记录，马上导致mysqld进程crash。

mysql>  INSERT INTO t1 (name) VALUES ('yayun'); ERROR 2013 (HY000): Lost connection to MySQL server during query

再次插入就ok了。

mysql> select * from t1;
+----+-------+ | id | name | +----+-------+ | 1 | yayun | | 2 | yayun | +----+-------+ 2 rows in set (0.00 sec) mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` tinyint(4) NOT NULL AUTO_INCREMENT, `name` char(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)

 

总结：

总之出现上面的情况也是历史业务留下来的坑，已经督促开发进行更改。上面这个属于一个bug，该bug早有人反馈，我又踩一次坑。

参考资料：

http://bugs.mysql.com/bug.php?id=66836

转自大师兄的文章

http://www.cnblogs.com/gomysql/p/5064004.html

转载于:https://www.cnblogs.com/itcomputer/articles/5064212.html