MySQL Bugs: #12671: SHOW CREATE DATABASE shows versioned CHARACTER SET specification

Bug #12671	SHOW CREATE DATABASE shows versioned CHARACTER SET specification
Submitted:	19 Aug 2005 10:32	Modified:	15 Dec 2010 10:08
Reporter:	Anders Karlsson	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.10, 5.0.91, 5.1.53	OS:	Any (Linux)
Assigned to:		CPU Architecture:	Any

Description:
The SHOW CREATE command doesn't show the "versioned" commented attributes for SHOW CREATE TABLE, this is a feature of mysqldump. But for SHOW CREATE DATABASE, the DEFAULT CHARACTER SET is shown as such a version commented attribute. This is inconsistent behaviour.

How to repeat:
mysql> create database test2;
Query OK, 1 row affected (0.00 sec)

mysql> use test2;
Database changed
mysql> create table t1(c1 integer);
Query OK, 0 rows affected (0.06 sec)

mysql> show create table t1;
+-------+--------------------------------------------------------------------------------------+
| Table | Create Table                                                                         |
+-------+--------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `c1` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create database test2;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| test2    | CREATE DATABASE `test2` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

This problem (I'd call it a bug, as we have inconsistency here in the way we represent metadata) is still repeatable with current versions:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.91-community-nt MySQL Community Edition (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show create database test;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `i` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.25 sec)

mysql> exit
Bye

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 5.1.53-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show create database test;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

Still identical in 8.0.3.

I'm not saying this should be fixed, I don't have an opinion. But having a well established behaviour documented as a bug (bugs.mysql.com also serves as a documentation) is probably the less desirable situation.