pingcap/tidb

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

Open

#9,307 opened on Feb 14, 2019

View on GitHub
 (1 comment) (0 reactions) (0 assignees)Go (40,090 stars) (6,186 forks)batch import
good first issuehelp wantedsig/sql-infratype/compatibility

Description

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)
  1. 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>
  1. 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 .

Contributor guide

Tech stack
gosqlmysql
Domain
backenddatabase
Issue type
bug
DifficultyEstimated implementation difficulty for a new contributor, from 1 for very small changes to 5 for expert-level work.
3
Estimated timeA rough time range for an experienced contributor to investigate, implement, test, and prepare a pull request.
1-3 hours
Activity statusHow available the issue appears right now: fresh, active, stale, blocked, or waiting on maintainer input.
stale
ClarityHow clearly the issue explains the expected change, acceptance criteria, and next step.
clear
Prerequisites
GoTiDB DDL moduleMySQL charset and collation
Newbie friendlinessA 1-100 score estimating how approachable this issue is for first-time contributors.
60
Research direction
Examine the difference between TiDB and MySQL outputs for SHOW CREATE TABLE. Look at PR #9229 (and #9113) to see previous attempts. The fix likely involves modifying the DDL code that generates the CREATE TABLE statement, possibly in the infoschema or ddl package. Ensure that the output matches MySQL exactly, including the use of 'CHARACTER SET' vs 'CHARSET' and whether collation is shown on column or table level.