set global character_set_server not working the same way in MySQL, may lead inconsistency

[zing22845]

Dolt

dolt  sql -q 'set global character_set_server=latin1'

dolt  sql -q 'show variables' |grep latin
| character_set_server                         | latin1                           |
| collation_server                             | latin1_swedish_ci                |

dolt  sql -q 'create database db_latin1'

dolt  sql -q 'show create database db_latin1'
+-----------+------------------------------------------------------------------------------------------------+
| Database  | Create Database                                                                                |
+-----------+------------------------------------------------------------------------------------------------+
| db_latin1 | CREATE DATABASE `db_latin1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin */ |
+-----------+------------------------------------------------------------------------------------------------+

The database DEFAULT CHARACTER SET still utf8mb4

MySQL

mysql8203 -Be  "set global character_set_server = latin1"

mysql8203 -Be  "show variables" | grep latin
character_set_server	latin1
collation_server	latin1_swedish_ci

mysql8203 -Be  "create database db_latin1"

mysql8203 -Be  "show create database db_latin1"
Database	Create Database
db_latin1	CREATE DATABASE `db_latin1` /*!40100 DEFAULT CHARACTER SET latin1 */ /*!80016 DEFAULT ENCRYPTION='N' */

The database DEFAULT CHARACTER SET is changed to latin1

[jycor]

Thanks for bringing this to our attention @zing22845.
I added some formatting for readbility.

A couple issues on our end:

• the SHOW CREATE DATABASE statement is not accurately reflecting the charset/collation
• we are not using the @@character_set_server variable when creating databases

We will get started on these as soon as possible.

As a workaround, use a database option, and use the @@character_set_database variable:

tmp/main> create database latin1_db character set latin1;
tmp/main> use latin1_db;
Database changed
latin1_db/main> select @@character_set_database;
+--------------------------+
| @@character_set_database |
+--------------------------+
| latin1                   |
+--------------------------+
1 row in set (0.00 sec)

Note: a show create database ... on the above will still not display correctly.

[zing22845]

@jycor Thanks again. Your rapidly response reinforced my confidence in the quality of the project and the supportiveness of this community.

[jycor]

Did not mean for dolthub/go-mysql-server#2418 to close the issue.
That PR only partially addresses this.

[bpf120]

@zing22845 Thanks again for filing this. We'd love to learn about your Dolt use case too. Feel free to join our Discord or send me an email if you want to share.

[jycor]

Hey @zing22845, the fix for this has made its way to dolt main.

In my testing, set global character_set_server = latin1 for mysql didn't cause the databases made to use latin1 collation;
however, set session character_set_server = latin1 did.

We will cut a release for you later today.
Please make another issue if something doesn't work as expected.

[zing22845]

Hey @zing22845, the fix for this has made its way to dolt main.

In my testing, set global character_set_server = latin1 for mysql didn't cause the databases made to use latin1 collation; however, set session character_set_server = latin1 did.

We will cut a release for you later today. Please make another issue if something doesn't work as expected.

Thanks for the fix. Thats right. This is what MySQL behavior, set global character_set_server only affect other sessions except the current one. set session character_set_server only works for current one.