MySQL Bugs: #4500: set character set replicates incorrectly

Bug #4500	set character set replicates incorrectly
Submitted:	10 Jul 2004 13:11	Modified:	29 Aug 2004 14:16
Reporter:	Przemyslaw Popielarski	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	4.0.20	OS:	Linux (Linux x86)
Assigned to:	Guilhem Bichot	CPU Architecture:	Any

Description:
I've got two Linux x86 servers: master and slave, both with MySQL 4.0.20
from the same package. On the master I do:
SET CHARACTER SET cp1250_latin2;
UPDATE `my_tab` SET `my_field`='my_cp1250_string';
and got both of these queries in both master's and slave's log.

On master the data is properly translated into latin2 and I get latin2
characters in my_field. On slave I don't, there are still cp1250 characters.

I think, although not 100% sure, thas this worked okey in 4.0.18.
Any ideas?

How to repeat:
On masters execute the following queries:
SET CHARACTER SET cp1250_latin2;
(you need to have this translation table compiled in)
UPDATE `my_tab` SET `my_field`='my_cp1250_string';
(my_cp1250_string is any string containing cp1250 characters).

Check if you got iso-8859-2 (latin2) characters on master and slave.

Suggested fix:
iso-8859-2 (latin2) characters should be on master and slave.

Hello,
It is expected to fail. And I don't think it worked in 4.0.18.
This is because the:
SET CHARACTER SET cp1250_latin2;
is lost by replication (it is not stored in the master's binary log, which is the file which the slave reads). It is a known deficiency of 4.0 versions, explained in our manual:
http://dev.mysql.com/doc/mysql/en/Replication_Features.html
"Replication between MySQL servers using different character sets is discussed here. First, you must ALWAYS use the same global character set and collation (--default-character-set, --default-collation, all global character-set-related variables) on the master and the slave. Otherwise, you may get duplicate-key errors on the slave, because a key that is regarded as unique in the master's character set may not be unique in the slave's character set. Second, if the master is strictly older than MySQL 4.1.3, the character set of the session should never be made different from its global value (i.e. don't use SET NAMES, SET CHARACTER SET etc) because this character set change will not be known to the slave."
For things to work it would require an upgrade of the replication master and slave to MySQL 4.1.3. The bugfix made in 4.1.3 can't be backported to 4.0 because it's a too big bugfix for the "frozen" status of 4.0.
Regards,
Guilhem

Guilhem,

If SET CHARACTER SET cp1250_latin2 is lost by replication in 4.0, then why do I find it in both master's and slave's log?

root@slave:/var/lib/mysql# mysqlbinlog slave-relay-bin.006 |tail -n 7
#040713 18:26:50 server id 1  log_pos 40866590  Query   thread_id=4180  exec_time=0     error_code=0
SET TIMESTAMP=1089736010;
SET CHARACTER SET cp1250_latin2;
# at 40867205
#040713 18:26:50 server id 1  log_pos 40866659  Query   thread_id=4180  exec_time=0     error_code=0
SET TIMESTAMP=1089736010;
UPDATE `my_table` SET `my_field`='my_cp1250_string'  WHERE `my_id` = 'some_value' AND `UPDATETIME` = '20040713182508';
root@slave:/var/lib/mysql#

Indeed, I must have been confused!
Looks like I need to involve our main character set  expert Mr Barkov (who is on holiday the next 2 weeks). Mr Barkov: I will need to discuss this issue with you.
"premax": I am sorry this issue will have to suffer from this delay. But character set support is tricky, and if you say it may have worked in 4.0.18, I really must check with specialists if they changed something between 4.0.18 and 4.0.20.
Sorry!
Guilhem

Could verify it. Fixing it this week-end.
It's impossible that it worked in 4.0.18; the faulty code is there since long.
4.1 does not have the bug.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Thanks Premax! Fix will be in 4.0.21.
ChangeSet@1.1998.1.1, 2004-08-29 14:13:51+02:00, guilhem@mysql.com