2 Replies Latest reply on May 26, 2008 6:29 AM by c-box

    Problems after upgrading to 4.2.2 with MySQL and BLOB

    c-box

      Hi,

      we just upgraded our JBoss from 4.0.2 to 4.2.2.

      Our MySQL is 5.0.51a --> all tables are INNODB because of transactions.

      Within our database we do have a table that has two BLOB fields inside holding some binaries (one is a JRXML-file = JasperReportDesign-Object and one is the compiled design as as so called JASPER-file = JasperReport-Object) and some other fields (varchar and integer).

      'ID', 'int(11)', 'NO', 'PRI', '', ''
      'Bezeichnung', 'varchar(100)', 'YES', '', '', ''
      'Design', 'longblob', 'YES', '', '', ''
      'Report', 'longblob', 'YES', '', '', ''
      'BusinessObjectID', 'int(11)', 'YES', '', '', ''
      'ReportTyp', 'int(11)', 'YES', '', '', ''
      'AfpsLockID', 'int(11)', 'YES', '', '', ''
      'AfpsVersion', 'int(11)', 'YES', '', '', ''
      


      We do use CMP logic.

      The TypeMapping within standardjbosscmp.xml hasn't changed from 4.0.2 to 4.2.2 for the MySQL mapping:

      for BLOB we always use:
      <mapping>
       <java-type>java.lang.Object</java-type>
       <jdbc-type>BLOB</jdbc-type>
       <sql-type>LONGBLOB</sql-type>
       </mapping>


      When I want to change one record within that table I get an exception, that says that the getInt method of the ResultSetImpl can't get an int from a field. I already created my own finder "findById" to not use the standard builtin findByPrimaryKey finder --> but without success - same error:

      here is the finder:
      <query>
       <query-method>
       <method-name>findById</method-name>
       <method-params>
       <method-param>java.lang.Integer</method-param>
       </method-params>
       </query-method>
       <ejb-ql>SELECT OBJECT(o) FROM DruckVorlageReport AS o WHERE o.id = ?1</ejb-ql>
       </query>



      and here the exception:

      
      2008-05-05 10:25:47,104 ERROR [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.DruckVorlageReport#findById] Find failed
      javax.ejb.EJBException: Internal error getting results for field member businessObjectID
      at org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCAbstractCMPFieldBridge.loadArgumentResults(JDBCAbstractCMPFieldBridge.java:498)
       at org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCAbstractCMPFieldBridge.loadArgumentResults(JDBCAbstractCMPFieldBridge.java:432)
       at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractQueryCommand$EagerCollectionFactory.createCollection(JDBCAbstractQueryCommand.java:765)
       at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractQueryCommand.execute(JDBCAbstractQueryCommand.java:265)
       at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractQueryCommand.execute(JDBCAbstractQueryCommand.java:144)
       at org.jboss.ejb.plugins.cmp.jdbc.JDBCFindEntityCommand.execute(JDBCFindEntityCommand.java:61)
       at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.findEntity(JDBCStoreManager.java:604)
       at org.jboss.ejb.plugins.CMPPersistenceManager.findEntity(CMPPersistenceManager.java:315)
       at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.findEntity(CachedConnectionInterceptor.java:236)
       at org.jboss.ejb.EntityContainer.findSingleObject(EntityContainer.java:1099)
       at org.jboss.ejb.EntityContainer.findLocal(EntityContainer.java:676)
      ....
      
      
      Caused by: java.sql.SQLException: Invalid value for getInt() - ' <?xml version="1.0" encoding="UTF-8"?>
      
      ... here the whole content of the column "design" is shown what is the JRXML file stored in the BLOB column "design"
       ....
      
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
       at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
       at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
       at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2709)


      the select statement that JBoss use seems to be okay:

      2008-05-05 10:25:47,089 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.DruckVorlageReport#findById] Executing SQL: SELECT t0_o.ID, t0_o.Bezeichnung, t0_o.Design, t0_o.Report, t0_o.BusinessObjectID, t0_o.ReportTyp, t0_o.AfpsVersion, t0_o.AfpsLockID FROM druckvorlagereport t0_o WHERE (t0_o.ID = ?)


      I already updated the mysql-driver to mysql-connector-java-5.1.6-bin.jar but without success.

      Has anybody a glue what's going wrong?
      If I use direct sql code with the connection to get a resultset it works like a charm, so I guess it can't be a driver problem.
      Seems that JBoss trys to convert my blob field into an int to find the record... but why????

      When I debug, I can see, that up to the call of the finder all arguments are correct (so no wrong input).

      Would be nice to get some response.
      regards from Germany

      Christian

        • 1. Re: Problems after upgrading to 4.2.2 with MySQL and BLOB
          c-box

          Nobody an idea????

          We still have that problem and google doesn't really help!?!?

          tia
          C-Box

          • 2. Re: Problems after upgrading to 4.2.2 with MySQL and BLOB
            c-box

            Hi again

            I can't say that the problem is really solved but we found a screw to turn that the error doesn't occur any longer...

            within our application ear in the involved jar's --> meta-inf --> jboss-cmp-jdbc.xml we changed the read-ahead strategy from "on-find" to "on-load" and now it's working again.

            Just came to it, while reading some comments in the jms config-file and JBoss-Wiki, that MySQL has some troubles reading more than one BLOB and therefore the "RecoverMessagesChunk" attribute can be set to 1.

            So our jar's jboss-cmp-jdbc.xml is now starting with this defaults:

            <defaults>
             <datasource>java:/AFPSDB</datasource>
             <create-table>false</create-table>
             <remove-table>false</remove-table>
             <row-locking>false</row-locking>
             <read-ahead>
             <strategy>on-load</strategy>
             <page-size>1000</page-size>
             <eager-load-group>*</eager-load-group>
             </read-ahead>
             <list-cache-max>1000</list-cache-max>
             <clean-read-ahead-on-load>false</clean-read-ahead-on-load>
             <entity-command name="no-select-before-insert"/>
             </defaults>


            now it's working again. But I guess it's not the real solution, so perhaps someone could clarify, if it's more a JBoss Bug (as it worked in 4.0.2 with "on-find") or a MySQL JDBC-Driver Problem (although direct SQL via JDBC is working fine). So I think JBoss 4.2.2 Problem!!?!?!

            thanks (to anyone who at least thought that problem but had no idea )
            C-Box