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

Data corruption after partition maintenance for table using legacy DATETIME storage.

Details

    Description

      After upgrading to 10.4 we saw data corruption in some of our partitioned tables. Turns out they were using the older DATETIME storage, and adding or removing partitions from them corrupted the existing datetime data, and caused other columns to be corrupted after additional inserts/updates.

      I think this is due to the fact that the ALTER TABLE ADD/DROP PARTITION statement triggers the automatic upgrade in 10.4 for the datetime columns. However since it is targeted at the partition that is being dropped/added, it does not actually perform the data upgrade on the datetime columns. Instead it might be trying to use the old data as if it were upgraded.

      The following script creates a table using the old formats by setting the global to disable the newer storage format. It then adds some data and adds a partition. In my tests the dates are corrupted directly after the partition is added. After the insert, even the varchar column has become corrupted.

      SET GLOBAL mysql56_temporal_format=off;
      		 
       
      		 
      CREATE OR REPLACE TABLE `t` (
      		 
          `pid` INT(5) NOT NULL,
      		 
          `createdAt` DATETIME NOT NULL DEFAULT NOW(),
      		 
          `comment` VARCHAR(20)
      		 
      ) ENGINE=INNODB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
      		 
      PARTITION BY LIST(pid)
      		 
      (PARTITION p1 VALUES IN (1), PARTITION p2 VALUES IN (2));
      		 
       
      		 
      INSERT INTO `t` (`pid`, `comment`)
      		 
      VALUES (1, 'First'), (2, 'Second');
      		 
       
      		 
      SELECT * FROM `t`;
      		 
       
      		 
      SET GLOBAL mysql56_temporal_format=on;
      		 
       
      		 
      ALTER TABLE `t` ADD PARTITION (PARTITION p3 VALUES IN (3));
      		 
       
      		 
      SELECT * FROM `t`;
      		 
       
      		 
      INSERT INTO `t` (`pid`, `comment`)
      		 
      VALUES (1, 'First'), (2, 'Second');
      		 
       
      		 
      SELECT * FROM `t`;

      OUTPUT

      pid	createdAt	comment
      		 
      1	2019-11-01 04:01:17	First
      		 
      2	2019-11-01 04:01:17	Second
      		 
      pid	createdAt	comment
      		 
      1	9271-10-19 17:45:29	First
      		 
      2	9271-10-19 17:45:29	Second
      		 
      pid	createdAt	comment
      		 
      1	9271-10-19 17:45:29	First
      		 
      1	1070-04-28 22:25:28	st\0\0\0
      		 
      2	9271-10-19 17:45:29	Second
      		 
      2	2253-01-22 06:25:28	ondond

      Attachments

        Issue Links

          duplicates

          Bug - A problem which impairs or prevents the functions of the product. MDEV-18147 [Draft] Assertion `templ->mysql_col_len >= len' failed in row_sel_field_store_in_mysql_format_func

          • Major - Major loss of function.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23167 Server crashes upon HANDLER READ from partitioned table

          • Major - Major loss of function.
          • Confirmed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25603 Assertion `templ->mysql_col_len == len' failed in row_sel_field_store_in_mysql_format_func

          • Major - Major loss of function.
          • Confirmed

          Activity

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova added a comment -

            Thanks a lot for the report and test case. Reproducible as described.

            Additionally, a debug build of 10.4 produces an assertion failure:

            10.4 0339cbe2

            		 
            mysqld: /data/src/10.4/storage/innobase/row/row0sel.cc:2861: void row_sel_field_store_in_mysql_format_func(byte*, const mysql_row_templ_t*, const dict_index_t*, ulint, const byte*, ulint): Assertion `templ->mysql_col_len == len' failed.
            		 
            191106  0:56:52 [ERROR] mysqld got signal 6 ;
            		 
             
            		 
            #7  0x00007f268f2bbf12 in __GI___assert_fail (assertion=0x558f18096c17 "templ->mysql_col_len == len", file=0x558f18096178 "/data/src/10.4/storage/innobase/row/row0sel.cc", line=2861, function=0x558f18099d60 <row_sel_field_store_in_mysql_format_func(unsigned char*, mysql_row_templ_t const*, dict_index_t const*, unsigned long, unsigned char const*, unsigned long)::__PRETTY_FUNCTION__> "void row_sel_field_store_in_mysql_format_func(byte*, const mysql_row_templ_t*, const dict_index_t*, ulint, const byte*, ulint)") at assert.c:101
            		 
            #8  0x0000558f178a88dd in row_sel_field_store_in_mysql_format_func (dest=0x7f263814cc35 "\200", templ=0x7f263807e2f0, index=0x7f26381501a8, field_no=4, data=0x7f2688e54096 "\200", len=8) at /data/src/10.4/storage/innobase/row/row0sel.cc:2861
            		 
            #9  0x0000558f178a9160 in row_sel_store_mysql_field (mysql_rec=0x7f263814cc30 "\377\001", prebuilt=0x7f2638137738, rec=0x7f2688e5407f "", index=0x7f26381501a8, offsets=0x7f2688689bf0, field_no=4, templ=0x7f263807e2f0) at /data/src/10.4/storage/innobase/row/row0sel.cc:3006
            		 
            #10 0x0000558f178a9866 in row_sel_store_mysql_rec (mysql_rec=0x7f263814cc30 "\377\001", prebuilt=0x7f2638137738, rec=0x7f2688e5407f "", vrow=0x0, rec_clust=false, index=0x7f26381501a8, offsets=0x7f2688689bf0) at /data/src/10.4/storage/innobase/row/row0sel.cc:3141
            		 
            #11 0x0000558f178b045d in row_search_mvcc (buf=0x7f263814cc30 "\377\001", mode=PAGE_CUR_G, prebuilt=0x7f2638137738, match_mode=0, direction=0) at /data/src/10.4/storage/innobase/row/row0sel.cc:5488
            		 
            #12 0x0000558f176c3184 in ha_innobase::index_read (this=0x7f263807f208, buf=0x7f263814cc30 "\377\001", key_ptr=0x0, key_len=0, find_flag=HA_READ_AFTER_KEY) at /data/src/10.4/storage/innobase/handler/ha_innodb.cc:9305
            		 
            #13 0x0000558f176c418c in ha_innobase::index_first (this=0x7f263807f208, buf=0x7f263814cc30 "\377\001") at /data/src/10.4/storage/innobase/handler/ha_innodb.cc:9678
            		 
            #14 0x0000558f176c4364 in ha_innobase::rnd_next (this=0x7f263807f208, buf=0x7f263814cc30 "\377\001") at /data/src/10.4/storage/innobase/handler/ha_innodb.cc:9771
            		 
            #15 0x0000558f174780de in handler::ha_rnd_next (this=0x7f263807f208, buf=0x7f263814cc30 "\377\001") at /data/src/10.4/sql/handler.cc:2826
            		 
            #16 0x0000558f17cbfe47 in ha_partition::rnd_next (this=0x7f263800b3a8, buf=0x7f263814cc30 "\377\001") at /data/src/10.4/sql/ha_partition.cc:5084
            		 
            #17 0x0000558f1747808c in handler::ha_rnd_next (this=0x7f263800b3a8, buf=0x7f263814cc30 "\377\001") at /data/src/10.4/sql/handler.cc:2826
            		 
            #18 0x0000558f1761d2e4 in rr_sequential (info=0x7f2638015ea0) at /data/src/10.4/sql/records.cc:477
            		 
            #19 0x0000558f17064a59 in READ_RECORD::read_record (this=0x7f2638015ea0) at /data/src/10.4/sql/records.h:69
            		 
            #20 0x0000558f17196821 in join_init_read_record (tab=0x7f2638015dd8) at /data/src/10.4/sql/sql_select.cc:21129
            		 
            #21 0x0000558f17194404 in sub_select (join=0x7f26380147c0, join_tab=0x7f2638015dd8, end_of_records=false) at /data/src/10.4/sql/sql_select.cc:20185
            		 
            #22 0x0000558f171938f5 in do_select (join=0x7f26380147c0, procedure=0x0) at /data/src/10.4/sql/sql_select.cc:19726
            		 
            #23 0x0000558f17168a61 in JOIN::exec_inner (this=0x7f26380147c0) at /data/src/10.4/sql/sql_select.cc:4386
            		 
            #24 0x0000558f17167b9e in JOIN::exec (this=0x7f26380147c0) at /data/src/10.4/sql/sql_select.cc:4168
            		 
            #25 0x0000558f171692b3 in mysql_select (thd=0x7f2638000af0, tables=0x7f2638013788, wild_num=1, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f2638014798, unit=0x7f2638004a20, select_lex=0x7f26380131c0) at /data/src/10.4/sql/sql_select.cc:4600
            		 
            #26 0x0000558f17159224 in handle_select (thd=0x7f2638000af0, lex=0x7f2638004958, result=0x7f2638014798, setup_tables_done_option=0) at /data/src/10.4/sql/sql_select.cc:420
            		 
            #27 0x0000558f1711fa1c in execute_sqlcom_select (thd=0x7f2638000af0, all_tables=0x7f2638013788) at /data/src/10.4/sql/sql_parse.cc:6357
            		 
            #28 0x0000558f17115103 in mysql_execute_command (thd=0x7f2638000af0) at /data/src/10.4/sql/sql_parse.cc:3899
            		 
            #29 0x0000558f17123b29 in mysql_parse (thd=0x7f2638000af0, rawbuf=0x7f2638013138 "SELECT * FROM `t`", length=17, parser_state=0x7f268868c170, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:7898
            		 
            #30 0x0000558f1710ed06 in dispatch_command (command=COM_QUERY, thd=0x7f2638000af0, packet=0x7f263819e0e1 "", packet_length=17, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:1842
            		 
            #31 0x0000558f1710d393 in do_command (thd=0x7f2638000af0) at /data/src/10.4/sql/sql_parse.cc:1360
            		 
            #32 0x0000558f17295241 in do_handle_one_connection (connect=0x558f1a2a2230) at /data/src/10.4/sql/sql_connect.cc:1412
            		 
            #33 0x0000558f17294f90 in handle_one_connection (arg=0x558f1a2a2230) at /data/src/10.4/sql/sql_connect.cc:1316
            		 
            #34 0x0000558f17c98723 in pfs_spawn_thread (arg=0x558f1a1c6e60) at /data/src/10.4/storage/perfschema/pfs.cc:1862
            		 
            #35 0x00007f2690e314a4 in start_thread (arg=0x7f268868d700) at pthread_create.c:456
            		 
            #36 0x00007f268f378d0f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:97

            Neither corruption nor the assertion failure are reproducible on 10.3.

            elenst Elena Stepanova added a comment - Thanks a lot for the report and test case. Reproducible as described. Additionally, a debug build of 10.4 produces an assertion failure: 10.4 0339cbe2 mysqld: /data/src/10.4/storage/innobase/row/row0sel.cc:2861: void row_sel_field_store_in_mysql_format_func(byte*, const mysql_row_templ_t*, const dict_index_t*, ulint, const byte*, ulint): Assertion `templ->mysql_col_len == len' failed. 191106 0:56:52 [ERROR] mysqld got signal 6 ;   #7 0x00007f268f2bbf12 in __GI___assert_fail (assertion=0x558f18096c17 "templ->mysql_col_len == len", file=0x558f18096178 "/data/src/10.4/storage/innobase/row/row0sel.cc", line=2861, function=0x558f18099d60 <row_sel_field_store_in_mysql_format_func(unsigned char*, mysql_row_templ_t const*, dict_index_t const*, unsigned long, unsigned char const*, unsigned long)::__PRETTY_FUNCTION__> "void row_sel_field_store_in_mysql_format_func(byte*, const mysql_row_templ_t*, const dict_index_t*, ulint, const byte*, ulint)") at assert.c:101 #8 0x0000558f178a88dd in row_sel_field_store_in_mysql_format_func (dest=0x7f263814cc35 "\200", templ=0x7f263807e2f0, index=0x7f26381501a8, field_no=4, data=0x7f2688e54096 "\200", len=8) at /data/src/10.4/storage/innobase/row/row0sel.cc:2861 #9 0x0000558f178a9160 in row_sel_store_mysql_field (mysql_rec=0x7f263814cc30 "\377\001", prebuilt=0x7f2638137738, rec=0x7f2688e5407f "", index=0x7f26381501a8, offsets=0x7f2688689bf0, field_no=4, templ=0x7f263807e2f0) at /data/src/10.4/storage/innobase/row/row0sel.cc:3006 #10 0x0000558f178a9866 in row_sel_store_mysql_rec (mysql_rec=0x7f263814cc30 "\377\001", prebuilt=0x7f2638137738, rec=0x7f2688e5407f "", vrow=0x0, rec_clust=false, index=0x7f26381501a8, offsets=0x7f2688689bf0) at /data/src/10.4/storage/innobase/row/row0sel.cc:3141 #11 0x0000558f178b045d in row_search_mvcc (buf=0x7f263814cc30 "\377\001", mode=PAGE_CUR_G, prebuilt=0x7f2638137738, match_mode=0, direction=0) at /data/src/10.4/storage/innobase/row/row0sel.cc:5488 #12 0x0000558f176c3184 in ha_innobase::index_read (this=0x7f263807f208, buf=0x7f263814cc30 "\377\001", key_ptr=0x0, key_len=0, find_flag=HA_READ_AFTER_KEY) at /data/src/10.4/storage/innobase/handler/ha_innodb.cc:9305 #13 0x0000558f176c418c in ha_innobase::index_first (this=0x7f263807f208, buf=0x7f263814cc30 "\377\001") at /data/src/10.4/storage/innobase/handler/ha_innodb.cc:9678 #14 0x0000558f176c4364 in ha_innobase::rnd_next (this=0x7f263807f208, buf=0x7f263814cc30 "\377\001") at /data/src/10.4/storage/innobase/handler/ha_innodb.cc:9771 #15 0x0000558f174780de in handler::ha_rnd_next (this=0x7f263807f208, buf=0x7f263814cc30 "\377\001") at /data/src/10.4/sql/handler.cc:2826 #16 0x0000558f17cbfe47 in ha_partition::rnd_next (this=0x7f263800b3a8, buf=0x7f263814cc30 "\377\001") at /data/src/10.4/sql/ha_partition.cc:5084 #17 0x0000558f1747808c in handler::ha_rnd_next (this=0x7f263800b3a8, buf=0x7f263814cc30 "\377\001") at /data/src/10.4/sql/handler.cc:2826 #18 0x0000558f1761d2e4 in rr_sequential (info=0x7f2638015ea0) at /data/src/10.4/sql/records.cc:477 #19 0x0000558f17064a59 in READ_RECORD::read_record (this=0x7f2638015ea0) at /data/src/10.4/sql/records.h:69 #20 0x0000558f17196821 in join_init_read_record (tab=0x7f2638015dd8) at /data/src/10.4/sql/sql_select.cc:21129 #21 0x0000558f17194404 in sub_select (join=0x7f26380147c0, join_tab=0x7f2638015dd8, end_of_records=false) at /data/src/10.4/sql/sql_select.cc:20185 #22 0x0000558f171938f5 in do_select (join=0x7f26380147c0, procedure=0x0) at /data/src/10.4/sql/sql_select.cc:19726 #23 0x0000558f17168a61 in JOIN::exec_inner (this=0x7f26380147c0) at /data/src/10.4/sql/sql_select.cc:4386 #24 0x0000558f17167b9e in JOIN::exec (this=0x7f26380147c0) at /data/src/10.4/sql/sql_select.cc:4168 #25 0x0000558f171692b3 in mysql_select (thd=0x7f2638000af0, tables=0x7f2638013788, wild_num=1, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f2638014798, unit=0x7f2638004a20, select_lex=0x7f26380131c0) at /data/src/10.4/sql/sql_select.cc:4600 #26 0x0000558f17159224 in handle_select (thd=0x7f2638000af0, lex=0x7f2638004958, result=0x7f2638014798, setup_tables_done_option=0) at /data/src/10.4/sql/sql_select.cc:420 #27 0x0000558f1711fa1c in execute_sqlcom_select (thd=0x7f2638000af0, all_tables=0x7f2638013788) at /data/src/10.4/sql/sql_parse.cc:6357 #28 0x0000558f17115103 in mysql_execute_command (thd=0x7f2638000af0) at /data/src/10.4/sql/sql_parse.cc:3899 #29 0x0000558f17123b29 in mysql_parse (thd=0x7f2638000af0, rawbuf=0x7f2638013138 "SELECT * FROM `t`", length=17, parser_state=0x7f268868c170, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:7898 #30 0x0000558f1710ed06 in dispatch_command (command=COM_QUERY, thd=0x7f2638000af0, packet=0x7f263819e0e1 "", packet_length=17, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:1842 #31 0x0000558f1710d393 in do_command (thd=0x7f2638000af0) at /data/src/10.4/sql/sql_parse.cc:1360 #32 0x0000558f17295241 in do_handle_one_connection (connect=0x558f1a2a2230) at /data/src/10.4/sql/sql_connect.cc:1412 #33 0x0000558f17294f90 in handle_one_connection (arg=0x558f1a2a2230) at /data/src/10.4/sql/sql_connect.cc:1316 #34 0x0000558f17c98723 in pfs_spawn_thread (arg=0x558f1a1c6e60) at /data/src/10.4/storage/perfschema/pfs.cc:1862 #35 0x00007f2690e314a4 in start_thread (arg=0x7f268868d700) at pthread_create.c:456 #36 0x00007f268f378d0f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:97 Neither corruption nor the assertion failure are reproducible on 10.3.
            alice Alice Sherepa added a comment -

            If I use Aria instead of InnoDB: "INSERT INTO `t` (`pid`, `comment`) VALUES (1, 'First'), (2, 'Second')' failed: 1194: Table 't' is marked as crashed and should be repaired"

            alice Alice Sherepa added a comment - If I use Aria instead of InnoDB: "INSERT INTO `t` (`pid`, `comment`) VALUES (1, 'First'), (2, 'Second')' failed: 1194: Table 't' is marked as crashed and should be repaired"
            alice Alice Sherepa added a comment -

            Currently works as expected on 10.4 (113bef50e33a838c5cb63) -10.11,11.0

            alice Alice Sherepa added a comment - Currently works as expected on 10.4 (113bef50e33a838c5cb63) -10.11,11.0

            People

              bar Alexander Barkov
              jacob.williams Jacob Williams
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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