Hi, I think the problem was "Issue 566: MVStore: unique indexes that were created later on did not work correctly if there were over 5000 rows in the table.". This is fixed with version 1.4.179. But you will need to re-create the database I'm afraid (via export to SQL script for example).
Regards, Thomas On Thursday, June 19, 2014, Mahen Perera <[email protected]> wrote: > 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] > <javascript:_e(%7B%7D,'cvml','h2-database%[email protected]');> > . > To post to this group, send email to [email protected] > <javascript:_e(%7B%7D,'cvml','[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.
