Shulou(Shulou.com)05/31 Report--

Editor to share with you the MySQL mysqldump export data abnormal restart and drop stack frame example analysis, I believe that most people do not know much, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to know it!

Abnormal restart of MySQL:mysqldump exported data and drop stack frame 1. Phenomenon

As long as a table in the library is accessed during mysqldump, an error is thrown to restart as soon as it is accessed:

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 [0x982f9] / 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_lex+0x218) [0x70e3c8] / dbdata/mysql5600/bin/mysqld (_ Z13handlehammer selectP3THDP13 select _ 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] II. Analysis.

After a little analysis, it is found that the error function is page_find_rec_max_not_deleted, and when opening the table, why should you access the actual data block when opening the table? I made a breakpoint in the debug environment and stacked the frames on page_find_rec_max_not_deleted as follows:

# 0page _ find_rec_max_not_deleted (page=0x7fffb055c000 "\ 213\ 032\ 063",) 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 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

In fact, the reason can be clear at a glance, if there is a self-increasing field that needs to access the last block of the index when opening the table, and the data file has been corrupted, as long as you open the table, it will restart.

III. Handling

Fortunately, this watch is not needed, it's just a simple drop. Mysqldump is normal again. 8.0 heard that the initialization self-increment has been enhanced, there is no need to access the data file, but the persistence, perhaps 8.0 will not have this problem.

4. Stack frames with two drop table and drop database

Sometimes even if our idb file does not exist, drop table can be carried out. Here are two stack frames.

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 up sqlbank 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 up sqlbank 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

The row_drop_single_table_tablespace function INNODB deletes the physical file if the file does not exist row_drop_single_table_tablespace-> fil_delete_tablespace has the following code

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;}

The first conditional os_file_delete function will return flase and he will call as follows:

The error value of false os_file_handle_error_cond_exit returned directly in os_file_handle_error_no_exit-> os_file_handle_error_cond_exit is: (gdb) p err$24 = 71

And report an error.

The second condition os_file_delete_if_exists

Will return

(gdb) p result$26 = true

That is, the function os_file_delete_if_exists_func returns true, which is true even if the file is not saved.

Therefore, this condition will not trigger an error, then drop table will continue even if the idb file does not exist.

The above is all the contents of this article entitled "abnormal restart of mysqldump exported data in MySQL and sample Analysis of drop Stack frames". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.