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.
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.