Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
10.4.31, 10.5.22, 10.6.15, 10.9.8, 10.10.6, 10.11.5, 11.0.3, 11.1.2
-
None
Description
We had in our migration some tables with a wrong character set created (we didn't set explicit which character set). So we added a while ago a migration to fix this.
For this we disable first the foreign key checks and then run the ALTER TABLE. It seems now that since the last patch versions this does not work anymore.
Simplified test case:
CREATE TABLE `t1` ( |
`hash` varchar(32) NOT NULL COLLATE 'utf8_unicode_ci', |
`title` varchar(100) DEFAULT NULL COLLATE 'utf8_unicode_ci', |
PRIMARY KEY (`hash`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
|
CREATE TABLE `t2` ( |
`id` int(11) unsigned NOT NULL AUTO_INCREMENT, |
`t1_hash` varchar(32) NOT NULL COLLATE 'utf8_unicode_ci', |
`name` varchar(100) DEFAULT NULL COLLATE 'utf8_unicode_ci', |
PRIMARY KEY (`id`), |
KEY `FK_t1_hash` (`t1_hash`), |
CONSTRAINT `FK_t1_hash` FOREIGN KEY (`t1_hash`) REFERENCES `t1` (`hash`) ON DELETE CASCADE ON UPDATE NO ACTION |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
|
|
SET FOREIGN_KEY_CHECKS = 0; |
ALTER TABLE t1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; |
ALTER TABLE t2 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; |
SET FOREIGN_KEY_CHECKS = 1; |
error is:
SET FOREIGN_KEY_CHECKS = 0 |
Query executed OK, 0 rows affected. (0.000 s) Edit |
|
ALTER TABLE t1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
|
Error in query (1833): Cannot change column 'hash': used in a foreign key constraint 'FK_t1_hash' of table 'test.t2' |
|
ALTER TABLE t2 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
|
Error in query (1832): Cannot change column 't1_hash': used in a foreign key constraint 'FK_t1_hash' |
|
SET FOREIGN_KEY_CHECKS = 1 |
Query executed OK, 0 rows affected. (0.000 s) Edit, Warnings |
Attachments
Issue Links
- is caused by
-
MDEV-31086 MODIFY COLUMN can break FK constraints, and lead to unrestorable dumps
- Closed
- is duplicated by
-
MDEV-32003 MODIFY COLUMN no longer possible with Foreign Key Constraints
- Closed
- relates to
-
MDEV-32171 When updating cascaded foreign keys, closing foreign key constraints is ignored and invalid.
- Closed
-
MDEV-32729 look at SET FOREIGN_KEY_CHECK=0
- Closed