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: Sergey Glukhov
Tags: regression
Triage: Triaged: D3 (Medium)

[14 Jun 2009 6:54] li binmeng
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
[14 Jun 2009 7:01] li binmeng
i am sorry i am not familiar with mysql, sorry, sorry
[15 Jun 2009 8:08] Sveta Smirnova
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';
[15 Jun 2009 8:09] Sveta Smirnova
test case for the testsuite

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

[15 Jun 2009 12:03] li binmeng
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.
[17 Jun 2009 6:54] Sergey Glukhov
The problems is not related to Bug #36680.
[19 Jun 2009 8:49] Bugs System
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.
[19 Jun 2009 10:04] Alexander Barkov
The patch  http://lists.mysql.com/commits/76643 looks ok to push.
[25 Jun 2009 7:23] Bugs System
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.
[8 Jul 2009 13:30] Bugs System
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)
[9 Jul 2009 7:36] Bugs System
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)
[10 Jul 2009 11:21] Bugs System
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)
[21 Jul 2009 18:55] Paul Dubois
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.
[12 Aug 2009 22:15] Paul Dubois
Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.
[14 Aug 2009 23:06] Paul Dubois
Ignore previous comment about 5.4.2.
[26 Aug 2009 13:46] Bugs System
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)
[26 Aug 2009 13:46] Bugs System
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)
[26 Aug 2009 13:48] Bugs System
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)
[27 Aug 2009 16:33] Bugs System
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)
[7 Oct 2009 18:32] Paul Dubois
The 5.4 fix has been pushed to 5.4.2.