Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.0.0
    • Fix Version/s: 2.0.0-beta
    • Component/s: jdbc
    • Labels:
      None
    • Environment:
      Weblogic 10.0 ; mySQL 5

      Description

      It's impossible to do bulk updates in mySQL. With subqueries enabled
      (<property name="openjpa.jdbc.DBDictionary"
      value="mysql(SupportsSubselect=true)" /> in persistence.xml)
      the updates generated are invalid, their execution ends with
      exception. For example:

      the jpql query is:
      UPDATE Token t SET t.token = :tokenValue WHERE t.status = :status

      the resulting exception is:

      <1.0.0-SNAPSHOT-SNAPSHOT nonfatal general error>
      org.apache.openjpa.persistence.PersistenceException: You can't specify
      target table 'TOKENS' for update in FROM clause

      {prepstmnt 134 UPDATE TOKENS SET TOKEN = ? WHERE ID_TOKEN IN (SELECT DISTINCT t0.ID_TOKEN FROM TOKENS t0 WHERE (t0.STATUS = ?)) [params=(String) token100, (long) 1]} [code=1093, state=HY000]
      FailedObject: org.apache.openjpa.jdbc.kernel.exps.ExpContext@1d94799
      at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:3784)
      at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:97)
      at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:67)
      at org.apache.openjpa.jdbc.kernel.JDBCStoreQuery.executeBulkOperation(JDBCStoreQuery.java:508)
      at org.apache.openjpa.jdbc.kernel.JDBCStoreQuery.executeUpdate(JDBCStoreQuery.java:430)
      at org.apache.openjpa.kernel.ExpressionStoreQuery$DataStoreExecutor.executeUpdate(ExpressionStoreQuery.java:690)
      at org.apache.openjpa.datacache.QueryCacheStoreQuery$QueryCacheExecutor.executeUpdate(QueryCacheStoreQuery.java:351)
      at org.apache.openjpa.kernel.QueryImpl.update(QueryImpl.java:1036)
      at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:843)
      at org.apache.openjpa.kernel.QueryImpl.updateAll(QueryImpl.java:879)
      at kodo.kernel.KodoQuery.updateAll(KodoQuery.java:71)
      at org.apache.openjpa.kernel.DelegatingQuery.updateAll(DelegatingQuery.java:581)
      at org.apache.openjpa.persistence.QueryImpl.executeUpdate(QueryImpl.java:313)
      at sample.dao.jpa.TokenDaoImpl.updateByStatus(TokenDaoImpl.java:154)
      ... 16 more
      Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: You
      can't specify target table 'TOKENS' for update in FROM clause{prepstmnt 134UPDATE TOKENSSET TOKEN = ?WHERE ID_TOKEN IN (SELECT DISTINCT t0.ID_TOKEN FROM TOKENS t0 WHERE(t0.STATUS = ?))[params=(String) token100, (long) 1]}

      [code=1093, state=HY000]
      at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:191)
      at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$800(LoggingConnectionDecorator.java:56)
      at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection$LoggingPreparedStatement.executeUpdate(LoggingConnectionDecorator.java:857)
      at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:269)
      at org.apache.openjpa.jdbc.kernel.JDBCStoreManager$CancelPreparedStatement.executeUpdate(JDBCStoreManager.java:1363)
      at org.apache.openjpa.jdbc.kernel.JDBCStoreQuery.executeBulkOperation(JDBCStoreQuery.java:501)
      ... 26 more

      We'd expect the generated sql would look more like:
      UPDATE TOKENS
      SET TOKEN = ?
      WHERE STATUS = ?

      For the following query:
      UPDATE Token t SET t.token = :tokenValue WHERE t.user.login = :login

      we get
      <1.0.0-SNAPSHOT-SNAPSHOT nonfatal general error>
      org.apache.openjpa.persistence.PersistenceException: You can't specify
      target table 'TOKENS' for update in FROM clause

      {prepstmnt 137 UPDATE TOKENS SET TOKEN = ? WHERE ID_TOKEN IN (SELECT DISTINCT t0.ID_TOKEN FROM TOKENS t0 INNER JOIN USERS t1 ON t0.ID_USER = t1.ID_USER WHERE (t1.LOGIN = ?)) [params=(String) token200, (String) noone]} [code=1093, state=HY000]
      FailedObject: org.apache.openjpa.jdbc.kernel.exps.ExpContext@713bd2
      at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:3784)
      at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:97)
      at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:67)
      at org.apache.openjpa.jdbc.kernel.JDBCStoreQuery.executeBulkOperation(JDBCStoreQuery.java:508)
      at org.apache.openjpa.jdbc.kernel.JDBCStoreQuery.executeUpdate(JDBCStoreQuery.java:430)
      at org.apache.openjpa.kernel.ExpressionStoreQuery$DataStoreExecutor.executeUpdate(ExpressionStoreQuery.java:690)
      at org.apache.openjpa.datacache.QueryCacheStoreQuery$QueryCacheExecutor.executeUpdate(QueryCacheStoreQuery.java:351)
      at org.apache.openjpa.kernel.QueryImpl.update(QueryImpl.java:1036)
      at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:843)
      at org.apache.openjpa.kernel.QueryImpl.updateAll(QueryImpl.java:879)
      at kodo.kernel.KodoQuery.updateAll(KodoQuery.java:71)
      at org.apache.openjpa.kernel.DelegatingQuery.updateAll(DelegatingQuery.java:581)
      at org.apache.openjpa.persistence.QueryImpl.executeUpdate(QueryImpl.java:313)
      at sample.dao.jpa.TokenDaoImpl.updateByLogin(TokenDaoImpl.java:129)
      ... 16 more
      Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: You
      can't specify target table 'TOKENS' for update in FROM clause{prepstmnt 137UPDATE TOKENSSET TOKEN = ?WHERE ID_TOKEN IN (SELECT DISTINCT t0.ID_TOKEN FROM TOKENS t0 INNERJOIN USERS t1 ON t0.ID_USER = t1.ID_USER WHERE (t1.LOGIN = ?))[params=(String) token200, (String) noone]}

      [code=1093, state=HY000]
      at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:191)
      at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$800(LoggingConnectionDecorator.java:56)
      at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection$LoggingPreparedStatement.executeUpdate(LoggingConnectionDecorator.java:857)
      at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:269)
      at org.apache.openjpa.jdbc.kernel.JDBCStoreManager$CancelPreparedStatement.executeUpdate(JDBCStoreManager.java:1363)
      at org.apache.openjpa.jdbc.kernel.JDBCStoreQuery.executeBulkOperation(JDBCStoreQuery.java:501)
      ... 26 more

      where we'd expect following sql:
      UPDATE TOKENS t0
      JOIN USERS t1 ON t0.ID_USER = t1.ID_USER
      SET t0.TOKEN = ?
      WHERE t1.LOGIN = ?

      If we turn subqueries off
      (<property name="openjpa.jdbc.DBDictionary" value="mysql" /> in persistence.xml)
      instead of generating bulk update queries, openjpa generates a series
      of single row updates like:
      UPDATE TOKENS
      SET TOKEN = ?, VERSION = ?
      WHERE ID_TOKEN = ? AND VERSION = ?
      [params=(String) token200, (int) 5, (long) 5, (int) 4]

      UPDATE TOKENS
      SET TOKEN = ?, VERSION = ?
      WHERE ID_TOKEN = ? AND VERSION = ?
      [params=(String) token200, (int) 5, (long) 6, (int) 4

        Activity

        Hide
        Pinaki Poddar added a comment -

        Verify against commit #835257 and report back.

        Show
        Pinaki Poddar added a comment - Verify against commit #835257 and report back.
        Hide
        Kevin Sutter added a comment -

        Based on the conversation on the Users mailing list [1], this JIRA Issue does not seem to be resolved yet. I've been in contact with Pinaki and Mike about the resolution status and we can't seem to come to any conclusion. And, the customer is still claiming that this is not resolved. So, I will re-open the Issue. Initially, I will assign it to Pinaki since he resolved it in the first place. Sorry for the confusion!

        [1] http://n2.nabble.com/Problem-with-bulk-updates-in-mySQL-td3871180.html#a3871180

        Thanks,
        Kevin

        Show
        Kevin Sutter added a comment - Based on the conversation on the Users mailing list [1] , this JIRA Issue does not seem to be resolved yet. I've been in contact with Pinaki and Mike about the resolution status and we can't seem to come to any conclusion. And, the customer is still claiming that this is not resolved. So, I will re-open the Issue. Initially, I will assign it to Pinaki since he resolved it in the first place. Sorry for the confusion! [1] http://n2.nabble.com/Problem-with-bulk-updates-in-mySQL-td3871180.html#a3871180 Thanks, Kevin
        Hide
        Ognjen Blagojevic added a comment -

        Just to confirm the bug, and to report that bulk DELETE statement is causing the same problems.

        Show
        Ognjen Blagojevic added a comment - Just to confirm the bug, and to report that bulk DELETE statement is causing the same problems.
        Hide
        Jacek Żoch added a comment -

        I think, that turning off subqueries is not a good solution, because in this case we can`t use subqueries in any query in our application
        As I am using Weblogic 10 I have reported this problem also to Bea support ( Case #: 760073 ).

        Show
        Jacek Żoch added a comment - I think, that turning off subqueries is not a good solution, because in this case we can`t use subqueries in any query in our application As I am using Weblogic 10 I have reported this problem also to Bea support ( Case #: 760073 ).
        Hide
        bouzahri added a comment -

        can we consider this as a bug?
        Workaround to turn off subquiries would not be an option when it concerns bulk updates.

        Show
        bouzahri added a comment - can we consider this as a bug? Workaround to turn off subquiries would not be an option when it concerns bulk updates.
        Hide
        Jacek Żoch added a comment -

        Is there any workaround possible now ?

        Show
        Jacek Żoch added a comment - Is there any workaround possible now ?
        Hide
        Patrick Linskey added a comment -

        I believe that the first query (which only involves one table) is due to a fairly trivial-to-resolve limitation of DBDIctionary: currently, if SupportsSubselect is true and AllowsAliasInBulkClause is false, OpenJPA always uses a subselect, even when it would be valid to just omit the aliases.

        Show
        Patrick Linskey added a comment - I believe that the first query (which only involves one table) is due to a fairly trivial-to-resolve limitation of DBDIctionary: currently, if SupportsSubselect is true and AllowsAliasInBulkClause is false, OpenJPA always uses a subselect, even when it would be valid to just omit the aliases.

          People

          • Assignee:
            Pinaki Poddar
            Reporter:
            Jacek Żoch
          • Votes:
            2 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development