I removed the FK contraint and hence the index CONSTRAINT_2_INDEX_2 - and that seems to have fixed the issue. It would be good to know why that error occured in the first place tho.
On Wednesday, 18 June 2014 10:42:12 UTC+1, Mahen Perera wrote: > Hi, > I am using h2 version 1.4.178, > and get below error from the application which uses H2: > Row not found when trying to delete from index > "PUBLIC.CONSTRAINT_2_INDEX_2: 12273"; SQL statement: > *delete from XXX WHERE A = ? and B in ( NULL , 'a' ) and C=3* > [90112-178] > > > when trying to run the same query in h2 web console I get below: > > > Row not found when trying to delete from index > "PUBLIC.CONSTRAINT_2_INDEX_2: 12226"; SQL statement: > *delete from XXX WHERE A = ? and B in ( NULL , 'a' ) and C=3 > [90112-178*]org.h2.jdbc.JdbcSQLException: > Row not found when trying to delete from index > "PUBLIC.CONSTRAINT_2_INDEX_2: 12226"; SQL statement: > *delete from XXX WHERE A = ? and B in ( NULL , 'a' ) and C=3 [90112-178]* > > at org.h2.message.DbException.getJdbcSQLException(DbException.java:344) > at org.h2.message.DbException.get(DbException.java:178) > at org.h2.message.DbException.get(DbException.java:154) > at org.h2.mvstore.db.MVSecondaryIndex.remove(MVSecondaryIndex.java:248) > at org.h2.mvstore.db.MVTable.removeRow(MVTable.java:584) > at org.h2.command.dml.Delete.update(Delete.java:95) > at org.h2.command.CommandContainer.update(CommandContainer.java:79) > at org.h2.command.Command.executeUpdate(Command.java:254) > at org.h2.server.TcpServerThread.process(TcpServerThread.java:346) > at org.h2.server.TcpServerThread.run(TcpServerThread.java:160) > at java.lang.Thread.run(Thread.java:722) > > at org.h2.engine.SessionRemote.done(SessionRemote.java:610) > at org.h2.command.CommandRemote.executeUpdate(CommandRemote.java:192) > at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:186) > at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:160) > at org.h2.server.web.WebApp.getResult(WebApp.java:1391) > at org.h2.server.web.WebApp.query(WebApp.java:1064) > at org.h2.server.web.WebApp$1.next(WebApp.java:1026) > at org.h2.server.web.WebApp$1.next(WebApp.java:1013) > at org.h2.server.web.WebThread.process(WebThread.java:168) > at org.h2.server.web.WebThread.run(WebThread.java:94) > at java.lang.Thread.run(Thread.java:722) > > here C is a foreign key of the table XXX. and there is an index which is > created internally by H2 to facilitate the FK. > > foreign key contraint added as part of the sql load script: > > ALTER TABLE XXX ADD CONSTRAINT PUBLIC.CONSTRAINT_2 FOREIGN KEY(C) > REFERENCES YYY(ID) NOCHECK; > > and when H2 creates the index... > the contraint is updated to below > > ALTER TABLE XXX ADD CONSTRAINT PUBLIC.CONSTRAINT_2 FOREIGN KEY(C) INDEX > PUBLIC.CONSTRAINT_2_INDEX_2 REFERENCES YYY(ID) NOCHECK > > > and below index is created. > > CREATE INDEX PUBLIC.CONSTRAINT_2_INDEX_2 ON XXX(*C*) > > > on top of the above index we have another index (composite) on the table > XXX which includes column C. This index is added as part of the sql load > script for the application. > > CREATE INDEX PUBLIC.CACHEDATA_LOOKUP ON XXX(A, B, TIMESTAMP, *C*) > > > The error we get doesnt happen all the time , and only for some > queries/deletes. > can someone help? appreciate any responses. > -- 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.
