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
| Field | Original Value | New Value |
|---|---|---|
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue is caused by |
| 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:
{code}[ERROR] mysqld: Can't find record in 'child'{code} The following tables reproduce this issue. I've linked {code:sql} CREATE DATABASE `testdb`; USE `testdb`; CREATE TABLE `parent` ( `id` INT PRIMARY KEY AUTO_INCREMENT ) ENGINE=INNODB; 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 {code:sql} 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`); {code} |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Labels | regression |
| Fix Version/s | 10.2 [ 14601 ] | |
| Assignee | Jan Lindström [ jplindst ] |
| Status | Confirmed [ 10101 ] | In Progress [ 3 ] |
| issue.field.resolutiondate | 2018-02-06 08:27:58.0 | 2018-02-06 08:27:58.467 |
| Fix Version/s | 10.2.13 [ 22910 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |
| Link |
This issue relates to |
| Affects Version/s | 10.2.18 [ 23112 ] | |
| Affects Version/s | 10.2.13 [ 22910 ] |
| Link |
This issue relates to |
| Workflow | MariaDB v3 [ 85100 ] | MariaDB v4 [ 153630 ] |