Bug #104504 COLLATE maybe overwritten by charset if both specified in create database stmt

• Submitted: 2 Aug 2021 12:26
• Modified: 20 Aug 2021 16:44
• Reporter: Cheng Zhou
• Status: Closed
• Category: MySQL Server: Parser
• Severity: S3 (Non-critical)
• Version: 8.0.25, 8.0.26
• OS: Any
• CPU Architecture: Any

[2 Aug 2021 12:26] Cheng Zhou

Description:
When specified the CHARACTER SET in create database statement, it would also set the charset to COLLATE internally and unconditionally. Thus, if the COLLATE is specified before CHARACTER SET, it would be overwritten.

How to repeat:
1、
mysql> create database test1 CHARACTER SET utf8mb4 collate utf8mb4_bin;
Query OK, 1 row affected (4.66 sec)

mysql> show create database test1;
+----------+--------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                          |
+----------+--------------------------------------------------------------------------------------------------------------------------+
| test1    | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (3.52 sec)

2、
mysql>  create database test2 collate utf8mb4_bin CHARACTER SET utf8mb4;
Query OK, 1 row affected (12.34 sec)

mysql> show create database test2;
+----------+---------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                 |
+----------+---------------------------------------------------------------------------------------------------------------------------------+
| test2    | CREATE DATABASE `test2` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (3.14 sec)

[3 Aug 2021 8:01] MySQL Verification Team

Hello Cheng Zhou,

Thank you for the report and test case.

regards,
Umesh

[20 Aug 2021 16:44] Jon Stephens

Documented fix as follows in the MySQL 8.0.27 changelog:

    Given the default collation c1 of a character set cs, and a
    different collation c2 (that is, not equal to c1), then the
    statement CREATE DATABASE d COLLATE c2 CHARACTER SET cs created
    a new database with the default collation set to c1 instead of
    c2.

Closed.