Table collation doesn't follow schema #58446

dveeden · 2024-12-21T09:22:25Z

Bug Report

When creating a table the collation should be the collation from the schema if it is not explicitly set for the table. This doesn't seem to be the case with TiDB if the character set is specified explicitly, but the collation is not.

1. Minimal reproduce step (Required)

CREATE SCHEMA s1 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE s1;
CREATE TABLE t11 (id INT PRIMARY KEY);
CREATE TABLE t12 (id INT PRIMARY KEY) CHARACTER SET utf8;
CREATE TABLE t13 (id INT PRIMARY KEY) CHARACTER SET utf8 COLLATE utf8_general_ci;
SELECT TABLE_NAME, TABLE_COLLATION FROM information_schema.tables WHERE TABLE_SCHEMA='s1';

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

MySQL 9.1.0:

mysql-9.1.0> CREATE SCHEMA s1 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected, 2 warnings (0.01 sec)

mysql-9.1.0> USE s1;
Database changed
mysql-9.1.0> CREATE TABLE t11 (id INT PRIMARY KEY);
Query OK, 0 rows affected (0.01 sec)

mysql-9.1.0> CREATE TABLE t12 (id INT PRIMARY KEY) CHARACTER SET utf8;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql-9.1.0> CREATE TABLE t13 (id INT PRIMARY KEY) CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql-9.1.0> SELECT TABLE_NAME, TABLE_COLLATION FROM information_schema.tables WHERE TABLE_SCHEMA='s1';
+------------+--------------------+
| TABLE_NAME | TABLE_COLLATION    |
+------------+--------------------+
| t11        | utf8mb3_general_ci |
| t12        | utf8mb3_general_ci |
| t13        | utf8mb3_general_ci |
+------------+--------------------+
3 rows in set (0.00 sec)

3. What did you see instead (Required)

TiDB v8.5.0

mysql-8.0.11-TiDB-v8.5.0> CREATE SCHEMA s1 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 0 rows affected (0.05 sec)

mysql-8.0.11-TiDB-v8.5.0> USE s1;
Database changed
mysql-8.0.11-TiDB-v8.5.0> CREATE TABLE t11 (id INT PRIMARY KEY);
Query OK, 0 rows affected (0.04 sec)

mysql-8.0.11-TiDB-v8.5.0> CREATE TABLE t12 (id INT PRIMARY KEY) CHARACTER SET utf8;
Query OK, 0 rows affected (0.04 sec)

mysql-8.0.11-TiDB-v8.5.0> CREATE TABLE t13 (id INT PRIMARY KEY) CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 0 rows affected (0.04 sec)

mysql-8.0.11-TiDB-v8.5.0> SELECT TABLE_NAME, TABLE_COLLATION FROM information_schema.tables WHERE TABLE_SCHEMA='s1';
+------------+-----------------+
| TABLE_NAME | TABLE_COLLATION |
+------------+-----------------+
| t11        | utf8_general_ci |
| t12        | utf8_bin        |
| t13        | utf8_general_ci |
+------------+-----------------+
3 rows in set (0.00 sec)

4. What is your TiDB version? (Required)

Release Version: v8.5.0
Edition: Community
Git Commit Hash: d13e52ed6e22cc5789bed7c64c861578cd2ed55b
Git Branch: HEAD
UTC Build Time: 2024-12-18 02:26:06
GoVersion: go1.23.3
Race Enabled: false
Check Table Before Drop: false
Store: tikv

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

dveeden · 2024-12-30T07:16:44Z

The behavior for the utf8mb4 character set is more complex due to default_collation_for_utf8mb4.

mysql-9.1.0> \W
Show warnings enabled.
mysql-9.1.0> CREATE SCHEMA s1 COLLATE utf8mb4_unicode_ci;
Query OK, 1 row affected (0.02 sec)

mysql-9.1.0> SET SESSION default_collation_for_utf8mb4='utf8mb4_general_ci';
Query OK, 0 rows affected, 1 warning (0.00 sec)

Warning (Code 1681): Updating 'default_collation_for_utf8mb4' is deprecated. It will be made read-only in a future release.
mysql-9.1.0> USE s1
Database changed
mysql-9.1.0> CREATE TABLE t1 (id INT PRIMARY KEY);
Query OK, 0 rows affected (0.02 sec)

mysql-9.1.0> CREATE TABLE t2 (id INT PRIMARY KEY) DEFAULT CHARACTER SET utf8mb4;
Query OK, 0 rows affected (0.02 sec)

mysql-9.1.0> SHOW VARIABLES LIKE 'collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database   | utf8mb4_unicode_ci |
| collation_server     | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)

mysql-9.1.0> SELECT TABLE_NAME, TABLE_COLLATION FROM information_schema.tables WHERE TABLE_SCHEMA='s1';
+------------+--------------------+
| TABLE_NAME | TABLE_COLLATION    |
+------------+--------------------+
| t1         | utf8mb4_unicode_ci |
| t2         | utf8mb4_general_ci |
+------------+--------------------+
2 rows in set (0.00 sec)

mysql-9.1.0> SET SESSION default_collation_for_utf8mb4='utf8mb4_0900_ai_ci';
Query OK, 0 rows affected, 1 warning (0.00 sec)

Warning (Code 1681): Updating 'default_collation_for_utf8mb4' is deprecated. It will be made read-only in a future release.
mysql-9.1.0> CREATE TABLE t3 (id INT PRIMARY KEY) DEFAULT CHARACTER SET utf8mb4;
Query OK, 0 rows affected (0.02 sec)

mysql-9.1.0> SELECT TABLE_NAME, TABLE_COLLATION FROM information_schema.tables WHERE TABLE_SCHEMA='s1';
+------------+--------------------+
| TABLE_NAME | TABLE_COLLATION    |
+------------+--------------------+
| t1         | utf8mb4_unicode_ci |
| t2         | utf8mb4_general_ci |
| t3         | utf8mb4_0900_ai_ci |
+------------+--------------------+
3 rows in set (0.00 sec)

dveeden added compatibility-mysql8 This is a compatibility issue with MySQL 8.0(but NOT 5.7) type/bug The issue is confirmed as a bug. type/compatibility component/executor component/ddl This issue is related to DDL of TiDB. and removed component/executor labels Dec 21, 2024

dveeden mentioned this issue Dec 23, 2024

ddl: table collation should follow schema #58482

Closed

13 tasks

dveeden added the severity/minor label Dec 23, 2024

D3Hunter added severity/major and removed severity/minor labels Dec 23, 2024

ti-chi-bot bot added may-affects-5.4 This bug maybe affects 5.4.x versions. may-affects-6.1 may-affects-6.5 may-affects-7.1 may-affects-7.5 may-affects-8.1 may-affects-8.5 labels Dec 23, 2024

dveeden added the affects-7.1 This bug affects the 7.1.x(LTS) versions. label Dec 24, 2024

dveeden linked a pull request Dec 27, 2024 that will close this issue

ddl: table collation should follow schema #58582

Open

13 tasks

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Table collation doesn't follow schema #58446

Table collation doesn't follow schema #58446

dveeden commented Dec 21, 2024

dveeden commented Dec 30, 2024 •

edited

Loading

Table collation doesn't follow schema #58446

Table collation doesn't follow schema #58446

Comments

dveeden commented Dec 21, 2024

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)

dveeden commented Dec 30, 2024 • edited Loading

dveeden commented Dec 30, 2024 •

edited

Loading