Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11.5, 10.4(EOL), 10.5, 10.6, 10.9(EOL), 10.10(EOL), 10.11
Description
When an `ALTER TABLE` statement tries to drop an constraint while also adding one, the drop operation is not performed:
|
|
MariaDB [test]> create table a (id int primary key); |
Query OK, 0 rows affected (0.016 sec) |
|
|
MariaDB [test]> create table b (id int primary key references a(id)); |
Query OK, 0 rows affected (0.015 sec) |
|
|
MariaDB [test]> show create table b\G |
*************************** 1. row ***************************
|
Table: b |
Create Table: CREATE TABLE `b` ( |
`id` int(11) NOT NULL, |
PRIMARY KEY (`id`), |
CONSTRAINT `b_ibfk_1` FOREIGN KEY (`id`) REFERENCES `a` (`id`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
1 row in set (0.001 sec) |
|
|
MariaDB [test]> alter table b drop constraint b_ibfk_1, add constraint other foreign key (id) references a(id) on update cascade; |
Query OK, 0 rows affected (0.023 sec) |
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> show create table b\G |
*************************** 1. row ***************************
|
Table: b |
Create Table: CREATE TABLE `b` ( |
`id` int(11) NOT NULL, |
PRIMARY KEY (`id`), |
CONSTRAINT `b_ibfk_1` FOREIGN KEY (`id`) REFERENCES `a` (`id`), |
CONSTRAINT `other` FOREIGN KEY (`id`) REFERENCES `a` (`id`) ON UPDATE CASCADE |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
1 row in set (0.010 sec) |
Attachments
Issue Links
- relates to
-
MDEV-16356 Allow ALGORITHM=NOCOPY for ADD CONSTRAINT
-
- Open
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
When an `ALTER TABLE` statement tries to drop an constraint while also adding one, the drop operation is not performed:
``` MariaDB [test]> create table a (id int primary key); Query OK, 0 rows affected (0.016 sec) MariaDB [test]> create table b (id int primary key references a(id)); Query OK, 0 rows affected (0.015 sec) MariaDB [test]> show create table b\G *************************** 1. row *************************** Table: b Create Table: CREATE TABLE `b` ( `id` int(11) NOT NULL, PRIMARY KEY (`id`), CONSTRAINT `b_ibfk_1` FOREIGN KEY (`id`) REFERENCES `a` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci 1 row in set (0.001 sec) MariaDB [test]> alter table b drop constraint b_ibfk_1, add constraint other foreign key (id) references a(id) on update cascade; Query OK, 0 rows affected (0.023 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [test]> show create table b\G *************************** 1. row *************************** Table: b Create Table: CREATE TABLE `b` ( `id` int(11) NOT NULL, PRIMARY KEY (`id`), CONSTRAINT `b_ibfk_1` FOREIGN KEY (`id`) REFERENCES `a` (`id`), CONSTRAINT `other` FOREIGN KEY (`id`) REFERENCES `a` (`id`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci 1 row in set (0.010 sec) ``` |
When an `ALTER TABLE` statement tries to drop an constraint while also adding one, the drop operation is not performed:
{code:sql} MariaDB [test]> create table a (id int primary key); Query OK, 0 rows affected (0.016 sec) MariaDB [test]> create table b (id int primary key references a(id)); Query OK, 0 rows affected (0.015 sec) MariaDB [test]> show create table b\G *************************** 1. row *************************** Table: b Create Table: CREATE TABLE `b` ( `id` int(11) NOT NULL, PRIMARY KEY (`id`), CONSTRAINT `b_ibfk_1` FOREIGN KEY (`id`) REFERENCES `a` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci 1 row in set (0.001 sec) MariaDB [test]> alter table b drop constraint b_ibfk_1, add constraint other foreign key (id) references a(id) on update cascade; Query OK, 0 rows affected (0.023 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [test]> show create table b\G *************************** 1. row *************************** Table: b Create Table: CREATE TABLE `b` ( `id` int(11) NOT NULL, PRIMARY KEY (`id`), CONSTRAINT `b_ibfk_1` FOREIGN KEY (`id`) REFERENCES `a` (`id`), CONSTRAINT `other` FOREIGN KEY (`id`) REFERENCES `a` (`id`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci 1 row in set (0.010 sec) {code} |
| Fix Version/s | 10.4 [ 22408 ] | |
| Fix Version/s | 10.5 [ 23123 ] | |
| Fix Version/s | 10.6 [ 24028 ] | |
| Fix Version/s | 10.9 [ 26905 ] | |
| Fix Version/s | 10.10 [ 27530 ] | |
| Fix Version/s | 10.11 [ 27614 ] | |
| Fix Version/s | 11.0 [ 28320 ] |
| Affects Version/s | 10.4 [ 22408 ] | |
| Affects Version/s | 10.5 [ 23123 ] | |
| Affects Version/s | 10.6 [ 24028 ] | |
| Affects Version/s | 10.9 [ 26905 ] | |
| Affects Version/s | 10.10 [ 27530 ] | |
| Affects Version/s | 10.11 [ 27614 ] |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Assignee | Sergei Golubchik [ serg ] |
| Assignee | Sergei Golubchik [ serg ] | Marko Mäkelä [ marko ] |
The native ALTER TABLE in InnoDB seems to work fine, at least in MariaDB Server 10.6, where I tested the following:
--source include/have_innodb.inc
|
CREATE TABLE t1 (id INT PRIMARY KEY) ENGINE=InnoDB; |
CREATE TABLE t2 (id INT PRIMARY KEY REFERENCES t1(id)) ENGINE=InnoDB; |
|
|
SHOW CREATE TABLE t2; |
SET foreign_key_checks=0; |
ALTER TABLE t2 |
DROP CONSTRAINT t2_ibfk_1, |
ADD CONSTRAINT other FOREIGN KEY (id) REFERENCES a(id) ON UPDATE CASCADE; |
SHOW CREATE TABLE t2; |
|
|
DROP TABLE t2,t1; |
If I add , ALGORITHM=COPY to the ALTER TABLE statement, I will get two foreign key constraints. For some reason, on the revision of 10.6 that I tested, if I omit the SET statement, the ALTER TABLE will fail as follows:
ER_CANT_CREATE_TABLE (1005): Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
|
The ALGORITHM=COPY version of the operation not only fails to drop the constraint; it will also unnecessarily rebuild the table and copy all data, until MDEV-16356 is fixed.
| Link | This issue relates to MDEV-16356 [ MDEV-16356 ] |
| Component/s | Data Definition - Alter Table [ 10114 ] | |
| Component/s | Storage Engine - InnoDB [ 10129 ] | |
| Labels | foreign-keys |
It actually gets funnier: When I wrote the mtr version of the test, I intended to rename the tables a and b to t1 and t2, respectively. But, I forgot to replace one reference. Because of foreign_key_checks=0, the reference to the missing table a will be allowed.
Here is a better test:
--source include/have_innodb.inc
|
CREATE TABLE t1 (id INT PRIMARY KEY) ENGINE=InnoDB; |
CREATE TABLE t2 (id INT PRIMARY KEY REFERENCES t1(id)) ENGINE=InnoDB; |
|
|
SET foreign_key_checks=0; |
ALTER TABLE t2 |
DROP CONSTRAINT t2_ibfk_1, |
ADD CONSTRAINT other FOREIGN KEY (id) REFERENCES t1(id) ON UPDATE CASCADE, |
ALGORITHM=NOCOPY;
|
SHOW CREATE TABLE t2; |
SET foreign_key_checks=1; |
ALTER TABLE t2 |
DROP CONSTRAINT other, |
ADD CONSTRAINT another FOREIGN KEY (id) REFERENCES t1(id) ON DELETE CASCADE, |
ALGORITHM=COPY;
|
SHOW CREATE TABLE t2; |
|
|
DROP TABLE t2,t1; |
The ALGORITHM clauses are redundant, only there to show what is going on. The first ALTER TABLE works correctly; the second one will wrongly end up with two constraints.
If I change the test to use DROP FOREIGN KEY instead of DROP CONSTRAINT, the constraint will be dropped.
MDEV-20480 was implemented incompletely, because it failed to remove or replace dict_foreign_parse_drop_constraints().
The ad-hoc parser in dict_foreign_parse_drop_constraints() looks for DROP followed by FOREIGN KEY. It fails to detect DROP CONSTRAINT or DROP CONSTRAINT IF EXISTS.
Also in this case, the SQL layer is definitely validating the constraint names, and InnoDB is duplicating some of that work. I think that dict_foreign_parse_drop_constraints() must be replaced with something that obtains the necessary information from the SQL layer, just like the native ALTER TABLE code path does.
| Assignee | Marko Mäkelä [ marko ] | Vladislav Lesin [ vlad.lesin ] |
| Fix Version/s | 10.9 [ 26905 ] |
| Fix Version/s | 10.10 [ 27530 ] |
| Fix Version/s | 11.0 [ 28320 ] |
| Fix Version/s | 10.4 [ 22408 ] |
Thank you! I repeated as described on MariaDB 10.4-11.0