Hi, As for high availability:
* Did you look at the H2HA project? Would that be enough? If not why not. * Do you know MongoDB replication? Would that be better? Regards, Thomas On Tuesday, September 16, 2014, cowwoc <[email protected]> wrote: > Hi Noel, > > This is already I what I do (in SERIALIZABLE mode). If you were to run the > same in READ_COMMITTED mode you could get a conflict on that last INSERT. > The problem with DUPLICATE_KEY_1 is the following: > > Companies might have a UNIQUE constraint on column "name" but the web > service needs to return the "id" of the conflicting row (resources are > looked up by ID even though they conflict on name). In other words, in case > of a collision, I need to get back the entire conflicting record ... > something that might not make sense in general for all developers. > > I'm a bit surprised I am the only person running into this problem though. > I'd expect this to be more common. What are other people doing? > > Gili > > On 16/09/2014 4:03 AM, Noel Grandin wrote: > >> >> 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 string munging. >> >> 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. > -- 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.
