Description:
MySQL has crashed in several environments after applying a database migration which alters a table by adding three new columns.
After mysql restarts, any query or statement on the table causes mysql to crash again.
Here is the stack trace from when it crashed after trying a simple select query on the table:
2024-07-31T17:52:23.267Z | 2024-07-31T17:52:23.267345Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-07-31T17:52:23.890Z | 2024-07-31T17:52:23.890923Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-07-31T17:52:24.179Z | 2024-07-31T17:52:24.179240Z 0 [System] [MY-010229] [Server] Starting XA crash recovery...
2024-07-31T17:52:24.187Z | 2024-07-31T17:52:24.187545Z 0 [System] [MY-010232] [Server] XA crash recovery finished.
.
.
2024-07-31T17:52:24.282Z | 2024-07-31T17:52:24.282399Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.31' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL.
2024-07-31T17:52:46.459Z | 2024-07-31T17:52:46Z UTC - mysqld got signal 6 ;
2024-07-31T17:52:46.459Z | Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
2024-07-31T17:52:46.459Z | BuildID[sha1]=3500d72a2d80e886c3a43a71eac12ad351dd2329
2024-07-31T17:52:46.459Z | Thread pointer: 0x7f78e0000b60
2024-07-31T17:52:46.459Z | Attempting backtrace. You can use the following information to find out
2024-07-31T17:52:46.459Z | where mysqld died. If you see no messages after this, something went
2024-07-31T17:52:46.459Z | terribly wrong...
2024-07-31T17:52:46.459Z | stack_bottom = 7f79483cec50 thread_stack 0x100000
2024-07-31T17:52:46.472Z | /usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x41) [0x2128961]
2024-07-31T17:52:46.472Z | /usr/sbin/mysqld(print_fatal_signal(int)+0x387) [0xfd60b7]
2024-07-31T17:52:46.472Z | /usr/sbin/mysqld(handle_fatal_signal+0xa5) [0xfd6175]
2024-07-31T17:52:46.472Z | /lib64/libpthread.so.0(+0x12d00) [0x7f7b2141bd00]
2024-07-31T17:52:46.472Z | /lib64/libc.so.6(gsignal+0x10f) [0x7f7b1f9d5aff]
2024-07-31T17:52:46.472Z | /lib64/libc.so.6(abort+0x127) [0x7f7b1f9a8ea5]
2024-07-31T17:52:46.472Z | /usr/sbin/mysqld() [0xd4bf66]
2024-07-31T17:52:46.472Z | /usr/sbin/mysqld(dict_index_add_to_cache_w_vcol(dict_table_t*, dict_index_t*, dict_add_v_col_t const*, unsigned int, bool)+0x1cb2) [0x2508462]
2024-07-31T17:52:46.472Z | /usr/sbin/mysqld(dd_fill_dict_index(dd::Table const&, TABLE const*, dict_table_t*, THD*)+0x1d2) [0x2529112]
2024-07-31T17:52:46.472Z | /usr/sbin/mysqld(dict_table_t* dd_open_table_one<dd::Table>(dd::cache::Dictionary_client*, TABLE const*, char const*, dd::Table const*, THD*, std::deque<char const*, ut::allocator<char const*, ut::detail::allocator_base_pfs<char const*> > >&)+0xd32) [0x252bb02]
2024-07-31T17:52:46.472Z | /usr/sbin/mysqld(dict_table_t* dd_open_table<dd::Table>(dd::cache::Dictionary_client*, TABLE const*, char const*, dd::Table const*, THD*)+0xc3) [0x252d823]
2024-07-31T17:52:46.472Z | /usr/sbin/mysqld(ha_innobase::open(char const*, int, unsigned int, dd::Table const*)+0x11a0) [0x222dd50]
2024-07-31T17:52:46.472Z | /usr/sbin/mysqld(handler::ha_open(TABLE*, char const*, int, int, dd::Table const*)+0x59) [0x10e5ce9]
2024-07-31T17:52:46.472Z | /usr/sbin/mysqld(open_table_from_share(THD*, TABLE_SHARE*, char const*, unsigned int, unsigned int, unsigned int, TABLE*, bool, dd::Table const*)+0x109a) [0xf7fe0a]
2024-07-31T17:52:46.472Z | /usr/sbin/mysqld(open_table(THD*, TABLE_LIST*, Open_table_context*)+0x111f) [0xdd4d9f]
2024-07-31T17:52:46.472Z | /usr/sbin/mysqld(open_tables(THD*, TABLE_LIST**, unsigned int*, unsigned int, Prelocking_strategy*)+0x3eb) [0xdd61ab]
2024-07-31T17:52:46.472Z | /usr/sbin/mysqld(open_tables_for_query(THD*, TABLE_LIST*, unsigned int)+0x91) [0xdd7591]
2024-07-31T17:52:46.472Z | /usr/sbin/mysqld(Sql_cmd_dml::prepare(THD*)+0x105) [0xec0225]
2024-07-31T17:52:46.472Z | /usr/sbin/mysqld(Sql_cmd_dml::execute(THD*)+0xfe) [0xecb91e]
2024-07-31T17:52:46.472Z | /usr/sbin/mysqld(mysql_execute_command(THD*, bool)+0xb32) [0xe68ce2]
2024-07-31T17:52:46.472Z | /usr/sbin/mysqld(dispatch_sql_command(THD*, Parser_state*)+0x41a) [0xe6c66a]
2024-07-31T17:52:46.472Z | /usr/sbin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0xd4c) [0xe6d91c]
2024-07-31T17:52:46.472Z | /usr/sbin/mysqld(do_command(THD*)+0x207) [0xe6fbf7]
2024-07-31T17:52:46.472Z | /usr/sbin/mysqld() [0xfc6060]
2024-07-31T17:52:46.472Z | /usr/sbin/mysqld() [0x28eb2f9]
2024-07-31T17:52:46.472Z | /lib64/libpthread.so.0(+0x81da) [0x7f7b214111da]
2024-07-31T17:52:46.472Z | /lib64/libc.so.6(clone+0x43) [0x7f7b1f9c0e73]
2024-07-31T17:52:46.472Z |
2024-07-31T17:52:46.472Z | Trying to get some variables.
2024-07-31T17:52:46.472Z | Some pointers may be invalid and cause the dump to abort.
2024-07-31T17:52:46.472Z | Query (7f78e0029df0): SELECT * FROM pricing.staging_itemDiscount LIMIT 0, 50
2024-07-31T17:52:46.472Z | Connection ID (thread ID): 96
2024-07-31T17:52:46.472Z | Status: NOT_KILLED
The next day, mysql was unable to start, getting stuck in a crash loop, with a similar stack trace on this query:
Query (7f288001ac90): SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ifnull(ENGINE, 'NONE') as ENGINE, ifnull(VERSION, '0') as VERSION, ifnull(ROW_FORMAT, 'NONE') as ROW_FORMAT, ifnull(TABLE_ROWS, '0') as TABLE_ROWS, ifnull(DATA_LENGTH, '0') as DATA_LENGTH, ifnull(INDEX_LENGTH, '0') as INDEX_LENGTH, ifnull(DATA_FREE, '0') as DATA_FREE, ifnull(CREATE_OPTIONS, 'NONE') as CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_SCHEMA = 'pricing'
Our guess is that the index on the table got corrupted.
We have only been able to recover by restoring a backup. After restoring the backup, we are able to run the database migration to alter the table and add the new columns.
A few other details
- we are using the InnoDB storage engine
- the table has no primary key
- we have upgraded MySQL from previous versions
How to repeat:
A database migration script is run to alter the table by adding three new columns.
After running the migration, mysql crashes and after restarting, any query or statement on the table causes mysql to crash again.
Description: MySQL has crashed in several environments after applying a database migration which alters a table by adding three new columns. After mysql restarts, any query or statement on the table causes mysql to crash again. Here is the stack trace from when it crashed after trying a simple select query on the table: 2024-07-31T17:52:23.267Z | 2024-07-31T17:52:23.267345Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2024-07-31T17:52:23.890Z | 2024-07-31T17:52:23.890923Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. 2024-07-31T17:52:24.179Z | 2024-07-31T17:52:24.179240Z 0 [System] [MY-010229] [Server] Starting XA crash recovery... 2024-07-31T17:52:24.187Z | 2024-07-31T17:52:24.187545Z 0 [System] [MY-010232] [Server] XA crash recovery finished. . . 2024-07-31T17:52:24.282Z | 2024-07-31T17:52:24.282399Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.31' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL. 2024-07-31T17:52:46.459Z | 2024-07-31T17:52:46Z UTC - mysqld got signal 6 ; 2024-07-31T17:52:46.459Z | Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware. 2024-07-31T17:52:46.459Z | BuildID[sha1]=3500d72a2d80e886c3a43a71eac12ad351dd2329 2024-07-31T17:52:46.459Z | Thread pointer: 0x7f78e0000b60 2024-07-31T17:52:46.459Z | Attempting backtrace. You can use the following information to find out 2024-07-31T17:52:46.459Z | where mysqld died. If you see no messages after this, something went 2024-07-31T17:52:46.459Z | terribly wrong... 2024-07-31T17:52:46.459Z | stack_bottom = 7f79483cec50 thread_stack 0x100000 2024-07-31T17:52:46.472Z | /usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x41) [0x2128961] 2024-07-31T17:52:46.472Z | /usr/sbin/mysqld(print_fatal_signal(int)+0x387) [0xfd60b7] 2024-07-31T17:52:46.472Z | /usr/sbin/mysqld(handle_fatal_signal+0xa5) [0xfd6175] 2024-07-31T17:52:46.472Z | /lib64/libpthread.so.0(+0x12d00) [0x7f7b2141bd00] 2024-07-31T17:52:46.472Z | /lib64/libc.so.6(gsignal+0x10f) [0x7f7b1f9d5aff] 2024-07-31T17:52:46.472Z | /lib64/libc.so.6(abort+0x127) [0x7f7b1f9a8ea5] 2024-07-31T17:52:46.472Z | /usr/sbin/mysqld() [0xd4bf66] 2024-07-31T17:52:46.472Z | /usr/sbin/mysqld(dict_index_add_to_cache_w_vcol(dict_table_t*, dict_index_t*, dict_add_v_col_t const*, unsigned int, bool)+0x1cb2) [0x2508462] 2024-07-31T17:52:46.472Z | /usr/sbin/mysqld(dd_fill_dict_index(dd::Table const&, TABLE const*, dict_table_t*, THD*)+0x1d2) [0x2529112] 2024-07-31T17:52:46.472Z | /usr/sbin/mysqld(dict_table_t* dd_open_table_one<dd::Table>(dd::cache::Dictionary_client*, TABLE const*, char const*, dd::Table const*, THD*, std::deque<char const*, ut::allocator<char const*, ut::detail::allocator_base_pfs<char const*> > >&)+0xd32) [0x252bb02] 2024-07-31T17:52:46.472Z | /usr/sbin/mysqld(dict_table_t* dd_open_table<dd::Table>(dd::cache::Dictionary_client*, TABLE const*, char const*, dd::Table const*, THD*)+0xc3) [0x252d823] 2024-07-31T17:52:46.472Z | /usr/sbin/mysqld(ha_innobase::open(char const*, int, unsigned int, dd::Table const*)+0x11a0) [0x222dd50] 2024-07-31T17:52:46.472Z | /usr/sbin/mysqld(handler::ha_open(TABLE*, char const*, int, int, dd::Table const*)+0x59) [0x10e5ce9] 2024-07-31T17:52:46.472Z | /usr/sbin/mysqld(open_table_from_share(THD*, TABLE_SHARE*, char const*, unsigned int, unsigned int, unsigned int, TABLE*, bool, dd::Table const*)+0x109a) [0xf7fe0a] 2024-07-31T17:52:46.472Z | /usr/sbin/mysqld(open_table(THD*, TABLE_LIST*, Open_table_context*)+0x111f) [0xdd4d9f] 2024-07-31T17:52:46.472Z | /usr/sbin/mysqld(open_tables(THD*, TABLE_LIST**, unsigned int*, unsigned int, Prelocking_strategy*)+0x3eb) [0xdd61ab] 2024-07-31T17:52:46.472Z | /usr/sbin/mysqld(open_tables_for_query(THD*, TABLE_LIST*, unsigned int)+0x91) [0xdd7591] 2024-07-31T17:52:46.472Z | /usr/sbin/mysqld(Sql_cmd_dml::prepare(THD*)+0x105) [0xec0225] 2024-07-31T17:52:46.472Z | /usr/sbin/mysqld(Sql_cmd_dml::execute(THD*)+0xfe) [0xecb91e] 2024-07-31T17:52:46.472Z | /usr/sbin/mysqld(mysql_execute_command(THD*, bool)+0xb32) [0xe68ce2] 2024-07-31T17:52:46.472Z | /usr/sbin/mysqld(dispatch_sql_command(THD*, Parser_state*)+0x41a) [0xe6c66a] 2024-07-31T17:52:46.472Z | /usr/sbin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0xd4c) [0xe6d91c] 2024-07-31T17:52:46.472Z | /usr/sbin/mysqld(do_command(THD*)+0x207) [0xe6fbf7] 2024-07-31T17:52:46.472Z | /usr/sbin/mysqld() [0xfc6060] 2024-07-31T17:52:46.472Z | /usr/sbin/mysqld() [0x28eb2f9] 2024-07-31T17:52:46.472Z | /lib64/libpthread.so.0(+0x81da) [0x7f7b214111da] 2024-07-31T17:52:46.472Z | /lib64/libc.so.6(clone+0x43) [0x7f7b1f9c0e73] 2024-07-31T17:52:46.472Z | 2024-07-31T17:52:46.472Z | Trying to get some variables. 2024-07-31T17:52:46.472Z | Some pointers may be invalid and cause the dump to abort. 2024-07-31T17:52:46.472Z | Query (7f78e0029df0): SELECT * FROM pricing.staging_itemDiscount LIMIT 0, 50 2024-07-31T17:52:46.472Z | Connection ID (thread ID): 96 2024-07-31T17:52:46.472Z | Status: NOT_KILLED The next day, mysql was unable to start, getting stuck in a crash loop, with a similar stack trace on this query: Query (7f288001ac90): SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ifnull(ENGINE, 'NONE') as ENGINE, ifnull(VERSION, '0') as VERSION, ifnull(ROW_FORMAT, 'NONE') as ROW_FORMAT, ifnull(TABLE_ROWS, '0') as TABLE_ROWS, ifnull(DATA_LENGTH, '0') as DATA_LENGTH, ifnull(INDEX_LENGTH, '0') as INDEX_LENGTH, ifnull(DATA_FREE, '0') as DATA_FREE, ifnull(CREATE_OPTIONS, 'NONE') as CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_SCHEMA = 'pricing' Our guess is that the index on the table got corrupted. We have only been able to recover by restoring a backup. After restoring the backup, we are able to run the database migration to alter the table and add the new columns. A few other details - we are using the InnoDB storage engine - the table has no primary key - we have upgraded MySQL from previous versions How to repeat: A database migration script is run to alter the table by adding three new columns. After running the migration, mysql crashes and after restarting, any query or statement on the table causes mysql to crash again.