MySQL Bugs: #116943: Inconsistent metadata-only table and column collation changes behavior of ALTER TABLE

Bug #116943	Inconsistent metadata-only table and column collation changes behavior of ALTER TABLE
Submitted:	11 Dec 2024 13:50	Modified:	20 Dec 2024 15:18
Reporter:	Przemyslaw Malkowski	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	8.0.40	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When you only change the COLLATE attribute for columns, the operation is immediate. It is the same if you change the default COLLATE for a table.
But, suddenly the operation becomes a full table rebuild when you combine both in single ALTER TABLE statement!

How to repeat:
mysql > select @@version,@@version_comment;
+-----------+------------------------------+
| @@version | @@version_comment            |
+-----------+------------------------------+
| 8.0.40    | MySQL Community Server - GPL |
+-----------+------------------------------+
1 row in set (0.00 sec)

mysql > select count(*) from test_table;
+----------+
| count(*) |
+----------+
|  1880000 |
+----------+
1 row in set (0.35 sec)

mysql > show create table test_table\G
*************************** 1. row ***************************
       Table: test_table
Create Table: CREATE TABLE `test_table` (
  `field1` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `field2` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

Now, when I modify the columns only first, it executes immediately:

mysql > ALTER TABLE test_table MODIFY field1 varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, MODIFY field2 text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci, ALGORITHM=INPLACE;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

The DDL log shows only these two notes:

2024-12-11T13:38:02.662949Z 9 [Note] [MY-012485] [InnoDB] DDL log post ddl : begin for thread id : 9
2024-12-11T13:38:02.663036Z 9 [Note] [MY-012486] [InnoDB] DDL log post ddl : end for thread id : 9

Same with table collation:

mysql > ALTER TABLE test_table COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

2024-12-11T13:40:22.045058Z 9 [Note] [MY-012485] [InnoDB] DDL log post ddl : begin for thread id : 9
2024-12-11T13:40:22.045112Z 9 [Note] [MY-012486] [InnoDB] DDL log post ddl : end for thread id : 9

But when I do both operations at once it takes a long time:

mysql > show create table test_table\G
*************************** 1. row ***************************
       Table: test_table
Create Table: CREATE TABLE `test_table` (
  `field1` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `field2` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql > ALTER TABLE test_table MODIFY field1 varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, MODIFY field2 text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, COLLATE=utf8mb4_general_ci, ALGORITHM=INPLACE;
Query OK, 0 rows affected (24.99 sec)
Records: 0  Duplicates: 0  Warnings: 0

And the DDL log shows why:

2024-12-11T13:41:09.282589Z 9 [Note] [MY-012473] [InnoDB] DDL log insert : [DDL record: DELETE SPACE, id=2446, thread_id=9, space_id=165, old_file_path=./test/#sql-ib1381-2278478771.ibd]
2024-12-11T13:41:09.282726Z 9 [Note] [MY-012478] [InnoDB] DDL log delete : 2446
2024-12-11T13:41:09.292017Z 9 [Note] [MY-012477] [InnoDB] DDL log insert : [DDL record: REMOVE CACHE, id=2447, thread_id=9, table_id=1382, new_file_path=test/#sql-ib1381-2278478771]
2024-12-11T13:41:09.292095Z 9 [Note] [MY-012478] [InnoDB] DDL log delete : 2447
2024-12-11T13:41:09.294560Z 9 [Note] [MY-012472] [InnoDB] DDL log insert : [DDL record: FREE, id=2448, thread_id=9, space_id=165, index_id=601, page_no=4]
2024-12-11T13:41:09.294670Z 9 [Note] [MY-012478] [InnoDB] DDL log delete : 2448
2024-12-11T13:41:34.230560Z 9 [Note] [MY-012475] [InnoDB] DDL log insert : [DDL record: DROP, id=2449, thread_id=9, table_id=1381]
2024-12-11T13:41:34.233251Z 9 [Note] [MY-012474] [InnoDB] DDL log insert : [DDL record: RENAME SPACE, id=2450, thread_id=9, space_id=164, old_file_path=./test/#sql-ib1382-2278478772.ibd, new_file_path=./test/test_table.ibd]
2024-12-11T13:41:34.233513Z 9 [Note] [MY-012478] [InnoDB] DDL log delete : 2450
2024-12-11T13:41:34.239667Z 9 [Note] [MY-012476] [InnoDB] DDL log insert : [DDL record: RENAME TABLE, id=2451, thread_id=9, table_id=1381, old_file_path=test/#sql-ib1382-2278478772, new_file_path=test/test_table]
2024-12-11T13:41:34.239711Z 9 [Note] [MY-012478] [InnoDB] DDL log delete : 2451
2024-12-11T13:41:34.241023Z 9 [Note] [MY-012474] [InnoDB] DDL log insert : [DDL record: RENAME SPACE, id=2452, thread_id=9, space_id=165, old_file_path=./test/test_table.ibd, new_file_path=./test/#sql-ib1381-2278478771.ibd]
2024-12-11T13:41:34.241063Z 9 [Note] [MY-012478] [InnoDB] DDL log delete : 2452
2024-12-11T13:41:34.244311Z 9 [Note] [MY-012476] [InnoDB] DDL log insert : [DDL record: RENAME TABLE, id=2453, thread_id=9, table_id=1382, old_file_path=test/test_table, new_file_path=test/#sql-ib1381-2278478771]
2024-12-11T13:41:34.244343Z 9 [Note] [MY-012478] [InnoDB] DDL log delete : 2453
2024-12-11T13:41:34.245720Z 9 [Note] [MY-012475] [InnoDB] DDL log insert : [DDL record: DROP, id=2454, thread_id=9, table_id=1381]
2024-12-11T13:41:34.245753Z 9 [Note] [MY-012473] [InnoDB] DDL log insert : [DDL record: DELETE SPACE, id=2455, thread_id=9, space_id=164, old_file_path=./test/#sql-ib1382-2278478772.ibd]
2024-12-11T13:41:34.261883Z 9 [Note] [MY-012485] [InnoDB] DDL log post ddl : begin for thread id : 9
2024-12-11T13:41:34.261940Z 9 [Note] [MY-012479] [InnoDB] DDL log replay : [DDL record: DELETE SPACE, id=2455, thread_id=9, space_id=164, old_file_path=./test/#sql-ib1382-2278478772.ibd]
2024-12-11T13:41:34.267432Z 9 [Note] [MY-012479] [InnoDB] DDL log replay : [DDL record: DROP, id=2454, thread_id=9, table_id=1381]
2024-12-11T13:41:34.267683Z 9 [Note] [MY-012479] [InnoDB] DDL log replay : [DDL record: DROP, id=2449, thread_id=9, table_id=1381]
2024-12-11T13:41:34.270126Z 9 [Note] [MY-012486] [InnoDB] DDL log post ddl : end for thread id : 9

Suggested fix:
First, fix the ALTER to consider both changes done in a single step as metadata only.

Second, unfortunately, there is no ALGORITHM=METADATA syntax available, as reported before in:
https://bugs.mysql.com/bug.php?id=80792
https://bugs.mysql.com/bug.php?id=89243

Therefore, one may expect immediate execution but end up in a long and very costly table rebuild.

I should also mention that for whatever reason, this metadata-only change is not considered an INSTANT type, despite being "instant" in reality:

mysql > ALTER TABLE test_table COLLATE=utf8mb4_general_ci, ALGORITHM=INSTANT;
ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.

Hello Przemyslaw,

Thank you for the report and feedback. 
Verified as described.

Thanks,
Umesh

Hi all:

I'm afraid I can't fully agree with the suggestion. When altering the COLLATE attribute for any column which is referred by an index, the innodb maybe rebuild the index tree. Then the INSTANT algorithm will not be able to support this situation.

Hi Huahua, 

Yes, the situation with an indexed column is different indeed. But my report is about non-indexed columns.