Regards,
Thomas
On Tuesday, September 16, 2014, cowwoc <[email protected]
<mailto:[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>
<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 a topic in the
Google Groups "H2 Database" group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/h2-database/tUdgguJPogg/unsubscribe.
To unsubscribe from this group and all its topics, 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.
