Bug #47665 | when write a binary file(e.g a picture) into blob, exception will occurs | ||
---|---|---|---|
Submitted: | 28 Sep 2009 1:09 | Modified: | 5 Nov 2022 1:49 |
Reporter: | Liu Changsheng | Email Updates: | |
Status: | Can't repeat | Impact on me: | |
Category: | Connector / J | Severity: | S1 (Critical) |
Version: | 5.1 | OS: | Windows (xp) |
Assigned to: | Assigned Account | CPU Architecture: | Any |
Tags: | binary file, BLOB, jdbc4.0 |
[28 Sep 2009 1:09]
Liu Changsheng
[28 Sep 2009 5:20]
Sveta Smirnova
Thank you for the report. Please provide values of max_allowed_packet and size of jpeg file you are trying to insert.
[29 Sep 2009 6:13]
Liu Changsheng
max_allowed_packet :16M the size of file I try to insert:162K. actually, I tried many files with different size.but non is successful.
[1 Oct 2009 9:46]
Tonci Grgin
Liu, this should work... Can you please do the following: o Check c/J sources, package testsuite.simple, BlobTest.java and see if it works in your environment. o Try plain Java test, apart from Tomcat and such. o Attach full test case (as well as connection string and JRE settings) I can use to check on problem.
[3 Oct 2009 23:39]
Liu Changsheng
OK, I will do it with your instructions, and I will report the result to you as soon as possible.Thank you.
[4 Oct 2009 3:22]
Liu Changsheng
When I run the testsuite.simple.BlobTest, Exceptions are as follows ==================================================================== .Loading JDBC driver 'com.mysql.jdbc.Driver' Done. Connected to 5.1.37-community-log Done. E Time: 11.672 There was 1 error: 1) testByteStreamInsert(testsuite.simple.BlobTest)com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'ì?Nb’,n????u?·5??A$??!tK?\"O?i?\0NC4?>???w?m???A?èy?&?·_\0x???o?0????rz÷?W' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27) at java.lang.reflect.Constructor.newInstance(Constructor.java:513) at com.mysql.jdbc.Util.handleNewInstance(Util.java:406) at com.mysql.jdbc.Util.getInstance(Util.java:381) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3558) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3490) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2109) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2648) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2077) at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1356) at testsuite.simple.BlobTest.testByteStreamInsert(BlobTest.java:140) at testsuite.simple.BlobTest.testByteStreamInsert(BlobTest.java:126) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at testsuite.simple.BlobTest.main(BlobTest.java:79) FAILURES!!! Tests run: 1, Failures: 0, Errors: 1 ========================================================================
[4 Oct 2009 3:58]
Liu Changsheng
My character environment are as follows,does it matter? mysql> show variables like 'character_set_%'; +--------------------------+----------------------------------+ | Variable_name | Value | +--------------------------+----------------------------------+ | character_set_client | gbk | | character_set_connection | gbk | | character_set_database | gbk | | character_set_filesystem | binary | | character_set_results | gbk | | character_set_server | gbk | | character_set_system | utf8 | | character_sets_dir | D:\project\mysql\share\charsets\ | +--------------------------+----------------------------------+ 8 rows in set (0.00 sec) mysql>
[5 Oct 2009 10:12]
Tonci Grgin
So Liu, you actually have the same error except that some chars are different now... I would really like to see " o Attach full test case (as well as connection string and JRE settings) I can use to check on problem." now.
[6 Oct 2009 3:02]
Liu Changsheng
I have attached the file, which includes jre setting,connction string and source code of BlobTest.java. Please check it. Thanks.
[6 Oct 2009 3:03]
Liu Changsheng
jre settings,connection string and source code of BlobTest.java
Attachment: JRE_SETTING_AND_CONNECTIONSTRING_AND_SOURCECODE.txt (text/plain), 15.26 KiB.
[12 Oct 2009 8:49]
Tonci Grgin
Thanks Liu.
[13 Jan 2010 7:45]
Tonci Grgin
After discussing this the conclusion is: o GBK is not 100% mapped in UTF8 c/J uses o This could also be a bug in Microsoft implementation of GBK o Thus either OS or Java are unable to encode GBK data to proper HEX values Mark, I think only you can tell which is the right reason.
[30 Aug 2010 8:25]
Tonci Grgin
Using Server-side prepared statements should solve this.
[5 Oct 2010 1:47]
Jonas Roque
I've implemented server-side prepared statements but that won't work also.
[6 Feb 2014 23:30]
Haiwu Li
I have the same issue, but in my case, I found if the picture size is less than about 2KB, then it's ok. I don't know what's the reason. The error message is: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 '\"摠O?z?xb:睈XF??!?^\'_揌$蓲銷 !%?IIkH跦-???i淟&霅m赊?瞼?棏窅' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) at java.lang.reflect.Constructor.newInstance(Unknown Source) at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) at com.mysql.jdbc.Util.getInstance(Util.java:386) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2617) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2825) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2156) at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1379) at ytl.DBUtil.insertMstx_Ads(DBUtil.java:602) at ytl.FileUploadServlet.doPost(FileUploadServlet.java:149) at javax.servlet.http.HttpServlet.service(HttpServlet.java:647) at javax.servlet.http.HttpServlet.service(HttpServlet.java:728) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408) at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1023) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589) at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source)
[5 Nov 2022 1:49]
Filipe Silva
Unable to reproduce using latest Connector/J version.