Details
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'
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
A patch sent for review.