There seems to be a regression in the way FK constraints are handled: Trying to insert a record into a child table without the parent row existing now causes a 1032 error (Can't find record in 'child') instead of 1452. Additionally this error (1032) also appears in the server log:
[ERROR] mysqld: Can't find record in 'child'
The following tables reproduce this issue. I've linked MDEV-13206 since this might be related: The above described behaviour is only appears from 10.2.11 onwards. 10.2.10 (and 10.1 series) are not affected.
CREATEDATABASE `testdb`;
USE `testdb`;
CREATETABLE `parent` (
`id` INTPRIMARYKEY AUTO_INCREMENT
) ENGINE=INNODB;
CREATETABLE `child` (
-- crucial bit: FK has to be primary key (or if composite, at the start of the primary key)
There seems to be a regression in the way FK constraints are handled: Trying to insert a record into a child table without the parent row existing now causes a 1032 error (_Can't find record in 'child'_) instead of 1452. Additionally this error (1032) also appears in the server log:
{code}[ERROR] mysqld: Can't find record in 'child'{code}
The following tables reproduce this issue. I've linked MDEV-13206 since this might be related: The above described behaviour is only appears from 10.2.11 onwards. 10.2.10 (and 10.1 series) are not affected.
{code:sql}
CREATE DATABASE `testdb`;
USE `testdb`;
CREATE TABLE `child` (
-- crucial bit: FK has be primary key (or if composite, at start of 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);
-- 10.2.10: 1452 error, as expected
-- 10.2.11 or 10.2.12: 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`);
{code}
There seems to be a regression in the way FK constraints are handled: Trying to insert a record into a child table without the parent row existing now causes a 1032 error (_Can't find record in 'child'_) instead of 1452. Additionally this error (1032) also appears in the server log:
{code}[ERROR] mysqld: Can't find record in 'child'{code}
The following tables reproduce this issue. I've linked MDEV-13206 since this might be related: The above described behaviour is only appears from 10.2.11 onwards. 10.2.10 (and 10.1 series) are not affected.
{code:sql}
CREATE DATABASE `testdb`;
USE `testdb`;
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);
-- 10.2.10: 1452 error, as expected
-- 10.2.11 or 10.2.12: 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`);
{code}
Thanks for the report and test case, and yes, you've identified the related issue correctly, the change was introduced by this commit:
commit 0c4d11e8199f4c7a9e41fc3f86819219c21ddc4d
Author: Jan Lindström <jan.lindstrom@mariadb.com>
Date: Thu Nov 16 11:05:24 2017 +0200
MDEV-13206: INSERT ON DUPLICATE KEY UPDATE foreign key fail
Elena Stepanova
added a comment - Thanks for the report and test case, and yes, you've identified the related issue correctly, the change was introduced by this commit:
commit 0c4d11e8199f4c7a9e41fc3f86819219c21ddc4d
Author: Jan Lindström <jan.lindstrom@mariadb.com>
Date: Thu Nov 16 11:05:24 2017 +0200
MDEV-13206: INSERT ON DUPLICATE KEY UPDATE foreign key fail
Problem was that wrong error message was returned when insert
returned FK-error and there was no duplicate key to process.
row_ins
If error from insert was DB_NO_REFERENCED_ROW and there was
no duplicate key we should ignore ON DUPLICATE KEY UPDATE
and return original error message.
Jan Lindström (Inactive)
added a comment - commit 60f51af755ea9d07c20a596ba21de184816fa265
Author: Jan Lindström <jan.lindstrom@mariadb.com>
Date: Mon Feb 5 18:21:28 2018 +0200
MDEV-15042 : INSERT ON DUPLICATE KEY UPDATE produces error 1032 (Can't find record)
Problem was that wrong error message was returned when insert
returned FK-error and there was no duplicate key to process.
row_ins
If error from insert was DB_NO_REFERENCED_ROW and there was
no duplicate key we should ignore ON DUPLICATE KEY UPDATE
and return original error message.
.. and I still get: ERROR 1032 (HY000): Can't find record in 'child'
I don't seem to be able to re-open this issue - should I create a new one?
Vilnis Termanis
added a comment - - edited Unfortunately it would appear this is not actually fixed (or maybe it's a different issue?):
I've re-tested the exact test case from above in 10.2.13 and 10.2.18 from:
https://downloads.mariadb.com/MariaDB/mariadb-10.2.13/yum/centos73-amd64
https://downloads.mariadb.com/MariaDB/mariadb-10.2.18/yum/centos73-amd64
.. and I still get:
ERROR 1032 (HY000): Can't find record in 'child'
I don't seem to be able to re-open this issue - should I create a new one?
Marko Mäkelä
added a comment - 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 (and also in this ‘fix’ that according to MDEV-16692 was not complete).
Bug
Major
Thanks for the report and test case, and yes, you've identified the related issue correctly, the change was introduced by this commit:
commit 0c4d11e8199f4c7a9e41fc3f86819219c21ddc4dAuthor: Jan Lindström <jan.lindstrom@mariadb.com>Date: Thu Nov 16 11:05:24 2017 +0200MDEV-13206: INSERT ON DUPLICATE KEY UPDATE foreign key fail