MySQL碰到UTC - mysqld got signal 11宕机一例

闲来无事，开了台MySQL数据库5.6.19进行升级操作，打算升级到5.6.26。目的是验证下这个说法，据说这个升级可以直接将MySQL安装包替换掉即可，不需要进行mysql_upgrade。

blog地址：http://blog.csdn.net/hw_libo/article/details/48298613

我这里使用的是二进制预编译包安装的，解压完成5.6.26版本包后，将数据库停止，然后做一下调整：

/usr/local/mysql -> /opt/mysql/mysql-5.5
变更成：
/usr/local/mysql -> /opt/mysql/mysql-5.6

重启了数据库，发现数据库正常启动，error log中也并无错误或warnning，一切正常，数据均能正常访问。

但是我还是想试试运行一下mysql_upgrade，于是在my.cnf中添加了skip-grant-table，然后重启了MySQL，接着：

[root@MySQL5 mysql3306]# /usr/local/mysql/bin/mysql_upgrade -S /tmp/mysql3306.sock 
Looking for 'mysql' as: /usr/local/mysql/bin/mysql
Looking for 'mysqlcheck' as: /usr/local/mysql/bin/mysqlcheck
Running 'mysqlcheck' with connection arguments: '--socket=/tmp/mysql3306.sock' 
Running 'mysqlcheck' with connection arguments: '--socket=/tmp/mysql3306.sock' 
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Running 'mysql_fix_privilege_tables'...
Running 'mysqlcheck' with connection arguments: '--socket=/tmp/mysql3306.sock' 
Running 'mysqlcheck' with connection arguments: '--socket=/tmp/mysql3306.sock' 
bosco.City                                         OK
bosco.Country                                      OK
bosco.CountryLanguage                              OK
bosco.data1                                        OK
bosco.dept                                         OK
bosco.dept1                                        OK
bosco.dept1_tmp                                    OK
bosco.dept2                                        OK
bosco.dept_p                                       OK
bosco.emp1                                         OK
bosco.innodb_tab1                                  OK
bosco.myisam_tab1                                  OK
bosco.p1                                           OK
bosco.sal1                                         OK
bosco.salaries                                     OK
bosco.student                                      OK
bosco.t                                            OK
bosco.t1                                           OK
bosco.t100                                         OK
bosco.t3                                           OK
bosco.tb_gbk                                       OK
bosco.tb_latin1                                    OK
bosco.tb_utf8                                      OK
employees.departments                              OK
employees.dept_emp                                 OK
employees.dept_manager                             OK
employees.emp                                      OK
employees.emp1                                     OK
employees.emp2                                     OK
employees.employees                                OK
employees.sal1                                     OK
employees.salaries                                 OK
employees.titles                                   OK
moni.dept                                          OK
moni.emp                                           OK
moni.employees                                     OK
moni.t2                                            OK
moni.tb_latin1                                     OK
moni.user                                          OK
test.dept                                          OK
test.emp                                           OK
test.emp1                                          OK
test.emp2                                          OK
test.employees                                     OK
test.innodb_lock_monitor                           OK
test.members                                       OK
test.mynum1                                        OK
test.pt                                            OK
test.t1                                            OK
test.t10                                           OK
test.t11                                           OK
test.t15                                           OK
test.t2                                            OK
test.t3                                            OK
test.t5                                            OK
test.t6                                            OK
test.t7                                            OK
test.t8                                            OK
test.t9                                            OK
test.t_int                                         OK
test.tb_1                                          OK
test.tb_gbk                                        OK
test.tb_latin1                                     OK
test.user                                          OK
test.yw                                            OK
tmp.dept                                           OK
tmp.log_message_1                                  OK
tmp.t1                                             OK
tmp.t2                                             OK
tmp.t3                                             OK
tmp.tb_gbk                                         OK
tmp.tb_latin1                                      OK
tmp.tb_utf8                                        OK
tmp.yw                                             OK
tpcc10.customer                                    OK
tpcc10.district                                    OK
tpcc10.history                                     OK
tpcc10.item                                        OK
tpcc10.new_orders                                  OK
tpcc10.order_line                                  OK
tpcc10.orders                                      OK
tpcc10.stock                                       OK
tpcc10.warehouse                                   OK
world.City                                         OK
world.Country                                      OK
world.CountryLanguage                              OK
OK

升级很顺利。数据库重启也很正常，但是我随便访问一张表，测试一下数据：

MySQL mysql3306.sock [mysql]> select * from bosco.dept1;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     60 | MySQL      | BJ       |
|    100 | 100        | 100      |
+--------+------------+----------+
6 rows in set (0.00 sec)

MySQL mysql3306.sock [mysql]> update bosco.dept1 set loc='CCC' where dname='SALES';
ERROR 2013 (HY000): Lost connection to MySQL server during query

靠……，什么情况呀？从error log中看到这样的错误：

Version: '5.6.26-log'  socket: '/tmp/mysql3306.sock'  port: 3306  MySQL Community Server (GPL)
04:31:01 UTC - mysqld got signal 11 ;
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=8388608
read_buffer_size=2097152
max_used_connections=2
max_threads=151
thread_count=2
connection_count=2
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 628620 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x3b1e7f0
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 = 7f2d93cd3e10 thread_stack 0x30000
/usr/local/mysql/bin/mysqld(my_print_stacktrace+0x35)[0x90d8e5]
/usr/local/mysql/bin/mysqld(handle_fatal_signal+0x3d8)[0x674078]
/lib64/libpthread.so.0[0x375d80f500]
/usr/local/mysql/bin/mysqld[0x9cc946]
/usr/local/mysql/bin/mysqld[0x92c788]
/usr/local/mysql/bin/mysqld(_ZN7handler27multi_range_read_info_constEjP15st_range_seq_ifPvjPjS3_P13Cost_estimate+0x1ee)[0x590fce]
/usr/local/mysql/bin/mysqld(_ZN10DsMrr_impl16dsmrr_info_constEjP15st_range_seq_ifPvjPjS3_P13Cost_estimate+0x53)[0x592593]
/usr/local/mysql/bin/mysqld[0x824270]
/usr/local/mysql/bin/mysqld[0x828f31]
/usr/local/mysql/bin/mysqld(_ZN10SQL_SELECT17test_quick_selectEP3THD6BitmapILj64EEyybN8st_order10enum_orderE+0xa17)[0x832797]
/usr/local/mysql/bin/mysqld(_Z12mysql_updateP3THDP10TABLE_LISTR4ListI4ItemES6_PS4_jP8st_ordery15enum_duplicatesbPySB_+0x6d9)[0x75df89]
/usr/local/mysql/bin/mysqld(_Z21mysql_execute_commandP3THD+0x94f)[0x6f42ef]
/usr/local/mysql/bin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x338)[0x6faa48]
/usr/local/mysql/bin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0xc68)[0x6fbe28]
/usr/local/mysql/bin/mysqld(_Z10do_commandP3THD+0xd7)[0x6fdc37]
/usr/local/mysql/bin/mysqld(_Z24do_handle_one_connectionP3THD+0x116)[0x6c4bb6]
/usr/local/mysql/bin/mysqld(handle_one_connection+0x45)[0x6c4c95]
/usr/local/mysql/bin/mysqld(pfs_spawn_thread+0x126)[0xaf92f6]
/lib64/libpthread.so.0[0x375d807851]
/lib64/libc.so.6(clone+0x6d)[0x34cbee890d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f2d68002dc0): is an invalid pointer
Connection ID (thread ID): 2
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

经检查，并非是参数配置导致的，空闲内存还有较多的。于是将MySQL重新启动，问题还是依旧。并且google了很久，也没有找到有效解决办法，非常郁闷！

在折腾的过程中，发现只是bosco.dept1这张表有问题，可以正常的select、delete操作，但是就是不能进行update操作，只要一进行update数据库立即宕机。

实在没有办法了，最终只能将这个表中的数据dump出来，然后重新导入，就恢复正常了，百思不得其解，或许是这个表被损坏了吧，呵呵……

blog地址：http://blog.csdn.net/hw_libo/article/details/48298613
-- Bosco  QQ：375612082
---- END ----