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
Triage: Triaged: D4 (Minor)

[19 Aug 2005 10:32] Anders Karlsson
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>
[15 Dec 2010 10:08] Valeriy Kravchuk
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)
[13 Nov 2017 20:42] Federico Razzoli
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.