| Bug #115457 | rename of a column in multi-column foreign key returns ERROR 1146 (42S02) | ||
|---|---|---|---|
| Submitted: | 28 Jun 14:42 | Modified: | 28 Jun 15:27 |
| Reporter: | John Scott | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | foreign key multi-column | ||
[28 Jun 15:07]
John Scott
Forgot to mention: work-around: "set foreign_key_checks=0;"
[28 Jun 15:27]
MySQL Verification Team
HI Mr. Scott, Thank you for your bug report. We repeated your test case: ysql> DROP TABLE IF EXISTS t1, t2; CREATE TABLE `t1` ( `id` bigint NOT NULL AUTO_INCREMENT, `descr` varchar(25) NOT NULL, `js_id` bigint NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY js_id_idx (descr,js_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; CREATE TABLE `t2` ( id bigint not null auto_increment, descr varchar(25) not null, t1_js_id bigint not null, PRIMARY KEY (`id`), UNIQUE KEY fk_t1_js_id_idx (descr,t1_js_id), CONSTRAINT fk_t1_js_id FOREIGN KEY (descr,t1_js_id) references t1 (descr,js_id) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; ALTER TABLE t1 change js_id internal_id bigint not null; show create table t1; show create table t2; DROP TABLE IF EXISTS t1, t2; Query OK, 0 rows affected, 2 warnings (0.06 sec) mysql> mysql> mysql> CREATE TABLE `t1` ( `id` bigint NOT NULL AUTO_INCREMENT, `descr` varchar(25) NOT NULL, `js_id` bigint NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY js_id_idx (descr,js_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; Query OK, 0 rows affected (0.07 sec) mysql> mysql> CREATE TABLE `t2` ( id bigint not null auto_increment, descr varchar(25) not null, t1_js_id bigint not null, PRIMARY KEY (`id`), UNIQUE KEY fk_t1_js_id_idx (descr,t1_js_id), CONSTRAINT fk_t1_js_id FOREIGN KEY (descr,t1_js_id) references t1 (descr,js_id) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; Query OK, 0 rows affected (0.01 sec) mysql> mysql> ALTER TABLE t1 change js_id internal_id bigint not null; ERROR 1146 (42S02): Table 'test.t1' doesn't exist mysql> mysql> show create table t1; +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `id` bigint NOT NULL AUTO_INCREMENT, `descr` varchar(25) NOT NULL, `internal_id` bigint NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `js_id_idx` (`descr`,`internal_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> show create table t2; +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t2 | CREATE TABLE `t2` ( `id` bigint NOT NULL AUTO_INCREMENT, `descr` varchar(25) NOT NULL, `t1_js_id` bigint NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `fk_t1_js_id_idx` (`descr`,`t1_js_id`), CONSTRAINT `fk_t1_js_id` FOREIGN KEY (`descr`, `t1_js_id`) REFERENCES `t1` (`descr`, `internal_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> DROP TABLE IF EXISTS t1, t2; Query OK, 0 rows affected (0.01 sec) This is now a verified bug report. Thank you .......
[28 Jun 15:28]
MySQL Verification Team
Changing relevant fields.
Description: Renaming a column involved in a multi-column foreign key reference results in: ERROR 1146 (42S02): Table 'tablename' doesn't exist However, the column alteration succeeds and correctly modifies the reference table key. This only happens with multi-column foreign key references. Testing with single column foreign keys works fine. Data types play no role. The synopsis below was tested with integer and varchar data types. The following synopsis was completed with 8.0.37-1 community version on Ubuntu 24.04 LTS on Amazon EC2 m7i.xlarge instance, but has been observed in RDS, Aurora, and MySQL community edition in various 8.0.x versions. How to repeat: mysql> select @@version; +-----------+ | @@version | +-----------+ | 8.0.37 | +-----------+ 1 row in set (0.00 sec) mysql> CREATE TABLE `t1` ( -> `id` bigint NOT NULL AUTO_INCREMENT, -> `descr` varchar(25) NOT NULL, -> `js_id` bigint NOT NULL, -> PRIMARY KEY (`id`), -> UNIQUE KEY js_id_idx (descr,js_id) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; Query OK, 0 rows affected (0.03 sec) mysql> CREATE TABLE `t2` ( -> id bigint not null auto_increment, -> descr varchar(25) not null, -> t1_js_id bigint not null, -> PRIMARY KEY (`id`), -> UNIQUE KEY fk_t1_js_id_idx (descr,t1_js_id), -> CONSTRAINT fk_t1_js_id FOREIGN KEY (descr,t1_js_id) references t1 (descr,js_id) ON DELETE NO ACTION ON UPDATE NO ACTION -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; Query OK, 0 rows affected (0.09 sec) mysql> ALTER TABLE t1 change js_id internal_id bigint not null; ERROR 1146 (42S02): Table 'js_mm_test.t1' doesn't exist As described above, the actual table alteration is completed successfully. mysql> show create table t1; +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `id` bigint NOT NULL AUTO_INCREMENT, `descr` varchar(25) NOT NULL, `internal_id` bigint NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `js_id_idx` (`descr`,`internal_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) mysql> show create table t2; +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t2 | CREATE TABLE `t2` ( `id` bigint NOT NULL AUTO_INCREMENT, `descr` varchar(25) NOT NULL, `t1_js_id` bigint NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `fk_t1_js_id_idx` (`descr`,`t1_js_id`), CONSTRAINT `fk_t1_js_id` FOREIGN KEY (`descr`, `t1_js_id`) REFERENCES `t1` (`descr`, `internal_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec)