MySQL full job - ddl - create database - the CHARACTER SET and COLLATE is not same as src database

[asiroliu]

Description

MySQL full job - ddl - create database - the CHARACTER SET and COLLATE is not same as src database

Steps to reproduce the issue

1. execute create database on src MySQL

sql> CREATE DATABASE db1 CHARACTER SET utf8 COLLATE utf8_general_ci;
sql> CREATE DATABASE db2 DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;
sql> CREATE DATABASE db3 CHARACTER SET=latin1 COLLATE=latin1_swedish_ci;
sql> CREATE DATABASE db4 COLLATE latin2_general_ci;
sql> CREATE DATABASE db5 CHARACTER SET binary;
sql> CREATE DATABASE db6 DEFAULT CHARACTER SET=gbk DEFAULT COLLATE=gbk_chinese_ci;

2. create instance level job

"replicate_do_db": []

3. wait for replicate finish，and check database status on dest

Describe the results you received

all database DEFAULT CHARACTER SET is latin1, same as dest MySQL server-level system variables

mysql> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| latin1                   | latin1_swedish_ci    |
+--------------------------+----------------------+
1 row in set (0.01 sec)

mysql> SHOW CREATE DATABASE db1;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| db1      | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE DATABASE db2;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| db2      | CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE DATABASE db3;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| db3      | CREATE DATABASE `db3` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE DATABASE db4;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| db4      | CREATE DATABASE `db4` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE DATABASE db5;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| db5      | CREATE DATABASE `db5` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE DATABASE db6;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| db6      | CREATE DATABASE `db6` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

Describe the results you expected

the CHARACTER SET and COLLATE should be same as src database

Output of ./dtle version:**

9.9.9.9-master-19b07a4

问题原因: 全量使用了固定的CREATE DATABASE IF NOT EXISTS语句, 而非 show create database的结果.

MySQL show create database 参考结果:

CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */