Re: [h2] Primary key violation for AUTO_INCREMENT column

Noel Grandin Tue, 16 Sep 2014 01:03:52 -0700

Hi


You could try doing this, which is roughly equivalent to SERIALIZABLE mode:

  exec("begin transaction")
  var id = exec("select id from company where name = 'Nintendo'");
  if (id != null) {
      exec("commit");
      throw new DuplicateCompanyException(id);
  }
  exec("insert into company(name) values('Nintendo')");
  exec("commit");

Hmmm, it looks like in some places we add the key information when we throw a 
DUPLICATE_KEY_1 error.

I could certainly update the other places to also add that information, which you could extract by doing some stringmunging.


Not ideal, but a relatively simple change.

Regards, Noel

On 2014-09-16 09:41 AM, cowwoc wrote:

Hi Noel,

I tried reverting early on but (surprisingly) I ran into the problem in 1.3.176 
as well.

Regarding the need for different isolation levels, my (web) application is 
partially at fault.

The default isolation mode might be very fast, but the application methods that 
INSERT require SERIALIZABLE isolation
(more on this below) and because I cannot change the isolation level at runtime 
I am forced to use SERIALIZABLE across
the board which ... as you can imagine ... is not great :)

Regarding needing SERIALIZABLE for INSERT, I didn't want to deal with the 
following scenario:

 1. T1: Try to INSERT company Nintendo.
 2. T1: INSERT failed due to a UNIQUE constraint violation (another company has 
the same name).
 3. T2: DELETE company Nintendo.
 4. T1: Look up the conflicting company by name ... oops, the record no longer 
exists.

So now T1 wants to report a conflict but doesn't have the necessary information 
to do so (the communication protocol
requires me to provide the primary key of the conflicting row). Even if T1 
could somehow repeat the operation (which it
cannot due to http://stackoverflow.com/q/16628713/14731) there is no guarantee 
that T2 won't insert a new company with
the same name (causing a second conflict to occur).

Anyway, this is why I am using READ_COMMITTED for all methods except for the 
ones that INSERT (which use SERIALIZABLE).
Anyway... I hope this clarifies why I consider the lack of this feature as a 
showstopper.

Gili

On 16/09/2014 3:20 AM, Noel Grandin wrote:

HI

Sorry to hear that.

If you're in crunch mode, you should probably not be using our beta release - 
either downgrade to 1.3.176 or run
1.4.181 with MV_STORE=false to use the old storage engine.

I would suggest simply not using isolation levels for H2 - we should be plenty 
fast enough in our default isolation mode.

Clustering is a very hard problem to do right - We still have so much we could 
do with the single machine case, that
unless someone else shows up who is keen to hack on that, we are not likely to 
do much there for quite a while.
At the moment Thomas does 98% of the work, I do about 1% and various random 
people showing up with patches account for
the other 1%.

A trace log is probably not going to help us very much, but reverting to the 
old storage engine will probably fix this
for you anyhow.

Regards, Noel.

On 2014-09-16 09:12 AM, Gili wrote:

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.


--
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.


--
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.

Re: [h2] Primary key violation for AUTO_INCREMENT column

Reply via email to