Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.11, 10.2.12, 10.2.13, 10.2.18
-
CentOS 7.3 x86_64, mariadb.com yum repository
Description
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.
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); |
|
|
-- 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`); |
Attachments
Issue Links
- is caused by
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Closed
-
Activity
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.
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?
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).
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