Description:
When foreign_key_checks is disabled, it allows ALTER TABLE which creates mismatching character sets between the parent/child FK columns.
How to repeat:
mysql> CREATE TABLE `parent` (
-> `name` varchar(20) NOT NULL,
-> PRIMARY KEY (`name`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 1 warning (0.08 sec)
mysql> CREATE TABLE `child` (
-> `id` int(11) NOT NULL,
-> `p_name` varchar(20) DEFAULT NULL,
-> PRIMARY KEY (`id`),
-> KEY `p_name_idx` (`p_name`),
-> CONSTRAINT `fk_pname` FOREIGN KEY (`p_name`) REFERENCES `parent` (`name`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Change column definition to mismatching charset is NOT allowed
mysql> alter table child modify p_name varchar(20) character set latin1 DEFAULT NULL;
ERROR 3780 (HY000): Referencing column 'p_name' and referenced column 'name' in foreign key constraint 'fk_pname' are incompatible.
-- Turn off foreign_key_checks
mysql> SET foreign_key_checks = 0 ;
Query OK, 0 rows affected (0.00 sec)
-- Drop index is NOT allowed (as expected)
mysql> alter table child drop index p_name_idx;
ERROR 1553 (HY000): Cannot drop index 'p_name_idx': needed in a foreign key constraint
-- Change column definition to mismatching charset is now allowed
mysql> alter table child modify p_name varchar(20) character set latin1 DEFAULT NULL;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
Suggested fix:
The ALTER TABLE should not be allowed per documentation, "Altering a table returns an error (errno: 150) if a foreign key definition is incorrectly formed for the altered table."
Description: When foreign_key_checks is disabled, it allows ALTER TABLE which creates mismatching character sets between the parent/child FK columns. How to repeat: mysql> CREATE TABLE `parent` ( -> `name` varchar(20) NOT NULL, -> PRIMARY KEY (`name`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected, 1 warning (0.08 sec) mysql> CREATE TABLE `child` ( -> `id` int(11) NOT NULL, -> `p_name` varchar(20) DEFAULT NULL, -> PRIMARY KEY (`id`), -> KEY `p_name_idx` (`p_name`), -> CONSTRAINT `fk_pname` FOREIGN KEY (`p_name`) REFERENCES `parent` (`name`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- Change column definition to mismatching charset is NOT allowed mysql> alter table child modify p_name varchar(20) character set latin1 DEFAULT NULL; ERROR 3780 (HY000): Referencing column 'p_name' and referenced column 'name' in foreign key constraint 'fk_pname' are incompatible. -- Turn off foreign_key_checks mysql> SET foreign_key_checks = 0 ; Query OK, 0 rows affected (0.00 sec) -- Drop index is NOT allowed (as expected) mysql> alter table child drop index p_name_idx; ERROR 1553 (HY000): Cannot drop index 'p_name_idx': needed in a foreign key constraint -- Change column definition to mismatching charset is now allowed mysql> alter table child modify p_name varchar(20) character set latin1 DEFAULT NULL; Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 Suggested fix: The ALTER TABLE should not be allowed per documentation, "Altering a table returns an error (errno: 150) if a foreign key definition is incorrectly formed for the altered table."