Bug #8998 information_schema: Table SCHEMATA should report default collations
Submitted: 6 Mar 2005 17:30 Modified: 9 May 2005 2:41
Reporter: Alexander M. Turek Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.3-alpha-nightly-20050223 OS:Linux (Linux 2.6)
Assigned to: Sergei Glukhov CPU Architecture:Any

[6 Mar 2005 17:30] Alexander M. Turek 
Description:
The table information_schema.SCHEMATA contains a column named DEFAULT_CHARACTER_SET_NAME, which reports the default character set of the corresponding database.

But, a table can also have a default collation, so it would be more helpful to report the default collation rather than just the character set, imho.

How to repeat:
mysql> CREATE DATABASE foo DEFAULT CHARACTER SET latin1 COLLATE latin1_german1_ci;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM information_schema.SCHEMATA WHERE SCHEMA_NAME='foo';
+--------------+-------------+----------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | SQL_PATH |
+--------------+-------------+----------------------------+----------+
| NULL         | foo         | latin1                     | NULL     |
+--------------+-------------+----------------------------+----------+
1 row in set (0.00 sec)

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

Suggested fix:
Replace column DEFAULT_CHARACTER_SET_NAME by DEFAULT_COLLATION_NAME.
[25 Mar 2005 15:51] Alexander M. Turek 
While I originally meant this as a feature request, I now consider this as a bug since the only way to retreive a database's default collation is via SHOW CREATE DATABASE.
[6 May 2005 15:18] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/24628
[7 May 2005 7:30] Sergei Glukhov 
Fixed in 5.0.6 tree
[9 May 2005 2:41] Paul DuBois 
Noted in 5.0.6 changelog.