Bug #59441 No information in GLOBAL 'character_set_database' variable
Submitted: 12 Jan 2011 10:42 Modified: 27 Jan 2011 12:28
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Options Severity:S3 (Non-critical)
Version:any OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: qc

[12 Jan 2011 10:42] Peter Laursen
Description:
(not sure about the category here!)

I find 'character_set_database' useless on GLOBAL level.  It has no information that 'character_set_server' has not. Or it refers implicitly the ´mysql` (or some other) database.

How to repeat:
SHOW GLOBAL VARIABLES LIKE 'character_set_server'; -- utf8
CREATE DATABASE db1 DEFAULT CHARACTER SET latin1;
CREATE DATABASE db2 DEFAULT CHARACTER SET utf8;
USE db1; 
SHOW GLOBAL VARIABLES LIKE 'character_set_database';  -- utf8
SHOW SESSION VARIABLES LIKE 'character_set_database';  -- latin1
USE db2; 
SHOW GLOBAL VARIABLES LIKE 'character_set_database'; -- utf8
SHOW SESSION VARIABLES LIKE 'character_set_database'; -- utf8

Suggested fix:
Consider remove/deprecate GLOBAL 'character_set_database' and/or document that is is or how it is not identical to GLOBAL 'character_set_server'
[12 Jan 2011 10:45] Peter Laursen
corrected synopsis
[12 Jan 2011 11:02] Valeriy Kravchuk
What exact version of MySQL server are you referring to?
[12 Jan 2011 11:39] Peter Laursen
I wrote 'any. version. 'any 5.x' at least.Test case was/is run on 5.1.54.

Also see this:

SHOW GLOBAL VARIABLES LIKE 'character_set_server'; -- utf8
SET @@global.character_set_database = latin1;
CREATE DATABASE db;
SHOW CREATE DATABASE db; -- CREATE DATABASE `db` /*!40100 DEFAULT CHARACTER SET utf8 */

GLOBAL character_set_database is not used at all, as far as I can see. It is useless because it is not used anywhere - though its value can be SET by a user having SUPER privilege.

This issue is similar to http://bugs.mysql.com/bug.php?id=45689 (where also a useless/unused variable (here a SESSION variable) is implemented<9.
[12 Jan 2011 14:00] Peter Laursen
And BTW - does this make any sense at all?  I do not think so!

-- new connection

SHOW GLOBAL VARIABLES LIKE 'character_set_server'; -- utf8
SHOW GLOBAL VARIABLES LIKE 'character_set_filesystem'; -- binary

SHOW SESSION VARIABLES LIKE 'character_set_server'; -- utf8
SHOW SESSION VARIABLES LIKE 'character_set_filesystem'; -- binary

SET @@session.character_set_server = latin1; 
SET @@session.character_set_filesystem = latin1;

SHOW SESSION VARIABLES LIKE 'character_set_server'; -- latin1
SHOW SESSION VARIABLES LIKE 'character_set_filesystem'; -- latin1

I cannot see any use of SESSION character_set_server and character_set_filesystem at all. You can SET them though (if you think that is funny :-) ).
[13 Jan 2011 7:31] Valeriy Kravchuk
Your last comment indeed shows a bug: there is no reason to let these variables (character_set_server and character_set_filesystem) be changed at session level.

As for changing character_set_database, there is a Footnote at http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_character_set_d...:

"This option is dynamic, but only the server should set this information. You should not set the value of this variable manually."

The same page explains what happened in your initial case, IMHO.
[13 Jan 2011 21:50] Peter Laursen
I do not agree! :-)

The passage from the docs "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." applies to SESSION variable.  The term 'default database' applies to a session/connection.  On the server/GLOBAL level there is no 'default database'. 

So I am afraid that i will have to add one more 'aspcect' of this bug; It should not be possible to SET SESSION 'character_set_database' in any other way than executing a USE statement. 

So

1) GLOBAL 'character_set_database' is useless.
2) SESSION 'character_set_system' and 'character_set_filesystem' are useless.
3) It should not be possible to SET SESSION 'character_set_database' in any other way than executing a USE statement.
[13 Jan 2011 22:04] Peter Laursen
I think I can share how this report started: a customer had an alert (from a monitoring tool) that global configuration had changed. This caused some panic as the DBA had not (to his best knowledge) changed configuration. 

It was possible to track that the only reason for the alert was that GLOBAL 'character_set_database' had been changed.  No general log is enabled so *how* it happened cannot be tracked unfortunately. Probably some human mistake only.
[14 Jan 2011 4:14] Valeriy Kravchuk
All 3 problems outlined look real to me.
[27 Jan 2011 12:28] Peter Laursen
Also see http://bugs.mysql.com/bug.php?id=59759