MySQL Bugs: #35380: collation_database does not effect CREATE DATABASE without characater set

Bug #35380	collation_database does not effect CREATE DATABASE without characater set
Submitted:	18 Mar 2008 8:02	Modified:	1 Oct 2008 15:12
Reporter:	Rizwan Maredia	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S4 (Feature request)
Version:	5.1.22	OS:	Windows
Assigned to:	Alexander Barkov	CPU Architecture:	Any
Tags:	collation_database, create database

Description:
collation_database does not effects CREATE DATABASE without characater set. The value for character set and collation is picked from character_set_server and collation_server. This is a corollary to character_set_database behavior.

How to repeat:
Here is how we can test this

SET @@session.collation_server = utf8_roman_ci;
SET @@session.collation_database = latin2_croatian_ci;
CREATE DATABASE db1;
SHOW CREATE DATABASE db1;
+Database	Create Database
+db1	CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_roman_ci */

Suggested fix:
Collation_database should be used for database without character set/collation clause. Otherwise there seems to be no definite purpose for setting this variable.

Many thanks for writing a bug report.
In my eyes this is a bug and not a feature request.

Verified as described by using 5.1 bk tree.

mysql> select version();
5.1.24-rc-debug

mysql> show variables like '%char%';
character_set_database   | latin1
character_set_server     | latin1
character_set_system     | utf8

mysql> show variables like '%collat%';
collation_database   | latin1_swedish_ci | 
collation_server     | latin1_swedish_ci | 

mysql> SET @@session.collation_server = utf8_roman_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @@session.collation_database = latin2_croatian_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%collat%';
collation_database   | latin2_croatian_ci | 
collation_server     | utf8_roman_ci      |

mysql> show variables like '%char%';
character_set_database   | latin2
character_set_server     | utf8

mysql> CREATE DATABASE db1;
Query OK, 1 row affected (0.00 sec)

mysql> SHOW CREATE DATABASE db1\G
*************************** 1. row ***************************
       Database: db1
Create Database: CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_roman_ci */
1 row in set (0.00 sec)

It should use latin2_croatian_ci here.

We plan to deprecate collation_database.
WL#3811  Deprecate GLOBAL @@collation_database, @@character_set_database; assignment of SESSION counterparts