[MDEV-23018] Database corruption involving an index on a virtual column and a charset change Created: 2020-06-25  Updated: 2020-06-29

Status: Confirmed
Project: MariaDB Server
Component/s: Virtual Columns
Affects Version/s: 10.4.13, 10.4, 10.5
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Major
Reporter: Simeon Maxein Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Ubuntu 18.04 x86_64

Issue Links:
Relates
relates to MDEV-18486 Database crash on a table with indexe... Closed

 Description   

First off, sorry that I can't narrow this bug down further - for one thing, I'm not an expert on the internals of mariadb, and also, when I tried reducing the reproduction code further it started exhibiting "heisenbug" behaviour, vanishing intermittently. The code below always managed to trigger the bug though.

A little bit of background first: The use case here is a table (TableA) with "versioned" data. The ValidTo column represents the end of validity of a particular entry and is set to '2099-12-31' for the active (currently valid) entries (there can be multiple). In order to allow having a unique constraint that only checks among the active entries, I introduced the ActiveOrNull column which is 1 for active rows, and null for inactive ones. Since unique keys don't see values as duplicate as long as one column is null, tagging this column to the end of a unique key will "weaken" that key to only consider collisions among active rows.

CREATE TABLE `TableB`
 
(
 
    `Id` int NOT NULL PRIMARY KEY
 
) ENGINE = InnoDB;
 
 
 
CREATE TABLE `TableA`
 
(
 
    `Id`           int          NOT NULL,
 
    `TableBId`     int          NOT NULL,
 
    `Name`         varchar(255) CHARSET utf8 NOT NULL,
 
    `ValidTo`      datetime     NOT NULL,
 
    `ActiveOrNull` TINYINT(1) AS (CASE WHEN `ValidTo` < '2099-01-01' THEN NULL ELSE 1 END) VIRTUAL,
 
    PRIMARY KEY (`Id`),
 
    UNIQUE KEY `uk_a_TableA_TableBId` (`TableBId`, `ActiveOrNull`),
 
    CONSTRAINT `fk_TableA_TableB` FOREIGN KEY (`TableBId`) REFERENCES `TableB` (`Id`)
 
) ENGINE = InnoDB;
 
 
 
ALTER TABLE `TableA`
 
    MODIFY `Name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_german2_ci NOT NULL;
 
 
 
BEGIN;
 
INSERT INTO TableB (Id) VALUES (54321);
 
INSERT INTO TableA (Id, TableBId, Name, ValidTo) VALUES (12345, 54321, 'SomeName', '2099-12-31');
 
COMMIT;
 
 
 
BEGIN;
 
DELETE FROM TableA WHERE Id = 12345;
 
DELETE FROM TableB WHERE Id = 54321;

At this point, the following error is returned:

Cannot delete or update a parent row: a foreign key constraint fails (`buergerkonto`.`TableA`, CONSTRAINT `fk_TableA_TableB` FOREIGN KEY (`TableBId`) REFERENCES `TableB` (`Id`))

This is unexpected - didn't we just delete the only row which might have referred to that one? Let's roll back and look at the data:

ROLLBACK;
 
 
 
SELECT * FROM TableA WHERE TableBId = 54321;


+-------+----------+----------+---------------------+--------------+
 
| Id    | TableBId | Name     | ValidTo             | ActiveOrNull |
 
+-------+----------+----------+---------------------+--------------+
 
| 12345 |    54321 | SomeName | 2099-12-31 00:00:00 |            1 |
 
| 12345 |    54321 | SomeName | 2099-12-31 00:00:00 |            1 |
 
+-------+----------+----------+---------------------+--------------+
 
2 rows in set (0.001 sec)

Whoops, we're getting two rows with the same primary key! From what I can make out, this looks like a corruption in the uk_a_TableA_TableBId index, because querying the table via the primary key looks fine:

SELECT * FROM TableA;
 
+-------+----------+----------+---------------------+--------------+
 
| Id    | TableBId | Name     | ValidTo             | ActiveOrNull |
 
+-------+----------+----------+---------------------+--------------+
 
| 12345 |    54321 | SomeName | 2099-12-31 00:00:00 |            1 |
 
+-------+----------+----------+---------------------+--------------+
 
1 row in set (0.001 sec)

I'm not sure how best to work around this issue since I don't really undestand what is going on. Removing the ALTER TABLE makes the problem disappear. Turning the VIRTUAL column into a PERSISTENT one also makes the problem disappear, and is probably what I'll do for now. I'd feel safer knowing that it's an actual solution though.

 Comments   
Comment by Alice Sherepa [ 2020-06-29 ]

Thank you very much for the report and the test case!

On debug version the test terminates with the assertion `is_valid_value_slow()' failed in Datetime::Datetime.

--source include/have_innodb.inc
 
 
CREATE TABLE t1( 
	id int NOT NULL primary key, 
	t2id int NOT NULL,
 
	name varchar(255) charset utf8 not null,
 
	validto datetime not null,
 
    activeornull tinyint(1) as (case when  validto  < '2099-01-01' then null else 1 end) virtual,
 
    unique key (t2id ,activeornull )
 
) engine = innodb;
 
 
alter table  t1  modify  name  varchar(255) character set utf8mb4 collate utf8mb4_german2_ci not null;
 
insert into t1 (id, t2id, name, validto) values (12345, 54321, 'somename', '2099-12-31');
 
delete from t1 where id = 12345;

10.4 d4d42a6ad07d4b6a3ea3f4f5ae

 
#3  <signal handler called>
 
#4  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:51
 
#5  0x00007f61d9454801 in __GI_abort () at abort.c:79
 
#6  0x00007f61d944439a in __assert_fail_base (fmt=0x7f61d95cb7d8 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=assertion@entry=0x5591d0f35458 "is_valid_value_slow()", file=file@entry=0x5591d0f35410 "/10.4/sql/sql_type.h", line=line@entry=2064, function=function@entry=0x5591d0f358e0 <Datetime::Datetime(THD*, Item*, date_mode_t)::__PRETTY_FUNCTION__> "Datetime::Datetime(THD*, Item*, date_mode_t)") at assert.c:92
 
#7  0x00007f61d9444412 in __GI___assert_fail (assertion=0x5591d0f35458 "is_valid_value_slow()", file=0x5591d0f35410 "/10.4/sql/sql_type.h", line=2064, function=0x5591d0f358e0 <Datetime::Datetime(THD*, Item*, date_mode_t)::__PRETTY_FUNCTION__> "Datetime::Datetime(THD*, Item*, date_mode_t)") at assert.c:101
 
#8  0x00005591d0174ff4 in Datetime::Datetime (this=0x7f61cde861f0, thd=0x7f6178000d50, item=0x7f617813bca8, fuzzydate=...) at /10.4/sql/sql_type.h:2064
 
#9  0x00005591d0175fce in Item::val_datetime_packed (this=0x7f617813bca8, thd=0x7f6178000d50) at /10.4/sql/item.h:1786
 
#10 0x00005591d0637691 in Arg_comparator::compare_datetime (this=0x7f617813be80) at /10.4/sql/item_cmpfunc.cc:742
 
#11 0x00005591d064f24c in Arg_comparator::compare (this=0x7f617813be80) at /10.4/sql/item_cmpfunc.h:104
 
#12 0x00005591d063b046 in Item_func_lt::val_int (this=0x7f617813bdb8) at /10.4/sql/item_cmpfunc.cc:1823
 
#13 0x00005591d04b5b51 in Type_handler_int_result::Item_val_bool (this=0x5591d1a20b70 <type_handler_bool>, item=0x7f617813bdb8) at /10.4/sql/sql_type.cc:4416
 
#14 0x00005591d0175aa8 in Item::val_bool (this=0x7f617813bdb8) at /10.4/sql/item.h:1462
 
#15 0x00005591d063f57d in Item_func_case_searched::find_item (this=0x7f617813c120) at /10.4/sql/item_cmpfunc.cc:2993
 
#16 0x00005591d063f889 in Item_func_case::int_op (this=0x7f617813c120) at /10.4/sql/item_cmpfunc.cc:3043
 
#17 0x00005591d04cc0e1 in Item_func_hybrid_field_type::val_int_from_int_op (this=0x7f617813c120) at /10.4/sql/item_func.h:701
 
#18 0x00005591d04b6c24 in Type_handler_int_result::Item_func_hybrid_field_type_val_int (this=0x5591d1a20b88 <type_handler_long>, item=0x7f617813c120) at /10.4/sql/sql_type.cc:4735
 
#19 0x00005591d027c3fb in Item_func_hybrid_field_type::val_int (this=0x7f617813c120) at /10.4/sql/item_func.h:757
 
#20 0x00005591d061eec8 in Item::save_int_in_field (this=0x7f617813c120, field=0x7f617813b018, no_conversions=false) at /10.4/sql/item.cc:6582
 
#21 0x00005591d04b4448 in Type_handler_int_result::Item_save_in_field (this=0x5591d1a20b88 <type_handler_long>, item=0x7f617813c120, field=0x7f617813b018, no_conversions=false) at /10.4/sql/sql_type.cc:3672
 
#22 0x00005591d061ef73 in Item::save_in_field (this=0x7f617813c120, field=0x7f617813b018, no_conversions=false) at /10.4/sql/item.cc:6592
 
#23 0x00005591d03d1fc0 in TABLE::update_virtual_field (this=0x7f6178144890, vf=0x7f617813b018) at /10.4/sql/table.cc:8390
 
#24 0x00005591d0857600 in innobase_get_computed_value (row=0x7f6178130a40, col=0x7f617812f180, index=0x7f617812f990, local_heap=0x7f61cde872a8, heap=0x7f617813cbc0, ifield=0x0, thd=0x7f6178000d50, mysql_table=0x7f6178144890, mysql_rec=0x7f61780633e0 '\217' <repeats 200 times>..., old_table=0x0, parent_update=0x0, foreign=0x0) at /10.4/storage/innobase/handler/ha_innodb.cc:20877
 
#25 0x00005591d0a45a76 in row_upd_store_v_row (node=0x7f6178061410, update=0x0, thd=0x7f6178000d50, mysql_table=0x7f6178144890) at /10.4/storage/innobase/row/row0upd.cc:2172
 
#26 0x00005591d0a45d21 in row_upd_store_row (node=0x7f6178061410, thd=0x7f6178000d50, mysql_table=0x7f6178144890) at /10.4/storage/innobase/row/row0upd.cc:2240
 
#27 0x00005591d0a48d6e in row_upd_del_mark_clust_rec (node=0x7f6178061410, index=0x7f617812f990, offsets=0x7f61cde87860, thr=0x7f61780617a8, referenced=0, foreign=false, mtr=0x7f61cde87ac0) at /10.4/storage/innobase/row/row0upd.cc:2983
 
#28 0x00005591d0a49886 in row_upd_clust_step (node=0x7f6178061410, thr=0x7f61780617a8) at /10.4/storage/innobase/row/row0upd.cc:3170
 
#29 0x00005591d0a49e5c in row_upd (node=0x7f6178061410, thr=0x7f61780617a8) at /10.4/storage/innobase/row/row0upd.cc:3299
 
#30 0x00005591d0a4a422 in row_upd_step (thr=0x7f61780617a8) at /10.4/storage/innobase/row/row0upd.cc:3443
 
#31 0x00005591d09e811b in row_update_for_mysql (prebuilt=0x7f617814a2e0) at /10.4/storage/innobase/row/row0mysql.cc:1888
 
#32 0x00005591d0841cb6 in ha_innobase::delete_row (this=0x7f6178139f58, record=0x7f617813a758 "\376\071\060") at /10.4/storage/innobase/handler/ha_innodb.cc:8942
 
#33 0x00005591d0601b14 in handler::ha_delete_row (this=0x7f6178139f58, buf=0x7f617813a758 "\376\071\060") at /10.4/sql/handler.cc:6827
 
#34 0x00005591d07bce0b in TABLE::delete_row (this=0x7f6178144890) at /10.4/sql/sql_delete.cc:289
 
#35 0x00005591d07b9c64 in mysql_delete (thd=0x7f6178000d50, table_list=0x7f61780134e0, conds=0x7f6178013d58, order_list=0x7f6178005708, limit=18446744073709551615, options=0, result=0x0) at /10.4/sql/sql_delete.cc:804
 
#36 0x00005591d029aaf3 in mysql_execute_command (thd=0x7f6178000d50) at /10.4/sql/sql_parse.cc:4727
 
#37 0x00005591d02a55f3 in mysql_parse (thd=0x7f6178000d50, rawbuf=0x7f61780133f8 "delete from t1 where id = 12345", length=31, parser_state=0x7f61cde894d0, is_com_multi=false, is_next_command=false) at /10.4/sql/sql_parse.cc:7901
 
#38 0x00005591d0291b88 in dispatch_command (command=COM_QUERY, thd=0x7f6178000d50, packet=0x7f6178008751 "delete from t1 where id = 12345", packet_length=31, is_com_multi=false, is_next_command=false) at /10.4/sql/sql_parse.cc:1841
 
#39 0x00005591d0290329 in do_command (thd=0x7f6178000d50) at /10.4/sql/sql_parse.cc:1359
 
#40 0x00005591d0418bde in do_handle_one_connection (connect=0x5591d306c820) at /10.4/sql/sql_connect.cc:1412
 
#41 0x00005591d041892d in handle_one_connection (arg=0x5591d306c820) at /10.4/sql/sql_connect.cc:1316
 
#42 0x00005591d0e16968 in pfs_spawn_thread (arg=0x5591d304cea0) at /10.4/storage/perfschema/pfs.cc:1869
 
#43 0x00007f61dad2b6db in start_thread (arg=0x7f61cde8a700) at pthread_create.c:463
 
#44 0x00007f61d953588f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

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