[MDEV-4489] Replication of big5, cp932, gbk, sjis strings makes wrong values on slave Created: 2013-05-06  Updated: 2013-05-14  Resolved: 2013-05-14

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.2, 5.5.30, 5.1.67
Fix Version/s: 10.0.3, 5.5.31

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None


 Description   

This does look to be a legitimate bug. This would apply to any character
set where charset_info_st field escape_with_backslash_is_dangerous is
true, which currently is: big5, cp932, gbk, sjis.

The problem here is that string parameters coming from prepared
statements are being converted into 0xHHHH form indiscriminately in
append_query_string, which is producing the string to be binlogged for
statement-based replication. While that works okay for insertion of
strings into string fields, it causes the
conversion-from-string-to-integer which is happening on the master for
insertion of a string into an integer field to not be happening on the
slave, since 0xHHHH form is more properly an integer than a string.

This can be captured by setting a breakpoint at str_to_hex and running
this test case:

DROP TABLE IF EXISTS t;
CREATE TABLE t (id INT NOT NULL AUTO_INCREMENT, a INT, PRIMARY KEY(id));
SET NAMES gbk;
PREPARE STMT FROM 'INSERT INTO t (a) VALUES (?)';
SET @a = '1';
EXECUTE STMT USING @a;

Using SHOW BINLOG EVENTS shows that the problem is from the server (binlogging) side:

    Log_name: 0.000001
         Pos: 450
  Event_type: Query
   Server_id: 1
End_log_pos: 544
        Info: use `test`; INSERT INTO t (a) VALUES (0x31) 

0xHHHH is a MySQL extension. It's a hybrid thing.
It can behave as a number and a string depending on context.

Binary log could use the X'HHHH' notation instead:
INSERT INTO t1 VALUES (a) VALUES (X'31');

which is an SQL standard thing, and which must always be a string.

However, it seems the behaviour of X'HHHH' and of 0xHHHH
is exactly the same, and X'HHHH' can also act as a number:

drop table if exists t1;
create table t1 (id int);
insert into t1 values (x'31'),(0x31),(concat(0x31));
select * from t1;
+------+
| id   |
+------+
|   49 | <-- wrong
|   49 | <-- ok
|    1 | <-- ok
+------+ 

Proposed fix:
1. Fix X'HHHH' to work always as string.
2. Fix binlog to use X'HHHH'



 Comments   
Comment by Alexander Barkov [ 2013-05-06 ]

A patch sent for review.

Comment by Alexander Barkov [ 2013-05-14 ]

Approved. Pushed into mariadb-5.5.31.

Generated at Thu Feb 08 06:56:50 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.