MySQL Bugs: #54380: Character set is not replicated thru 'INSERT DELAYED' when different from global

Bug #54380	Character set is not replicated thru 'INSERT DELAYED' when different from global
Submitted:	9 Jun 2010 19:35	Modified:	12 Jan 2015 15:22
Reporter:	Fred Crandall	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.0.84, 5.0.92, 5.1 bzr	OS:	Linux (Debian & Gentoo)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	character set, replication

Description:
Hi All,

In a master-slave replication setup on MySQL v5.0.84 I have the following issue. If the database is in a different character set then the server (in this case the server global is utf8 and the database is latin1). And a client connects to the server and to that specific database using latin1 and does a 'INSERT DELAYED' rather then a INSERT, the character set value for the data is NOT replicated to the slave. Regular INSERT's work fine but not 'INSERT DELAYED'. As a result unique characters in each character set are not replicated correctly.

I was able to duplicate the issue on another master-slave setup running 5.0.32  (the server global was latin1 and the specific db was utf8)

How to repeat:
Global char set on master and slave is utf8

CREATE DATABASE `test95` DEFAULT CHARACTER SET latin1;
USE test95;
CREATE TABLE `test1` ( `time` int(10), `blah` tinytext ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT DELAYED INTO test1 (time, blah) VALUES (UNIX_TIMESTAMP(),'shorty’s');

Compare the data between the master and slave. The hyphen in 'shorty’s' (or any other character that different between the char sets) will not have replicated properly.

Thank you for the report.

Verified as described.

Repeatable with all versions.

Test case for MTR:

$cat src/tests/t/rpl_bug54380.test 
--source include/master-slave.inc

CREATE DATABASE `test95` DEFAULT CHARACTER SET latin1;
USE test95;
CREATE TABLE `test1` ( `time` int(10), `blah` tinytext ) ENGINE=MyISAM DEFAULT
CHARSET=latin1;
INSERT DELAYED INTO test1 (time, blah) VALUES (UNIX_TIMESTAMP(),'shorty’s');
select * from test1;

sleep 1;
connection slave;
select * from test95.test1;

$cat src/tests/t/rpl_bug54380-master.opt 
--character-set-server=utf8

$cat src/tests/t/rpl_bug54380-slave.opt 
--character-set-server=utf8

Can you clarify what you mean by "not replicated properly" ? 

We are having issues that sound the same as this where the slave replication stops with an SQL error.

"not replicated properly", would be a character in latin that doesn't correlate to the same character in utf8 (like a hyphen). It would be entered in one format on the master and then replicated to the slave as something else in the situation described above. In this case, only when using 'INSERT DELAYED' rather then a 'INSERT'. 

I also have slave-skip-errors set to 'all' on my slave so that replication would not break completely when coming upon a problem like this. Relying on the mysql checksum script that's part of maatkit instead, to let me know when there's a problem.

Any update on this issue ?

It's older than 1 year now, and no "pushed into" or "duplicate bug" record,
though it's marked "serious"

we are hitting this bug right now, just after we upgraded from this bug
http://bugs.mysql.com/bug.php?id=23966

are they related in code base and can be fixed similar way, maybe ?

best regards
Andreas

Please ignore my previous comment. Here's what I had meant to say:

DELAYED has been deprecated in MySQL 5.6 (http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-6.html), and removed in MySQL 5.7 (http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-0.html).