pingcap/tidb

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

Open

#9 307 ouverte le 14 févr. 2019

Voir sur GitHub
 (1 commentaire) (0 réactions) (0 assignés)Go (6 186 forks)batch import
good first issuehelp wantedsig/sql-infratype/compatibility

Métriques du dépôt

Stars
 (40 090 stars)
Métriques de merge PR
 (Merge moyen 14j 4h) (346 PRs mergées en 30 j)

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 .

Guide contributeur

Direction de recherche
Examinez le chemin de code de SHOW CREATE TABLE dans TiDB, en particulier comment le jeu de caractères et le collation sont affichés pour les colonnes et les options de table. Comparez avec le comportement de MySQL 5.7 et ajustez pour afficher les clauses CHARACTER SET et COLLATE comme le fait MySQL. Référez vous aux PR #9229 et #9113 pour le contexte antérieur.
Stack technique
go
Domaine
database
Type d'issue
Bug
DifficultéDifficulté estimée pour un nouveau contributeur, de 1 pour un très petit changement à 5 pour un travail expert.
2
Temps estiméFourchette de temps approximative pour investiguer, implémenter, tester et préparer une pull request.
Une demi journée
Statut d'activitéDisponibilité apparente de l'issue : fraîche, active, ancienne, bloquée ou en attente d'un mainteneur.
Active
ClartéClarté avec laquelle l'issue explique le changement attendu, les critères d'acceptation et la prochaine étape.
Claire
Prérequis
SQL
Accessibilité débutantScore de 1 à 100 estimant l'accessibilité de cette issue pour un premier contributeur.
70