Description:
When a user creates a table with a check constraint, and does not supply a name for the CHECK constraint, MySQL creates a name based on <table_name>_chk_<number>.
When the user RENAMEs such a table, MySQL changes the CHECK constraint name using the new table name. But, if the new table name is long, e.g. 60 characters, this will fail.
It's noteworthy that similarly you can't even `CREATE TABLE` with a long name and with an unnamed constraint. It may be worthwhile opening a bug report for that, too. But the problem intensifies when the table is already in production and populated with data, which is why I think the error in RENAME TABLE is more painful.
How to repeat:
create table some_table (
id int,
primary key(id),
constraint check (id % 2 = 0)
);
Query OK, 0 rows affected (0.03 sec)
show create table some_table \G
*************************** 1. row ***************************
Table: some_table
Create Table: CREATE TABLE `some_table` (
`id` int NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `some_table_chk_1` CHECK (((`id` % 2) = 0))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
rename table some_table to this_table_name_is_very_long_and_totals_at_sixty_characters;
ERROR 1059 (42000): Identifier name 'this_table_name_is_very_long_and_totals_at_sixty_characters_chk_1' is too long
Suggested fix:
I'm not sure MySQL should at all attempt to rename the check constraint name upon RENAME TABLE. But if so, and since the name is auto generated anyhow, the name should be truncated to fit within the 64 character limit. This will result in a name that does NOT confirm to <tablename>_chk_<number>, which means the next RENAME TABLE will not even attempt to rename the constraint. Which leads back to my original suggestion: do not attempt to rename a constraint upon RENAME TABLE.
Thank you!
Description: When a user creates a table with a check constraint, and does not supply a name for the CHECK constraint, MySQL creates a name based on <table_name>_chk_<number>. When the user RENAMEs such a table, MySQL changes the CHECK constraint name using the new table name. But, if the new table name is long, e.g. 60 characters, this will fail. It's noteworthy that similarly you can't even `CREATE TABLE` with a long name and with an unnamed constraint. It may be worthwhile opening a bug report for that, too. But the problem intensifies when the table is already in production and populated with data, which is why I think the error in RENAME TABLE is more painful. How to repeat: create table some_table ( id int, primary key(id), constraint check (id % 2 = 0) ); Query OK, 0 rows affected (0.03 sec) show create table some_table \G *************************** 1. row *************************** Table: some_table Create Table: CREATE TABLE `some_table` ( `id` int NOT NULL, PRIMARY KEY (`id`), CONSTRAINT `some_table_chk_1` CHECK (((`id` % 2) = 0)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) rename table some_table to this_table_name_is_very_long_and_totals_at_sixty_characters; ERROR 1059 (42000): Identifier name 'this_table_name_is_very_long_and_totals_at_sixty_characters_chk_1' is too long Suggested fix: I'm not sure MySQL should at all attempt to rename the check constraint name upon RENAME TABLE. But if so, and since the name is auto generated anyhow, the name should be truncated to fit within the 64 character limit. This will result in a name that does NOT confirm to <tablename>_chk_<number>, which means the next RENAME TABLE will not even attempt to rename the constraint. Which leads back to my original suggestion: do not attempt to rename a constraint upon RENAME TABLE. Thank you!