Bug #58956 Tables created on master are created on slave with wrong character set
Submitted: 15 Dec 2010 21:50 Modified: 17 Dec 2010 12:04
Reporter: chris valaas Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.5.7 OS:Any
Assigned to: CPU Architecture:Any
Tags: character sets, charset, replication

[15 Dec 2010 21:50] chris valaas
Description:
After creating a new table on our master (whose default charset is utf8), our slave creates the table as latin1, even though the slave also has utf8 as the default charset.

How to repeat:
master> > show variables like 'char%';
+--------------------------+-----------------------------------------------------+
| Variable_name            | Value                                               |
+--------------------------+-----------------------------------------------------+
| character_set_client     | utf8                                                | 
| character_set_connection | utf8                                                | 
| character_set_database   | utf8                                                | 
| character_set_filesystem | binary                                              | 
| character_set_results    | utf8                                                | 
| character_set_server     | utf8                                                | 
| character_set_system     | utf8                                                | 
| character_sets_dir       | /u0/mysql-5.5.7-rc-solaris10-x86_64/share/charsets/ | 
+--------------------------+-----------------------------------------------------+

master> create table test_utf8 ( id int,blah varchar(100)) CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

master> show create table test_utf8;
+-----------+---------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                    |
+-----------+---------------------------------------------------------------------------------------------------------------------------------+
| test_utf8 | CREATE TABLE `test_utf8` (
  `id` int(11) DEFAULT NULL,
  `blah` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 | 
+-----------+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

Meanwhile, on the slave:

slave> show variables like '%char%';
+--------------------------+-----------------------------------------------------+
| Variable_name            | Value                                               |
+--------------------------+-----------------------------------------------------+
| character_set_client     | utf8                                                | 
| character_set_connection | utf8                                                | 
| character_set_database   | utf8                                                | 
| character_set_filesystem | binary                                              | 
| character_set_results    | utf8                                                | 
| character_set_server     | utf8                                                | 
| character_set_system     | utf8                                                | 
| character_sets_dir       | /u0/mysql-5.5.7-rc-solaris10-x86_64/share/charsets/ | 
+--------------------------+-----------------------------------------------------+
8 rows in set (0.01 sec)

slave> show create table test_utf8;
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                          |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_utf8 | CREATE TABLE `test_utf8` (
  `id` int(11) DEFAULT NULL,
  `blah` varchar(100) COLLATE latin1_german1_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci | 
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
Specifing "CHARSET=utf8" explicitly in the create statement will cause the slave to create a utf8 table properly. But one would think that wouldn't be necessary.
[15 Dec 2010 23:23] Sveta Smirnova
Thank you for the report.

> master> create table test_utf8 ( id int,blah varchar(100)) CHARSET=utf8;
...
> Specifing "CHARSET=utf8" explicitly in the create statement will cause the slave to
create a utf8 table properly. 

Have you made a typo in how-to-repeat instructions?

Also I can not repeat described behavior. Please try with current version 5.5.8 and if problem still exists send both master and slave configuration files.
[16 Dec 2010 0:29] chris valaas
Oops, yes. That first create table statement run on the master should NOT contain "CHARSET=utf8".

We've been having this problem with all versions of the 5.5 branch that we've used thus far. And when the master was running 5.4.* (slaves still 5.5.*).

We'll try to repeat with 5.5.8 if we can fit an upgrade into our schedule.
[16 Dec 2010 5:07] Valeriy Kravchuk
Also, please, send the results of

echo $LANG

from master and slave.
[16 Dec 2010 7:25] chris valaas
master# echo $LANG

master#

[...]

slave# echo $LANG
en_US.UTF-8
slave#
[16 Dec 2010 10:22] Peter Laursen
An idea only! You have 

character_set_database   | utf8

.. but are you sure that you USEd the correct database before getting this?

Peter
(not a MySQL person).
[16 Dec 2010 12:50] Sveta Smirnova
Thank you for the feedback.

Please also send both master and slave configuration files.
[16 Dec 2010 20:08] Sveta Smirnova
Thank you for the feedback.

I still can not repeat described behavior. What is the content of /etc/init.sql on both master and slave?
[16 Dec 2010 20:13] chris valaas
No such files on either system.

Could this be a Solaris-specific issue?
[16 Dec 2010 20:50] Sveta Smirnova
Thank you for the feedback.

Everything can affect it: we test every release on all supported paltforms and have not seen such issue before. Therefore it can matter what is in the init.sql
[16 Dec 2010 21:36] Sveta Smirnova
Thank you for the feedback.

Cache preloading should not affect this. Please indicate also accurate package name (file name you downloaded) and accurate version of operating system you run: I try to run this in environment as much closest to yours as possible.
[17 Dec 2010 4:27] chris valaas
I've figured it out.
Apparently, all the databases on our slaves are configured with DEFAULT CHARACTER SET latin1. I have no idea why.

Doing an ALTER DATABASE xxxxx CHARACTER SET=utf8; fixes this issue.
[17 Dec 2010 4:40] chris valaas
The reason latin1 didn't show up in character_set_database is because connecting without a default database causes character_set_database to use character_set_server, apparently.
[17 Dec 2010 12:04] Sveta Smirnova
Thank you for the feedback.

Closing as "Not a Bug"