| Bug #24695 | Problems with binary stream retreival from BlobFromLocator | ||
|---|---|---|---|
| Submitted: | 29 Nov 2006 14:36 | Modified: | 3 Jan 2007 12:40 |
| Reporter: | Slobodan Marjanovic | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / J Documentation | Severity: | S1 (Critical) |
| Version: | 5.0.4 | OS: | Windows (Windows) |
| Assigned to: | MC Brown | CPU Architecture: | Any |
[7 Dec 2006 10:34]
Tonci Grgin
Hi Slobodan and thanks for your problem report.
I have noticed 3 mistakes in it:
- "SELECT coverimage, imageData" should be "SELECT coverimage, Data" according to DDL statement.
- I don't see locatorFetchBufferSize in your connect string.
- I don't see PreparedStatement.setBlob() (see quote below)
A quote from manual:
JDBC API Implementation Notes
This section gives details on a interface-by-interface level about how certain implementation decisions may affect how you use MySQL Connector/J.
• Blob
The Blob implementation does not allow in-place modification (they are copies, as reported by the DatabaseMetaData.locatorsUpdateCopies() method).
* Because of this, you should use the corresponding PreparedStatement.setBlob() or ResultSet.updateBlob() (in the case of updatable result sets) methods to save changes back to the database.*
Starting with Connector/J version 3.1.0, you can emulate Blobs with locators by adding the property 'emulateLocators=true' to your JDBC URL. You must then use a column alias with the value of the column set to the actual name of the Blob column in the SELECT that you write to retrieve the Blob. The SELECT must also reference only one table, the table must have a primary key, and the SELECT must cover all columns that make up the primary key. The driver will then delay loading the actual Blob data until you retrieve the Blob and call retrieval methods (getInputStream(), getBytes(), and so forth) on it.
[11 Dec 2006 12:39]
Slobodan Marjanovic
>I have noticed 3 mistakes in it: > - "SELECT coverimage, imageData" should be "SELECT coverimage, Data" >according to DDL statement. This is my mistake. The name of the column is 'imageData'. This was a copy-paste, error. In db I used for testing column name is 'imageData'. > - I don't see locatorFetchBufferSize in your connect string. I didn't find anywhere in the docs, that this is mandatory, because it has a default value. After I set this property to different value, I still got the same error. > - I don't see PreparedStatement.setBlob() (see quote below) I don't understand why should I use setBlob method, and what should be the value passed to this method ? I wasn't trying to change data stored in blob, I was just opening the stream for reading. BTW I noticed that the error message changes, depending on data in db. For example when blob has a value, message is "Unknown column '[B@3580ab' in 'field list'", but when blob is empty, message is "Unknown column '' in 'field list'". Could there be a problem in constructor, when column name is determined(seems like column name is replaced with column data) ?
[11 Dec 2006 14:31]
Tonci Grgin
Hi Slobodan. Please try with PreparedStatement.setBlob() as asked and inform me of result. >BTW I noticed that the error message changes, depending on data in db. For example when blob has a value, message is "Unknown column '[B@3580ab' in 'field list'", but when blob is empty, message is "Unknown column '' in 'field list'". Could there be a problem in constructor, when column name is determined(seems like column name is replaced with column data) ? No this is not the case.
[11 Dec 2006 15:02]
Slobodan Marjanovic
When I try it on prepared statement, I get error message "Parameter index out of bounds. 2 is not between valid values of 1 and 1" (as expected since I don't have more than one parameter in the query). When I try it on result set, I get error message "Result Set not updatable....." (as expected since I have no updatable result set in code).
[13 Dec 2006 15:14]
Tonci Grgin
Slobodan sorry, took me some time to figure out what this is about...
Verified as described by reporter with test case attached:
- MySQL server 5.0.27BK on WinXP Pro SP2 localhost
- JDK 1.5.10
- Latest SVN sources of c/J
/**
* Returns the number of bytes in the BLOB value designated by this Blob
* object.
*
* @return the length of this blob
*
* @throws SQLException
* if a database error occurs
*/
public long length() throws SQLException {
java.sql.ResultSet blobRs = null;
java.sql.PreparedStatement pStmt = null;
// FIXME: Needs to use identifiers for column/table names
StringBuffer query = new StringBuffer("SELECT LENGTH(");
query.append(this.blobColumnName); << <placeholder> is not replaced with actual column name.
061213 15:58:28 17 Connect root@localhost on test
17 Query SHOW VARIABLES
17 Query SHOW COLLATION
17 Query SET character_set_results = NULL
17 Query SET autocommit=1
17 Query SET sql_mode='STRICT_TRANS_TABLES'
17 Query SELECT VERSION()
17 Query SELECT coverimage, data FROM tblTestBug24695 WHERE coverimage = 1
17 Query SELECT LENGTH(`<placeholder>`) FROM `test`.`tbltestbug24695` WHERE `coverimage` = '1'
Thanks for your interest in MySQL.
[13 Dec 2006 15:20]
Tonci Grgin
Test case, use "emulateLocators=true"
Attachment: TestBug24695.java (text/x-java), 1.62 KiB.
[13 Dec 2006 17:19]
Mark Matthews
This is not a bug (but perhaps the documentation needs to be more clear). You _have_ to use a column alias. For example in the testcase, the SELECT should read like: SELECT coverimage, 'data' as data FROM tblTestBug24695 WHERE coverimage = ? The reason for this, is that if you use the column as-is, the BLOB is pulled back in its entirety. There is no way currently for a client to tell MySQL to "skip" sending the BLOB and that the client will retrieve it later.
[3 Jan 2007 12:40]
MC Brown
I've updated the document to make this requirement clearer. The notes on using blobs as streams is now first, and I've listed explicit requirements and an example to the Blob specific notes.
[18 Jun 2007 11:32]
James H
Regarding the com.mysql.jdbc.exceptions.MySQLSyntaxErrorException Unknown Column problem, I have experienced this also. In my case, the cause behind it was that we had used MySQL Control Center to add a column to a table, but it turns out this new column wasn't actually created (despite MySQL CC reporting it as existing). Solution was to create the new column via our database web interface, rather than with MySQL CC. Took me a while to find the cause, so I hope this helps someone!... Cheers, James
[3 Apr 2008 11:23]
Edwin Palathinkal
I got a Similar Error. JUnit test don't have this error but calling the method from GUI has the error. created_at is a datetime.
OS: Mac OS X
Java version "1.5.0_13"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_13-b05-241)
Java HotSpot(TM) Client VM (build 1.5.0_13-121, mixed mode, sharing)
J/Connector Version: 5.1.6
Apr 3, 2008 7:10:29 PM controllers.CustomerController createCustomer
SEVERE: Unknown column 'created_at' in 'field list'
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown column 'created_at' in 'field list'
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1026)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2536)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2465)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:734)
at controllers.CustomerController.createCustomer(CustomerController.java:36)
at views.customer.NewCustomer.createButtonActionPerformed(NewCustomer.java:219)
at views.customer.NewCustomer.access$000(NewCustomer.java:16)
at views.customer.NewCustomer$1.actionPerformed(NewCustomer.java:199)
at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1882)
at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2202)
at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:420)
at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:258)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236)
at java.awt.Component.processMouseEvent(Component.java:5583)
at javax.swing.JComponent.processMouseEvent(JComponent.java:3135)
at java.awt.Component.processEvent(Component.java:5348)
at java.awt.Container.processEvent(Container.java:2010)
at java.awt.Component.dispatchEventImpl(Component.java:4050)
at java.awt.Container.dispatchEventImpl(Container.java:2068)
at java.awt.Component.dispatchEvent(Component.java:3885)
at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4256)
at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3936)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3866)
at java.awt.Container.dispatchEventImpl(Container.java:2054)
at java.awt.Window.dispatchEventImpl(Window.java:1791)
at java.awt.Component.dispatchEvent(Component.java:3885)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:463)
at java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:269)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:190)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:184)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:176)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:110)
Description: I ran into problem with retreival of binary stream from BlobFromLocator implmentation of Blob interface. Basic informations: Java 1.5.09 MySql Server 5.0 MySql-Connector 5.0.4 Stack trace: 2006-11-29 15:21:49,733 1758127 WARN http-8080-Processor24 SLO6 streamImage 62 - Failed to stream cover image with id: 9. Reason: Unknown column '[B@3580ab' in 'field list' 2006-11-29 15:21:49,733 1758127 WARN http-8080-Processor24 SLO6 resolveException 22 - java.lang.RuntimeException: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown column '[B@3580ab' in 'field list' at com.openfloodgate.webapp.beans.hibdao.CoverImageDao.streamImage(CoverImageDao.java:63) at com.openfloodgate.webapp.controllers.ImageController.handleRequest(ImageController.java:96) at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:45) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:806) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:736) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:396) at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:350) at javax.servlet.http.HttpServlet.service(HttpServlet.java:689) at javax.servlet.http.HttpServlet.service(HttpServlet.java:802) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157) at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:264) at org.acegisecurity.intercept.web.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:107) at org.acegisecurity.intercept.web.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:72) at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274) at org.acegisecurity.ui.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:110) at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274) at org.acegisecurity.ui.rememberme.RememberMeProcessingFilter.doFilter(RememberMeProcessingFilter.java:142) at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274) at org.acegisecurity.ui.AbstractProcessingFilter.doFilter(AbstractProcessingFilter.java:217) at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274) at org.acegisecurity.ui.logout.LogoutFilter.doFilter(LogoutFilter.java:108) at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274) at org.acegisecurity.context.HttpSessionContextIntegrationFilter.doFilter(HttpSessionContextIntegrationFilter.java:193) at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274) at org.acegisecurity.util.FilterChainProxy.doFilter(FilterChainProxy.java:148) at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:98) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214) at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520) at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152) at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137) at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:118) at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929) at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705) at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577) at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683) at java.lang.Thread.run(Thread.java:595) Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown column '[B@3580ab' in 'field list' at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665) at com.mysql.jdbc.Connection.execSQL(Connection.java:3176) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1153) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1266) at com.mysql.jdbc.BlobFromLocator.length(BlobFromLocator.java:333) at com.mysql.jdbc.BlobFromLocator$LocatorInputStream.<init>(BlobFromLocator.java:575) at com.mysql.jdbc.BlobFromLocator.getBinaryStream(BlobFromLocator.java:178) at com.openfloodgate.webapp.beans.hibdao.CoverImageDao.streamImage(CoverImageDao.java:60) ... 50 more How to repeat: Connection URL: jdbc:mysql://localhost:3306/dbName?emulateLocators=true Code: PreparedStatement stmt = conn.prepareStatement("SELECT coverimage, imageData FROM coverimages WHERE coverimage = ?"); stmt.setLong(1, coverImageId.longValue()); ResultSet results = stmt.executeQuery(); Blob imageData = null; while(results.next()) { imageData = results.getBlob(2); } FileCopyUtils.copy(imageData.getBinaryStream(), out); DDL: CREATE TABLE floodgate.coverimages ( coverimage INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, description VARCHAR(255) NULL, keywords varchar(255) NULL, data MEDIUMBLOB NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8;