Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.4.13, 10.4(EOL), 10.5
-
None
-
Ubuntu 18.04 x86_64
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.
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| 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. {code:sql} 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; {code} At this point, the following error is returned: {quote} Cannot delete or update a parent row: a foreign key constraint fails (`buergerkonto`.`TableA`, CONSTRAINT `fk_TableA_TableB` FOREIGN KEY (`TableBId`) REFERENCES `TableB` (`Id`)) {quote} OK, so let's roll back and look at the data: {code:sql} ROLLBACK; SELECT * FROM TableA WHERE TableBId = 54321; {code} {noformat} +-------+----------+----------+---------------------+--------------+ | 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) {noformat} 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: {noformat} 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) {noformat} 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. |
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. {code:sql} 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; {code} At this point, the following error is returned: {quote} Cannot delete or update a parent row: a foreign key constraint fails (`buergerkonto`.`TableA`, CONSTRAINT `fk_TableA_TableB` FOREIGN KEY (`TableBId`) REFERENCES `TableB` (`Id`)) {quote} 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: {code:sql} ROLLBACK; SELECT * FROM TableA WHERE TableBId = 54321; {code} {noformat} +-------+----------+----------+---------------------+--------------+ | 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) {noformat} 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: {noformat} 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) {noformat} 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. |
| Affects Version/s | 10.4 [ 22408 ] | |
| Affects Version/s | 10.5 [ 23123 ] |
| Fix Version/s | 10.4 [ 22408 ] | |
| Fix Version/s | 10.5 [ 23123 ] |
| Assignee | Alexander Barkov [ bar ] |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Link |
This issue relates to |
| Component/s | Virtual Columns [ 10803 ] |
| Workflow | MariaDB v3 [ 110517 ] | MariaDB v4 [ 144270 ] |
| Fix Version/s | 10.4 [ 22408 ] |
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) engine = innodb;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