| 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: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 4.0.20 | OS: | Linux (Redhat ES 3) |
| Assigned to: | CPU Architecture: | Any | |
[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
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)