[h2] Re: Unique index or primary key violation: SYS_ID ON PUBLIC.SYS(ID) [23505-168] (using 1.3.168)
Having the same problem any solution to come out of this. using h2-1.3.160.jar and Apache-Tomcat-7.0.22. When tried to run recover option and recreate database found that create statement for some tables are found twice. What could be the reason for this? On Monday, March 17, 2014 3:07:11 PM UTC+8, Dieter Cailliau wrote: Using version 1.3.168 (in jboss 7.2.0). URL: jdbc:h2:/tmp/bms;DB_CLOSE_DELAY=-1;LOCK_TIMEOUT=5000 Database file is attached. Caused by: org.h2.jdbc.JdbcSQLException: Unique index or primary key violation: SYS_ID ON PUBLIC.SYS(ID) [23505-168] »···at org.h2.message.DbException.getJdbcSQLException(DbException.java:329) [h2-1.3.168.jar:1.3.168] »···at org.h2.message.DbException.get(DbException.java:169) [h2-1.3.168.jar:1.3.168] »···at org.h2.message.DbException.get(DbException.java:146) [h2-1.3.168.jar:1.3.168] »···at org.h2.index.BaseIndex.getDuplicateKeyException(BaseIndex.java:81) [h2-1.3.168.jar:1.3.168] »···at org.h2.index.TreeIndex.add(TreeIndex.java:62) [h2-1.3.168.jar:1.3.168] »···at org.h2.table.RegularTable.addRowsToIndex(RegularTable.java:327) [h2-1.3.168.jar:1.3.168] »···at org.h2.table.RegularTable.addIndex(RegularTable.java:256) [h2-1.3.168.jar:1.3.168] »···at org.h2.engine.Database.open(Database.java:619) [h2-1.3.168.jar:1.3.168] »···at org.h2.engine.Database.openDatabase(Database.java:222) [h2-1.3.168.jar:1.3.168] »···at org.h2.engine.Database.init(Database.java:217) [h2-1.3.168.jar:1.3.168] »···at org.h2.engine.Engine.openSession(Engine.java:56) [h2-1.3.168.jar:1.3.168] »···at org.h2.engine.Engine.openSession(Engine.java:159) [h2-1.3.168.jar:1.3.168] »···at org.h2.engine.Engine.createSessionAndValidate(Engine.java:138) [h2-1.3.168.jar:1.3.168] »···at org.h2.engine.Engine.createSession(Engine.java:121) [h2-1.3.168.jar:1.3.168] »···at org.h2.engine.Engine.createSession(Engine.java:28) [h2-1.3.168.jar:1.3.168] »···at org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:305) [h2-1.3.168.jar:1.3.168] »···at org.h2.jdbc.JdbcConnection.init(JdbcConnection.java:108) [h2-1.3.168.jar:1.3.168] »···at org.h2.jdbc.JdbcConnection.init(JdbcConnection.java:92) [h2-1.3.168.jar:1.3.168] »···at org.h2.Driver.connect(Driver.java:72) [h2-1.3.168.jar:1.3.168] »···at org.h2.jdbcx.JdbcDataSource.getJdbcConnection(JdbcDataSource.java:181) [h2-1.3.168.jar:1.3.168] »···at org.h2.jdbcx.JdbcDataSource.getXAConnection(JdbcDataSource.java:331) [h2-1.3.168.jar:1.3.168] »···at org.jboss.jca.adapters.jdbc.xa.XAManagedConnectionFactory.getXAManagedConnection(XAManagedConnectionFactory.java:441) -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
[h2] Re: Unique index or primary key violation: SYS_ID ON PUBLIC.SYS(ID) [23505-168] (using 1.3.168)
mano wrote Opening it with version 1.3.168 will give the error. Sorry, I meant 1.3.176. -- View this message in context: http://h2-database.66688.n3.nabble.com/Unique-index-or-primary-key-violation-SYS-ID-ON-PUBLIC-SYS-ID-23505-168-using-1-3-168-tp4028700p4029078.html Sent from the H2 Database mailing list archive at Nabble.com. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
[h2] Re: Unique index or primary key violation: SYS_ID ON PUBLIC.SYS(ID) [23505-168] (using 1.3.168)
Same problem here. I can add that my application is widely deployed, and each instance created a db for its internal configuration. Now everyone of them is failing, so I think it's not a problem of corruption... maybe the start parameters? The jdbc url with which it was created is: jdbc:h2://amp_db;LOCK_MODE=0;UNDO_LOG=0;TRACE_LEVEL_FILE=0;FILE_LOCK=SOCKET;AUTO_SERVER=TRUE You can find a failing db here: https://www.dropbox.com/s/h0i9ys98dz5xp88/amp_db.h2.zip Opening it with version 1.3.168 will give the error. Please advise here. I can't upgrade if this is not solved somehow... anyhow, thanks for the excellent work you're doing! Germano -- View this message in context: http://h2-database.66688.n3.nabble.com/Unique-index-or-primary-key-violation-SYS-ID-ON-PUBLIC-SYS-ID-23505-168-using-1-3-168-tp4028700p4029077.html Sent from the H2 Database mailing list archive at Nabble.com. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Re: Unique index or primary key violation: SYS_ID ON PUBLIC.SYS(ID) [23505-168] (using 1.3.168)
URL: jdbc:h2:/tmp/bms;DB_CLOSE_DELAY=-1;LOCK_TIMEOUT=5000 According to your query, the create_build was 168. I'm not using temporary tables (afaik) just regular JPA (hibernate inside jboss7). I didn't see OutOfMemory in my logs, but they don't go back to the first occurence of the problem, so i'm not sure. I am using XA. The error shows up when the first connection is obtained: org.h2.jdbcx.JdbcDataSource.getXAConnection(JdbcDataSource.java:331). -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
[h2] Re: Unique index or primary key violation: SYS_ID ON PUBLIC.SYS(ID) [23505-168] (using 1.3.168)
With 1.3.175 the error message is a bit longer: Uniqhttp://127.0.1.1:8082/test.do?jsessionid=4ea64ad64b043f1dd5de67ef7339a197#ue index or primary key violation: SYS_ID ON PUBLIC.SYS(ID) VALUES ( /* key:266 */ 15, 0, 3, 'CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_803C1AF8_DCEB_49F0_8A0D_14C8577C2238 START WITH 34 BELONGS_TO_TABLE') [23505-175]http://127.0.1.1:8082/test.do?jsessionid=4ea64ad64b043f1dd5de67ef7339a197# 23505/23505 -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Re: Unique index or primary key violation: SYS_ID ON PUBLIC.SYS(ID) [23505-168] (using 1.3.168)
Hi, Could you tell us, what database URL do you or did you use? It looks like a corruption of some kind, but I'm not sure. Regards, Thomas On Mon, Mar 17, 2014 at 8:11 AM, Dieter Cailliau dieter.caill...@gmail.comwrote: With 1.3.175 the error message is a bit longer: Uniqhttp://127.0.1.1:8082/test.do?jsessionid=4ea64ad64b043f1dd5de67ef7339a197#ue index or primary key violation: SYS_ID ON PUBLIC.SYS(ID) VALUES ( /* key:266 */ 15, 0, 3, 'CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_803C1AF8_DCEB_49F0_8A0D_14C8577C2238 START WITH 34 BELONGS_TO_TABLE') [23505-175]http://127.0.1.1:8082/test.do?jsessionid=4ea64ad64b043f1dd5de67ef7339a197# 23505/23505 -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Re: Unique index or primary key violation: SYS_ID ON PUBLIC.SYS(ID) [23505-168] (using 1.3.168)
Hi, Some more questions: - With which version of H2 was this database created? You can find it out using: select * from information_schema.settings where name='CREATE_BUILD' or have a look in the SQL script created by the recover tool. - Did the application run out of memory (once, or multiple times)? Known corruptions with older versions are: With version 1.3.171 and older: when using local temporary tables and not dropping them manually before closing the session, and then killing the process could result in a database that couldn't be opened. Regards, Thomas On Tue, Mar 18, 2014 at 6:31 AM, Thomas Mueller thomas.tom.muel...@gmail.com wrote: Hi, Could you tell us, what database URL do you or did you use? It looks like a corruption of some kind, but I'm not sure. Regards, Thomas On Mon, Mar 17, 2014 at 8:11 AM, Dieter Cailliau dieter.caill...@gmail.com wrote: With 1.3.175 the error message is a bit longer: Uniqhttp://127.0.1.1:8082/test.do?jsessionid=4ea64ad64b043f1dd5de67ef7339a197#ue index or primary key violation: SYS_ID ON PUBLIC.SYS(ID) VALUES ( /* key:266 */ 15, 0, 3, 'CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_803C1AF8_DCEB_49F0_8A0D_14C8577C2238 START WITH 34 BELONGS_TO_TABLE') [23505-175]http://127.0.1.1:8082/test.do?jsessionid=4ea64ad64b043f1dd5de67ef7339a197# 23505/23505 -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
