openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jacek Żoch (JIRA) <j...@apache.org>
Subject [jira] Created: (OPENJPA-459) Problem with bulk updates in mySQL
Date Wed, 05 Dec 2007 16:39:43 GMT
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.


Mime
View raw message