MySQL Bugs: #114221: The description of "Specifying a character set" is incorrect in Online DDL

Bug #114221	The description of "Specifying a character set" is incorrect in Online DDL
Submitted:	5 Mar 2024 9:00	Modified:	5 Mar 2024 10:25
Reporter:	zhang yinggang	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
DOC:
https://dev.mysql.com/doc/refman/8.3/en/innodb-online-ddl-operations.html#online-ddl-table...

At "Table 17.21 Online DDL Support for Table Operations" , "Specifying a character set" is described as need to rebuild the table if the new character encoding is different.

However, this operation can be done without rebuilding table even the the new character encoding is different. Furthermore, it can be done by only modifying the table's metadata

 The innobase_need_rebuild() function in storage/innobase/handler/handler0alter.cc also supports this judgment:

```
  if (alter_inplace_flags == Alter_inplace_info::CHANGE_CREATE_OPTION &&
      !(ha_alter_info->create_info->used_fields &
        (HA_CREATE_USED_ROW_FORMAT | HA_CREATE_USED_KEY_BLOCK_SIZE |
         HA_CREATE_USED_TABLESPACE))) {
    /* Any other CHANGE_CREATE_OPTION than changing
    ROW_FORMAT, KEY_BLOCK_SIZE or TABLESPACE can be done
    without rebuilding the table. */
    return (false);
  }
```

`Specifying a character set` only records HA_CREATE_USED_DEFAULT_CHARSET flag in ha_alter_info->create_info->used_fields. Through the above code and comments，`Specifying a character set` can be done without rebuilding the table.

How to repeat:
# 1.create a database and table:
MySQL [(none)]> create database test;
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> use test
Database changed
MySQL [test]> create table t1(a1 char(100)) charset = latin1;
Query OK, 0 rows affected (0.09 sec)

MySQL [test]> show create table t1;
+-------+------------------------------------------------------------------------------------------+
| Table | Create Table                                                                             |
+-------+------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a1` char(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

# 2. insert some data:
MySQL [test]> insert into t1(a1) values('a');
Query OK, 1 row affected (0.01 sec)

# 3. Query test.t1's table_id from information_schema.INNODB_tables
root@(none)> select * from information_schema.INNODB_tables where NAME = 'test/t1';
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+
| TABLE_ID | NAME    | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+
| 1121     | test/t1 | 33   | 4      | 42    | Dynamic    | 0             | Single     | 0            |
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+

# 4. Specifying character set from latin1 to gbk: the new character encoding is different.
MySQL [test]> ALTER TABLE t1 CHARACTER SET = GBK, ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

MySQL [test]> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                               |
+-------+------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a1` char(100) CHARACTER SET latin1 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

# 5. t1's table_id is not changed, it means t1 is not rebuilt by the above DDL operation.
MySQL [test]> select * from information_schema.INNODB_tables where NAME = 'test/t1';
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+
| TABLE_ID | NAME    | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+
|     1123 | test/t1 |   33 |      4 |    44 | Dynamic    |             0 | Single     |            0 |
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+

# 6. For comparison, if the table is rebuilt, it's table_id will be changed.
MySQL [test]> alter table t1 engine = innodb;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

MySQL [test]> select * from information_schema.INNODB_tables where NAME = 'test/t1';
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+
| TABLE_ID | NAME    | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+
|     1124 | test/t1 |   33 |      4 |    45 | Dynamic    |             0 | Single     |            0 |
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+
1 row in set (0.02 sec)

Suggested fix:
Change the content from:

Operation	Instant	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Specifying a character set	No	Yes	Yes*	Yes	No						

to:

Operation	Instant	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Specifying a character set	No	Yes	NO	Yes	YES

Sorry, there were some content errors in chapter "How to repeat". Please refer to the following content for repeating:

# 1.create a database and table:
MySQL [(none)]> create database test;
Query OK, 1 row affected (0.01 sec)

MySQL [(none)]> use test;
Database changed
MySQL [test]> create table t1(a1 char(100)) charset = latin1;
Query OK, 0 rows affected (0.10 sec)

MySQL [test]> show create table t1;
+-------+------------------------------------------------------------------------------------------+
| Table | Create Table                                                                             |
+-------+------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a1` char(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

# 2. insert some data:
MySQL [test]> insert into t1(a1) values('a');
Query OK, 1 row affected (0.01 sec)

# 3. Query test.t1's table_id from information_schema.INNODB_tables
MySQL [test]> select * from information_schema.INNODB_tables where NAME = 'test/t1';
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+
| TABLE_ID | NAME    | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+
|     1125 | test/t1 |   33 |      4 |    46 | Dynamic    |             0 | Single     |            0 |
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+
1 row in set (0.07 sec)

# 4. Specifying character set from latin1 to gbk: the new character encoding is different.
MySQL [test]> ALTER TABLE t1 CHARACTER SET = GBK, ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

MySQL [test]> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                               |
+-------+------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a1` char(100) CHARACTER SET latin1 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

# 5. t1's table_id is not changed, it means t1 is not rebuilt by the above DDL operation.
MySQL [test]> select * from information_schema.INNODB_tables where NAME = 'test/t1';
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+
| TABLE_ID | NAME    | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+
|     1125 | test/t1 |   33 |      4 |    46 | Dynamic    |             0 | Single     |            0 |
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+
1 row in set (0.03 sec)

# 6. For comparison, if the table is rebuilt, it's table_id will be changed.
MySQL [test]> alter table t1 engine = innodb;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

MySQL [test]> select * from information_schema.INNODB_tables where NAME = 'test/t1';
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+
| TABLE_ID | NAME    | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+
|     1126 | test/t1 |   33 |      4 |    47 | Dynamic    |             0 | Single     |            0 |
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+
1 row in set (0.03 sec)

Hi Mr. yinggang,

Thank you for your bug report.

We managed to repeat your report with both, the latest 8.0 and 8.3.

This is the output :

Table	Create Table
t1	CREATE TABLE `t1` (\n  `a1` char(100) DEFAULT NULL\n) ENGINE=InnoDB DEFAULT CHARSET=latin1
TABLE_ID	NAME	FLAG	N_COLS	SPACE	ROW_FORMAT	ZIP_PAGE_SIZE	SPACE_TYPE	INSTANT_COLS	TOTAL_ROW_VERSIONS

3965	test/t1	33	4	2801	Dynamic	0	Single	0	0

Table	Create Table
t1	CREATE TABLE `t1` (\n  `a1` char(100) CHARACTER SET latin1 DEFAULT NULL\n) ENGINE=InnoDB DEFAULT CHARSET=gbk

TABLE_ID	NAME	FLAG	N_COLS	SPACE	ROW_FORMAT	ZIP_PAGE_SIZE	SPACE_TYPE	INSTANT_COLS	TOTAL_ROW_VERSIONS
3965	test/t1	33	4	2801	Dynamic	0	Single	0	0

TABLE_ID	NAME	FLAG	N_COLS	SPACE	ROW_FORMAT	ZIP_PAGE_SIZE	SPACE_TYPE	INSTANT_COLS	TOTAL_ROW_VERSIONS
3966	test/t1	33	4	2802	Dynamic	0	Single	0	0

This is now a fully verified bug report for 8.0 and higher.

Version 5.7 is no longer supported.