Hello!
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
jhx1008's test case (fixed up a bit):
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;
This is the stack backtrace at that point (sorry, this is from
MySQL-5.5, not MariaDB, since that's what I had handily set up for
debugging, and I suspected this problem to be much broader than MariaDB):
#0 str_to_hex (to=0x7fffe0029f60 "", from=0x7fffe002ab90 "1", len=1) at
sql/log_event.cc:585
#1 0x00000000006dee14 in append_query_string (thd=0x15b9bb0,
csinfo=0xeebd00 <my_charset_gbk_chinese_ci>, from=0x7fffe000d400,
to=0x7ffff404e9d0) at sql/log_event.cc:616
#2 0x000000000066da2d in Item_param::query_val_str
(this=0x7fffe000d3f0, thd=<optimized out>, str=0x7ffff404e9d0) at
sql/item.cc:3333
#3 0x000000000058a138 in insert_params_from_vars_with_log
(stmt=<optimized out>, varnames=..., query=0x7ffff404eaf0) at
sql/sql_prepare.cc:1216
#4 0x000000000058c314 in Prepared_statement::set_parameters
(this=this@entry=0x7fffe0026660,
expanded_query=expanded_query@entry=0x7ffff404eaf0, packet=0x0,
packet_end=<optimized out>) at sql/sql_prepare.cc:3364
#5 0x000000000058ce50 in Prepared_statement::execute_loop
(this=0x7fffe0026660, expanded_query=0x7ffff404eaf0,
open_cursor=<optimized out>, packet=<optimized out>,
packet_end=<optimized out>) at sql/sql_prepare.cc:3432
#6 0x000000000058d04a in mysql_sql_stmt_execute (thd=<optimized out>)
at sql/sql_prepare.cc:2634
#7 0x000000000057d948 in mysql_execute_command
(thd=thd@entry=0x15b9bb0) at sql/sql_parse.cc:2161
#8 0x0000000000580480 in mysql_parse (parser_state=0x7ffff40500c0,
thd=0x15b9bb0, rawbuf=<optimized out>, length=<optimized out>) at
sql/sql_parse.cc:5627
#9 mysql_parse (thd=0x15b9bb0, rawbuf=<optimized out>, length=21,
parser_state=0x7ffff40500c0) at sql/sql_parse.cc:5551
#10 0x00000000005816b6 in dispatch_command (command=COM_QUERY,
thd=0x15b9bb0, packet=<optimized out>, packet_length=<optimized out>) at
sql/sql_parse.cc:1037
#11 0x000000000060a666 in do_handle_one_connection
(thd_arg=thd_arg@entry=0x15b9bb0) at sql/sql_connect.cc:853
#12 0x000000000060a6ca in handle_one_connection
(arg=arg@entry=0x15b9bb0) at sql/sql_connect.cc:772
#13 0x00000000008b3f35 in pfs_spawn_thread (arg=0x15eacf0) at
storage/perfschema/pfs.cc:1015
#14 0x00007ffff77a5e9a in start_thread () from
/lib/x86_64-linux-gnu/libpthread.so.0
#15 0x00007ffff6a96cbd in clone () from /lib/x86_64-linux-gnu/libc.so.6
#16 0x0000000000000000 in ?? ()
Using SHOW BINLOG EVENTS shows that the problem is from the server
(binlogging) side:
*************************** 6. row ***************************
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)
Seems like this bug has existed since at least 2006. It would seemingly
make replication completely broken when using prepared statements along
with any of big5, cp932, gbk, sjis character sets.
Regards,
Jeremy
On Sat, Apr 27, 2013 at 7:31 PM, jhx1008 <jhx1008@xxxxxxxxx
<mailto:jhx1008@xxxxxxxxx>> wrote:
__
__
hi all:
I have a problem about the type cast when I have read the source of
Maria & MySQL
I notice that when cast from string into int, Maria & MySQL do it
like the C function atoi
code:
*for (ul= 0 ; str < end9 && (ch= (uchar) (*str - '0')) < 10; str++)
{
ul= ul * 10 + ch;
}*
but cast from hex into int, the code is:
code:
*char *end=(char*) str_value.ptr()+str_value.length(),
*ptr=end-min(str_value.length(),sizeof(longlong));
ulonglong value=0;
for (; ptr != end ; ptr++)
value=(value << 8)+ (ulonglong) (uchar) *ptr;*
so I do the test blew:
*create table t(id int auto_increment primary key, a int)engine=innodb;*
*insert into t(a) values('1');*
*insert into t(a) values(0x31);*
everything is ok, I get the result:
mysql> select * from t;
+----+------+
| id | a |
+----+------+
| 1 | 1 |
| 2 | 49 |
+----+------+
the value 0x31 is the ascii of the string '1', but we got the
different result after inserting
maybe it's not a problem, but in the replication environment, it
maybe cause the data of slave not the same as master
In the master, when we set the connection characterset into
multi-bytes characterset and this characterset may escape with
backslash is dangerous (like gbk, cp932) and then we use the
prepared statement to do the insert, the string value in the insert
must be cast into hex before write into the binlog, so we do the
insert(insert into t(a) values('1')) on the master, but in the slave
it does the another(insert into t(a) values(0x31)) , the data is not
the same.
how to reappear:
master(binlog format=statement):
*create table t(id int auto_increment primay key, a int) engine=innodb;*
*set names gbk;*
*prepare stmt from 'insert into t(a) values(?)';*
*set @a='1';*
*execute stmt using @a;*
the result in master is:
mysql> select * from t;
+----+------+
| id | a |
+----+------+
| 1 | 1 |
+----+------+
and result in slave is:
mysql> select * from t;
+----+------+
| id | a |
+----+------+
| 1 | 49 |
+----+------+
I test the newest release version mariadb-10.0.2 and MySQL-5.6.10,
the problem is still exist, is this a bug?
2013-04-28
------------------------------------------------------------------------
jhx1008 @netease
__
_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@xxxxxxxxxxxxxxxxxxx
<mailto:maria-developers@xxxxxxxxxxxxxxxxxxx>
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@xxxxxxxxxxxxxxxxxxx
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
