You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by "Pinaki Poddar (JIRA)" <ji...@apache.org> on 2008/07/16 07:15:31 UTC
[jira] Resolved: (OPENJPA-459) Problem with bulk updates in mySQL
[ https://issues.apache.org/jira/browse/OPENJPA-459?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Pinaki Poddar resolved OPENJPA-459.
-----------------------------------
Resolution: Fixed
> Problem with bulk updates in mySQL
> ----------------------------------
>
> Key: OPENJPA-459
> URL: https://issues.apache.org/jira/browse/OPENJPA-459
> Project: OpenJPA
> Issue Type: Bug
> Components: jdbc
> Affects Versions: 1.0.0
> Environment: Weblogic 10.0 ; mySQL 5
> Reporter: Jacek Żoch
>
> 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 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]
> 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 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]
> 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
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.