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.
Bug
Major
