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 h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
