SHOW CREATE TABLE strips character set information

[kennytm]

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.

create database if not exists x;
drop table if exists x.y;
create table x.y (a varchar(100) charset 'utf8mb4', b varchar(100) charset 'utf8', c varchar(100) charset 'ascii');
show create table x.y;

2. What did you expect to see?

The character sets of the non-default columns should be retained

CREATE TABLE `y` (
  `a` varchar(100) DEFAULT NULL,
  `b` varchar(100) CHARSET utf8 COLLATE utf8_bin DEFAULT NULL,
  `c` varchar(100) CHARSET ascii COLLATE ascii_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

3. What did you see instead?

The schema has no charset information

CREATE TABLE `y` (
  `a` varchar(100) DEFAULT NULL,
  `b` varchar(100) DEFAULT NULL,
  `c` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

Note that the charset are present when queried through the HTTP status port.

// curl http://127.0.0.1:10080/schema/x | jq '.[0].cols[].type'
{
  "Tp": 15,
  "Flag": 0,
  "Flen": 100,
  "Decimal": 0,
  "Charset": "utf8mb4",
  "Collate": "utf8mb4_bin",
  "Elems": null
}
{
  "Tp": 15,
  "Flag": 0,
  "Flen": 100,
  "Decimal": 0,
  "Charset": "utf8",
  "Collate": "utf8_bin",
  "Elems": null
}
{
  "Tp": 15,
  "Flag": 0,
  "Flen": 100,
  "Decimal": 0,
  "Charset": "ascii",
  "Collate": "ascii_bin",
  "Elems": null
}

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

Release Version: v2.1.1
Git Commit Hash: 990f859384b834fe5edf1410d2a4142def5c3fa9
Git Branch: HEAD
UTC Build Time: 2018-12-13 08:18:19
GoVersion: go version go1.11.2 darwin/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false

[kennytm]

If conflating everything to utf8mb4 is intended (as indicated by #8604) then maybe there should be warnings, but currently it's empty.

mysql> show warnings;
Empty set (0.00 sec)

[AndrewDi]

During my test on MySQL 8.0, even column charset is the same with table charset, result would like this:

CREATE TABLE `y` (
  `a` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `b` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `c` varchar(100) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL,
  `d` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Only CHARSET=binary should not print.

[jackysp]

Fixed by #8866