[h2] Unique index or primary key violation
Hi, I am facing the following error when writing to a table via hibernate: 03/11/23 19:44:52 808 ERROR SqlExceptionHelper: Unique index or primary key violation: "PRIMARY_KEY_6 ON PUBLIC.AD(ACCU_ID, TARGET_ID, REPLAY_ID) VALUES (8, 24, 1, 7)"; SQL statement: insert into ad (end_time, start_time, accu_id, target_id, replay_id) values (?, ?, ?, ?, ?) [23505-192] I wonder what the 7 in (8, 24, 1, 7) means. (ACCU_ID, TARGET_ID, REPLAY_ID) has 3 columns (the primary key for ad table). It is a bit strange why a fourth number shows up. Thanks -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/6a3606a7-a4bb-4ddf-bfa9-5d5c54e173e6n%40googlegroups.com.
Re: [h2] "Unique index or primary key violation" at concurrent insers (MULTI_THREADED)
Great, thanks! > > > -- 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 https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
[h2] "Unique index or primary key violation" at concurrent insers (MULTI_THREADED)
Hi I've attached test which generates the exception if MULTI_THREADED=1: org.h2.jdbc.JdbcSQLException: Unique index or primary key violation: "PRIMARY KEY ON PUBLIC.TRAN"; SQL statement: INSERT INTO tran (id) values(?) [23505-193] at org.h2.message.DbException.getJdbcSQLException(DbException.java:345) at org.h2.message.DbException.get(DbException.java:179) at org.h2.message.DbException.get(DbException.java:155) at org.h2.mvstore.db.MVPrimaryIndex.add(MVPrimaryIndex.java:138) at org.h2.mvstore.db.MVTable.addRow(MVTable.java:704) at org.h2.command.dml.Insert.insertRows(Insert.java:156) at org.h2.command.dml.Insert.update(Insert.java:114) at org.h2.command.CommandContainer.update(CommandContainer.java:98) at org.h2.command.Command.executeUpdate(Command.java:258) at org.h2.server.TcpServerThread.process(TcpServerThread.java:344) at org.h2.server.TcpServerThread.run(TcpServerThread.java:158) at java.lang.Thread.run(Unknown Source) at org.h2.engine.SessionRemote.done(SessionRemote.java:624) at org.h2.command.CommandRemote.executeUpdate(CommandRemote.java:191) at org.h2.jdbc.JdbcPreparedStatement.execute(JdbcPreparedStatement.java:201) at Arte.call(Bug_H2_2.java:82) at Arte.call(Bug_H2_2.java:64) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) In case MULTI_THREADED=0 this test doesn't fail -- 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 https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.concurrent.Callable; import java.util.concurrent.ExecutorService; import java.util.concurrent.Executors; public class Bug_H2_2 { static String dbUrl = "jdbc:h2:tcp://localhost:9002/nioMemFS:db;DB_CLOSE_DELAY=-1;MULTI_THREADED=1"; static String user = "sa", pwd = ""; static int threadCount = 100; public static void main(String args[]) throws InterruptedException, SQLException { Arte[] artes = new Arte[threadCount]; Connection dbConnect; try { Class.forName("org.h2.Driver"); dbConnect = DriverManager.getConnection(dbUrl, user, pwd); DbPreparator.prepareScheme(dbConnect); System.out.println("DB scheme prepared"); for (int i = 0; i < threadCount; i++) { artes[i] = new Arte(DriverManager.getConnection(dbUrl, user, pwd), i); } System.out.println("ARTEs created"); } catch (ClassNotFoundException | SQLException e) { System.out.println("DB Connection Failed: " + dbUrl); e.printStackTrace(); return; } ExecutorService threadPool = Executors.newFixedThreadPool(threadCount); for (int i = 0; i < threadCount; i++) { threadPool.submit(artes[i]); } while (true) { Thread.sleep(100); } } } class DbPreparator { public static final int OBJ_CNT = 1; private static final String[] SQLs = new String[]{ "CREATE TABLE IF NOT EXISTS TRAN (" + " ID NUMBER(18,0) not null PRIMARY KEY\n" + ")" }; public static void prepareScheme(Connection db) throws SQLException { for (String sql : SQLs) { db.createStatement().execute(sql); db.commit(); } } } class Arte implements Callable { Connection db; PreparedStatement insertTranStmt; long tranId; public Arte(Connection db_, int number) throws SQLException { db = db_; db.setAutoCommit(false); insertTranStmt = db.prepareStatement("INSERT INTO tran (id) values(?)"); tranId = number * 1_000_000_000 + System.currentTimeMillis(); //to garantee uniques } @Override public Integer call() { try { while (true) { insertTranStmt.setLong(1, tranId++); insertTranStmt.execute(); db.commit(); } } catch (SQLException e) { System.out.println("DB write error: "); e.printStackTrace(); System.exit(0); } return null; } }
Re: [h2] Unique index or primary key violation in version 1.4.191
I have not tested the scenario with MVCC off because that is pretty much a non-starter for our performance requirements. It is kind of an odd bug because the application, the embedded h2 web console, and the h2 shell do not have a problem with the database file. Only other database viewers seem to have the problem - of which I certainly do not have an exhaustive list. When I started looking into the bug I did not realize that I'd be able to access the database file via the H2 shell, so it was initially a higher priority bug. As I mentioned, the most important thing for me that has come out of this is learning about MVCC support/stability for PageStore. Noel mentioned it is not a priority for them, which I totally agree with, and I would not want anyone to spend more of their time trying to research this because the solution for me is to switch to MVStore and to use the H2 shell instead of some of db viewer. On Wednesday, February 17, 2016 at 7:08:20 AM UTC-6, Kartweel wrote: > > I've been using MVCC with PageStore for years. LOBS don't work very well > (but I didn't want them in the db anyway, so we don't use LOBS). Haven't > had any issues with anything else though. > > Doesn't mean bugs don't exist though. > > You only get the issue with MVCC on though hey? > > On 17/02/2016 1:18 PM, Noel Grandin wrote: > > At some point in the future is all I can say, not anytime soon. My > > point was more that MVCC on PageStore is a dead end for us, so we're > > not going to expend any effort debugging problems with it. > > > > So if you want reliability, use PageStore without MVCC. > > > > > -- 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 https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Unique index or primary key violation in version 1.4.191
I've been using MVCC with PageStore for years. LOBS don't work very well (but I didn't want them in the db anyway, so we don't use LOBS). Haven't had any issues with anything else though. Doesn't mean bugs don't exist though. You only get the issue with MVCC on though hey? On 17/02/2016 1:18 PM, Noel Grandin wrote: At some point in the future is all I can say, not anytime soon. My point was more that MVCC on PageStore is a dead end for us, so we're not going to expend any effort debugging problems with it. So if you want reliability, use PageStore without MVCC. -- 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 https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Unique index or primary key violation in version 1.4.191
At some point in the future is all I can say, not anytime soon. My point was more that MVCC on PageStore is a dead end for us, so we're not going to expend any effort debugging problems with it. So if you want reliability, use PageStore without MVCC. -- 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 https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Unique index or primary key violation in version 1.4.191
Do you know of any timelines to disable MVCC support in PageStore? I don't see it listed in the roadmap http://www.h2database.com/html/roadmap.html but I do see that the removal of at least one PageStore feature is being called out. -- 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 https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Unique index or primary key violation in version 1.4.191
I spent some time trying to recreate the issue with a simple table set up but was not able to. There must be something else in my database schema that is contributing to the problem. Interestingly enough, when I use the org.h2.tools.Shell to view my h2 database, I am able to connect to it successfully and am able to browse the database with no errors. As a result of this topic I at least know that 1) I can use the h2 shell or embedded h2 server to browse a database with this error, and 2) I need to migrate away from PageStore and MVCC to MVStore. Based on what you said about the support and future PageStore and MVCC, and the fact that I have a workaround, I don't think it makes sense (for me and my company) to pursue researching this bug further because we will be migrating away from PageStore. Thank you very much for your clarification and help on the issue. On Tuesday, February 16, 2016 at 12:30:45 AM UTC-6, Noel Grandin wrote: > > Of course,it could also be that you've uncovered another tricky corner > case - in which case we need some kind of > standalone test case to be able to debug it > -- 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 https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Unique index or primary key violation in version 1.4.191
I'm working on creating a standalone test and will update once I have one. Thanks! On Tuesday, February 16, 2016 at 12:30:45 AM UTC-6, Noel Grandin wrote: > > Of course,it could also be that you've uncovered another tricky corner > case - in which case we need some kind of > standalone test case to be able to debug it > -- 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 https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Unique index or primary key violation in version 1.4.191
Of course,it could also be that you've uncovered another tricky corner case - in which case we need some kind of standalone test case to be able to debug it -- 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 https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
[h2] Unique index or primary key violation in version 1.4.191
I still get the following message using the latest version of the H2 jar via maven: [23505][23505] Unique index or primary key violation: "PRIMARY KEY ON .PAGE_INDEX"; SQL statement: I have a join table that seems to be the culprit - it uses a composite primary key with foreign key references to two other tables. My understanding was that this issue had been fixed https://groups.google.com/forum/?fromgroups#!topic/h2-database/r2tfqY8i4RA but potentially not for the PageStore database? Here is my connection string: jdbc:h2:file:${config.dir}/databaseName;DB_CLOSE_ON_EXIT=FALSE;MV_STORE= false;LOCK_TIMEOUT=1;MVCC=true I will get the error even on brand new database. My springboot application has no issues accessing the database, nor do I have any issues using the integrated h2-console within springboot. However, I cannot access the h2 database through an external viewer, such as IntelliJ or DbVisualizer, This causes me concern in the event that I need to examine a customer's database. -- 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 https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Unique index or primary key violation: PRIMARY KEY ON .PAGE_INDEX error
Hi, I think this issue was resolved in version 1.4.179, see the changelog entry: - Databases created with version 1.3.175 and earlier that contained foreign keys in combination with multi-column indexes could not be opened in some cases. This was due to a bugfix in version 1.3.176: Referential integrity constraints sometimes used the wrong index. I suggest to use a recent version of H2, but append ;mv_store=false to the database URL. Worst case, you would need to export the database to a SQL script with an old version, and re-created it from the script in the new version. Regards, Thomas On Fri, May 22, 2015 at 6:18 PM, Carson carsonhuang2...@gmail.com wrote: Hi, I'm new to H2, and I'm working with an already existing H2 database. When I first run H2 Console and access the database, I manage to get in. However, after I disconnect, and try to re-access the database, the following error occurs: Unique index or primary key violation: PRIMARY KEY ON .PAGE_INDEX; SQL statement: ALTER TABLE PUBLIC.PPT_TEMPLATE ADD CONSTRAINT PUBLIC.CONSTRAINT_21 FOREIGN KEY(ID_TEMPLATE) REFERENCES PUBLIC.PPT_TEMPLATE(ID_TEMPLATE) NOCHECK [23505-176] I would need to get a fresh copy of the db from my git to be able to 're-access' the database. My URL is the following: jdbc:h2:C:\Users\carson\.db\PPT_DB;CIPHER=AES My H2 version is, I think 1.3.176 (it's the version I see on the H2 console tab) Do you guys know why this is occurring? -- 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.
[h2] Unique index or primary key violation: PRIMARY KEY ON .PAGE_INDEX error
Hi, I'm new to H2, and I'm working with an already existing H2 database. When I first run H2 Console and access the database, I manage to get in. However, after I disconnect, and try to re-access the database, the following error occurs: Unique index or primary key violation: PRIMARY KEY ON .PAGE_INDEX; SQL statement: ALTER TABLE PUBLIC.PPT_TEMPLATE ADD CONSTRAINT PUBLIC.CONSTRAINT_21 FOREIGN KEY(ID_TEMPLATE) REFERENCES PUBLIC.PPT_TEMPLATE(ID_TEMPLATE) NOCHECK [23505-176] I would need to get a fresh copy of the db from my git to be able to 're-access' the database. My URL is the following: jdbc:h2:C:\Users\carson\.db\PPT_DB;CIPHER=AES My H2 version is, I think 1.3.176 (it's the version I see on the H2 console tab) Do you guys know why this is occurring? -- 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] Unique index or primary key violation: SYS_ID ON PUBLIC.SYS(ID) [23505-168] (using 1.3.168)
Reason for our case seems to be due to JVM crash. We use Runscript method to run SQL files with large number of inserts statements preceded by delete statement. During execution JVM is crashing, due to which dB file becomes corrupt. I tried with out of memory error also. -- 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] Unique index or primary key violation: SYS_ID ON PUBLIC.SYS(ID) [23505-168] (using 1.3.168)
Hi, I'm afraid you will need to manually edit the SQL script file. 1.3.160 That's quite old, many bugs have been fixed since then. I suggest to upgrade to a more recent (1.3.x) version. Regards, Thomas On Saturday, July 26, 2014, Sanchand sangli.mu...@gmail.com wrote: 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 javascript:_e(%7B%7D,'cvml','h2-database%2bunsubscr...@googlegroups.com'); . To post to this group, send email to h2-database@googlegroups.com javascript:_e(%7B%7D,'cvml','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] Unique index or primary key violation: SYS_ID ON PUBLIC.SYS(ID) [23505-168] (using 1.3.168)
Hi, To re-create the database, you can use the Recover tool. Regards, Thomas On Sun, Jun 29, 2014 at 11:38 PM, Ravindra Gullapalli sharewithra...@gmail.com wrote: Another input (might be useful) I did a checkout of source code of revision # 5751 and tried to generate script. However I got the error which I posted above. Then I set the flag *retry=true* in *public void add(Session session, Row row)* method in *org.h2.index.PageDataIndex* and it generated the script. With that script I reconstructed the database and now I got my data back. I am not able to attach the file as the attach file dialog is not working here as of now. -- 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] Unique index or primary key violation: SYS_ID ON PUBLIC.SYS(ID) [23505-168] (using 1.3.168)
Hi, i am getting the error using 1.4.179 version also. Same error is coming when I try to generate script. Please help me out to get rid of this error. Ravindra. On Monday, June 2, 2014 9:46:18 PM UTC+5:30, Thomas Mueller wrote: Hi, I finally found and fixed the problem now. This problem will be fixed in the next release. One workaround is to not upgrade to version 1.3.176 if the database was created with an earlier version. Another workaround is to export the database file to a SQL script and re-create it. Regards, Thomas On Wed, May 28, 2014 at 4:42 PM, Nick99 nail.a...@gmail.com javascript: wrote: Same problem. 1. The database was created in CREATE_BUILD 170, then was used with 1.3.174. I've tried using 1.4.178 on that database in Hibernate-based product - got org.h2.jdbc.JdbcSQLException: Unique index or primary key violation: PRIMARY KEY ON .PAGE_INDEX; SQL statement:[...] 1.3.175 works ok on that db. 1.3.176 is not. 2. Opening the db in 1.3.176 Console produces this (table names removed): Unique index or primary key violation: PRIMARY KEY ON .PAGE_INDEX; SQL statement: ALTER TABLE PUBLIC.-- ADD CONSTRAINT PUBLIC.FK28D9808AF98221CA FOREIGN KEY(JESID) REFERENCES PUBLIC.--(ID) NOCHECK [23505-176] 23505/23505 (Help) On Monday, May 26, 2014 10:33:05 AM UTC+3, Germano Rizzo wrote: Provided here https://groups.google.com/forum/?hl=it#!topic/h2-database/lNp80bgbvBY. Thanks! Il giorno giovedì 22 maggio 2014 17:43:18 UTC+2, Thomas Mueller ha scritto: Hi I would need a reproducible test case, or the database file. Regards, Thomas On Thursday, May 22, 2014, jack jin supe...@gmail.com wrote: when I upgrade to 1.3.176,I have the same problem. the old version h2 is works fine with the db file On Tuesday, May 6, 2014 5:00:52 PM UTC+8, Germano Rizzo wrote: Hi Thomas, so your opinion is that all the db's are corrupted? The strange thing is, they fail to open only with the latest stable version. Every other version I tried works fine, and this configuration have worked in 40 installations for 3 years now, across different versions of H2. Do you want me to start another thread? Germano Il giorno martedì 6 maggio 2014 07:59:45 UTC+2, Thomas Mueller ha scritto: Hi, If you are using LOCK_MODE=0;UNDO_LOG=0 then you need to be aware of the risks. See the FAQ and the documentation. Please use different email subjects for different problems. Regards, Thomas On Monday, May 5, 2014, mano german...@gmail.com wrote: 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.na bble.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. -- 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...@googlegroups.com javascript:. To post to this group, send email to h2-da...@googlegroups.com javascript:. 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] Unique index or primary key violation: SYS_ID ON PUBLIC.SYS(ID) [23505-168] (using 1.3.168)
With 1.4.179 I am facing this problem Unique index or primary key violation: PRIMARY KEY ON .PAGE_INDEX; SQL statement: ALTER TABLE PUBLIC.TABACCOUNT ADD CONSTRAINT PUBLIC.FK_TABACCOUNT_PARENTACCOUNTNO FOREIGN KEY(PARENTACCOUNTNO) INDEX PUBLIC.FK_TABACCOUNT_PARENTACCOUNTNO_INDEX_D REFERENCES PUBLIC.TABACCOUNT(ACCOUNTNO) INDEX PUBLIC.PRIMARY_KEY_DC6 NOCHECK [23505-179] http://192.168.43.206:8082/login.do?jsessionid=3a9c7be376a442f869ae96c77692d959# 23505/23505 (Help) http://h2database.com/javadoc/org/h2/constant/ErrorCode.html#c23505 org.h2.jdbc.JdbcSQLException: Unique index or primary key violation: PRIMARY KEY ON .PAGE_INDEX; SQL statement: ALTER TABLE PUBLIC.TABACCOUNT ADD CONSTRAINT PUBLIC.FK_TABACCOUNT_PARENTACCOUNTNO FOREIGN KEY(PARENTACCOUNTNO) INDEX PUBLIC.FK_TABACCOUNT_PARENTACCOUNTNO_INDEX_D REFERENCES PUBLIC.TABACCOUNT(ACCOUNTNO) INDEX PUBLIC.PRIMARY_KEY_DC6 NOCHECK [23505-179] at org.h2.message.DbException.getJdbcSQLException(DbException.java:345 http://h2database.com/html/source.html?file=org/h2/message/DbException.javaline=345build=179) at org.h2.message.DbException.get(DbException.java:179 http://h2database.com/html/source.html?file=org/h2/message/DbException.javaline=179build=179) at org.h2.message.DbException.get(DbException.java:155 http://h2database.com/html/source.html?file=org/h2/message/DbException.javaline=155build=179) at org.h2.index.PageDataIndex.getNewDuplicateKeyException(PageDataIndex.java:165 http://h2database.com/html/source.html?file=org/h2/index/PageDataIndex.javaline=165build=179) at org.h2.index.PageDataIndex.add(PageDataIndex.java:143 http://h2database.com/html/source.html?file=org/h2/index/PageDataIndex.javaline=143build=179) at org.h2.store.PageStore.addMeta(PageStore.java:1812 http://h2database.com/html/source.html?file=org/h2/store/PageStore.javaline=1812build=179) at org.h2.index.PageBtreeIndex.init(PageBtreeIndex.java:63 http://h2database.com/html/source.html?file=org/h2/index/PageBtreeIndex.javaline=63build=179) at org.h2.table.RegularTable.addIndex(RegularTable.java:234 http://h2database.com/html/source.html?file=org/h2/table/RegularTable.javaline=234build=179) at org.h2.command.ddl.AlterTableAddConstraint.createIndex(AlterTableAddConstraint.java:287 http://h2database.com/html/source.html?file=org/h2/command/ddl/AlterTableAddConstraint.javaline=287build=179) at org.h2.command.ddl.AlterTableAddConstraint.tryUpdate(AlterTableAddConstraint.java:236 http://h2database.com/html/source.html?file=org/h2/command/ddl/AlterTableAddConstraint.javaline=236build=179) at org.h2.command.ddl.AlterTableAddConstraint.update(AlterTableAddConstraint.java:72 http://h2database.com/html/source.html?file=org/h2/command/ddl/AlterTableAddConstraint.javaline=72build=179) at org.h2.engine.MetaRecord.execute(MetaRecord.java:58 http://h2database.com/html/source.html?file=org/h2/engine/MetaRecord.javaline=58build=179) at org.h2.engine.Database.open(Database.java:729 http://h2database.com/html/source.html?file=org/h2/engine/Database.javaline=729build=179) at org.h2.engine.Database.openDatabase(Database.java:263 http://h2database.com/html/source.html?file=org/h2/engine/Database.javaline=263build=179) at org.h2.engine.Database.init(Database.java:257 http://h2database.com/html/source.html?file=org/h2/engine/Database.javaline=257build=179) at org.h2.engine.Engine.openSession(Engine.java:58 http://h2database.com/html/source.html?file=org/h2/engine/Engine.javaline=58build=179) at org.h2.engine.Engine.openSession(Engine.java:165 http://h2database.com/html/source.html?file=org/h2/engine/Engine.javaline=165build=179) at org.h2.engine.Engine.createSessionAndValidate(Engine.java:143 http://h2database.com/html/source.html?file=org/h2/engine/Engine.javaline=143build=179) at org.h2.engine.Engine.createSession(Engine.java:126 http://h2database.com/html/source.html?file=org/h2/engine/Engine.javaline=126build=179) at org.h2.engine.Engine.createSession(Engine.java:26 http://h2database.com/html/source.html?file=org/h2/engine/Engine.javaline=26build=179) at org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:335 http://h2database.com/html/source.html?file=org/h2/engine/SessionRemote.javaline=335build=179) at org.h2.jdbc.JdbcConnection.init(JdbcConnection.java:107 http://h2database.com/html/source.html?file=org/h2/jdbc/JdbcConnection.javaline=107build=179) at org.h2.jdbc.JdbcConnection.init(JdbcConnection.java:91 http://h2database.com/html/source.html?file=org/h2/jdbc/JdbcConnection.javaline=91build=179) at org.h2.Driver.connect(Driver.java:72 http://h2database.com/html/source.html?file=org/h2/Driver.javaline=72build=179) at org.h2.server.web.WebServer.getConnection(WebServer.java:682 http://h2database.com/html/source.html?file=org/h2/server/web/WebServer.javaline=682build=179)
Re: [h2] Unique index or primary key violation: SYS_ID ON PUBLIC.SYS(ID) [23505-168] (using 1.3.168)
Another input (might be useful) I did a checkout of source code of revision # 5751 and tried to generate script. However I got the error which I posted above. Then I set the flag *retry=true* in *public void add(Session session, Row row)* method in *org.h2.index.PageDataIndex* and it generated the script. With that script I reconstructed the database and now I got my data back. I am not able to attach the file as the attach file dialog is not working here as of now. -- 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] Unique index or primary key violation: SYS_ID ON PUBLIC.SYS(ID) [23505-168] (using 1.3.168)
Hi, I finally found and fixed the problem now. This problem will be fixed in the next release. One workaround is to not upgrade to version 1.3.176 if the database was created with an earlier version. Another workaround is to export the database file to a SQL script and re-create it. Regards, Thomas On Wed, May 28, 2014 at 4:42 PM, Nick99 nail.abda...@gmail.com wrote: Same problem. 1. The database was created in CREATE_BUILD 170, then was used with 1.3.174. I've tried using 1.4.178 on that database in Hibernate-based product - got org.h2.jdbc.JdbcSQLException: Unique index or primary key violation: PRIMARY KEY ON .PAGE_INDEX; SQL statement:[...] 1.3.175 works ok on that db. 1.3.176 is not. 2. Opening the db in 1.3.176 Console produces this (table names removed): Unique index or primary key violation: PRIMARY KEY ON .PAGE_INDEX; SQL statement: ALTER TABLE PUBLIC.-- ADD CONSTRAINT PUBLIC.FK28D9808AF98221CA FOREIGN KEY(JESID) REFERENCES PUBLIC.--(ID) NOCHECK [23505-176] 23505/23505 (Help) On Monday, May 26, 2014 10:33:05 AM UTC+3, Germano Rizzo wrote: Provided here https://groups.google.com/forum/?hl=it#!topic/h2-database/lNp80bgbvBY. Thanks! Il giorno giovedì 22 maggio 2014 17:43:18 UTC+2, Thomas Mueller ha scritto: Hi I would need a reproducible test case, or the database file. Regards, Thomas On Thursday, May 22, 2014, jack jin supe...@gmail.com wrote: when I upgrade to 1.3.176,I have the same problem. the old version h2 is works fine with the db file On Tuesday, May 6, 2014 5:00:52 PM UTC+8, Germano Rizzo wrote: Hi Thomas, so your opinion is that all the db's are corrupted? The strange thing is, they fail to open only with the latest stable version. Every other version I tried works fine, and this configuration have worked in 40 installations for 3 years now, across different versions of H2. Do you want me to start another thread? Germano Il giorno martedì 6 maggio 2014 07:59:45 UTC+2, Thomas Mueller ha scritto: Hi, If you are using LOCK_MODE=0;UNDO_LOG=0 then you need to be aware of the risks. See the FAQ and the documentation. Please use different email subjects for different problems. Regards, Thomas On Monday, May 5, 2014, mano german...@gmail.com wrote: 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.na bble.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. -- 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. -- 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] Unique index or primary key violation: SYS_ID ON PUBLIC.SYS(ID) [23505-168] (using 1.3.168)
Same problem. 1. The database was created in CREATE_BUILD 170, then was used with 1.3.174. I've tried using 1.4.178 on that database in Hibernate-based product - got org.h2.jdbc.JdbcSQLException: Unique index or primary key violation: PRIMARY KEY ON .PAGE_INDEX; SQL statement:[...] 1.3.175 works ok on that db. 1.3.176 is not. 2. Opening the db in 1.3.176 Console produces this (table names removed): Unique index or primary key violation: PRIMARY KEY ON .PAGE_INDEX; SQL statement: ALTER TABLE PUBLIC.-- ADD CONSTRAINT PUBLIC.FK28D9808AF98221CA FOREIGN KEY(JESID) REFERENCES PUBLIC.--(ID) NOCHECK [23505-176] 23505/23505 (Help) On Monday, May 26, 2014 10:33:05 AM UTC+3, Germano Rizzo wrote: Provided herehttps://groups.google.com/forum/?hl=it#!topic/h2-database/lNp80bgbvBY. Thanks! Il giorno giovedì 22 maggio 2014 17:43:18 UTC+2, Thomas Mueller ha scritto: Hi I would need a reproducible test case, or the database file. Regards, Thomas On Thursday, May 22, 2014, jack jin supe...@gmail.com wrote: when I upgrade to 1.3.176,I have the same problem. the old version h2 is works fine with the db file On Tuesday, May 6, 2014 5:00:52 PM UTC+8, Germano Rizzo wrote: Hi Thomas, so your opinion is that all the db's are corrupted? The strange thing is, they fail to open only with the latest stable version. Every other version I tried works fine, and this configuration have worked in 40 installations for 3 years now, across different versions of H2. Do you want me to start another thread? Germano Il giorno martedì 6 maggio 2014 07:59:45 UTC+2, Thomas Mueller ha scritto: Hi, If you are using LOCK_MODE=0;UNDO_LOG=0 then you need to be aware of the risks. See the FAQ and the documentation. Please use different email subjects for different problems. Regards, Thomas On Monday, May 5, 2014, mano german...@gmail.com wrote: 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. -- 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] Unique index or primary key violation: SYS_ID ON PUBLIC.SYS(ID) [23505-168] (using 1.3.168)
Provided herehttps://groups.google.com/forum/?hl=it#!topic/h2-database/lNp80bgbvBY. Thanks! Il giorno giovedì 22 maggio 2014 17:43:18 UTC+2, Thomas Mueller ha scritto: Hi I would need a reproducible test case, or the database file. Regards, Thomas On Thursday, May 22, 2014, jack jin supe...@gmail.com javascript: wrote: when I upgrade to 1.3.176,I have the same problem. the old version h2 is works fine with the db file On Tuesday, May 6, 2014 5:00:52 PM UTC+8, Germano Rizzo wrote: Hi Thomas, so your opinion is that all the db's are corrupted? The strange thing is, they fail to open only with the latest stable version. Every other version I tried works fine, and this configuration have worked in 40 installations for 3 years now, across different versions of H2. Do you want me to start another thread? Germano Il giorno martedì 6 maggio 2014 07:59:45 UTC+2, Thomas Mueller ha scritto: Hi, If you are using LOCK_MODE=0;UNDO_LOG=0 then you need to be aware of the risks. See the FAQ and the documentation. Please use different email subjects for different problems. Regards, Thomas On Monday, May 5, 2014, mano german...@gmail.com wrote: 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. -- 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] Unique index or primary key violation: SYS_ID ON PUBLIC.SYS(ID) [23505-168] (using 1.3.168)
Hi I would need a reproducible test case, or the database file. Regards, Thomas On Thursday, May 22, 2014, jack jin super...@gmail.com wrote: when I upgrade to 1.3.176,I have the same problem. the old version h2 is works fine with the db file On Tuesday, May 6, 2014 5:00:52 PM UTC+8, Germano Rizzo wrote: Hi Thomas, so your opinion is that all the db's are corrupted? The strange thing is, they fail to open only with the latest stable version. Every other version I tried works fine, and this configuration have worked in 40 installations for 3 years now, across different versions of H2. Do you want me to start another thread? Germano Il giorno martedì 6 maggio 2014 07:59:45 UTC+2, Thomas Mueller ha scritto: Hi, If you are using LOCK_MODE=0;UNDO_LOG=0 then you need to be aware of the risks. See the FAQ and the documentation. Please use different email subjects for different problems. Regards, Thomas On Monday, May 5, 2014, mano german...@gmail.com wrote: 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. -- 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.comjavascript:_e(%7B%7D,'cvml','h2-database%2bunsubscr...@googlegroups.com'); . To post to this group, send email to h2-database@googlegroups.comjavascript:_e(%7B%7D,'cvml','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] Unique index or primary key violation: SYS_ID ON PUBLIC.SYS(ID) [23505-168] (using 1.3.168)
when I upgrade to 1.3.176,I have the same problem. the old version h2 is works fine with the db file On Tuesday, May 6, 2014 5:00:52 PM UTC+8, Germano Rizzo wrote: Hi Thomas, so your opinion is that all the db's are corrupted? The strange thing is, they fail to open only with the latest stable version. Every other version I tried works fine, and this configuration have worked in 40 installations for 3 years now, across different versions of H2. Do you want me to start another thread? Germano Il giorno martedì 6 maggio 2014 07:59:45 UTC+2, Thomas Mueller ha scritto: Hi, If you are using LOCK_MODE=0;UNDO_LOG=0 then you need to be aware of the risks. See the FAQ and the documentation. Please use different email subjects for different problems. Regards, Thomas On Monday, May 5, 2014, mano german...@gmail.com wrote: 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. -- 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] Unique index or primary key violation: SYS_ID ON PUBLIC.SYS(ID) [23505-168] (using 1.3.168)
Hi, If you are using LOCK_MODE=0;UNDO_LOG=0 then you need to be aware of the risks. See the FAQ and the documentation. Please use different email subjects for different problems. Regards, Thomas On Monday, May 5, 2014, mano germano.ri...@gmail.com wrote: 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. -- 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] 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.176 will give the error. Latest 1.4.* version doesn't. Please advise here. I can't upgrade if this is not solved somehow... anyhow, thanks for the excellent work you're doing! Germano Il giorno giovedì 10 aprile 2014 12:11:22 UTC+2, Joerg Z. ha scritto: Hi, same problem here. After upgrade h2-1.3.174.jar to h2-1.3.176.jar and connect to my exist db file I get to following trace: (without any executed statement, just connect) 04-10 11:50:39 database: ALTER TABLE PUBLIC.REL_USERS_RIGHTS ADD CONSTRAINT PUBLIC.REL_USERS_RIGHTS_FK_ID_USER FOREIGN KEY(ID_USER) REFERENCES PUBLIC.TBL_USERS(ID) NOCHECK org.h2.jdbc.JdbcSQLException: Eindeutiger Index oder Primärschlüssel verletzt: PRIMARY KEY ON .PAGE_INDEX Unique index or primary key violation: PRIMARY KEY ON .PAGE_INDEX; SQL statement: ALTER TABLE PUBLIC.REL_USERS_RIGHTS ADD CONSTRAINT PUBLIC.REL_USERS_RIGHTS_FK_ID_USER FOREIGN KEY(ID_USER) REFERENCES PUBLIC.TBL_USERS(ID) NOCHECK [23505-176] A post repair with backup/restorehttp://www.h2database.com/html/tutorial.html#upgrade_backup_restore doesn't help and run into same issue. Regards, Joerg -- 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] Unique index or primary key violation: SYS_ID ON PUBLIC.SYS(ID) [23505-168] (using 1.3.168)
Hi, same problem here. After upgrade h2-1.3.174.jar to h2-1.3.176.jar and connect to my exist db file I get to following trace: (without any executed statement, just connect) 04-10 11:50:39 database: ALTER TABLE PUBLIC.REL_USERS_RIGHTS ADD CONSTRAINT PUBLIC.REL_USERS_RIGHTS_FK_ID_USER FOREIGN KEY(ID_USER) REFERENCES PUBLIC.TBL_USERS(ID) NOCHECK org.h2.jdbc.JdbcSQLException: Eindeutiger Index oder Primärschlüssel verletzt: PRIMARY KEY ON .PAGE_INDEX Unique index or primary key violation: PRIMARY KEY ON .PAGE_INDEX; SQL statement: ALTER TABLE PUBLIC.REL_USERS_RIGHTS ADD CONSTRAINT PUBLIC.REL_USERS_RIGHTS_FK_ID_USER FOREIGN KEY(ID_USER) REFERENCES PUBLIC.TBL_USERS(ID) NOCHECK [23505-176] A post repair with backup/restorehttp://www.h2database.com/html/tutorial.html#upgrade_backup_restore doesn't help and run into same issue. Regards, Joerg -- 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] Unique index or primary key violation: SYS_ID ON PUBLIC.SYS(ID) [23505-168] (using 1.3.168)
Hi, I'm afraid I can't tell what the problem is. The database was created with version 1.3.168. I didn't see any temporary tables in the database file. But I'm afraid I would need a reproducible test case in order to help for this case. Regards, Thomas On Thu, Mar 27, 2014 at 8:31 PM, Dieter Cailliau dieter.caill...@gmail.comwrote: Thoms the database is attached to the first post of this topic. Op donderdag 20 maart 2014 07:53:26 UTC+1 schreef Thomas Mueller: Hi, I'm not using temporary tables I think there are some cases where Hibernate creates temporary tables, and for some queries (those that return many rows) the database itself creates temporary tables. If you still have the database file, could you send it to me please? I would like to analyze it. Regards, Thomas On Tuesday, March 18, 2014, Dieter Cailliau dieter@gmail.com wrote: 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. -- 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] Unique index or primary key violation: SYS_ID ON PUBLIC.SYS(ID) [23505-168] (using 1.3.168)
Thoms the database is attached to the first post of this topic. Op donderdag 20 maart 2014 07:53:26 UTC+1 schreef Thomas Mueller: Hi, I'm not using temporary tables I think there are some cases where Hibernate creates temporary tables, and for some queries (those that return many rows) the database itself creates temporary tables. If you still have the database file, could you send it to me please? I would like to analyze it. Regards, Thomas On Tuesday, March 18, 2014, Dieter Cailliau dieter@gmail.comjavascript: wrote: 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. -- 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] Unique index or primary key violation: SYS_ID ON PUBLIC.SYS(ID) [23505-168] (using 1.3.168)
Hi, I'm not using temporary tables I think there are some cases where Hibernate creates temporary tables, and for some queries (those that return many rows) the database itself creates temporary tables. If you still have the database file, could you send it to me please? I would like to analyze it. Regards, Thomas On Tuesday, March 18, 2014, Dieter Cailliau dieter.caill...@gmail.com wrote: 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.comjavascript:_e(%7B%7D,'cvml','h2-database%2bunsubscr...@googlegroups.com'); . To post to this group, send email to h2-database@googlegroups.comjavascript:_e(%7B%7D,'cvml','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.
[h2] Unique index or primary key violation in cluster failover test
I test HTTP session persistence to db using latest WildFly application server (Beta2 - SNAPSHOT, h2 1.3.173). I use this connection url jdbc:h2:file;AUTO_SERVER=true;DB_CLOSE_ON_EXIT=FALSE, because I want database to survive serve shutdown. Occassionally I am facing following exception: Unique index or primary key violation: PRIMARY_KEY_A ON PUBLIC.binarybased_default_host_session_db_cluster(ID); SQL statement: INSERT INTO binarybased_default_host_session_db_cluster (datum, version, id) VALUES(?,?,?) [23505-159] at org.h2.message.DbException.getJdbcSQLException(DbException.java:329) at org.h2.message.DbException.get(DbException.java:169) at org.h2.message.DbException.get(DbException.java:146) at org.h2.index.BaseIndex.getDuplicateKeyException(BaseIndex.java:81) at org.h2.index.PageBtree.find(PageBtree.java:121) at org.h2.index.PageBtreeLeaf.addRow(PageBtreeLeaf.java:146) at org.h2.index.PageBtreeLeaf.addRowTry(PageBtreeLeaf.java:100) at org.h2.index.PageBtreeIndex.addRow(PageBtreeIndex.java:105) at org.h2.index.PageBtreeIndex.add(PageBtreeIndex.java:96) at org.h2.table.RegularTable.addRow(RegularTable.java:130) at org.h2.command.dml.Insert.insertRows(Insert.java:124) at org.h2.command.dml.Insert.update(Insert.java:84) at org.h2.command.CommandContainer.update(CommandContainer.java:71) at org.h2.command.Command.executeUpdate(Command.java:212) at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:143) at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:129) You can also check full stack trace (slightly different) at https://issues.jboss.org/browse/WFLY-2409. I've taken brief look to h2 code and I would like to ask at following - looking at org.h2.index.PageBtreeLeaf int addRow(SearchRow row, boolean tryOnly) method line 145. What should happen when entryCount variable is not equal to 0? Then it's called find(row, false, true, true), where is following code construction: if (add index.indexType.isUnique()) { if (!index.containsNullAndAllowMultipleNull(compare)) { throw index.getDuplicateKeyException(compare.toString()); } } First condition is always true in this case and unfortunately in my case also the second condition evaluates as true. I am not really sure, what could be the problem. I'll appreciate any suggestions. Thank's in advance. -- 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/groups/opt_out.
Re: [h2] Unique index or primary key violation in cluster failover test
On 2013-10-31 13:07, Tomáš Remeš wrote: I test HTTP session persistence to db using latest WildFly application server (Beta2 - SNAPSHOT, h2 1.3.173). I use this connection url jdbc:h2:file;AUTO_SERVER=true;DB_CLOSE_ON_EXIT=FALSE, because I want database to survive serve shutdown. Occassionally I am facing following exception: Unique index or primary key violation: PRIMARY_KEY_A ON PUBLIC.binarybased_default_host_session_db_cluster(ID); SQL statement: It looks like you have declared ID as the primary key of the binarybased_default_host_session_db_cluster table, and you are trying to insert a row that has the same ID as an existing row. This is most likely a bug in the application logic somewhere. -- 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/groups/opt_out.
[h2] Unique index or primary key violation in cluster failover test
I test http session persistence to db with latest WildFly application server (Beta2-SNAPSHOT, h2 1.3.173). I have two nodes running in my test, where failover is tested and I use this connection url - jdbc:h2:file;AUTO_SERVER=true. I need to use file storing, because of server shutdown failover. Occassionally I am facing following exception: Unique index or primary key violation: PRIMARY_KEY_A ON PUBLIC.binarybased_default_host_session_db_cluster(ID); SQL statement: INSERT INTO binarybased_default_host_session_db_cluster (datum, version, id) VALUES(?,?,?) [23505-159] at org.h2.message.DbException.getJdbcSQLException(DbException.java:329) at org.h2.message.DbException.get(DbException.java:169) at org.h2.message.DbException.get(DbException.java:146) at org.h2.index.BaseIndex.getDuplicateKeyException(BaseIndex.java:81) at org.h2.index.PageBtree.find(PageBtree.java:121) at org.h2.index.PageBtreeLeaf.addRow(PageBtreeLeaf.java:146) at org.h2.index.PageBtreeLeaf.addRowTry(PageBtreeLeaf.java:100) at org.h2.index.PageBtreeIndex.addRow(PageBtreeIndex.java:105) at org.h2.index.PageBtreeIndex.add(PageBtreeIndex.java:96) at org.h2.table.RegularTable.addRow(RegularTable.java:130) at org.h2.command.dml.Insert.insertRows(Insert.java:124) at org.h2.command.dml.Insert.update(Insert.java:84) at org.h2.command.CommandContainer.update(CommandContainer.java:71) at org.h2.command.Command.executeUpdate(Command.java:212) at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:143) at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:129) You can see full stack trace (slightly different) at https://issues.jboss.org/browse/WFLY-2409. I am not really sure, what could be the problem. I'll appreciate any suggestions. Thank's in advance. Tom -- 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/groups/opt_out.
Re: [h2] Unique index or primary key violation in cluster failover test
Hi, Actually, you are not using H2 version 1.3.173. You are using an old version of H2 (1.3.159 according to the error code, [23505-159]). Could you try with a newer version? Regards, Thomas On Thu, Oct 31, 2013 at 12:07 PM, Tomáš Remeš tomas.re...@gmail.com wrote: I test HTTP session persistence to db using latest WildFly application server (Beta2 - SNAPSHOT, h2 1.3.173). I use this connection url jdbc:h2:file;AUTO_SERVER=true;DB_CLOSE_ON_EXIT=FALSE, because I want database to survive serve shutdown. Occassionally I am facing following exception: Unique index or primary key violation: PRIMARY_KEY_A ON PUBLIC.binarybased_default_host_session_db_cluster(ID); SQL statement: INSERT INTO binarybased_default_host_session_db_cluster (datum, version, id) VALUES(?,?,?) [23505-159] at org.h2.message.DbException.getJdbcSQLException(DbException.java:329) at org.h2.message.DbException.get(DbException.java:169) at org.h2.message.DbException.get(DbException.java:146) at org.h2.index.BaseIndex.getDuplicateKeyException(BaseIndex.java:81) at org.h2.index.PageBtree.find(PageBtree.java:121) at org.h2.index.PageBtreeLeaf.addRow(PageBtreeLeaf.java:146) at org.h2.index.PageBtreeLeaf.addRowTry(PageBtreeLeaf.java:100) at org.h2.index.PageBtreeIndex.addRow(PageBtreeIndex.java:105) at org.h2.index.PageBtreeIndex.add(PageBtreeIndex.java:96) at org.h2.table.RegularTable.addRow(RegularTable.java:130) at org.h2.command.dml.Insert.insertRows(Insert.java:124) at org.h2.command.dml.Insert.update(Insert.java:84) at org.h2.command.CommandContainer.update(CommandContainer.java:71) at org.h2.command.Command.executeUpdate(Command.java:212) at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:143) at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:129) You can also check full stack trace (slightly different) at https://issues.jboss.org/browse/WFLY-2409. I've taken brief look to h2 code and I would like to ask at following - looking at org.h2.index.PageBtreeLeaf int addRow(SearchRow row, boolean tryOnly) method line 145. What should happen when entryCount variable is not equal to 0? Then it's called find(row, false, true, true), where is following code construction: if (add index.indexType.isUnique()) { if (!index.containsNullAndAllowMultipleNull(compare)) { throw index.getDuplicateKeyException(compare.toString()); } } First condition is always true in this case and unfortunately in my case also the second condition evaluates as true. I am not really sure, what could be the problem. I'll appreciate any suggestions. Thank's in advance. -- 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/groups/opt_out. -- 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/groups/opt_out.