| Bug #51527 | wrong escaping with characterEncoding=MS932 and useServerPrepStmts=false | ||
|---|---|---|---|
| Submitted: | 25 Feb 2010 19:27 | Modified: | 11 Jan 2011 10:54 |
| Reporter: | Andrii Nikitin | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / J | Severity: | S3 (Non-critical) |
| Version: | 5.1.12, 5.1.0 | OS: | Any |
| Assigned to: | Tony Bedford | CPU Architecture: | Any |
[25 Feb 2010 19:28]
Andrii Nikitin
testcase
Attachment: BlobT.java (application/octet-stream, text), 2.64 KiB.
[25 Feb 2010 19:47]
Andrii Nikitin
corrupted blob exported with 'SELECT ... INTO DUMPFILE'
Attachment: out51-corrupted.gif (image/gif, text), 1.23 KiB.
[25 Feb 2010 19:54]
Andrii Nikitin
Comparison of files mysql.gif and out51-corrupted.gif shows that six occurrences of byte '\0' (i.e. 0x00) are replaced with two bytes '\\' and '0' (0x57 and 0x30).
[26 Feb 2010 16:08]
Tonci Grgin
Andrii, there is something else that's wrong here... E:\>fc mysql.gif test.gif Comparing files mysql.gif and TEST.GIF FC: no differences encountered Test case is attached. -Xmx1024M -XX:+UseParallelGC -Dcom.mysql.jdbc.java6.javac=C:\jvms\jdk1.6.0\bin\javac.exe -Dcom.mysql.jdbc.java6.rtjar=C:\jvms\jdk1.6.0\jre\lib\rt.jar -Dcom.mysql.jdbc.testsuite.url.default=jdbc:mysql://opensol:xx/test?user=xx&password=xx&autoReconnect=false&connectTimeout=5000&socketTimeout=30000&useInformationSchema=true&useServerPrepStmts=true&useAffectedRows=false
[26 Feb 2010 16:09]
Tonci Grgin
Test case for our test framework. Tested with JDK 1.5.
Attachment: TestBug51527_1.java (text/x-java), 2.52 KiB.
[2 Mar 2010 15:08]
Andrii Nikitin
Looks problem occurs when useServerPrepStmts=false . C/J produces different escaped string than MySQL server for two-byte-sequence: 0x90 and 0x00 (with characterEncoding=MS932) Extract from general log when inserting BLOB with these two bytes: SET NAMES cp932 INSERT INTO imgdata_tab(img) VALUES (_binary'_\0') But server produces differently escaped sequence (so I assume it expects different sequence also): SET NAMES cp932; select concat(0x90,0x00) into outfile '/tmp/.outfile'; \_\0 (note leading backslash). Also please note that if we do "SET NAMES utf8;", the SELECT statement will return sequence identical to one C/J produces.
[2 Mar 2010 16:09]
Tonci Grgin
Andrii, this could also be due to SQL_MODE, please note the one used.
[3 Mar 2010 8:52]
Andrii Nikitin
sql_mode=""
[3 Mar 2010 9:01]
Andrii Nikitin
With sql_mode="NO_BACKSLASH_ESCAPES" bug is not reproducible.
[11 Jan 2011 10:54]
Tony Bedford
No changelog entry has been created. A note on the problem and solution has been added to the Connector/J: Common Problems and Solutions section of the Connector/J documentation.
Description: When characterEncoding=MS932 is part of connection string, inserting BLOBs leads to corruption. The same java source code works properly: if connector 3.1.12 is used or if characterEncoding=UTF-8 Source code for BLOB insert looks like: java.sql.PreparedStatement pstmt = null; java.io.File fFile = null; java.io.FileInputStream isFile = null; pstmt = conn.prepareStatement("INSERT INTO imgdata_tab (img,name) VALUES( ?, ? )"); fFile = new File(filename); isFile = new FileInputStream(fFile); pstmt.setBinaryStream(1, isFile, (int)(fFile.length())); pstmt.setString(2, name); pstmt.executeUpdate(); How to repeat: 1. CREATE TABLE test.imgdata_tab(img blob, name varchar(40)); 2. download sample file mysql.gif and test program BlobT.java 3. compile and run like: java -cp mysql-connector-java-5.1.12-bin.jar BlobT insert mysql.gif logo 4. execute SQL command: mysql> SELECT md5(img) FROM imgdata_tab; +----------------------------------+ | md5(img) | +----------------------------------+ | 45e7df410e7d9d6ccefd822905707acc | +----------------------------------+ 1 row in set (0.00 sec) 5. execute shell command: shell> md5sum mysql.gif ae6ad088288f265bbc397de78f4b1359 *mysql.gif 6. Try Connector/J 3.1 java -cp mysql-connector-java-3.1.12-bin.jar BlobT insert mysql.gif logo Select md5() returns correct 'ae6ad088288f265bbc397de78f4b1359' 7. Uncomment following line in source BlobT.java and recompile: String encodingName = "UTF-8"; java -cp mysql-connector-java-5.1.12-bin.jar BlobT insert mysql.gif logo Select md5() also returns correct 'ae6ad088288f265bbc397de78f4b1359' Suggested fix: Not sure if any character set setting may influence Blob inserting.