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:
None 
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
Description:
I cannot change the value of the character_set_database with ALTER DATABASE nor with db.opt.

How to repeat:
mysql> create database test_charset default charset big5;
Query OK, 1 row affected %2
[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?

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.050 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.