Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-27280

server crashes on CHECK TABLE after COLLATE change for utf8mb4

Details

    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

          Activity

            alice Alice Sherepa added a comment -

            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
            

            alice Alice Sherepa added a comment - 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

            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.

            kevg Eugene Kosov (Inactive) added a comment - 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.

            People

              thiru Thirunarayanan Balathandayuthapani
              sbedorf Stefan Bedorf
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.