Bug #42203 | cannot change the character_set_database value with ALTER DATABASE or db.opt | ||
---|---|---|---|
Submitted: | 19 Jan 2009 20:06 | Modified: | 19 Jan 2009 21:16 |
Reporter: | Sheeri Cabral (Candidate Quality Contributor) | Email Updates: | |
Status: | Can't repeat | Impact on me: | |
Category: | MySQL Server: Charsets | Severity: | S2 (Serious) |
Version: | 5.1.30-log Source distribution | OS: | Linux (CentOS 4.5, x86_64) |
Assigned to: | CPU Architecture: | Any | |
Tags: | alter database, BIG5, db.opt |
[19 Jan 2009 20:06]
Sheeri Cabral
[19 Jan 2009 20:08]
Sheeri Cabral
It helps when the web form actually accepts my content. Take 2: mysql> create database test_charset default charset big5; Query OK, 1 row affected (0.07 sec) mysql> \! more /dbs/mysql/test_charset/db.opt default-character-set=big5 default-collation=big5_chinese_ci mysql> use test_charset; Database changed mysql> show session variables like '%set_database%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | character_set_database | big5 | +------------------------+-------+ 1 row in set (0.00 sec) #### OK so here we've established that the db was created and the db.opt file is correct, as is the character_set_database session variable. #### now, let's changed the charset: mysql> alter database test_charset default charset latin1; Query OK, 1 row affected (0.00 sec) mysql> show session variables like '%set_database%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | character_set_database | big5 | +------------------------+-------+ 1 row in set (0.00 sec) mysql> \! more /dbs/mysql/test_charset/db.opt default-character-set=big5 default-collation=big5_chinese_ci ### Hrm, let's try changing charset, not default charset? mysql> \! more /dbs/mysql/test_charset/db.opt default-character-set=big5 default-collation=big5_chinese_ci mysql> alter database test_charset charset latin1; Query OK, 1 row affected (0.00 sec) mysql> \! more /dbs/mysql/test_charset/db.opt default-character-set=big5 default-collation=big5_chinese_ci mysql> show session variables like '%set_database%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | character_set_database | big5 | +------------------------+-------+ 1 row in set (0.00 sec) #### nope, still doesn't work. I'm pulling out the big guns, and modifying db.opt manually: mysql> \! more /dbs/mysql/test_charset/db.opt default-character-set=latin1 default-collation=latin1_swedish_ci mysql> show session variables like '%set_database%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | character_set_database | big5 | +------------------------+-------+ 1 row in set (0.00 sec) ####odd! Well, let's try flushing table to pick up the change? mysql> flush tables; Query OK, 0 rows affected (0.01 sec) mysql> show session variables like '%set_database%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | character_set_database | big5 | +------------------------+-------+ 1 row in set (0.00 sec) mysql> \! more /dbs/mysql/test_charset/db.opt default-character-set=latin1 default-collation=latin1_swedish_ci #### if I alter the database now, the db.opt file is written -- WITH THE WRONG INFORMATION!! mysql> alter database test_charset charset latin1; Query OK, 1 row affected (0.00 sec) mysql> show session variables like '%set_database%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | character_set_database | big5 | +------------------------+-------+ 1 row in set (0.01 sec) mysql> \! more /dbs/mysql/test_charset/db.opt default-character-set=big5 default-collation=big5_chinese_ci
[19 Jan 2009 21:16]
Mark Leith
Hi Sheeri, I can't repeat this: Medusa:~/mysql mark$ mysql -u root -S mysql-5.1.30.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.30-log MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create database test_charset default charset big5; Query OK, 1 row affected (0.00 sec) mysql> use test_charset; Database changed mysql> show session variables like '%set_database%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | character_set_database | big5 | +------------------------+-------+ 1 row in set (0.00 sec) mysql> alter database test_charset default charset latin1; Query OK, 1 row affected (0.01 sec) mysql> show session variables like '%set_database%'; +------------------------+--------+ | Variable_name | Value | +------------------------+--------+ | character_set_database | latin1 | +------------------------+--------+ 1 row in set (0.00 sec) Can you repeat this on a standard distribution by any chance? What modifications does your source distribution have?