Bug #27208 If no current database, character_set_database !=character_set_server
Submitted: 16 Mar 2007 11:39 Modified: 13 Feb 2009 20:29
Reporter: Konstantin Osipov (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Charsets Severity:S3 (Non-critical)
Version:4.1, 5.0, 5.1, 5.2 OS:Any (Linix)
Assigned to: Staale Smedseng
Triage: Triaged: D3 (Medium)

[16 Mar 2007 11:39] Konstantin Osipov
Description:
Unlike what manual says on

when one drops the current database, the value of character_set_database is taken from system_charset_info, not from global_variables.character_set_server.

Example:
mysql> set global character_set_server=latin5;
Query OK, 0 rows affected (0.00 sec)

mysql> create database foo;
Query OK, 1 row affected (0.00 sec)
mysql> use foo;
Database changed
mysql> drop database foo;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@character_set_database;
+--------------------------+
| @@character_set_database |
+--------------------------+
| latin1                   | 
+--------------------------+

How to repeat:
set global character_set_server=latin5;
create database foo;
use foo;
drop database foo;
select @@character_set_database;
[16 Mar 2007 11:41] Konstantin Osipov
Link to the manual:

http://dev.mysql.com/doc/refman/4.1/en/server-system-variables.html

character_set_database

The character set used by the default database. The server sets this variable whenever the default database changes. If there is no default database, the variable has the same value as character_set_server. This variable was added in MySQL 4.1.1.
[16 Mar 2007 11:57] Sveta Smirnova
Thank you for the report. But SET GLOBAL starts work only for next connection. For next connection all work fine:

set global character_set_server=latin5;
connect (addconroot, localhost, root,,);
connection addconroot;
create database foo;
use foo;
drop database foo;
select @@character_set_database;
[16 Mar 2007 15:52] Konstantin Osipov
Hello Sveta,

I think you missed one thing in your verification, namely - the character set of the database should be specified explicitly.
Could you please try this:

kostja@bodhi:~> mysql -uroot  
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.38-valgrind-max-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> set global character_set_server=latin5;
Query OK, 0 rows affected (0.00 sec)

mysql> Bye
kostja@bodhi:~> mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.38-valgrind-max-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database foo character set=utf8;
Query OK, 1 row affected (0.00 sec)

mysql> use foo;
Database changed
mysql> drop database foo;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@character_set_database;
+--------------------------+
| @@character_set_database |
+--------------------------+
| utf8                     | 
+--------------------------+
1 row in set (0.00 sec)
[19 Mar 2007 7:56] Sveta Smirnova
Thank you for the comment.

Verified as described. All versions are affected.
[11 Jan 2009 18:25] Staale Smedseng
This bug appears to have been fixed as part of Bug#25843 (http://lists.mysql.com/commits/33451).
[20 Jan 2009 15:59] 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/commits/63613

2748 Staale Smedseng	2009-01-20
      Adding a test to verify that Bug#27208 "If no current database, 
      character_set_database !=character_set_server" is fixed.
[21 Jan 2009 10:20] 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/commits/63681

2970 Staale Smedseng	2009-01-21 [merge]
      Adding a test to verify that Bug#27208 "If no current database,
      character_set_database !=character_set_server" is fixed.
[3 Feb 2009 9:39] Bugs System
Pushed into 5.1.32 (revid:joro@sun.com-20090203090549-gos3v4320vimrzg6) (version source revid:horst@mysql.com-20090123182108-2efo5zwexcrf0h3o) (merge vers: 5.1.32) (pib:6)
[4 Feb 2009 11:15] Bugs System
Pushed into 6.0.10-alpha (revid:kostja@sun.com-20090204104420-mw1i2u9lum4bxjo6) (version source revid:staale.smedseng@sun.com-20090121101927-youffdd39oko7id5) (merge vers: 6.0.10-alpha) (pib:6)
[13 Feb 2009 20:29] Paul Dubois
Noted in 5.1.32, 6.0.10 changelogs.

If the default database was dropped, the value of
character_set_database was not reset to character_set_server as it
should have been.
[17 Feb 2009 14:53] Bugs System
Pushed into 5.1.32-ndb-6.3.23 (revid:tomas.ulin@sun.com-20090217131017-6u8qz1edkjfiobef) (version source revid:tomas.ulin@sun.com-20090203133556-9rclp06ol19bmzs4) (merge vers: 5.1.32-ndb-6.3.22) (pib:6)
[17 Feb 2009 16:41] Bugs System
Pushed into 5.1.32-ndb-6.4.3 (revid:tomas.ulin@sun.com-20090217134419-5ha6xg4dpedrbmau) (version source revid:tomas.ulin@sun.com-20090203133556-9rclp06ol19bmzs4) (merge vers: 5.1.32-ndb-6.3.22) (pib:6)
[17 Feb 2009 18:17] Bugs System
Pushed into 5.1.32-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090217134216-5699eq74ws4oxa0j) (version source revid:tomas.ulin@sun.com-20090202111723-1zzwax187rtls913) (merge vers: 5.1.32-ndb-6.2.17) (pib:6)