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.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.063 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
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.