[jira] Created: (OPENJPA-459) Problem with bulk updates in mySQL

11 messages

[jira] Created: (OPENJPA-459) Problem with bulk updates in mySQL

Reply Threaded

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.

JIRA jira@apache.org

[jira] Commented: (OPENJPA-459) Problem with bulk updates in mySQL

Reply Threaded

[ https://issues.apache.org/jira/browse/OPENJPA-459?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12548940 ]

Patrick Linskey commented on OPENJPA-459:
-----------------------------------------

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.

> 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.

JIRA jira@apache.org

[jira] Commented: (OPENJPA-459) Problem with bulk updates in mySQL

Reply Threaded

In reply to this post by JIRA jira@apache.org

[ https://issues.apache.org/jira/browse/OPENJPA-459?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12549375 ]

Jacek Żoch commented on OPENJPA-459:
------------------------------------

Is there any workaround possible now ?

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

JIRA jira@apache.org

[jira] Commented: (OPENJPA-459) Problem with bulk updates in mySQL

Reply Threaded

In reply to this post by JIRA jira@apache.org

[ https://issues.apache.org/jira/browse/OPENJPA-459?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12550415 ]

Abdel Bouzahri commented on OPENJPA-459:
----------------------------------------

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

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

JIRA jira@apache.org

[jira] Commented: (OPENJPA-459) Problem with bulk updates in mySQL

Reply Threaded

In reply to this post by JIRA jira@apache.org

[ https://issues.apache.org/jira/browse/OPENJPA-459?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12551002 ]

Jacek Żoch commented on OPENJPA-459:
------------------------------------

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 ).

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

JIRA jira@apache.org

[jira] Commented: (OPENJPA-459) Problem with bulk updates in mySQL

Reply Threaded

In reply to this post by JIRA jira@apache.org

[ https://issues.apache.org/jira/browse/OPENJPA-459?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12551450 ]

Ognjen Blagojevic commented on OPENJPA-459:
-------------------------------------------

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

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

JIRA jira@apache.org

[jira] Resolved: (OPENJPA-459) Problem with bulk updates in mySQL

Reply Threaded

In reply to this post by JIRA jira@apache.org

[ https://issues.apache.org/jira/browse/OPENJPA-459?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Pinaki Poddar resolved OPENJPA-459.
-----------------------------------

Resolution: Fixed

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

JIRA jira@apache.org

[jira] Updated: (OPENJPA-459) Problem with bulk updates in mySQL

Reply Threaded

In reply to this post by JIRA jira@apache.org

[ https://issues.apache.org/jira/browse/OPENJPA-459?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Michael Dick updated OPENJPA-459:
---------------------------------

Fix Version/s: 1.2.0

> 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
> 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.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.

JIRA jira@apache.org

[jira] Reopened: (OPENJPA-459) Problem with bulk updates in mySQL

Reply Threaded

In reply to this post by JIRA jira@apache.org

[ 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.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.

JIRA jira@apache.org

[jira] Resolved: (OPENJPA-459) Problem with bulk updates in mySQL

Reply Threaded

In reply to this post by JIRA jira@apache.org

> 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: 2.0.0-M4
>
>
> 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.

JIRA jira@apache.org

[jira] Commented: (OPENJPA-459) Problem with bulk updates in mySQL

Reply Threaded

In reply to this post by JIRA jira@apache.org

[ https://issues.apache.org/jira/browse/OPENJPA-459?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12776872#action_12776872 ]

Pinaki Poddar commented on OPENJPA-459:
---------------------------------------

Verify against commit #835257 and report back.

> 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: 2.0.0-M4
>
>
> 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.