MySQL Bugs: #24381: setString() put a wrong escaping character into some GBK encoded values

Bug #24381	setString() put a wrong escaping character into some GBK encoded values
Submitted:	17 Nov 2006 4:27	Modified:	22 Nov 2006 6:38
Reporter:	McEase Tu	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	mysql-connector-java-5.0.4	OS:	Windows (Windows/Linux)
Assigned to:		CPU Architecture:	Any

Description:
It seems that mysql j-connecter doesn't deal with character-set correctly.

I have a table which is GBK encoded. When I try to insert a row through PreparedStatement, It works correctly but will generate incorrect binlog for some GBK characters. The reason is that PreparedStatement.setString() will do character escaping for some special characters while some GBK characters contain such specail characters. For example, the GBK code of "黒" is 0xFC 0x5C, in which 0x5C ("\\") will be escaped. For such binlog, I can't use it for recovering because it will lead to syntax error.

How to repeat:
1. create a GBK encouded table and clean binlog (not necessary):

use test;
CREATE TABLE `test` (
  `s` varchar(16) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk;
reset master;

2. Insert a row through following java program:

public class EncodeTest {
	public static void main (String[] args) {
		Connection conn = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test?user=root");
			String sql = "insert into test values(?)";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setEscapeProcessing(false);
			pstmt.setString(1, "黒");
			pstmt.execute();
			pstmt.close();
			conn.close();
			System.out.println("OK");
		}
		catch (Exception e) {
			e.printStackTrace();
		}
	}
}

3. dump the binlog:

# at 102
#061117 10:54:17 server id 1  end_log_pos 195   Query   thread_id=30    exec_time=0     error_code=0
use test;
SET TIMESTAMP=1163732057;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
SET @@session.sql_mode=2097152;
/*!\C gbk */;
SET @@session.character_set_client=28,@@session.collation_connection=28,@@session.collation_server=28;
insert into test values('黒\');
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

4. The output above contains an INSERT statement which is not correct in syntax.

If the data ends up correct in the table on the master, but the binlog is incorrect, then it's more than likely a server issue (so I've adjusted the category accordingly). What version of the server are you using?

I've tried mysql-5.0.26 and mysql-5.1.11. Both of them have this problem.

I think it's a bug of j-connector but not the server because when I execute the statement "insert into test values('黒')" through mysql command-line tool it won't generate wrong binlog.

If it makes it into the master's table, it's not wrong escaping on the client's part. 

You can't demonstrate this error with the mysql command-line client, because you're using prepared statements with the JDBC driver.

The data in master table seems correct. The only problem is sometimes it generates incorrect binlog through JDBC's PreparedStatement.

So, it's a bug of the server?

Yes, it is a server bug. Looks like a duplicate of Bug #24492 (that has higher priority and verified already). Same problem with 0x5C, but for sjis there.