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: | |
Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
Version: | 8.0 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[5 Mar 2024 9:00]
zhang yinggang
[5 Mar 2024 9:14]
zhang yinggang
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)
[5 Mar 2024 10:25]
MySQL Verification Team
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.