SHOW CREATE TABLE output with AUTO_RANDOM results in missing AUTO_RANDOM #24176

dveeden · 2021-04-20T11:20:20Z

Bug Report

1. Minimal reproduce step (Required)

mysql> create table t (id bigint auto_random primary key);
Query OK, 0 rows affected, 1 warning (0.55 sec)

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` bigint(20) NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.01 sec)

mysql> drop table t;
Query OK, 0 rows affected (0.53 sec)

mysql> CREATE TABLE `t` (
    ->   `id` bigint(20) NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */,
    ->   PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Query OK, 0 rows affected (0.55 sec)

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

2. What did you expect to see? (Required)

The table having a AUTO_RANDOM after re-creating it from the SHOW CREATE TABLE... statement.

3. What did you see instead (Required)

No default for id.

4. What is your TiDB version? (Required)

mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-2687-g3f22a5440-dirty
Edition: Community
Git Commit Hash: 3f22a5440c60a8d2645e33ece1c828d86275fcc6
Git Branch: automatic_tls
UTC Build Time: 2021-04-20 08:57:10
GoVersion: go1.16.3
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

The text was updated successfully, but these errors were encountered:

tangenta · 2021-04-21T06:44:07Z

I can't reproduce it in branch master.

I couldn't found the branch automatic_tls or the git hash. @dveeden Can you provide the link of repo with this git hash?

dveeden · 2021-04-21T06:48:01Z

tidb 5.7.25-TiDB-v5.0.0 > SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v5.0.0
Edition: Community
Git Commit Hash: bdac0885cd11bdf571aad9353bfc24e13554b91c
Git Branch: heads/refs/tags/v5.0.0
UTC Build Time: 2021-04-06 16:36:29
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)

tidb 5.7.25-TiDB-v5.0.0 > create table t (id bigint auto_random primary key);
Query OK, 0 rows affected, 1 warning (0.15 sec)

tidb 5.7.25-TiDB-v5.0.0 > show warnings;
+-------+------+---------------------------------------------------------+
| Level | Code | Message                                                 |
+-------+------+---------------------------------------------------------+
| Note  | 1105 | Available implicit allocation times: 288230376151711743 |
+-------+------+---------------------------------------------------------+
1 row in set (0.00 sec)

tidb 5.7.25-TiDB-v5.0.0 > show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` bigint(20) NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

tidb 5.7.25-TiDB-v5.0.0 > drop table t;
Query OK, 0 rows affected (0.26 sec)

tidb 5.7.25-TiDB-v5.0.0 > CREATE TABLE `t` (
    ->   `id` bigint(20) NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */,
    ->   PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Query OK, 0 rows affected (0.14 sec)

tidb 5.7.25-TiDB-v5.0.0 > show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

So looks like v5.0.0 also has the same issue.

dveeden · 2021-04-21T06:53:59Z

tidb 5.7.25-TiDB-v4.0.0-beta.2-2694-g7a224f10c > SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-2694-g7a224f10c
Edition: Community
Git Commit Hash: 7a224f10ca622ddc2c81c4085d53d4f8ea41180f
Git Branch: master
UTC Build Time: 2021-04-21 06:48:40
GoVersion: go1.13.15
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

tidb 5.7.25-TiDB-v4.0.0-beta.2-2694-g7a224f10c > create table t (id bigint auto_random primary key);
Query OK, 0 rows affected, 1 warning (0.54 sec)

tidb 5.7.25-TiDB-v4.0.0-beta.2-2694-g7a224f10c > show warnings;
+-------+------+---------------------------------------------------------+
| Level | Code | Message                                                 |
+-------+------+---------------------------------------------------------+
| Note  | 1105 | Available implicit allocation times: 288230376151711743 |
+-------+------+---------------------------------------------------------+
1 row in set (0.00 sec)

tidb 5.7.25-TiDB-v4.0.0-beta.2-2694-g7a224f10c > show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` bigint(20) NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

tidb 5.7.25-TiDB-v4.0.0-beta.2-2694-g7a224f10c > drop table t;
Query OK, 0 rows affected (0.53 sec)

tidb 5.7.25-TiDB-v4.0.0-beta.2-2694-g7a224f10c > CREATE TABLE `t` (
    ->   `id` bigint(20) NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */,
    ->   PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Query OK, 0 rows affected (0.54 sec)

tidb 5.7.25-TiDB-v4.0.0-beta.2-2694-g7a224f10c > show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

This is with a recent master branch

tangenta · 2021-04-21T06:57:29Z

This is strange...

Database changed
mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v5.0.0-nightly-71-gbdac0885c
Edition: Community
Git Commit Hash: bdac0885cd11bdf571aad9353bfc24e13554b91c
Git Branch: HEAD
UTC Build Time: 2021-04-21 06:50:27
GoVersion: go1.16
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

mysql> create table t (id bigint auto_random primary key);
ERROR 1050 (42S01): Table 'test.t' already exists
mysql> drop table t;
Query OK, 0 rows affected (0.03 sec)

mysql> create table t (id bigint auto_random primary key);
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` bigint(20) NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> drop table t;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE `t` (
    ->   `id` bigint(20) NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */,
    ->   PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` bigint(20) NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

tangenta · 2021-04-21T07:01:48Z

Please make sure the option --comments is specified in the command line.

tangenta · 2021-04-21T07:04:14Z

https://docs.pingcap.com/tidb/stable/comment-syntax#optimizer-comment-syntax

Note
In MySQL client before 5.7.7, TiDB specific comment syntax and optimizer comment syntax are treated as comments and cleared by default. To use the two syntaxes in the old client, add the --comments option when you start the client. For example, mysql -h 127.0.0.1 -P 4000 -uroot --comments.

dveeden · 2021-04-21T07:16:07Z

Yes this was indeed the issue. However this still surprised me. This is because I use a MySQL 8.0.23 client which I thought was not stripping out comments. However I was wrong.

The reason for me thinking this is that the client is not stripping out comments that start with /*! or /*+ (mysql version comments and optimizer hints). The /*T! does get stripped out.

https://github.com/mysql/mysql-server/blob/3e90d07c3578e4da39dc1bce73559bbdf655c28c/client/mysql.cc#L2599-L2600

One way to make this easier for other users would be:

Have tiup playground and/or tiup cluster create a ~/.my.cnf with something like the config below
The same as above but store in ~/tidb.cnf and then tell the user to use mysql --defaults-file=tidb.cnf.
Create a /etc/my.cnf.d/tidb.cnf with something similar
Try to have a TiDB version comment that matches the things that aren't stripped out by the MySQL client by default
Have a tiup mysql that invokes mysql --comments ... etc

[mysql]
prompt="tidb \v > "
comments

[client]
port=4000
host=127.0.0.1
user=root

tangenta · 2021-04-21T07:51:24Z

One way to make this easier for other users would be:

Have tiup playground and/or tiup cluster create a ~/.my.cnf with something like the config below

Even if we change all of the deploying tools, the MySQL client may not be on the same machine of the TiDB servers.

The same as above but store in ~/tidb.cnf and then tell the user to use mysql --defaults-file=tidb.cnf.

Create a /etc/my.cnf.d/tidb.cnf with something similar

Have a tiup mysql that invokes mysql --comments ... etc

I think these approaches are not different from telling the user to use mysql --comments directly.

Try to have a TiDB version comment that matches the things that aren't stripped out by the MySQL client by default

The syntax is initially borrowed from MariaDB(/*M! */). MariaDB meets the same problem(/*M!50701 ... */) but they have their own client... https://github.com/MariaDB/server/blob/10.6/client/mysql.cc#L2466-L2467

With the 8.0.23 MySQL client TiDB specific comments are cleared out, so even with the latest client you still need to add `--comments`. Related: - pingcap/tidb#24176 (comment)

dveeden · 2021-04-21T08:04:47Z

One way to make this easier for other users would be:

Have tiup playground and/or tiup cluster create a ~/.my.cnf with something like the config below

Even if we change all of the deploying tools, the MySQL client may not be on the same machine of the TiDB servers.

That's likely not the case for how most people would use tiup playground. Creating the config file and letting users
distribute them may work. However you are correct to flag this as an issue.

The same as above but store in ~/tidb.cnf and then tell the user to use mysql --defaults-file=tidb.cnf.

Create a /etc/my.cnf.d/tidb.cnf with something similar

Have a tiup mysql that invokes mysql --comments ... etc

I think these approaches are not different from telling the user to use mysql --comments directly.

Currently tiup playground outputs this:

To connect TiDB: mysql --host 127.0.0.1 --port 4000 -u root -p (no password)
To view the dashboard: http://127.0.0.1:2379/dashboard
To view the Prometheus: http://127.0.0.1:9090
To view the Grafana: http://127.0.0.1:3000

Note that --comments is missing here. There might be other cases were we (or the user) may forget this option. Also this line is getting quite long.

Try to have a TiDB version comment that matches the things that aren't stripped out by the MySQL client by default

The syntax is initially borrowed from MariaDB(/*M! */). MariaDB meets the same problem(/*M!50701 ... */) but they have their own client... https://github.com/MariaDB/server/blob/10.6/client/mysql.cc#L2466-L2467

Thanks. Good to know the history on this.

As the Oracle MySQL documentation indicates they want to get rid of --comments as well. Not sure why they didn't make this the default yet. We could try to work with them on this.

dveeden · 2021-04-21T08:08:16Z

Another thing we could consider is to recommend MySQL Shell instead of MySQL Client:

[dvaneeden@dve-carbon ~]$ mysqlsh --sql mysql://root@127.0.0.1:4000/test
MySQL Shell 8.0.23

Copyright (c) 2016, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
Creating a Classic session to 'root@127.0.0.1:4000/test'
Fetching schema names for autocompletion... Press ^C to stop.
Fetching table and column names from `test` for auto-completion... Press ^C to stop.
Your MySQL connection id is 111
Server version: 5.7.25-TiDB-v5.0.0 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible
Default schema set to `test`.
 MySQL  127.0.0.1:4000  test  SQL > SELECT CONCAT('TiDB' /*T! ,' rocks!' */ );
+----------------------------------+
| CONCAT('TiDB' /*T! ,' rocks!'  ) |
+----------------------------------+
| TiDB rocks!                      |
+----------------------------------+
1 row in set (0.0003 sec)
 MySQL  127.0.0.1:4000  test  SQL > 
Bye!

ti-srebot · 2021-04-21T08:17:24Z

Please edit this comment or add a new comment to complete the following information

Not a bug

This is a usability problem.

dveeden · 2021-04-21T08:19:06Z

@tangenta could you set the correct labels on this issue etc? I don't seem to have the right permissions for that.

With the 8.0.23 MySQL client TiDB specific comments are cleared out, so even with the latest client you still need to add `--comments`. Related: - pingcap/tidb#24176 (comment)

dveeden added the type/bug The issue is confirmed as a bug. label Apr 20, 2021

dveeden mentioned this issue Apr 21, 2021

comment-syntax: Correct the note about --comments pingcap/docs#5421

Merged

10 tasks

dveeden closed this as completed Apr 21, 2021

ti-srebot added the need-more-info label Apr 21, 2021

tangenta removed the type/bug The issue is confirmed as a bug. label Apr 21, 2021

ti-srebot removed the need-more-info label Apr 21, 2021

tangenta mentioned this issue Apr 21, 2021

playground: add --comments option to the mysql client connection tips pingcap/tiup#1323

Merged

This was referenced Apr 22, 2021

comment-syntax: Correct the note about --comments (#5421) pingcap/docs#5427

Merged

comment-syntax: Correct the note about --comments (#5421) pingcap/docs#5428

Merged

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

SHOW CREATE TABLE output with AUTO_RANDOM results in missing AUTO_RANDOM #24176

SHOW CREATE TABLE output with AUTO_RANDOM results in missing AUTO_RANDOM #24176

dveeden commented Apr 20, 2021

tangenta commented Apr 21, 2021

dveeden commented Apr 21, 2021

dveeden commented Apr 21, 2021

tangenta commented Apr 21, 2021

tangenta commented Apr 21, 2021 •

edited

Loading

tangenta commented Apr 21, 2021

dveeden commented Apr 21, 2021 •

edited

Loading

tangenta commented Apr 21, 2021

dveeden commented Apr 21, 2021 •

edited by tangenta

Loading

dveeden commented Apr 21, 2021

ti-srebot commented Apr 21, 2021 •

edited by tangenta

Loading

dveeden commented Apr 21, 2021

SHOW CREATE TABLE output with AUTO_RANDOM results in missing AUTO_RANDOM #24176

SHOW CREATE TABLE output with AUTO_RANDOM results in missing AUTO_RANDOM #24176

Comments

dveeden commented Apr 20, 2021

Bug Report

1. Minimal reproduce step (Required)

2. What did you expect to see? (Required)

3. What did you see instead (Required)

4. What is your TiDB version? (Required)

tangenta commented Apr 21, 2021

dveeden commented Apr 21, 2021

dveeden commented Apr 21, 2021

tangenta commented Apr 21, 2021

tangenta commented Apr 21, 2021 • edited Loading

tangenta commented Apr 21, 2021

dveeden commented Apr 21, 2021 • edited Loading

tangenta commented Apr 21, 2021

dveeden commented Apr 21, 2021 • edited by tangenta Loading

dveeden commented Apr 21, 2021

ti-srebot commented Apr 21, 2021 • edited by tangenta Loading

Please edit this comment or add a new comment to complete the following information

Not a bug

dveeden commented Apr 21, 2021

tangenta commented Apr 21, 2021 •

edited

Loading

dveeden commented Apr 21, 2021 •

edited

Loading

dveeden commented Apr 21, 2021 •

edited by tangenta

Loading

ti-srebot commented Apr 21, 2021 •

edited by tangenta

Loading