Tested on:
CentOS 7 (10.3.7)
Ubuntu 18.04 (10.2.16)

Thanks for the report! reproducible on 10.2 (058554027f6696775), 10.3 (commit 1748a31ae8d69e4)

Might this be a duplicate of MDEV-17521? Please also see added comment to said issue.

@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

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;

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).

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.

MDEV-13206 was an incorrect fix of something that was correctly fixed in MDEV-17073 in a special case (no statement-based replication), and MDEV-17614 in the general case.

MDEV-15042 was an incorrect attempt to fix a regression caused by MDEV-13206. All MDEV-13206 and MDEV-15042 changes were reverted in MDEV-17614.