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.__ResourceMethodInvocationHandle__rFactory$1.invoke(__ResourceMethodInvocationHandle__rFactory.java:81)
at
org.glassfish.jersey.server.__model.internal.__AbstractJavaResourceMethodDisp__atcher$1.run(__AbstractJavaResourceMethodDisp__atcher.java:151)
at
org.glassfish.jersey.server.__model.internal.__AbstractJavaResourceMethodDisp__atcher.invoke(__AbstractJavaResourceMethodDisp__atcher.java:171)
at
org.glassfish.jersey.server.__model.internal.__JavaResourceMethodDispatcherPr__ovider$ResponseOutInvoker.__doDispatch(__JavaResourceMethodDispatcherPr__ovider.java:152)
at
org.glassfish.jersey.server.__model.internal.__AbstractJavaResourceMethodDisp__atcher.dispatch(__AbstractJavaResourceMethodDisp__atcher.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
<http://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
<http://groups.google.com/group/h2-database>.
For more options, visit https://groups.google.com/d/optout
<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]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
