| Bug #115457 | rename of a column in multi-column foreign key returns ERROR 1146 (42S02) | ||
|---|---|---|---|
| Submitted: | 28 Jun 2024 14:42 | Modified: | 16 Jan 19:56 |
| Reporter: | John Scott | Email Updates: | |
| Status: | Closed | 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 2024 15:07]
John Scott
Forgot to mention: work-around: "set foreign_key_checks=0;"
[28 Jun 2024 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 2024 15:28]
MySQL Verification Team
Changing relevant fields.
[3 Jul 2024 11:53]
huahua xu
Add a test case: mysql> insert into t1 values (1, 'a', 1); Query OK, 1 row affected (2.65 sec) mysql> insert into t2 values (1, 'a', 1); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_t1_js_id` FOREIGN KEY (`descr`, `t1_js_id`) REFERENCES `t1` (`descr`, `js_id`))
[3 Jul 2024 14:27]
huahua xu
Hi John Scott,
Your statement `ALTER TABLE t1 change js_id internal_id bigint not null;` is considered as a instant DDL and executed in install algorithm. The mysqkd could not rename columns of table in the data dictionary cache with replacing the field names in every foreign key constraint.
The funaction `innobase_rename_or_enlarge_columns_cache` should be called at an appropriate time to update the field names in foreign keys.
```
@@ -7457,6 +7457,9 @@ bool ha_innobase::commit_inplace_alter_table_impl(
if (!(ha_alter_info->handler_flags & ~INNOBASE_INPLACE_IGNORE) ||
is_instant(ha_alter_info)) {
assert(!ctx0);
+
+ innobase_rename_or_enlarge_columns_cache(ha_alter_info, table, m_prebuilt->table);
+
MONITOR_ATOMIC_DEC(MONITOR_PENDING_ALTER_TABLE);
ha_alter_info->group_commit_ctx = nullptr;
return false;
```
[3 Jul 2024 14:31]
huahua xu
The issue has been introduced since mysql-8.0.29
[3 Jul 2024 14:34]
MySQL Verification Team
Thank you, Mr. xu.
[16 Jan 19:56]
Philip Olson
Posted by developer: Fixed as of the upcoming MySQL Server 8.0.41, 8.4.4, and 9.2.0 releases, and here's the proposed changelog entry from the documentation team: The check enforcing the rule that ALGORITHM=INSTANT cannot be used on a column referenced by a foreign key constraint from another table did not inspect the last field of said constraint. Thank you for the bug report.
[17 Jan 11:53]
MySQL Verification Team
Thank you, Philip.
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)