MySQL：mysqldump 匯出資料異常重啟及drop棧幀

一、現象

在進行mysqldump的時候只要訪問到庫中一個表，只要一訪問就拋錯重啟如下：

stack_bottom = 7f656f792e28 thread_stack 0x30000
/dbdata/mysql5600/bin/mysqld(my_print_stacktrace+0x35)[0x905a25]
/dbdata/mysql5600/bin/mysqld(handle_fatal_signal+0x43b)[0x65c50b]
/dbdata/mysql5600/bin/mysqld(_Z29page_find_rec_max_not_deletedPKh+0xa0)[0x9b5570]
/dbdata/mysql5600/bin/mysqld[0x9fdca0]
/dbdata/mysql5600/bin/mysqld[0x967202]
/dbdata/mysql5600/bin/mysqld[0x9682f9]
/dbdata/mysql5600/bin/mysqld(_ZN7handler7ha_openEP5TABLEPKcii+0x3e)[0x599f3e]
/dbdata/mysql5600/bin/mysqld(_Z21open_table_from_shareP3THDP11TABLE_SHAREPKcjjjP5TABLEb+0x68c)[0x77dedc]
/dbdata/mysql5600/bin/mysqld(_Z10open_tableP3THDP10TABLE_LISTP18Open_table_context+0xcaf)[0x699fff]
/dbdata/mysql5600/bin/mysqld(_Z11open_tablesP3THDPP10TABLE_LISTPjjP19Prelocking_strategy+0xf50)[0x69bbf0]
/dbdata/mysql5600/bin/mysqld(_Z30open_normal_and_derived_tablesP3THDP10TABLE_LISTj+0x48)[0x69bd48]
/dbdata/mysql5600/bin/mysqld[0x71dc3f]
/dbdata/mysql5600/bin/mysqld(_Z14get_all_tablesP3THDP10TABLE_LISTP4Item+0x738)[0x72d388]
/dbdata/mysql5600/bin/mysqld(_Z24get_schema_tables_resultP4JOIN23enum_schema_table_state+0x2e1)[0x718c71]
/dbdata/mysql5600/bin/mysqld(_ZN4JOIN14prepare_resultEPP4ListI4ItemE+0x9d)[0x70c94d]
/dbdata/mysql5600/bin/mysqld(_ZN4JOIN4execEv+0xdc)[0x6c5abc]
/dbdata/mysql5600/bin/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_P10SQL_I_ListI8st_orderESB_S7_yP13select_resultP18st_select_lex_unitP13st_select_le
x+0x218)[0x70e3c8]
/dbdata/mysql5600/bin/mysqld(_Z13handle_selectP3THDP13select_resultm+0x17f)[0x70ecbf]
/dbdata/mysql5600/bin/mysqld[0x6e6b05]
/dbdata/mysql5600/bin/mysqld(_Z21mysql_execute_commandP3THD+0x26ce)[0x6eb6ce]
/dbdata/mysql5600/bin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x598)[0x6ee818]
/dbdata/mysql5600/bin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x1766)[0x6f0026]
/dbdata/mysql5600/bin/mysqld(_Z24do_handle_one_connectionP3THD+0x115)[0x6b6e95]
/dbdata/mysql5600/bin/mysqld(handle_one_connection+0x42)[0x6b7012]
/dbdata/mysql5600/bin/mysqld(pfs_spawn_thread+0x127)[0x941627]
libc.so.6(clone+0x6d)[0x34582e8b5d]

二、分析

稍做分析發現出錯的函式是page_find_rec_max_not_deleted，並且出現在開啟表的時候，開啟表為什麼要去訪問實際的資料塊呢？我在debug環境做了一個斷點在page_find_rec_max_not_deleted上棧幀如下：

#0  page_find_rec_max_not_deleted (page=0x7fffb055c000 "\213\032\063", <incomplete sequence \316>)
    at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/page/page0page.cc:2762#1  0x0000000001b505be in row_search_get_max_rec (index=0x7fff249e8cf0, mtr=0x7ffff02d77b0)
    at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/row/row0sel.cc:6335#2  0x0000000001b506f5 in row_search_max_autoinc (index=0x7fff249e8cf0, col_name=0x7fff249e2551 "id", value=0x7ffff02d7d10)
    at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/row/row0sel.cc:6373#3  0x00000000019ac668 in ha_innobase::innobase_initialize_autoinc (this=0x7fff249e28a0)
    at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:6118#4  0x00000000019ad722 in ha_innobase::open (this=0x7fff249e28a0, name=0x7fff249eac00 "./test/mch_pay_rate", mode=2, test_if_locked=2)
    at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:6533#5  0x0000000000f664be in handler::ha_open (this=0x7fff249e28a0, table_arg=0x7fff249e43a0, name=0x7fff249eac00 "./test/mch_pay_rate", mode=2, test_if_locked=2)
    at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:2904#6  0x00000000016a1542 in open_table_from_share (thd=0x7fff24000b70, share=0x7fff249ea820, alias=0x7fff24005a38 "mch_pay_rate", db_stat=39, prgflag=8, 
    ha_open_flags=0, outparam=0x7fff249e43a0, is_create_table=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/table.cc:3335#7  0x00000000015186cc in open_table (thd=0x7fff24000b70, table_list=0x7ffff02d9600, ot_ctx=0x7ffff02d8d40)
    at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_base.cc:3560#8  0x000000000151b24f in open_and_process_table (thd=0x7fff24000b70, lex=0x7fff24003150, tables=0x7ffff02d9600, counter=0x7fff24003210, flags=1024, 
    prelocking_strategy=0x7ffff02d8e70, has_prelocking_list=false, ot_ctx=0x7ffff02d8d40) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_base.cc:5171#9  0x000000000151c3ab in open_tables (thd=0x7fff24000b70, start=0x7ffff02d8e30, counter=0x7fff24003210, flags=1024, prelocking_strategy=0x7ffff02d8e70)
    at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_base.cc:5789#10 0x000000000151d7e5 in open_tables_for_query (thd=0x7fff24000b70, tables=0x7ffff02d9600, flags=1024)
    at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_base.cc:6564#11 0x000000000160cda1 in mysqld_list_fields (thd=0x7fff24000b70, table_list=0x7ffff02d9600, wild=0x7fff24006388 "")
    at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_show.cc:1092#12 0x00000000015a248f in dispatch_command (thd=0x7fff24000b70, com_data=0x7ffff02d9d70, command=COM_FIELD_LIST)
    at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1622#13 0x00000000015a09c6 in do_command (thd=0x7fff24000b70) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1010#14 0x00000000016e29d0 in handle_connection (arg=0x387b9e0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/conn_handler/connection_handler_per_thread.cc:312#15 0x0000000001d7b4b0 in pfs_spawn_thread (arg=0x380b6e0) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/perfschema/pfs.cc:2188#16 0x0000003f74807aa1 in start_thread () from /lib64/libpthread.so.0#17 0x0000003f740e8bcd in clone () from /lib64/libc.so.6

其實原因可以一目瞭然，原來開啟表的時候如果有自增欄位需要訪問索引的最後一個塊，而資料檔案已經損壞了只要開啟表就會重啟。

三、處理

還好這個表是不需要的，只是簡單drop 就行了。再次進行mysqldump正常了。8.0聽說對於初始化自增值做了加強，不需要訪問資料檔案了而是做了持久化，也許8.0就不會有這種問題了。

四、附帶兩個drop table 和drop database的棧幀

有的時候即便我們idb檔案不存在drop table是可以進行的下面是兩個棧幀。

drop db#0  open_table (thd=0x7fff20000b70, table_list=0x7ffff02d5ec0, ot_ctx=0x7ffff02d5da0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_base.cc:3612#1  0x000000000151d21c in open_ltable (thd=0x7fff20000b70, table_list=0x7ffff02d5ec0, lock_type=TL_WRITE, lock_flags=2048)
    at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_base.cc:6400#2  0x000000000152692f in open_system_table_for_update (thd=0x7fff20000b70, one_table=0x7ffff02d5ec0)
    at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_base.cc:10710#3  0x00000000014d90e9 in open_proc_table_for_update (thd=0x7fff20000b70) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sp.cc:485#4  0x00000000014dc840 in sp_drop_db_routines (thd=0x7fff20000b70, db=0x7fff20006388 "employees") at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sp.cc:1634#5  0x0000000001551203 in mysql_rm_db (thd=0x7fff20000b70, db=..., if_exists=false, silent=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_db.cc:891#6  0x00000000015a86e0 in mysql_execute_command (thd=0x7fff20000b70, first_level=true) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:4007#7  0x00000000015adcd6 in mysql_parse (thd=0x7fff20000b70, parser_state=0x7ffff02d9600) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5836#8  0x00000000015a1b95 in dispatch_command (thd=0x7fff20000b70, com_data=0x7ffff02d9d70, command=COM_QUERY)
    at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1447#9  0x00000000015a09c6 in do_command (thd=0x7fff20000b70) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1010#10 0x00000000016e29d0 in handle_connection (arg=0x387d890) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/conn_handler/connection_handler_per_thread.cc:312#11 0x0000000001d7b4b0 in pfs_spawn_thread (arg=0x3866c20) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/perfschema/pfs.cc:2188#12 0x0000003f74807aa1 in start_thread () from /lib64/libpthread.so.0#13 0x0000003f740e8bcd in clone () from /lib64/libc.so.6drop table
(gdb) bt#0  os_file_handle_error_no_exit (name=0x7fff24028dd8 "./test/t1.ibd", operation=0x22e1538 "delete", on_error_silent=false)
    at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/os/os0file.cc:5946#1  0x0000000001a7b154 in os_file_delete_func (name=0x7fff24028dd8 "./test/t1.ibd") at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/os/os0file.cc:3849#2  0x0000000001cd6d8d in pfs_os_file_delete_func (key=46, name=0x7fff24028dd8 "./test/t1.ibd", 
    src_file=0x2364a38 "/root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/fil/fil0fil.cc", src_line=2896)
    at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/include/os0file.ic:470#3  0x0000000001cdf3ae in fil_delete_tablespace (id=617, buf_remove=BUF_REMOVE_FLUSH_NO_WRITE)
    at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/fil/fil0fil.cc:2896#4  0x0000000001b1686a in row_drop_single_table_tablespace (space_id=617, tablename=0x7fff24010e00 "test/t1", filepath=0x7fff2403b258 "./test/t1.ibd", is_temp=false, 
    is_encrypted=false, trx=0x7ffff2f2e5d0) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/row/row0mysql.cc:4217#5  0x0000000001b17be2 in row_drop_table_for_mysql (name=0x7ffff02d63d0 "test/t1", trx=0x7ffff2f2e5d0, drop_db=false, nonatomic=true, handler=0x0)
    at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/row/row0mysql.cc:4751#6  0x00000000019b9e1a in ha_innobase::delete_table (this=0x7fff24006b30, name=0x7ffff02d7840 "./test/t1")
    at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:12955#7  0x0000000000f6c2da in handler::ha_delete_table (this=0x7fff24006b30, name=0x7ffff02d7840 "./test/t1")
    at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:5071#8  0x0000000000f65ba5 in ha_delete_table (thd=0x7fff24000b70, table_type=0x2e9edd0, path=0x7ffff02d7840 "./test/t1", db=0x7fff24006938 "test", 
    alias=0x7fff24006378 "t1", generate_warning=true) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:2722#9  0x00000000016347d9 in mysql_rm_table_no_locks (thd=0x7fff24000b70, tables=0x7fff240063b0, if_exists=false, drop_temporary=false, drop_view=false, 
    dont_log_query=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_table.cc:2644#10 0x0000000001633572 in mysql_rm_table (thd=0x7fff24000b70, tables=0x7fff240063b0, if_exists=0 '\000', drop_temporary=0 '\000')
    at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_table.cc:2207#11 0x00000000015a78df in mysql_execute_command (thd=0x7fff24000b70, first_level=true) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:3742#12 0x00000000015adcd6 in mysql_parse (thd=0x7fff24000b70, parser_state=0x7ffff02d9600) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5836#13 0x00000000015a1b95 in dispatch_command (thd=0x7fff24000b70, com_data=0x7ffff02d9d70, command=COM_QUERY)
    at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1447#14 0x00000000015a09c6 in do_command (thd=0x7fff24000b70) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1010#15 0x00000000016e29d0 in handle_connection (arg=0x3855a50) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/conn_handler/connection_handler_per_thread.cc:312#16 0x0000000001d7b4b0 in pfs_spawn_thread (arg=0x37ef3b0) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/perfschema/pfs.cc:2188#17 0x0000003f74807aa1 in start_thread () from /lib64/libpthread.so.0#18 0x0000003f740e8bcd in clone () from /lib64/libc.so.6

row_drop_single_table_tablespace 函式INNODB刪除物理檔案如果檔案不存在row_drop_single_table_tablespace -> fil_delete_tablespace 中有如下程式碼

if (!os_file_delete(innodb_data_file_key, path)
            && !os_file_delete_if_exists(
                innodb_data_file_key, path, NULL)) {            /* Note: This is because we have removed the
            tablespace instance from the cache. */
            err = DB_IO_ERROR;
        }

• 第一個條件os_file_delete函式會返回flase他會呼叫如下：

os_file_handle_error_no_exit ->os_file_handle_error_cond_exit 中會直接返回false os_file_handle_error_cond_exit 的err值為：
(gdb) p err$24 = 71

並且報錯

• 第二個條件os_file_delete_if_exists

會返回

(gdb) p result$26 = true

也就是 函式os_file_delete_if_exists_func返回了true，即便檔案不存也是true。
因此本條件不會觸發報錯，則drop table即便idb檔案不存在也會繼續。

作者微信：