[MDEV-20940] Data corruption after partition maintenance for table using legacy DATETIME storage. - Jira

Details

Type: Bug
Status: Confirmed (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.4.8, 10.4(EOL), 10.5, 10.6, 10.7(EOL)
Fix Version/s: 10.5, 10.6
Component/s: Partitioning, Storage Engine - InnoDB, Temporal Types
Labels:
- datetime
Environment:
Ubuntu 19.04, AWS Linux, Docker

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

MDEV-18147 [Draft] Assertion `templ->mysql_col_len >= len' failed in row_sel_field_store_in_mysql_format_func

Closed

relates to

MDEV-23167 Server crashes upon HANDLER READ from partitioned table

Confirmed

MDEV-25603 Assertion `templ->mysql_col_len == len' failed in row_sel_field_store_in_mysql_format_func

Confirmed

Activity

Ascending order - Click to sort in descending order

Jacob Williams created issue - 2019-11-01 04:10

Jacob Williams made changes - 2019-11-01 04:15

Field	Original Value	New Value
Affects Version/s		10.4.8 [ 23721 ]

Elena Stepanova added a comment - 2019-11-05 22:57

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.

Elena Stepanova added a comment - 2019-11-05 22:57 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.

Elena Stepanova made changes - 2019-11-05 22:58

Component/s		Temporal Types [ 11000 ]
Fix Version/s		10.4 [ 22408 ]
Assignee		Alexander Barkov [ bar ]

Elena Stepanova made changes - 2019-11-05 22:58

Status

Open [ 1 ]

Confirmed [ 10101 ]

Elena Stepanova made changes - 2019-11-05 22:58

Link

This issue duplicates ~~MDEV-18147~~ [ ~~MDEV-18147~~ ]

Alexander Barkov made changes - 2020-01-29 11:14

Priority

Critical [ 2 ]

Major [ 3 ]

Alice Sherepa made changes - 2020-07-14 10:33

Link

This issue relates to MDEV-23167 [ MDEV-23167 ]

Alice Sherepa made changes - 2020-07-14 10:36

Affects Version/s

10.5 [ 23123 ]

Alice Sherepa made changes - 2020-07-14 10:36

Fix Version/s

10.5 [ 23123 ]

Alice Sherepa added a comment - 2020-07-14 10:38

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 Sherepa added a comment - 2020-07-14 10:38 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 Sherepa made changes - 2021-05-05 13:13

Link

This issue relates to MDEV-25603 [ MDEV-25603 ]

Sergei Golubchik made changes - 2021-12-06 21:36

Workflow

MariaDB v3 [ 100741 ]

MariaDB v4 [ 144184 ]

Alice Sherepa made changes - 2022-01-24 12:38

Affects Version/s		10.6 [ 24028 ]
Affects Version/s		10.7 [ 24805 ]

Alice Sherepa made changes - 2022-01-24 12:38

Fix Version/s		10.6 [ 24028 ]
Fix Version/s		10.7 [ 24805 ]

Julien Fritsch made changes - 2023-03-03 17:31

Fix Version/s

10.7 [ 24805 ]

Alice Sherepa added a comment - 2023-03-30 12:56

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

Alice Sherepa added a comment - 2023-03-30 12:56 Currently works as expected on 10.4 (113bef50e33a838c5cb63) -10.11,11.0

Julien Fritsch made changes - 2024-09-10 15:07

Fix Version/s

10.4 [ 22408 ]

People

Assignee:: Alexander Barkov

Reporter:: Jacob Williams

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2019-11-01 04:10

Updated:: 2024-09-10 15:07

Git Integration

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

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration