Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.16, 10.3.7
-
None
-
Tested on:
CentOS 7 (10.3.7)
Ubuntu 18.04 (10.2.16)
Description
The linked bug (MDEV-15042) appears to not be fixed, or has regressed in the affected versions. The provided test script (also below) still produces the 'Cant' find record in <tablename>' as opposed to the more helpful FK error.
Test Script:
CREATE DATABASE `testdb`; |
USE `testdb`; |
|
|
CREATE TABLE `parent` ( |
`id` INT PRIMARY KEY AUTO_INCREMENT |
) ENGINE=INNODB;
|
|
|
CREATE TABLE `child` ( |
-- crucial bit: FK has to be primary key (or if composite, at the start of the primary key) |
`parent_id` INT NOT NULL PRIMARY KEY, |
`id` INT NOT NULL, |
CONSTRAINT `fk_c_parent` FOREIGN KEY (`parent_id`) REFERENCES `parent`(`id`) ON UPDATE CASCADE ON DELETE CASCADE |
) ENGINE=INNODB;
|
|
|
-- This works as expected, producing a 1452 error (foreign key constraint fails)
|
|
|
INSERT INTO `child` (`id`, `parent_id`) VALUES (1, 1); |
|
|
-- ERROR 1032 (HY000): Can't find record in 'child'
|
|
|
INSERT INTO `child` (`id`, `parent_id`) VALUES (1, 1) ON DUPLICATE KEY UPDATE `id` = VALUES(`id`); |
Attachments
Issue Links
- duplicates
-
-
- Closed
-
-
-
- Closed
-
- relates to
-
-
- Closed
-
Activity
It is true that the test case that was added in the MDEV-13206 commit was removed in a merge. However, I just updated MDEV-13206 with a note that an MDEV-17614 after-merge fix removed the problematic code that was changed in the MDEV-13206 fix. Thus, I do not think that we need to add back the accidentally removed test case.
Apart from MDEV-17614 or MDEV-17073, I might also suspect that this bug was fixed by MDEV-15199, which reverted another change that MariaDB Server 10.2 inherited from MySQL 5.7. However, that change is present already in 10.2.13, and this report was filed against 10.2.16.
However, even with the parent commit of the MDEV-17614 after-merge fix the following test passes for me:
--source include/have_innodb.inc
|
|
|
CREATE TABLE parent (id INT PRIMARY KEY AUTO_INCREMENT) ENGINE=INNODB; |
|
|
CREATE TABLE child ( |
parent_id INT PRIMARY KEY, |
id INT NOT NULL, |
CONSTRAINT fk_c_parent FOREIGN KEY (parent_id) REFERENCES parent(id) ON UPDATE CASCADE ON DELETE CASCADE |
) ENGINE=INNODB;
|
--error ER_NO_REFERENCED_ROW_2
|
INSERT INTO child (id, parent_id) VALUES (1, 1); |
--error ER_NO_REFERENCED_ROW_2
|
INSERT INTO child (id, parent_id) VALUES (1, 1) ON DUPLICATE KEY UPDATE id = VALUES(id); |
DROP TABLE child,parent; |
In MariaDB 10.2.17 and 10.2.18, the second INSERT would fail with ER_KEY_NOT_FOUND (1032) instead of ER_NO_REFERENCED_ROW_2 (1452). So, the MDEV-15042 bug indeed is present there.
In MariaDB 10.2.19, 10.2.20 and 10.2.26, the test passed.
While we can say that any trace of MDEV-13206 was removed by MDEV-17614, we still must find out what exactly seems to have fixed this regression in MariaDB 10.2.19. It turns out that the answer is logical: MDEV-17073 (which disabled the problematic MySQL 5.7.4 code that MDEV-17614 finally removed).
Even with the MDEV-17073 fix, the problem can be repeated with anything up to MariaDB 10.2.26 (not 10.2.27) by starting the test with an extra argument:
./mtr --mysqld=--log-bin
|
I think that it is correct to say that this bug was fixed by MDEV-17614.
However, the test case is for some reason lost starting from 10.2. Based on investigation merge commit 4771ae4b22d2bdef0aafc563570c71d4636a2493 removed it. (test case is e.g. in git show 60f51a).
Tested with
- 10.2.24 (commit e0271a7)
- 10.3.14 (commit ab7b9cf)
- 10.4.3 (commit 6471c15)
All produced correct output:
CREATE TABLE parent (
|
id INT PRIMARY KEY AUTO_INCREMENT
|
) ENGINE=INNODB;
|
CREATE TABLE child (
|
parent_id INT NOT NULL PRIMARY KEY,
|
id INT NOT NULL,
|
CONSTRAINT fk_c_parent FOREIGN KEY (parent_id) REFERENCES parent (id) ON UPDATE CASCADE ON DELETE CASCADE
|
) ENGINE=INNODB;
|
INSERT INTO child (id, parent_id) VALUES (1, 1);
|
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fk_c_parent` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
|
INSERT INTO child (id, parent_id) VALUES (1, 1) ON DUPLICATE KEY UPDATE id = VALUES(id);
|
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fk_c_parent` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
|
select * from parent;
|
id
|
select * from child;
|
parent_id id
|
drop table child, parent;
|
@serg - I don't think this is fixed looking at the 10.4 branch in its current state (on GitHub) at commit https://github.com/MariaDB/server/tree/c8f8d5ceb7227ff825f86ea2d61dd1edd9e610b2
Might this be a duplicate of MDEV-17521? Please also see added comment to said issue.
Thanks for the report! reproducible on 10.2 (058554027f6696775), 10.3 (commit 1748a31ae8d69e4)
MDEV-13206was an incorrect fix of something that was correctly fixed inMDEV-17073in a special case (no statement-based replication), andMDEV-17614in the general case.MDEV-15042was an incorrect attempt to fix a regression caused byMDEV-13206. AllMDEV-13206andMDEV-15042changes were reverted inMDEV-17614.