Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5.8
-
Windows 10 Enterprise
Description
If a row in a system versioned table is updated (by triggering an ON DELETE SET NULL constraint on one foreign key) and then the row is deleted (by triggering an ON DELETE CASCADE constraint on another foreign key) in the same transaction, then a duplicate entry error is thrown.
Reproducible example:
CREATE TABLE `a` (
|
`id` int(6) NOT NULL AUTO_INCREMENT,
|
`start_txn_id` bigint(20) unsigned GENERATED ALWAYS AS ROW START,
|
`end_txn_id` bigint(20) unsigned GENERATED ALWAYS AS ROW END,
|
PERIOD FOR SYSTEM_TIME(start_txn_id, end_txn_id),
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 WITH SYSTEM VERSIONING;
|
|
|
CREATE TABLE `b` (
|
`id` int(6) NOT NULL AUTO_INCREMENT,
|
`start_txn_id` bigint(20) unsigned GENERATED ALWAYS AS ROW START,
|
`end_txn_id` bigint(20) unsigned GENERATED ALWAYS AS ROW END,
|
PERIOD FOR SYSTEM_TIME(start_txn_id, end_txn_id),
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 WITH SYSTEM VERSIONING;
|
|
|
CREATE TABLE `c` (
|
`id` int(6) NOT NULL AUTO_INCREMENT,
|
`start_txn_id` bigint(20) unsigned GENERATED ALWAYS AS ROW START,
|
`end_txn_id` bigint(20) unsigned GENERATED ALWAYS AS ROW END,
|
PERIOD FOR SYSTEM_TIME(start_txn_id, end_txn_id),
|
`a_id` int,
|
`b_id` int,
|
FOREIGN KEY (a_id) REFERENCES a(id) ON UPDATE CASCADE ON DELETE CASCADE,
|
FOREIGN KEY (b_id) REFERENCES b(id) ON UPDATE CASCADE ON DELETE SET NULL,
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 WITH SYSTEM VERSIONING;
|
|
|
insert into a(id) values (1);
|
insert into b(id) values (1);
|
insert into c(id, a_id, b_id) values (1, 1, 1);
|
Now if we do the following:
start transaction;
|
delete from b where id = 1;
|
delete from a where id = 1;
|
commit;
|
After executing the third line, the following error is returned: ERROR 1761 (23000): Foreign key constraint for table 'a', record '1-18446744073709551615' would lead to a duplicate entry in table 'c', key 'PRIMARY'
This error occurs because of versioned tables:
MariaDB [test]> start transaction;
|
Query OK, 0 rows affected (0.001 sec)
|
|
|
MariaDB [test]> select * from c for system_time all;
|
+----+--------------+----------------------+------+------+
|
| id | start_txn_id | end_txn_id | a_id | b_id |
|
+----+--------------+----------------------+------+------+
|
| 1 | 611545 | 18446744073709551615 | 1 | 1 |
|
+----+--------------+----------------------+------+------+
|
1 row in set (0.000 sec)
|
|
|
MariaDB [test]> delete from b where id = 1;
|
Query OK, 1 row affected (0.001 sec)
|
|
|
MariaDB [test]> select * from c for system_time all;
|
+----+--------------+----------------------+------+------+
|
| id | start_txn_id | end_txn_id | a_id | b_id |
|
+----+--------------+----------------------+------+------+
|
| 1 | 611545 | 611548 | 1 | 1 |
|
| 1 | 611548 | 18446744073709551615 | 1 | NULL |
|
+----+--------------+----------------------+------+------+
|
2 rows in set (0.001 sec)
|
When we run the command delete from a where id = 1; the end_txn_id of the second row in table c is set to the ID of the transaction that we're currently in. Because this is the same transaction ID as the ID in the end_txn_id of the first row, a duplicate entry error occurs. In this scenario, shouldn't MariaDB just delete the second row instead??
A solution to this particular example is to reverse the order of the delete statements (delete the row from table a first before deleting from table b), however this isn't always feasible.
Attachments
Issue Links
- relates to
-
MDEV-16226 TRX_ID-based System Versioning refactoring
-
- Stalled
-