Bug #8064 With sjis config, inserting some data into blob column causes "Syntax error"
Submitted: 21 Jan 2005 9:13 Modified: 28 Jan 2005 14:13
Reporter: Shu SATO Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.0.20 OS:Linux (Redhat ES 3)
Assigned to: CPU Architecture:Any

[21 Jan 2005 9:13] Shu SATO
Description:
With default-character-set=sjis, Java client via Connector/J fails to insert
some double-byte binary data into BLOB column and catches java.sql.SQLException.

The double-byte binary data with the exception consists of:

   * 0x81-0x9F or 0xE0-0xFC placed in the first byte
   * 0x27 in the second byte

The caught exception is like the following:

  Exception in thread "main" java.sql.SQLException: Syntax error or 
  access violation message from server: "You have an error in 
  your SQL syntax.  Check the manual that corresponds to your MySQL server
  version for the right syntax to use near ''?'')' at line 1"
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1997)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1167)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1278)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:2247)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1772)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1619)
        at SQLTest03.doInsert(SQLTest03.java:32)
        at SQLTest03.main(SQLTest03.java:23)

If you set 'default-character-set' to other values than 'sjis' (eg. ujis, latin1),
this exception doesn't happen and the binary data is stored.

[Environment]
OS             : Red Hat Enterprise Linux ES release 3 (Taroon Update 2)
MySQL        : 4.0.20-standard-log
JVM            : Sun J2SDK 1.4.2_04
Connector/J : 3.0.15

[MySQL confguration]
$ cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
default-character-set=sjis
socket=/var/lib/mysql/mysql.sock
[client]
socket=/var/lib/mysql/mysql.sock

How to repeat:
Run the following simple Java program:

  $ java -classpath mysql-connector-java-3.0.15-ga-bin.jar:. SQLTest03

[Java program source]
import java.sql.*;

public class SQLTest03 {
    private static final String DRIVER_NAME = "com.mysql.jdbc.Driver";
    private static final String URL 
    	= "jdbc:mysql://<host>:3306/<dbname>?user=<username>&password=<passwd>";
    private static final String SQL 
    	= "insert into t1(bdata) values(?)";

    private static final byte[] BDATA = {
            (byte)0x81,  // 
            (byte)0x27,  // '            
    };
    
    public static void main(String[] args) throws Exception {
        Class.forName(DRIVER_NAME).newInstance();
        Connection con = DriverManager.getConnection(URL);
        doInsert(con, BDATA);        
        con.close();
    }

    private static void doInsert(Connection con, byte[] barray) throws Exception {
        PreparedStatement pstmt = con.prepareStatement(SQL); 
        pstmt.setBytes(1, barray);
        pstmt.executeUpdate();
        pstmt.close();
    }        
}

[Environment]
OS             : Red Hat Enterprise Linux ES release 3 (Taroon Update 2)
MySQL        : 4.0.20-standard-log
JVM            : Sun J2SDK 1.4.2_04
Connector/J : 3.0.15

[MySQL confguration]
$ cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
default-character-set=sjis
socket=/var/lib/mysql/mysql.sock
[client]
socket=/var/lib/mysql/mysql.sock

[Table definition]
mysql> create table t1(bdata blob);
Query OK, 0 rows affected (0.00 sec)
[21 Jan 2005 13:56] Mark Matthews
Thank you for taking the time to report a problem.  Unfortunately
you are not using a current version of the product your reported a
problem with -- the problem might already be fixed. Please download
a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with one of the latest versions,
please change the version on this bug report to the version you
tested and change the status back to "Open".  Again, thank you for
your continued support of MySQL.
[21 Jan 2005 13:57] Mark Matthews
3.0.16 is the latest released version and does have fixes in it for SJIS and escaping. Please use this version to test.
[24 Jan 2005 1:58] Shu SATO
Hi Mark,

Thank you very much for your comments.
I tried mysql-connector-java-3.0.16-ga-bin.jar but unfortunately the same exception as I reported above was reproduced.

In the CHANGES document of 3.0.16-ga, I can't find any descriptions about fixes for sjis/escaping made in 3.0.16-ga. Could you let me know any specific bug# if any fix for sjis/escaping was made in the version? 

Best regards,
Shu
[25 Jan 2005 11:29] Shu SATO
I tired the simple test program above using latest versions of MySQL server and Connector/J, and got the following results:

           MySQL  4.1.19   4.0.23a   4.0.20
--------------------------------------------
Connector/J
3.1.6                OK        FAIL         FAIL
3.0.16              FAIL      FAIL         FAIL
3.0.15              FAIL      FAIL         FAIL

In conclusion MySQL4.1.19/ConnectorJ3.1.6 is 
the only successful combination.

Because my cusutomer currently uses MySQL4.0.20/ConnectorJ3.0.15 and it's tough work for them to upgrade MySQL to 4.1.x, patching MySQL 4.0.x is a better
solution at the moment. So they would be very happy if a patch for MySQL4.0.x would be available.
[28 Jan 2005 14:13] Mark Matthews
I can't repeat this with 4.0.23, can you test with 4.0.23?:

mysql> show variables like 'character_set'\G
*************************** 1. row ***************************
Variable_name: character_set
        Value: sjis
1 row in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 4.0.23-log |
+------------+
1 row in set (0.00 sec)

with the following testcase (which is effectively the same as yours):

public void testBug8064() throws SQLException {
    	try {
    		this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug8064");
    		this.stmt.executeUpdate("CREATE TABLE testBug8064(bdata BLOB)");
    		
    		PreparedStatement pStmt = this.conn.prepareStatement("INSERT INTO testBug8064 VALUES (?)");
    		byte[] byteData = new byte[] { (byte)0x81, (byte)0x27};
    		pStmt.setBytes(1, byteData);
    		pStmt.executeUpdate();
    	} finally {
    		this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug8064");
    	}
    }
[31 Jan 2005 5:09] Shu SATO
Hello Mark,

Thanks for your testing and info. I tried my test program(attached) with MySQL4.0.23/ConnectorJ3.0.16-ga and strangely it's reproduced in my environment.

If possible, I would like you to do what I tired using my program on your environment and let me know. Steps to reproduce are described below and 
the program is attached(SQLTest06.java).

Also info on my environment are described and output file of 'show variables' is 
attatched(showvars.txt) that you could compare with yours.

=====================
Steps to reproduce
=====================
1. $ echo "show variables like 'character_set'" | mysql -u root
   Variable_name   Value
   character_set   sjis

2. $ javac -classpath <path_to>/mysql-connector-java-3.0.15-ga-bin.jar SQLTest06.java

3. $ java -classpath <path_to>/mysql-connector-java-3.0.16-ga-bin.jar:. SQLTest06
   === DatabaseProductVersion : 4.0.23-standard
   === DriverVersion()        : mysql-connector-java-3.0.16-ga ( $Date: 2004/09/30 07:35:03 $, 

$Revision: 1.27.2.44 $ )
   Exception in thread "main" java.sql.SQLException: Syntax error or access violation message
    from server: "You have an error in your SQL syntax.  Check the manual that corresponds to
    your MySQL server version for the right syntax to use near ''?\'')' at line 1"
           at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1997)
           at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1167)
           at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1278)
           at com.mysql.jdbc.Connection.execSQL(Connection.java:2251)
           at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1772)
           at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1619)
           at SQLTest06.main(SQLTest06.java:35)

===================================
Environment
===================================
[OS]
$ cat /etc/redhat-release
Red Hat Enterprise Linux ES release 3 (Taroon Update 2)

$ uname -a
Linux hpksrd 2.4.21-15.ELsmp #1 SMP Thu Apr 22 00:18:24 EDT 2004 i686 i686 i386 GNU/Linux

$ echo $LANG
C

[Java]
$ java -version
java version "1.4.2_06"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2_06-b03)
Java HotSpot(TM) Client VM (build 1.4.2_06-b03, mixed mode)

[MySQL]
Intalled from scratch using an rpm archive downloaded 
at 10.30am JST(1.30am GTM) on 31 Jan 2005 from 
http://dev.mysql.com/get/Downloads/MySQL-4.0/MySQL-server-4.0.23-0.i386.rpm/from/ftp://ftp.... 

$ cksum <path_to>/mysql/MySQL-server-4.0.23-0.i386.rpm
3699565258 11068557 <path_to>/MySQL-server-4.0.23-0.i386.rpm

$ cksum /usr/sbin/mysqld
4230688367 3890076 /usr/sbin/mysqld

$ ps -ef | grep mysqld | head -2 
root     25580     1  0 10:37 pts/1    00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/my
sql40.data --pid-file=/mysql40.data/hpksrd.pid
mysql    25604 25580  0 10:37 pts/1    00:00:00 /usr/sbin/mysqld --basedir=/ --datadir=/my
sql40.data --user=mysql --pid-file=/mysql40.data/hpksrd.pid --skip-locking

Best regards,
Shu
[31 Jan 2005 5:10] Shu SATO
Simple java client program to reproduce

Attachment: SQLTest06.java (application/octet-stream, text), 2.27 KiB.

[31 Jan 2005 5:12] Shu SATO
Results of 'show variables' in reproduction environment

Attachment: showvars.txt (text/plain), 3.19 KiB.

[31 Jan 2005 7:33] Hiromichi Mizuno
I am Hiromichi Mizuno, who is a co-worker of Shu Sato.

I investigated the problem and found possible root
caus in mysqld.

I created a patch and tested simple cases using
the following versions.

  MySQL 4.0.20
  MySQL 4.0.23a

  (Connector/J : 3.0.16)

================
a cause of error
================

1. Java Client makes "INSERT" sql statement.
   This sql statement try to byte array data into
   BLOB column using the following code.

     pstmt = con.prepareStatement(
        "INSERT INTO BLOB_TABLE (MESSAGEBLOB) VALUES(?)");

     arry[0]=(byte)0x81; // is a first byte of SJIS
     arry[1]=(byte)0x27; // is a quotation charactor "\'"
     pstmt.setBytes(1, arry);

     pstmt.executeUpdate();

2. mysqld receives the following sql statement string.

    "INSERT INTO BLOB_TABLE (MESSAGEBLOB) VALUES('\201\\'')"

  This string is in "packet" local variable of
  dispatch_command() function.

  The value of the inserted column is changed by
  Connectoer/J, I think this behaviour is right.

    from: 0x81 0x27
    to  : 0x81 0x5c 0x27

3. get_text() function try to get string data from
   above sql statement string.

   But get_text() function can not get the string
   data because of parsing failure and get a syntax error.

    '\201\\'' = 0x81 0x5c 0x27

========
FIX
========
  I fixed get_text() function in sql_lex.cc.

  New rule of get_text():

  1. If string data has SJIS code with escape
     character (0x5c), get_text() escapes the
     escape character.

  2. If string data has SJIS code with escape
     character (0x5c), get_text() copy string data
     without the escape code. So, three byte
     string with escape character is stored in
     database as two bytes string.

  Finally, the data without escape code is stored
  into MESSAGEBLOB.

    stored data : 0x81 0x27

=================
PATCH FILE
=================
A patch file is attached.

how to patch and build.
  $ cd mysql-4.0.23a/sql
  $ patch sql_lex.cc /tmp/sql_lex.cc.patch
  $ make

best regards.

-- 
Hiromichi Mizuno
[31 Jan 2005 7:43] Shu SATO
a patch kindly provided by Hiromichi Mizuno

Attachment: sql_lex.cc.patch (application/octet-stream, text), 763 bytes.

[31 Jan 2005 8:05] Shu SATO
I tested the patch for mysqld described in Hiromichi's previous comment. You can find it as the attached(sql_lex.cc.patch).

Test results:

- my simple client(SQLTest06.java, also attached) was successful.

  With MySQL 4.0.23 + Hiromichi's patch and Connector/J 3.0.16 as follows:

    $ java -classpath <path_to>/mysql-connector-java-3.0.16-ga-bin.jar:. SQLTest06
    === DatabaseProductVersion : 4.0.23a-log
    === DriverVersion()        : mysql-connector-java-3.0.16-ga ( $Date: 2004/09/30 07:35:03 $, $Revision: 1.27.2.44 $ )
    === Expected : 0x81 0x27
    === Result   : 0x81 0x27

   The client with Connector/J 3.0.15 and 3.1.6 had successful results as well.

   See my previous comment for test result(Syntax error) with original MySQL4.0.23. 

- new more unit tests were successful.

   I created some more unit tests and attached a source file containing
  them(BlobSJISTest.java).
 
   With MySQL4.0.23 + Hiromichi's patch, all the test cases in it were passed
   without any error/failure. I used Connector/J 3.0.15 but results with 3.0.16 
   might be the same.

   With orignal MySQL4.0.23,  some of the test cases were failed with the same
   exception(SQLException:Syntax error) as I reported.  

  
Thank Hiromichi for the patch and I would like MySQL developers to take a look
at it.

Best regards,
Shu
[31 Jan 2005 8:05] Shu SATO
Unit test cases for Hiromichi's patch

Attachment: BlobSJISTest.java (application/octet-stream, text), 19.96 KiB.

[1 Feb 2005 8:29] Shu SATO
Hello,

During unit tests(testsuite/**/*Test.java) of Connector/J 3.0.16
toward the mysqld patch Hiromichi provided above, a hidden problem was 
found in PreparedStatement#setString().

I and Hiromich believe the problem is not a degradation but a potential
one of Connector/J.

The following is descrption about the problem and how to fix 

PreparedStatement#setString(). A patch for PreparedStatement is 
attached as PreparedStatement.java.patch.

I think both the Hiromichi's patch for mysqld and this patch for
Connector/J are necessary to fix the behaviour of bug#8064 more effectively.

----------------------------
Explanation of the problem
----------------------------
[Environment]
- MySQL 4.0.23a + Hiromichi's patch(sql_lex.cc.patch)
- Connector/J 3.0.16

[Behaviour]
testsuite.regression.StringRegressionTest#testEscapeSJISDoubleEscapeBug()
ends with 'Error' status caused by "java.sql.SQLException : Syntax error".

For instance, if an SJIS String which s converted as 0x8c 0x5c in bytes is 
done setString() to BLOB column, Connector/J sends the String as  0x27 0x8c 0x5c 0x27. Finally the exception occures.

      original String(in SJIS)                      : 0x8c 0x5c
      bytes sent to MySQL by Connector/J  : 0x27 0x8c 0x5c 0x27

[Cause]
(1) According to specifications of the Hiromichi's patch,
    every 0x5c between the beginning and ending single quotes
    must be escaped.

(2) An SJIS String like 0x8c 0x5c contains 0x5c but it's not escaped 
    in setString()  and then 0x8c 0x5c is sent to MySQL 
    as it is(exactly surrounded with 0x27).

(3) The current setString() escapes a single quote of Unicode (
    in 'case' statement). However the SJIS String(0x8c 0x5c) is
    not a single quote of Unicode, so it is not escaped.

(4) Because of (1)-(3), a byte array containing non-escaped 0x5c
    causes parsing error in the MySQL side.

--------------------
Fix of Connector/J
--------------------
Now setString() of PreparedStatement converts any String(non-null)
to byte array without any escaping and passes the array to 
setBytes(index, array, false), which correctly does escaping.

Please see the attached patch(PreparedStatement.java.patch) for details.

Best regards,
Shu
[1 Feb 2005 8:30] Shu SATO
A patch for PreparedStatement.java of Connector/J 3.0.16

Attachment: PreparedStatement.java.patch (application/octet-stream, text), 2.82 KiB.

[1 Feb 2005 15:19] Mark Matthews
>--------------------
>Fix of Connector/J
>--------------------
>Now setString() of PreparedStatement converts any String(non-null)
>to byte array without any escaping and passes the array to 
>setBytes(index, array, false), which correctly does escaping.

>Please see the attached patch(PreparedStatement.java.patch) for details.

Unfortunately, your patch breaks most other character sets (I just tested it). We'll have to look at a different fix.
[1 Feb 2005 20:27] Mark Matthews
Okay, it looks like the fix for MySQL server 4.0 is to encode binary data as hex when using multi-byte character sets, instead of trying to escape it, as the parser gets confused.

I've written a patch to Connector/J 3.0 that I will push before the nightly build, so that you can test it.
[2 Feb 2005 5:04] Shu SATO
Hello Mark,

Thank you very much for the fix!

I tested the attached test client(SQLTest06.java) with the latest 
nightly build version(mysql-connector-java-3.0-nightly-20050202-bin.jar).
The client was successful with the version. (Previously it failed with older versions(3.0.15/3.0.16) as reported.)

Coule you let me know when you are planning to make a new 3.0 version of 
the driver inculding the fix 'Generally Available'?

Thanks,
Shu