Re: ID field auto generation issue for multiple databases supporting
Hi Milosz, Yes, you're right. And I have checked the source code, the AUTO is total identical with TABLE in OpenJPA. Thank you for your patient help! Regards, Yu Wang On Wed, Jun 17, 2009 at 6:02 PM, Miłosz Tylenda wrote: > Hi Yu Wang, > > The error message seems to say "Attempt to modify an identity column 'ID'". > Probably Derby does not allow you to insert a value to an identity column. I > suspect you have created the table when you had GenerationType.IDENTITY in > your entity. The table has been created with an identity column. Then you > changed the annotation to GenerationType.AUTO which made OpenJPA use a > sequence. However, the table definition did not change automatically and > OpenJPA tries to insert an explicit value to the identity column. Try to drop > the table and let OpenJPA create it again (or create it manualy but don't use > "GENERATED BY DEFAULT AS IDENTITY" with ID column). Hope this helps. > > Regards, > Milosz > > > >> Hello Milosz, >> I found OpenJpa created table OPENJPA_SEQUENCE_TABLE automatically in >> the runtime, following is a fragment of my log file: >> >> 44048 xxx TRACE [Persistence-thread-0] openjpa.jdbc.SQL - executing >> prepstmnt 25906379 SELECT >> SEQUENCE_VALUE FROM xxx.OPENJPA_SEQUENCE_TABLE WHERE ID = ? FOR UPDATE >> WITH RR [params=(int) 0] >> 44047 xxx TRACE [Persistence-thread-0] openjpa.jdbc.SQL - [0 ms] spent >> 44063 xxx TRACE [Persistence-thread-0] openjpa.jdbc.SQL - executing >> prepstmnt 15975876 INSERT INTO >> xxx.OPENJPA_SEQUENCE_TABLE (ID, SEQUENCE_VALUE) VALUES (?, ?) >> [params=(int) 0, (int) 1] >> 44078 xxx TRACE [Persistence-thread-0] openjpa.jdbc.SQL - [15 ms] spent >> 44078 xxx TRACE [Persistence-thread-0] openjpa.jdbc.SQL - executing >> prepstmnt 3112190 SELECT >> SEQUENCE_VALUE FROM xxx.OPENJPA_SEQUENCE_TABLE WHERE ID = ? FOR UPDATE >> WITH RR [params=(int) 0] >> 44078 xxx TRACE [Persistence-thread-0] openjpa.jdbc.SQL - [0 ms] spent >> 44094 xxx TRACE [Persistence-thread-0] openjpa.jdbc.SQL - executing >> prepstmnt 20716393 UPDATE >> xxx.OPENJPA_SEQUENCE_TABLE SET SEQUENCE_VALUE = ? WHERE ID = ? AND >> SEQUENCE_VALUE = ? [params=(long) 51, (int) 0, (long) 1] >> 44094 xxx TRACE [Persistence-thread-0] openjpa.jdbc.SQL - [0 ms] spent >> 44188 xxx TRACE [Persistence-thread-0] openjpa.jdbc.SQL - executing >> prepstmnt 13686851 SELECT >> SEQUENCE_VALUE FROM xxx.OPENJPA_SEQUENCE_TABLE WHERE ID = ? FOR UPDATE >> WITH RR [params=(int) 0] >> 44203 xxx TRACE [Persistence-thread-0] openjpa.jdbc.SQL - [0 ms] spent >> 44203 xxx TRACE [Persistence-thread-0] openjpa.jdbc.SQL - executing >> prepstmnt 29070643 UPDATE >> xxx.OPENJPA_SEQUENCE_TABLE SET SEQUENCE_VALUE = ? WHERE ID = ? AND >> SEQUENCE_VALUE = ? [params=(long) 101, (int) 0, (long) 51] >> 44203 xxx TRACE [Persistence-thread-0] openjpa.jdbc.SQL - [0 ms] spent >> >> >> >> org.apache.openjpa.persistence.RollbackException: The transaction has >> been rolled back. See the nested exceptions for details on the errors >> that occurred. >> at >> org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:523) >> at >> com.xxx.xxx.dao.DAOContainer.commitTransaction(DAOContainer.java:195) >> at com.xxx.xxx.ds.Persister.persist(Persister.java:221) >> at com.xxx.xxx.ds.PersistRunner.run(PersistRunner.java:49) >> at >> java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441) >> at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303) >> at java.util.concurrent.FutureTask.run(FutureTask.java:138) >> at >> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:98) >> at >> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:207) >> at >> java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:885) >> at >> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:907) >> at java.lang.Thread.run(Thread.java:619) >> Caused by: >> org.apache.openjpa.persistence.PersistenceException: The transaction >> has been rolled back. See the nested exceptions for details on the >> errors that occurred. >> at >> org.apache.openjpa.kernel.BrokerImpl.newFlushException(BrokerImpl.java:2163) >> at org.apache.openjpa.kernel.BrokerImpl.flush(BrokerImpl.java:2010) >> at org.apache.openjpa.kernel.BrokerImpl.flushSafe(BrokerImpl.java:1908) >> at >> org.apache.openjpa.kernel.BrokerImpl.beforeCompletion(BrokerImpl.java:1826) >> at >> org.apache.openjpa.kernel.LocalManagedRuntime.commit(LocalManagedRuntime.java:81) >> at org.apache.openjpa.kernel.BrokerImpl.commit(BrokerImpl.java:1350) >> at >> org.apache.openjpa.kernel.DelegatingBroker.commit(DelegatingBroker.java:877) >> at >> org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl
Re: ID field auto generation issue for multiple databases supporting
Hi Yu Wang, The error message seems to say "Attempt to modify an identity column 'ID'". Probably Derby does not allow you to insert a value to an identity column. I suspect you have created the table when you had GenerationType.IDENTITY in your entity. The table has been created with an identity column. Then you changed the annotation to GenerationType.AUTO which made OpenJPA use a sequence. However, the table definition did not change automatically and OpenJPA tries to insert an explicit value to the identity column. Try to drop the table and let OpenJPA create it again (or create it manualy but don't use "GENERATED BY DEFAULT AS IDENTITY" with ID column). Hope this helps. Regards, Milosz > Hello Milosz, > I found OpenJpa created table OPENJPA_SEQUENCE_TABLE automatically in > the runtime, following is a fragment of my log file: > > 44048 xxx TRACE [Persistence-thread-0] openjpa.jdbc.SQL - executing > prepstmnt 25906379 SELECT > SEQUENCE_VALUE FROM xxx.OPENJPA_SEQUENCE_TABLE WHERE ID = ? FOR UPDATE > WITH RR [params=(int) 0] > 44047 xxx TRACE [Persistence-thread-0] openjpa.jdbc.SQL - [0 ms] spent > 44063 xxx TRACE [Persistence-thread-0] openjpa.jdbc.SQL - executing > prepstmnt 15975876 INSERT INTO > xxx.OPENJPA_SEQUENCE_TABLE (ID, SEQUENCE_VALUE) VALUES (?, ?) > [params=(int) 0, (int) 1] > 44078 xxx TRACE [Persistence-thread-0] openjpa.jdbc.SQL - [15 ms] spent > 44078 xxx TRACE [Persistence-thread-0] openjpa.jdbc.SQL - executing > prepstmnt 3112190 SELECT > SEQUENCE_VALUE FROM xxx.OPENJPA_SEQUENCE_TABLE WHERE ID = ? FOR UPDATE > WITH RR [params=(int) 0] > 44078 xxx TRACE [Persistence-thread-0] openjpa.jdbc.SQL - [0 ms] spent > 44094 xxx TRACE [Persistence-thread-0] openjpa.jdbc.SQL - executing > prepstmnt 20716393 UPDATE > xxx.OPENJPA_SEQUENCE_TABLE SET SEQUENCE_VALUE = ? WHERE ID = ? AND > SEQUENCE_VALUE = ? [params=(long) 51, (int) 0, (long) 1] > 44094 xxx TRACE [Persistence-thread-0] openjpa.jdbc.SQL - [0 ms] spent > 44188 xxx TRACE [Persistence-thread-0] openjpa.jdbc.SQL - executing > prepstmnt 13686851 SELECT > SEQUENCE_VALUE FROM xxx.OPENJPA_SEQUENCE_TABLE WHERE ID = ? FOR UPDATE > WITH RR [params=(int) 0] > 44203 xxx TRACE [Persistence-thread-0] openjpa.jdbc.SQL - [0 ms] spent > 44203 xxx TRACE [Persistence-thread-0] openjpa.jdbc.SQL - executing > prepstmnt 29070643 UPDATE > xxx.OPENJPA_SEQUENCE_TABLE SET SEQUENCE_VALUE = ? WHERE ID = ? AND > SEQUENCE_VALUE = ? [params=(long) 101, (int) 0, (long) 51] > 44203 xxx TRACE [Persistence-thread-0] openjpa.jdbc.SQL - [0 ms] spent > > > > org.apache.openjpa.persistence.RollbackException: The transaction has > been rolled back. See the nested exceptions for details on the errors > that occurred. > at > org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:523) > at com.xxx.xxx.dao.DAOContainer.commitTransaction(DAOContainer.java:195) > at com.xxx.xxx.ds.Persister.persist(Persister.java:221) > at com.xxx.xxx.ds.PersistRunner.run(PersistRunner.java:49) > at > java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441) > at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303) > at java.util.concurrent.FutureTask.run(FutureTask.java:138) > at > java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:98) > at > java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:207) > at > java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:885) > at > java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:907) > at java.lang.Thread.run(Thread.java:619) > Caused by: > org.apache.openjpa.persistence.PersistenceException: The transaction > has been rolled back. See the nested exceptions for details on the > errors that occurred. > at > org.apache.openjpa.kernel.BrokerImpl.newFlushException(BrokerImpl.java:2163) > at org.apache.openjpa.kernel.BrokerImpl.flush(BrokerImpl.java:2010) > at org.apache.openjpa.kernel.BrokerImpl.flushSafe(BrokerImpl.java:1908) > at > org.apache.openjpa.kernel.BrokerImpl.beforeCompletion(BrokerImpl.java:1826) > at > org.apache.openjpa.kernel.LocalManagedRuntime.commit(LocalManagedRuntime.java:81) > at org.apache.openjpa.kernel.BrokerImpl.commit(BrokerImpl.java:1350) > at > org.apache.openjpa.kernel.DelegatingBroker.commit(DelegatingBroker.java:877) > at > org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:512) > ... 11 more > Caused by: > org.apache.openjpa.persistence.PersistenceException: 尝?修改标?列“ID”。 > {INSERT INTO xxx.MON_SERVICE (ID, ATTRIBUTES, CPUTIME, DOMAIN, ERRNO, > EXECTIME, TUXGROUP, MACHINE, MSGQUEUED, MSGSIZE, MSGTYPE, MSGWAITTIME, > PID, PROCESSNAME, PROCTYPE, SERVERID, STATUS, SVCNAME, THREADID, >
Re: ID field auto generation issue for multiple databases supporting
Hello Milosz, I found OpenJpa created table OPENJPA_SEQUENCE_TABLE automatically in the runtime, following is a fragment of my log file: 44048 xxx TRACE [Persistence-thread-0] openjpa.jdbc.SQL - executing prepstmnt 25906379 SELECT SEQUENCE_VALUE FROM xxx.OPENJPA_SEQUENCE_TABLE WHERE ID = ? FOR UPDATE WITH RR [params=(int) 0] 44047 xxx TRACE [Persistence-thread-0] openjpa.jdbc.SQL - [0 ms] spent 44063 xxx TRACE [Persistence-thread-0] openjpa.jdbc.SQL - executing prepstmnt 15975876 INSERT INTO xxx.OPENJPA_SEQUENCE_TABLE (ID, SEQUENCE_VALUE) VALUES (?, ?) [params=(int) 0, (int) 1] 44078 xxx TRACE [Persistence-thread-0] openjpa.jdbc.SQL - [15 ms] spent 44078 xxx TRACE [Persistence-thread-0] openjpa.jdbc.SQL - executing prepstmnt 3112190 SELECT SEQUENCE_VALUE FROM xxx.OPENJPA_SEQUENCE_TABLE WHERE ID = ? FOR UPDATE WITH RR [params=(int) 0] 44078 xxx TRACE [Persistence-thread-0] openjpa.jdbc.SQL - [0 ms] spent 44094 xxx TRACE [Persistence-thread-0] openjpa.jdbc.SQL - executing prepstmnt 20716393 UPDATE xxx.OPENJPA_SEQUENCE_TABLE SET SEQUENCE_VALUE = ? WHERE ID = ? AND SEQUENCE_VALUE = ? [params=(long) 51, (int) 0, (long) 1] 44094 xxx TRACE [Persistence-thread-0] openjpa.jdbc.SQL - [0 ms] spent 44188 xxx TRACE [Persistence-thread-0] openjpa.jdbc.SQL - executing prepstmnt 13686851 SELECT SEQUENCE_VALUE FROM xxx.OPENJPA_SEQUENCE_TABLE WHERE ID = ? FOR UPDATE WITH RR [params=(int) 0] 44203 xxx TRACE [Persistence-thread-0] openjpa.jdbc.SQL - [0 ms] spent 44203 xxx TRACE [Persistence-thread-0] openjpa.jdbc.SQL - executing prepstmnt 29070643 UPDATE xxx.OPENJPA_SEQUENCE_TABLE SET SEQUENCE_VALUE = ? WHERE ID = ? AND SEQUENCE_VALUE = ? [params=(long) 101, (int) 0, (long) 51] 44203 xxx TRACE [Persistence-thread-0] openjpa.jdbc.SQL - [0 ms] spent <2009-05-17 17:04:11,921> org.apache.openjpa.persistence.RollbackException: The transaction has been rolled back. See the nested exceptions for details on the errors that occurred. at org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:523) at com.xxx.xxx.dao.DAOContainer.commitTransaction(DAOContainer.java:195) at com.xxx.xxx.ds.Persister.persist(Persister.java:221) at com.xxx.xxx.ds.PersistRunner.run(PersistRunner.java:49) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441) at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303) at java.util.concurrent.FutureTask.run(FutureTask.java:138) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:98) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:207) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:885) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:907) at java.lang.Thread.run(Thread.java:619) Caused by: org.apache.openjpa.persistence.PersistenceException: The transaction has been rolled back. See the nested exceptions for details on the errors that occurred. at org.apache.openjpa.kernel.BrokerImpl.newFlushException(BrokerImpl.java:2163) at org.apache.openjpa.kernel.BrokerImpl.flush(BrokerImpl.java:2010) at org.apache.openjpa.kernel.BrokerImpl.flushSafe(BrokerImpl.java:1908) at org.apache.openjpa.kernel.BrokerImpl.beforeCompletion(BrokerImpl.java:1826) at org.apache.openjpa.kernel.LocalManagedRuntime.commit(LocalManagedRuntime.java:81) at org.apache.openjpa.kernel.BrokerImpl.commit(BrokerImpl.java:1350) at org.apache.openjpa.kernel.DelegatingBroker.commit(DelegatingBroker.java:877) at org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:512) ... 11 more Caused by: org.apache.openjpa.persistence.PersistenceException: 尝试修改标识列“ID”。 {INSERT INTO xxx.MON_SERVICE (ID, ATTRIBUTES, CPUTIME, DOMAIN, ERRNO, EXECTIME, TUXGROUP, MACHINE, MSGQUEUED, MSGSIZE, MSGTYPE, MSGWAITTIME, PID, PROCESSNAME, PROCTYPE, SERVERID, STATUS, SVCNAME, THREADID, LOGTIME, xxxVERSION, URCODE) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)} [code=-1, state=42Z23] at org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4232) at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4197) at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:102) at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:88) at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:64) at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flush(PreparedStatementManagerImpl.java:74) at org.apache.openjpa.jdbc.kernel.ConstraintUpdateManager.flush(ConstraintUpdateManager.java:543) at org.apache.ope
Re: ID field auto generation issue for multiple databases supporting
Yu Wang, Can we see your entity with "@GeneratedValue(strategy=GenerationType.AUTO)" and the stack trace you are receiving with Derby? Greetings, Milosz > Milosz, > > I have tried "@GeneratedValue(strategy=GenerationType.AUTO)", it works > fine for oracle but doesn't work for Derby. > > Regards, > Yu Wang > > On Sat, Jun 13, 2009 at 7:19 PM, Miłosz Tylenda wrote: > > Yu Wang, > > > > Try using "@GeneratedValue(strategy=GenerationType.AUTO)". This should > > automatically pick up either IDENTITY or SEQUENCE depending on the database > > you are using. > > > > Cheers, > > Milosz > > > >> Hello, > >> I found If I user derby, I need to use > >> "@GeneratedValue(strategy=GenerationType.IDENTITY)" for ID field. > >> And for oracle database, I need to use > >> "@GeneratedValue(strategy=GenerationType.SEQUENCE)". > >> > >> The question is if I want to use same entity classes to support both > >> derby and oracle, how should I do? > >> Do we have best practice for ID field value generation strategy for > >> multiple databases supporting? > >> > >> Regards, > >> Yu Wang > >> > > >
Re: ID field auto generation issue for multiple databases supporting
Milosz, I have tried "@GeneratedValue(strategy=GenerationType.AUTO)", it works fine for oracle but doesn't work for Derby. Regards, Yu Wang On Sat, Jun 13, 2009 at 7:19 PM, Miłosz Tylenda wrote: > Yu Wang, > > Try using "@GeneratedValue(strategy=GenerationType.AUTO)". This should > automatically pick up either IDENTITY or SEQUENCE depending on the database > you are using. > > Cheers, > Milosz > >> Hello, >> I found If I user derby, I need to use >> "@GeneratedValue(strategy=GenerationType.IDENTITY)" for ID field. >> And for oracle database, I need to use >> "@GeneratedValue(strategy=GenerationType.SEQUENCE)". >> >> The question is if I want to use same entity classes to support both >> derby and oracle, how should I do? >> Do we have best practice for ID field value generation strategy for >> multiple databases supporting? >> >> Regards, >> Yu Wang >> >
Re: ID field auto generation issue for multiple databases supporting
Yu Wang, Try using "@GeneratedValue(strategy=GenerationType.AUTO)". This should automatically pick up either IDENTITY or SEQUENCE depending on the database you are using. Cheers, Milosz > Hello, > I found If I user derby, I need to use > "@GeneratedValue(strategy=GenerationType.IDENTITY)" for ID field. > And for oracle database, I need to use > "@GeneratedValue(strategy=GenerationType.SEQUENCE)". > > The question is if I want to use same entity classes to support both > derby and oracle, how should I do? > Do we have best practice for ID field value generation strategy for > multiple databases supporting? > > Regards, > Yu Wang >