Description:
Collation only changes metadata only changes, allowing immediate ALTER execution, for example:
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)
mysql > select count(*) from test_table;
+----------+
| count(*) |
+----------+
| 1880000 |
+----------+
1 row in set (0.38 sec)
mysql > ALTER TABLE test_table COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
The DDL log confirms there is no substantial table space change:
2024-12-11T14:04:01.750721Z 9 [Note] [MY-012485] [InnoDB] DDL log post ddl : begin for thread id : 9
2024-12-11T14:04:01.750796Z 9 [Note] [MY-012486] [InnoDB] DDL log post ddl : end for thread id : 9
However, the documentation does not mention this operation.
How to repeat:
There is no table nor column collation change described in:
https://dev.mysql.com/doc/refman/8.4/en/innodb-online-ddl-operations.html#online-ddl-colum...
https://dev.mysql.com/doc/refman/8.4/en/innodb-online-ddl-operations.html#online-ddl-table...
Suggested fix:
Complement the documentation accordingly.
Take https://bugs.mysql.com/bug.php?id=116943 into account as well.
Description: Collation only changes metadata only changes, allowing immediate ALTER execution, for example: 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) mysql > select count(*) from test_table; +----------+ | count(*) | +----------+ | 1880000 | +----------+ 1 row in set (0.38 sec) mysql > ALTER TABLE test_table COLLATE=utf8mb4_0900_ai_ci; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 The DDL log confirms there is no substantial table space change: 2024-12-11T14:04:01.750721Z 9 [Note] [MY-012485] [InnoDB] DDL log post ddl : begin for thread id : 9 2024-12-11T14:04:01.750796Z 9 [Note] [MY-012486] [InnoDB] DDL log post ddl : end for thread id : 9 However, the documentation does not mention this operation. How to repeat: There is no table nor column collation change described in: https://dev.mysql.com/doc/refman/8.4/en/innodb-online-ddl-operations.html#online-ddl-colum... https://dev.mysql.com/doc/refman/8.4/en/innodb-online-ddl-operations.html#online-ddl-table... Suggested fix: Complement the documentation accordingly. Take https://bugs.mysql.com/bug.php?id=116943 into account as well.