Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.4.31, 10.5.22
Description
From horrockss@github in MariaDB docker GH issue 528, it was previously possible in 10.5.21 to add an auto_increment to a FK field under SET FOREIGN_KEY_CHECKS=0.
Since MDEV-31086, this is no longer possible to prevent index corruption. The AUTO_INCREMENT isn't something that can cause corruption however.
Test case:
MariaDB [test]> CREATE TABLE `device_type` ( |
-> `id` int(10) unsigned NOT NULL, |
-> `name` varchar(128) DEFAULT NULL, |
-> PRIMARY KEY (`id`) |
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; |
Query OK, 0 rows affected (0.009 sec) |
|
|
MariaDB [test]>
|
MariaDB [test]> CREATE TABLE `device_field` ( |
-> `id` int(10) unsigned NOT NULL AUTO_INCREMENT, |
-> `name` varchar(128) DEFAULT NULL, |
-> `device_type_id` int(10) unsigned DEFAULT NULL, |
-> PRIMARY KEY (`id`), |
-> CONSTRAINT `FK_device_field_device_type` FOREIGN KEY (`device_type_id`) REFERENCES `device_type` (`id`) |
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; |
Query OK, 0 rows affected (0.000 sec) |
|
|
MariaDB [test]>
|
MariaDB [test]> SET FOREIGN_KEY_CHECKS=0; |
Query OK, 0 rows affected (0.000 sec) |
|
|
MariaDB [test]>
|
MariaDB [test]> select @@foreign_key_checks; |
+----------------------+ |
| @@foreign_key_checks |
|
+----------------------+ |
| 0 |
|
+----------------------+ |
1 row in set (0.000 sec) |
|
|
MariaDB [test]>
|
MariaDB [test]> ALTER TABLE device_type MODIFY id INT unsigned AUTO_INCREMENT; |
ERROR 1833 (HY000): Cannot change column 'id': used in a foreign key constraint 'FK_device_field_device_type' of table 'test.device_field' |
Expected:
MariaDB [test]> CREATE TABLE `device_type` ( |
-> `id` int(10) unsigned NOT NULL, |
-> `name` varchar(128) DEFAULT NULL, |
-> PRIMARY KEY (`id`) |
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; |
Query OK, 0 rows affected (0.001 sec) |
|
|
MariaDB [test]>
|
MariaDB [test]> CREATE TABLE `device_field` ( |
-> `id` int(10) unsigned NOT NULL AUTO_INCREMENT, |
-> `name` varchar(128) DEFAULT NULL, |
-> `device_type_id` int(10) unsigned DEFAULT NULL, |
-> PRIMARY KEY (`id`), |
-> CONSTRAINT `FK_device_field_device_type` FOREIGN KEY (`device_type_id`) REFERENCES `device_type` (`id`) |
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; |
Query OK, 0 rows affected (0.001 sec) |
|
|
MariaDB [test]>
|
MariaDB [test]> ALTER TABLE device_type MODIFY id INT unsigned AUTO_INCREMENT; |
Query OK, 0 rows affected (0.001 sec) |
Records: 0 Duplicates: 0 Warnings: 0
|
To preserve the working setup we add this functionality that was previously available before the MDEV-31086 bug fix.
Attachments
Issue Links
- is caused by
-
-
- Closed
-