[MDEV-27280] server crashes on CHECK TABLE after COLLATE change for utf8mb4 Created: 2021-12-16  Updated: 2022-07-28  Resolved: 2022-07-20

Status: Closed
Project: MariaDB Server
Component/s: Character Sets, Storage Engine - InnoDB
Affects Version/s: 10.4.19, 10.5.13, 10.6.5, 10.4, 10.5, 10.6, 10.7
Fix Version/s: 10.4.26, 10.5.17, 10.6.9, 10.7.5, 10.8.4

Type: Bug Priority: Major
Reporter: Stefan Bedorf Assignee: Thirunarayanan Balathandayuthapani
Resolution: Duplicate Votes: 0
Labels: crash, innodb, utf8mb4

Attachments: File mariadb-mysqld.log     File mariadb_conversion_issue_poc.sql    
Issue Links:
Duplicate
duplicates MDEV-26294 Duplicate entries in unique index not... Closed
Problem/Incident
is caused by MDEV-15564 Avoid table rebuild in ALTER TABLE on... Closed
is caused by MDEV-17301 Change of COLLATE unnecessarily requi... Closed

 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.

 Comments   
Comment by Alice Sherepa [ 2021-12-16 ]

Thank you very much!
Repeatable as described on 10.4-10.7.

--source include/have_innodb.inc
 
--source include/have_sequence.inc
 
 
 
CREATE TABLE t1 (
 
  id varchar(30),
 
  KEY (id)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci;
 
 
 
INSERT INTO t1 VALUES ('[00000000000000000000000000');
 
INSERT INTO t1 select concat('00000000000000000000000000',seq) from seq_1_to_394;
 
ALTER TABLE t1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 
 
 
CHECK TABLE t1;
 
 
 
DROP TABLE t1;

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

Comment by Eugene Kosov (Inactive) [ 2021-12-27 ]

This looks a bit different but the issue is actually a duplicate of https://jira.mariadb.org/browse/MDEV-26294
The issue is: innodb creates a new index and uses and old charset instead of a new one.

Generated at Thu Feb 08 09:51:43 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.