[
https://issues.apache.org/jira/browse/OPENJPA-459?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Kevin Sutter reopened OPENJPA-459:
----------------------------------
Assignee: Pinaki Poddar
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
> 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
> Assignee: Pinaki Poddar
> Fix For: 1.2.0
>
>
> 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.expcont...@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.expcont...@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.
