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.
