Message about Charset&&Collation in show create table is not same with MYSQL

[xiekeyi98]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

If possible, provide a recipe for reproducing the error.

Exec

drop table if exists t,t1,t2;
create table t( a varchar(10) character set utf8 ) charset=utf8;
create table t1 ( a varchar(10) character set utf8 collate utf8_bin ) charset=utf8,collate=utf8_bin;
create table t2( a varchar(10) character set utf8 collate utf8_bin ) charset=utf8;
Query OK, 0 rows affected (0.01 sec)

2. What did you expect to see?

In MYSQL

xiekeyi@ubuntu:~$ mysql -P 4000 -h localhost -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.25-0ubuntu0.18.04.2 (Ubuntu)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> drop table if exists t,t1,t2;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> create table t( a varchar(10) character set utf8 ) charset=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> create table t1 ( a varchar(10) character set utf8 collate utf8_bin ) charset=utf8,collate=utf8_bin;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t2( a varchar(10) character set utf8 collate utf8_bin ) charset=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table t;
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table                                                                           |
+-------+----------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `a` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table t1;
+-------+---------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                              |
+-------+---------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` varchar(10) COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+-------+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table t2;
+-------+-----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                |
+-------+-----------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `a` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.7.25-0ubuntu0.18.04.2 |
+-------------------------+
1 row in set (0.00 sec)

mysql> status;
--------------
mysql  Ver 14.14 Distrib 5.7.25, for Linux (x86_64) using  EditLine wrapper

Connection id:		2
Current database:	test
Current user:		xiekeyi@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.7.25-0ubuntu0.18.04.2 (Ubuntu)
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/var/run/mysqld/mysqld.sock
Uptime:			2 min 3 sec

Threads: 1  Questions: 18  Slow queries: 0  Opens: 109  Flush tables: 1  Open tables: 101  Queries per second avg: 0.146
--------------

mysql> 

3. What did you see instead?

In TiDB

xiekeyi@ubuntu:~$ mysql -P 4000 -h 127.0.0.1 -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.10-TiDB-v3.0.0-beta-61-gf73c4e2d5 MySQL Community Server (Apache License 2.0)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> drop table if exists t,t1,t2;
Query OK, 0 rows affected (0.00 sec)
mysql> create table t( a varchar(10) character set utf8 ) charset=utf8;
Query OK, 0 rows affected (0.02 sec)
mysql> create table t1 ( a varchar(10) character set utf8 collate utf8_bin ) charset=utf8,collate=utf8_bin;
Query OK, 0 rows affected (0.01 sec)
mysql> create table t2( a varchar(10) character set utf8 collate utf8_bin ) charset=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table t;
+-------+---------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                            |
+-------+---------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `a` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+-------+---------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table t1;
+-------+----------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                             |
+-------+----------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+-------+----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table t2;
+-------+----------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                             |
+-------+----------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `a` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+-------+----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+---------------------------------------+
| version()                             |
+---------------------------------------+
| 5.7.10-TiDB-v3.0.0-beta-61-gf73c4e2d5 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> select tidb_version();
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                                     |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v3.0.0-beta-61-gf73c4e2d5
Git Commit Hash: f73c4e2d58ac6c0f85453d97777b1c8e095ab294
Git Branch: master
UTC Build Time: 2019-02-14 12:05:02
GoVersion: go version go1.11.2 linux/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 

---

1. If you notice in TiDB is CHARSET but in MYSQL is CHARACTER SET , please BE CAREFUL to decide whether to fix it, because there is already a PR try to solve it #9229 .
2. If you want to solve this issue, please leave a message here to avoid duplication of work.
3. For your reference , there is a similiar PR #9113 .

[ghost]

Confirming this can still be reproduced:

drop table if exists t,t1,t2;
create table t( a varchar(10) character set utf8 ) charset=utf8;
create table t1 ( a varchar(10) character set utf8 collate utf8_bin ) charset=utf8,collate=utf8_bin;
create table t2( a varchar(10) character set utf8 collate utf8_bin ) charset=utf8;
show create table t\G
show create table t1\G
show create table t2\G

..

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `a` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-870-g2a8b96845
Edition: Community
Git Commit Hash: 2a8b968453520e4fcf9d6ff46c9f23b4ad23feee
Git Branch: master
UTC Build Time: 2020-07-31 08:45:35
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)