Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.4.19, 10.5.13, 10.6.5, 10.4(EOL), 10.5, 10.6, 10.7(EOL)
Description
During a migration project I stumbled over invalid data and mariadb server crashes after changing charset and collation of tables.
I managed to boil the issue down to apparent InnoDB index issues in combination with modifying collations on existing tables. The issue is reproducible on different servers and appears in 10.4.19, 10.5.13 and 10.6.5. It does not appear in 10.2.38 and 10.3.29.
All that is required to provoke the issue is adding a one-column table with a key, adding some (partially crafted, but very basic) rows and then converting the collation (e.g. from utf8mb4_general_ci to utf8mb4_unicode_ci).
Afterwards the table/index is broken and mariadb crashes if CHECK TABLE is issued for that table. I attached an .sql file which - if passed to the server - reliably causes a crash. I also added a log excerpt for that crash.
The table/index can be repaired manually by issuing an OPTIMIZE TABLE or ALTER TABLE <table> ENGINE=INNODB.
In our actual environment the issue also manifested in a way that some data just would not be retrieved when selected by the indexed column (and index errors were shown in the log). I believe that part has the same root cause, so I'm not adding any further info.
Do let me know if you need further information.
Attachments
Issue Links
- duplicates
-
MDEV-26294 Duplicate entries in unique index not detected when changing collation with INPLACE / NOCOPY algoritm
-
- Closed
-
- is caused by
-
MDEV-15564 Avoid table rebuild in ALTER TABLE on collation or charset changes
-
- Closed
-
-
MDEV-17301 Change of COLLATE unnecessarily requires ALGORITHM=COPY
-
- Closed
-
Thank you very much!
Repeatable as described on 10.4-10.7.
--source include/have_innodb.inc
--source include/have_sequence.inc
10.4 74b3d4252a291456982c8e61a
2021-12-16 11:36:27 10 [ERROR] InnoDB: Records in wrong order
InnoDB: previous record PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 28; hex 30303030303030303030303030303030303030303030303030303939; asc 0000000000000000000000000099;;
1: len 6; hex 000000000263; asc c;;
InnoDB: record PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 27; hex 5b3030303030303030303030303030303030303030303030303030; asc [00000000000000000000000000;;
1: len 6; hex 000000000200; asc ;;
2021-12-16 11:36:27 10 [ERROR] InnoDB: Corruption of an index tree: table `test`.`t1` index `id`, father ptr page no 10, child page no 11
PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 28; hex 30303030303030303030303030303030303030303030303030303930; asc 0000000000000000000000000090;;
1: len 6; hex 00000000025a; asc Z;;
2021-12-16 11:36:27 10 [Note] InnoDB: n_owned: 0; heap_no: 2; next rec: 168
PHYSICAL RECORD: n_fields 3; compact format; info bits 16
0: len 27; hex 303030303030303030303030303030303030303030303030303031; asc 000000000000000000000000001;;
1: len 6; hex 000000000201; asc ;;
2: len 4; hex 0000000a; asc ;;
2021-12-16 11:36:27 10 [Note] InnoDB: n_owned: 0; heap_no: 2; next rec: 171
2021-12-16 11:36:27 10 [ERROR] [FATAL] InnoDB: You should dump + drop + reimport the table to fix the corruption. If the crash happens at database startup. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery. Then dump + drop + reimport.
211216 11:36:27 [ERROR] mysqld got signal 6 ;
Server version: 10.4.23-MariaDB-debug-log
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0xcb)[0x7fbac912f18b]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x12b)[0x7fbac910e859]
ut/ut0ut.cc:604(ib::fatal::~fatal())[0x55b8788cc411]
btr/btr0btr.cc:933(btr_page_get_father_node_ptr_func(unsigned short*, mem_block_info_t*, btr_cur_t*, unsigned long, char const*, unsigned int, mtr_t*))[0x55b8788de54f]
btr/btr0btr.cc:5125(btr_validate_level(dict_index_t*, trx_t const*, unsigned long, bool))[0x55b8788f8596]
btr/btr0btr.cc:5362(btr_validate_index(dict_index_t*, trx_t const*))[0x55b8788f9cf4]
handler/ha_innodb.cc:14793(ha_innobase::check(THD*, st_ha_check_opt*))[0x55b8783ba7c5]
sql/handler.cc:4372(handler::ha_check(THD*, st_ha_check_opt*))[0x55b877966671]
sql/sql_admin.cc:874(mysql_admin_table(THD*, TABLE_LIST*, st_ha_check_opt*, char const*, thr_lock_type, bool, bool, unsigned int, int (*)(THD*, TABLE_LIST*, st_ha_check_opt*), int (handler::*)(THD*, st_ha_check_opt*), int (*)(THD*, TABLE_LIST*, st_ha_check_opt*), bool))[0x55b8775c144b]
sql/sql_admin.cc:1448(Sql_cmd_check_table::execute(THD*))[0x55b8775c6b32]
sql/sql_parse.cc:6192(mysql_execute_command(THD*))[0x55b8771b5315]
sql/sql_parse.cc:7995(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55b8771c0e51]
sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55b8771975d8]
sql/sql_parse.cc:1373(do_command(THD*))[0x55b877194055]
sql/sql_connect.cc:1420(do_handle_one_connection(CONNECT*))[0x55b87758e979]
sql/sql_connect.cc:1317(handle_one_connection)[0x55b87758e0d2]
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x55b87828ce45]
nptl/pthread_create.c:478(start_thread)[0x7fbac9638609]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x43)[0x7fbac920b293]
Query (0x62b0000b6290): CHECK TABLE t1