I've been H2 for many years but sadly three different factors have compelled me to try to migrate to Postgresql:
1. The inability to use different isolation levels for different connections (a short-term showstopper). 2. A poor clustering story (a long-term showstopper) 3. This AUTO_INCREMENT bug (probably easy to fix but it's the straw that broke the camel's back). I'm going to go break my teeth migrating to PL/PGSQL (which I dislike with a passion). When that's over with, I'll try to go back and produce a testcase for this issue. Sorry I can't handle this the other way around. I've got people breathing down my neck asking for a stable release :( Out of curiosity, do you think a trace log is enough to get started on this issue (I'm not sure I will be able to produce a minimal testcase)? I've already got one handy. I'd just need to clean it up a bit and walk you through what lines to look at. Gili On Tuesday, September 16, 2014 2:12:02 AM UTC-4, Thomas Mueller wrote: > > Hi, > > I'm not aware of any bugs in this area. It would be great if you could > provide a reproducible test case! > > Regards, > Thomas > > > On Sunday, September 14, 2014, Gili <[email protected] <javascript:>> > wrote: > >> I caught the exception and dumped the database contents after such a >> conflict and, sure enough, H2 is trying to insert an existing primary key >> value. >> >> I didn't have these kind of problems before and I'm wondering what has >> been triggering all of this. I'm beginning to think this has something to >> do with the use of negative numbers with AUTO_INCREMENT or the use of >> primary keys that are smaller than BIGINT. >> >> Gili >> >> On Saturday, September 13, 2014 4:53:16 PM UTC-4, Gili wrote: >>> >>> Hi, >>> >>> I'm running into constraint violations that I believe should be >>> impossible using H2 1.4.181. My table definition is: >>> >>> CREATE TABLE user (id SMALLINT AUTO_INCREMENT(-32768, 1) PRIMARY KEY, >>> owner_id SMALLINT, >>> email VARCHAR(254) NOT NULL UNIQUE, password VARCHAR(82) NOT NULL, name >>> VARCHAR(100) NOT NULL, >>> owned_permission_id SMALLINT NOT NULL, view_id SMALLINT NOT NULL, >>> edit_id SMALLINT NOT NULL, >>> delete_id SMALLINT NOT NULL, version INTEGER NOT NULL, last_modified >>> TIMESTAMP NOT NULL, >>> FOREIGN KEY (owned_permission_id) REFERENCES permission(id) ON DELETE >>> CASCADE, >>> FOREIGN KEY (owner_id) REFERENCES user(id), >>> FOREIGN KEY (view_id) REFERENCES permission(id), >>> FOREIGN KEY (edit_id) REFERENCES permission(id), >>> FOREIGN KEY (delete_id) REFERENCES permission(id)); >>> >>> I am getting this exception: >>> >>> com.vtlr.backend.ConstraintViolationException: Unique index or primary >>> key violation: "PRIMARY KEY ON PUBLIC.USER(ID)"; SQL statement: >>> insert into USER (EMAIL, PASSWORD, NAME, OWNED_PERMISSION_ID, OWNER_ID, >>> VIEW_ID, EDIT_ID, DELETE_ID, VERSION, LAST_MODIFIED) >>> values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [23505-181] >>> at com.vtlr.backend.SqlExceptions.getConstraintViolation( >>> SqlExceptions.java:91) >>> at com.vtlr.backend.row.User.insert(User.java:102) >>> at com.vtlr.backend.resource.AnonymousUserResource.createUser( >>> AnonymousUserResource.java:85) >>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) >>> at sun.reflect.NativeMethodAccessorImpl.invoke( >>> NativeMethodAccessorImpl.java:62) >>> at sun.reflect.DelegatingMethodAccessorImpl.invoke( >>> DelegatingMethodAccessorImpl.java:43) >>> at java.lang.reflect.Method.invoke(Method.java:483) >>> at org.glassfish.jersey.server.model.internal. >>> ResourceMethodInvocationHandlerFactory$1.invoke( >>> ResourceMethodInvocationHandlerFactory.java:81) >>> at org.glassfish.jersey.server.model.internal. >>> AbstractJavaResourceMethodDispatcher$1.run( >>> AbstractJavaResourceMethodDispatcher.java:151) >>> at org.glassfish.jersey.server.model.internal. >>> AbstractJavaResourceMethodDispatcher.invoke( >>> AbstractJavaResourceMethodDispatcher.java:171) >>> at org.glassfish.jersey.server.model.internal. >>> JavaResourceMethodDispatcherProvider$ResponseOutInvoker.doDispatch( >>> JavaResourceMethodDispatcherProvider.java:152) >>> at org.glassfish.jersey.server.model.internal. >>> AbstractJavaResourceMethodDispatcher.dispatch( >>> AbstractJavaResourceMethodDispatcher.java:104) >>> at org.glassfish.jersey.server.model.ResourceMethodInvoker. >>> invoke(ResourceMethodInvoker.java:387) >>> at org.glassfish.jersey.server.model.ResourceMethodInvoker. >>> apply(ResourceMethodInvoker.java:331) >>> at org.glassfish.jersey.server.model.ResourceMethodInvoker. >>> apply(ResourceMethodInvoker.java:103) >>> at org.glassfish.jersey.server.ServerRuntime$1.run( >>> ServerRuntime.java:271) >>> at org.glassfish.jersey.internal.Errors$1.call(Errors.java:271) >>> at org.glassfish.jersey.internal.Errors$1.call(Errors.java:267) >>> at org.glassfish.jersey.internal.Errors.process(Errors.java:315) >>> at org.glassfish.jersey.internal.Errors.process(Errors.java:297) >>> at org.glassfish.jersey.internal.Errors.process(Errors.java:267) >>> at org.glassfish.jersey.process.internal.RequestScope. >>> runInScope(RequestScope.java:297) >>> at org.glassfish.jersey.server.ServerRuntime.process( >>> ServerRuntime.java:254) >>> at org.glassfish.jersey.server.ApplicationHandler.handle( >>> ApplicationHandler.java:1028) >>> at org.glassfish.jersey.servlet.WebComponent.service( >>> WebComponent.java:372) >>> at org.glassfish.jersey.servlet.ServletContainer.service( >>> ServletContainer.java:381) >>> at org.glassfish.jersey.servlet.ServletContainer.doFilter( >>> ServletContainer.java:534) >>> at org.glassfish.jersey.servlet.ServletContainer.doFilter( >>> ServletContainer.java:482) >>> at org.glassfish.jersey.servlet.ServletContainer.doFilter( >>> ServletContainer.java:419) >>> at org.eclipse.jetty.servlet.ServletHandler$CachedChain. >>> doFilter(ServletHandler.java:1650) >>> at org.eclipse.jetty.servlet.ServletHandler.doHandle( >>> ServletHandler.java:583) >>> at org.eclipse.jetty.server.handler.ScopedHandler.handle( >>> ScopedHandler.java:143) >>> at org.eclipse.jetty.security.SecurityHandler.handle( >>> SecurityHandler.java:577) >>> at org.eclipse.jetty.server.session.SessionHandler. >>> doHandle(SessionHandler.java:223) >>> at org.eclipse.jetty.server.handler.ContextHandler. >>> doHandle(ContextHandler.java:1125) >>> at org.eclipse.jetty.servlet.ServletHandler.doScope( >>> ServletHandler.java:515) >>> at org.eclipse.jetty.server.session.SessionHandler. >>> doScope(SessionHandler.java:185) >>> at org.eclipse.jetty.server.handler.ContextHandler. >>> doScope(ContextHandler.java:1059) >>> at org.eclipse.jetty.server.handler.ScopedHandler.handle( >>> ScopedHandler.java:141) >>> at org.eclipse.jetty.server.handler.HandlerWrapper.handle( >>> HandlerWrapper.java:97) >>> at org.eclipse.jetty.server.Server.handle(Server.java:485) >>> at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:290) >>> at org.eclipse.jetty.server.HttpConnection.onFillable( >>> HttpConnection.java:248) >>> at org.eclipse.jetty.io.AbstractConnection$2.run( >>> AbstractConnection.java:540) >>> at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob( >>> QueuedThreadPool.java:606) >>> at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run( >>> QueuedThreadPool.java:535) >>> at java.lang.Thread.run(Thread.java:745) >>> Caused by: org.h2.jdbc.JdbcSQLException: Unique index or primary key >>> violation: "PRIMARY KEY ON PUBLIC.USER(ID)"; SQL statement: >>> insert into USER (EMAIL, PASSWORD, NAME, OWNED_PERMISSION_ID, OWNER_ID, >>> VIEW_ID, EDIT_ID, DELETE_ID, VERSION, LAST_MODIFIED) >>> values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [23505-181] >>> 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.index.PageDataIndex.getNewDuplicateKeyException( >>> PageDataIndex.java:165) >>> at org.h2.index.PageDataIndex.add(PageDataIndex.java:143) >>> at org.h2.table.RegularTable.addRow(RegularTable.java:119) >>> 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:78) >>> at org.h2.command.Command.executeUpdate(Command.java:254) >>> at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal( >>> JdbcPreparedStatement.java:157) >>> at org.h2.jdbc.JdbcPreparedStatement.executeUpdate( >>> JdbcPreparedStatement.java:143) >>> at com.jolbox.bonecp.PreparedStatementHandle.executeUpdate( >>> PreparedStatementHandle.java:205) >>> at com.mysema.query.sql.dml.SQLInsertClause.executeWithKeys( >>> SQLInsertClause.java:302) >>> at com.mysema.query.sql.dml.SQLInsertClause.executeWithKey( >>> SQLInsertClause.java:186) >>> at com.mysema.query.sql.dml.SQLInsertClause.executeWithKey( >>> SQLInsertClause.java:169) >>> at com.vtlr.backend.row.User.insert(User.java:94) >>> ... 45 common frames omitted >>> >>> If I understand correctly, H2 is complaining that I am inserting a >>> duplicate ID. But as you can see, the ID is AUTO_INCREMENT and I am not >>> specifying an explicit value (not ever in any place in my code). Any ideas? >>> >>> This error is intermittent so I've been unable to create a testcase for >>> it. >>> >>> Thanks, >>> Gili >>> >> -- >> 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 [email protected]. >> To post to this group, send email to [email protected]. >> 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
