小编给大家分享一下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就不会有这种问题了。
有的时候即便我们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文件不存在也会继续。
以上是“MySQL中mysqldump导出数据异常重启及drop栈帧的示例分析”这篇文章的所有内容,感谢各位的阅读!相信大家都有了一定的了解,希望分享的内容对大家有所帮助,如果还想学习更多知识,欢迎关注创新互联行业资讯频道!
新闻标题:MySQL中mysqldump导出数据异常重启及drop栈帧的示例分析
文章链接:/article28/jcgecp.html
成都网站建设公司_创新互联,为您提供网站改版、品牌网站建设、ChatGPT、定制开发、网站设计、搜索引擎优化
声明:本网站发布的内容(图片、视频和文字)以用户投稿、用户转载内容为主,如果涉及侵权请尽快告知,我们将会在第一时间删除。文章观点不代表本网站立场,如需处理请联系客服。电话:028-86922220;邮箱:631063699@qq.com。内容未经允许不得转载,或转载时需注明来源: 创新互联