MySQL Bugs: #54791: String values are converted to hex in binlog

Bug #54791	String values are converted to hex in binlog
Submitted:	25 Jun 2010 6:37	Modified:	30 Jun 2010 4:49
Reporter:	Lei Wang	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.1.47	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	binlog, PreparedStatement, replication, useCursorFetch

Description:
We have set up an new master-slave system on MySQL5.1.47 (the original one is on MySQL5.0). But the statement-based replication often fails on statements like "insert into test (id) values(0x31)" which can be found in binlog. It is confused that the string value in a statement like "insert into test (id) values('1')" is converted into hex. For the integer column id, this binlog statment will lead to wrong replication result.

How to repeat:
Repeating this problem need to meet the following condition:

1. the global variable character_set_server=gbk;
2. set up a JDBC connection with useCursorFetch=ture;
3. create a JDBC PreparedStatement and call setString(or setObject) to assign an String value to an column.

You can refence the following code to repeat:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class BinlogTest {
	public static void main(String[] args) {
		Connection conn;
		PreparedStatement pstmt;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test?useCursorFetch=true&user=root");
			pstmt = conn.prepareStatement("insert into test (id) values(?)");
			pstmt.setString(1, "1");
			pstmt.execute();
			pstmt.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

Table test is defined as CREATE TABLE test (id int primary key)ENGINE=innodb character set=gbk;

The J-Connector version is 5.1.12.

If the variable character_set_server is set utf8, the problem will not occur.

Thank you for the problem report. Please, send the results of:

show variables like 'char%';

from the same environment.

Server version: 5.1.47-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | gbk                        |
| character_set_connection | gbk                        |
| character_set_database   | gbk                        |
| character_set_filesystem | binary                     |
| character_set_results    | gbk                        |
| character_set_server     | gbk                        |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read at http://dev.mysql.com/doc/refman/5.1/en/connector-j-usagenotes-troubleshooting.html#qandait...:

22.3.5.3.11:  You get an ER_NET_PACKET_TOO_LARGE  exception, even though the binary blob size you want to insert via JDBC is safely below the max_allowed_packet size.

This is because the hexEscapeBlock() method in com.mysql.jdbc.PreparedStatement.streamToBytes() may almost double the size of your data.

Hi Lei.

Afais, you are using streaming and there is no way, ion that case, to avoid data being converted to HEX representation by the hexEscapeBlock() method in com.mysql.jdbc.PreparedStatement.streamToBytes()...

This is the long going misunderstanding, as far back as Bug#8064. Please read Bar's and Mark's comments there as well as in Bug#36680 (!), Bug#47665, Bug#45554, Bug#33048 and so on. The proper way for inserting data via PS, and especially binary data, is to convert stream to HEX representation as escaping proves error prone especially on mb charsets.

To avoid this you can try removing SetFetchSize (and streaming in general) and add useServerPrepStmts=true to your connection string and inform us of results.

And please please review the discussions in aforementioned bugs before proceeding.

Lei, I completely missed it... Thanks to Todd here's the final words.

You are using 
pstmt.setString(1, "1");
for integer column... As a string value it does pass through hexEscapeBlock() method and there is little you can do. But why didn't you use setInt() for INT column???

Using pstmt.setString(1, "1") for integer column is a problem left over by history. I don't think it is the same issue as above.  In our replication environment, the master node can excute the statement and get corrent result. But the binary log statement "insert into test (id) values(0x31)" producted by the master node will lead to inconsistent data on the slave node. That's the problem.

I encounter the same issue.