MySQL Bugs: #45485: replication different between master/slaver using procedure with gbk

Bug #45485	replication different between master/slaver using procedure with gbk
Submitted:	14 Jun 2009 6:54	Modified:	21 Jul 2009 18:55
Reporter:	li binmeng	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S3 (Non-critical)
Version:	5.1.30, 5.1, 6.0	OS:	Linux
Assigned to:	Sergei Glukhov	CPU Architecture:	Any
Tags:	regression

Description:
i use mysql.5.1.30, with a master, a slave; and use PROCEDURE to update the master, i found that for some GBK blob item; the slave get the wrong data, while the master is fine; like 0xe9 00 00 will become 0xe9 5c 30 00 one the slave database;

i change mysql version to 5.0.22, then, it works fine;

How to repeat:
1. install mysql
install master/slave mysql with default gbk charset；
./configure --with-charset=gbk --with-extra-charsets=all --prefix=/home/space/database/mysql.5.1.30.host/

modify master mysql port to 3380
slave mysql port to 3360
and add to slave mysql my.cnf
server-id   = 2
master-host = 127.0.0.1
master-port = 3380
master-user=root

2. create table and procedure
create database mytest;
CREATE TABLE `mytest` (
  `a` blob
) ENGINE=MyISAM DEFAULT CHARSET=gbk;

CREATE PROCEDURE `my_proc`(IN val BLOB)
BEGIN
     SET @tval = val;
     SET @sql_cmd = CONCAT_WS(' ', 'insert into mytest(a) values(?)');
     PREPARE stmt FROM @sql_cmd;
     EXECUTE stmt USING @tval;
     DEALLOCATE PREPARE stmt;
END

3.
now, on the master mysql, add a row

mysql> SET @`tcontent`:=_binary 0x50434B000900000000000000E9000000 COLLATE `binary`/*!*/;
Query OK, 0 rows affected (0.00 sec)

mysql> call my_proc(@`tcontent`);
Query OK, 0 rows affected (0.00 sec)

mysql> select hex(a) from mytest;
+----------------------------------+
| hex(a)                           |
+----------------------------------+
| 50434B000900000000000000E9000000 | 
+----------------------------------+
1 row in set (0.00 sec)

but on the slave
mysql> select hex(a) from mytest;
+------------------------------------+
| hex(a)                             |
+------------------------------------+
| 50434B000900000000000000E95C300000 |  
+------------------------------------+
1 row in set (0.00 sec)

4. some analyze

when i run ./bin/mysqlbinlog var/mysql-bin.00001, i get 
     insert into mytest(a) values('PCK\0     \0\0\0\0\0\0\0閈0\0\0')
in the binlog; actually, if is in gbk code, it should be something like.
     insert into mytest(a) values('PCK\0     \0\0\0\0\0\0\0\閈0\0\0')

i use gdb to follow the code, find that in item.cc::set_from_user_var(THD *thd, const user_var_entry *entry)
it looks like use charset binary, but not gbk.

compared with 5.0.22, the binlog has been different， in 5.0.22 is like
SET @`tval`:=_binary 0x50434B000900000000000000E9000000 COLLATE `binary`/*!*/;
insert into mytest values(@tval);

i think it's the reason why 5.0.22 works fine;

by the way, is there some suggestion about how to use charset well?
i find it very complex to use

Suggested fix:
i am sorry i am familiar with mysql，is it ok if we change the charset in item.cc::set_from_user_var to the mysql server's charset; 
at lest, the master and the slave should use the same charset in replication

i am sorry i am not familiar with mysql, sorry, sorry

Thank you for the report.

Verified as described. Should be checked if it is duplicate of bug #36680 when bug #36680 is fixed.

Workaround: set binlog_format='row';

test case for the testsuite

Attachment: rpl_bug45485.test (application/octet-stream, text), 615 bytes.

howerver is the description different with bug #36680？
in my case, the insert operation works fine on the master, but the master write the incorrect bin log.

set binlog_format='row'; can really the solve this problem.
but i don't like "binlog_format = row" too much because it makes me hard to read the real operation from the bin log.
also, i use the group of master/slave mysqls for a lot of other applications.
so i need to make a lot of tests to make sure this config will not bring other problems.

thanks.

The problems is not related to Bug #36680.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/76643

2936 Sergey Glukhov	2009-06-19
      Bug#45485 replication different between master/slaver using procedure with gbk
      In Item_param::set_from_user_var
      value.cs_info.character_set_client is set
      to 'fromcs' value. It's wrong, it should be set to
      thd->variables.character_set_client.
     @ mysql-test/r/ctype_gbk_binlog.result
        test result
     @ mysql-test/t/ctype_gbk_binlog.test
        test case
     @ sql/item.cc
        In Item_param::set_from_user_var
        value.cs_info.character_set_client is set
        to 'fromcs' value. It's wrong, it should be set to
        thd->variables.character_set_client.

The patch  http://lists.mysql.com/commits/76643 looks ok to push.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/77111

2974 Sergey Glukhov	2009-06-25
      Bug#45485 replication different between master/slaver using procedure with gbk
      In Item_param::set_from_user_var
      value.cs_info.character_set_client is set
      to 'fromcs' value. It's wrong, it should be set to
      thd->variables.character_set_client.
     @ mysql-test/r/ctype_gbk_binlog.result
        test result
     @ mysql-test/t/ctype_gbk_binlog.test
        test case
     @ sql/item.cc
        In Item_param::set_from_user_var
        value.cs_info.character_set_client is set
        to 'fromcs' value. It's wrong, it should be set to
        thd->variables.character_set_client.

Pushed into 5.1.37 (revid:joro@sun.com-20090708131116-kyz8iotbum8w9yic) (version source revid:staale.smedseng@sun.com-20090626084621-g6zteyvzehto95j0) (merge vers: 5.1.37) (pib:11)

Pushed into 5.1.37 (revid:joro@sun.com-20090708131116-kyz8iotbum8w9yic) (version source revid:staale.smedseng@sun.com-20090626084621-g6zteyvzehto95j0) (merge vers: 5.1.37) (pib:11)

Pushed into 5.4.4-alpha (revid:anozdrin@bk-internal.mysql.com-20090710111017-bnh2cau84ug1hvei) (version source revid:sergey.glukhov@sun.com-20090625063835-mubi5gq27yd4eqza) (merge vers: 5.4.4-alpha) (pib:11)

Noted in 5.1.37, 5.4.4 changelogs.

For replication of a stored procedure that uses the gbk character
set, the result on the master and slave differed.

Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.

Ignore previous comment about 5.4.2.

Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)

Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)

Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

The 5.4 fix has been pushed to 5.4.2.