Bug #2326 Charset of table is determined by charset of db only if "USE db;"
Submitted: 8 Jan 2004 12:59 Modified: 8 Jul 2004 12:06
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.0 OS:Any (all)
Assigned to: Alexander Barkov

[8 Jan 2004 12:59] Guilhem Bichot
Description:
Hi,
I tested it on the latest 5.0; I guess it would behave the same on 4.1.

MASTER> create database test2 character set latin5;
Query OK, 1 row affected (0.00 sec)

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

MASTER> create table test2.t1 (a int);
Query OK, 0 rows affected (0.10 sec)

MASTER> show create table test2.t1;
+-------+---------------------------------------------------------------------------------------+
| Table | Create Table                                                                          |
+-------+---------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

MASTER> use test2;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MASTER> create table test2.t3 (a int);
Query OK, 0 rows affected (0.00 sec)

MASTER> show create table test2.t3;
+-------+---------------------------------------------------------------------------------------+
| Table | Create Table                                                                          |
+-------+---------------------------------------------------------------------------------------+
| t3    | CREATE TABLE `t3` (
  `a` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin5 |
+-------+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

See how t3 gets the charset of test2, but t1 does not?
The manual says:
"MySQL chooses the table character set and collation thus:
    * If both CHARACTER SET X and COLLATE Y were specified, then character set X and collation Y.
    * If CHARACTER SET X was specified without COLLATE, then character set X and its default collation.
    * Otherwise, the database character set and collation. "
It does not mention that you first have to USE the database, that the db.table syntax does not work...

How to repeat:
create database test2 character set latin5;
show create database test2;
create table test2.t1 (a int);
show create table test2.t1;
use test2;
create table test2.t3 (a int);
show create table test2.t3;

notice the difference between the charsets of t1 and t3.

Suggested fix:
Indeed, the db.opt file is read by mysql_change_db() (which is called by USE db), not in other cases.
So, from the code, it is expected to depend on USE. Still, as it's surprising for the user, I'd say it can be called either a bug in MySQL, or a doc bug.
[8 Jan 2004 13:10] Guilhem Bichot
This is a little show-stopper for replication of character sets, by the way. "USE db1" on the master calls mysql_change_db() (which reads db.opt), and causes the next query to be written with "default db = db1" in the binary log; when the slave reads the binary log, it sees "default db = db1" and all it does with this is
thd->db="db1" (mysql_change_db() is not called so db.opt is not read).
If this is not fixed, then I would have to change replication to use mysql_change_db() for every query, which is unwanted (because it would slow replication down).
[28 Jun 2004 14:00] Alexander Barkov
WL#807 is going to fix this problem.
A patch for WL#807 is currently being reviewed.