Hi Thomas- We tried recreating the index when this problems occurs, however, recreating the index did not help alone as the next attempt to delete rows from the table resulted in the same error. This indicates that the corruption was not in the physical index, instead it is a bug somewhere in the logic that updates the index. Our case is this-
A scheduled service runs every 15 mins to delete a max of thousand rows from the database table starting from the oldest record based on the timestamp column. Sometimes this delete succeeds just fine, (typically when the number of records are less in the table, but that is not always true as occasionally we have seen it happening with fewer records as well ). At other times the delete fails and deletes no rows and complains that it cannot find a particular key in the index. We have all this happening under JBoss, and restarting JBoss helps recover from this problem but it recurs sometime later while trying the delete the rows again. As a short term solution, we are dropping the index, deleting rows from the table, and then recreating the index. That way, we are kind of working around the problem. Obviously, the delete from the table after dropping the index is not as fast as it will be with the index and recreating the index also consumes some time. The other downside of this work around is that we have to acquire application level read locks while reading from the table and write locks while writing to it and while dropping the index. Without these locks, the drop and recreate fails saying it failed to lock the table before the timeout. Trying with a new database is not an option for us as replicating the database may be expensive operation. Regards- Sanjeev. Regards- Sanjeev. On Fri, May 31, 2013 at 12:51 AM, Thomas Mueller < [email protected]> wrote: > Hi, > > Did you re-create the problematic index, or (even better) re-create the > database? To re-create the database, first create a SQL script using the > "script to" command, then create a new database, and run "runscript". > > Regards, > Thomas > > > On Thursday, May 30, 2013, Sanjeev Gour wrote: > >> We have tried this with the latest build but that did not solve the >> problem for us. Any other advice on this one? >> >> On Tuesday, 14 May 2013 10:51:06 UTC+5:30, Sanjeev Gour wrote: >> >> Sorry, I mentioned the incorrect version number, I am using *1.3.168*. >> Earlier we also tried without MVCC but that didn't help. >> >> Just for your information that the index which is creating the problem is >> on a timestamp column if that can give you some clue about the problem. >> >> We are very close to go into production and upgrading H2 does not look a >> preferred option. Also I read through the change log from 1.3.168 to >> 1.3.171 and did not see any mention for this defect being fixed so I am not >> quite sure if upgrading would help. I will try providing more information >> on this as and when I hit it again. >> >> >> On Monday, 13 May 2013 18:37:16 UTC+5:30, Noel Grandin wrote: >> >> >> You're also running quite an old version of H2, updating to the latest >> version would probably help. >> >> I would also suggest running without MVCC, it's still a little rough >> around the edges. >> >> On 2013-05-13 14:47, Sanjeev Gour wrote: >> >> I am getting the following error when running a clean up routine on some >> of the tables. I am using version 1.3.162. The JDBC url is configured with >> the following options- >> >> CACHE_TYPE=LRU;PAGE_SIZE=**16384;MVCC=TRUE;DB_CLOSE_**DELAY=-1 >> >> Also tried with ";optimize_update=false" to no avail. This typically >> happens when my applications runs for long hours so I don't really have an >> easy test case to reproduce this problem. Is there anything else to try out >> on this one? >> >> *org.h2.jdbc.JdbcSQLException: Row not found when trying to delete from >> index "TIMESERIES.IDX_METRIC_DATA_START_TIME*: ( /* key:18158 */ 43848, >> TIMESTAMP '2013-03-27 18:30:00.0', TIMESTAMP '2013-03-27 23:10:00.0', X'* >> *aced000573720020636f6d2e63612e**63686f7275732e74696d6573657269** >> 65732e54534172726179018c63d061**05fca80200054a0007656e6454696d** >> 654900066c656e6774684a00097374**61727454696d654c000c636c6f636b** >> 4d656d656e746f74002a4c636f6d2f**63612f63686f7275732f74696d6573** >> 65726965732f6e756d657269632f4d**656d656e746f3b4c000b646174614d** >> 656d656e746f71007e000178700000**013dae1d7940000000390000013dad** >> 1d20407372003a636f6d2e63612e63**686f7275732e74696d657365726965** >> 732e6e756d657269632e4e756d6265**7244656c74614f7574707574244d65** >> 6d656e746f7390f51c1bf5d3fc0200**014c00076d656d656e746f71007e00** >> 01787073720038636f6d2e63612e63**686f7275732e74696d657365726965** >> 732e6e756d657269632e4e756d6265**72524c454f7574707574244d656d65** >> 6e746ff49d0987111300c102000549**000a63757272656e74496e744a000b** >> 63757272656e744c6f6e674900066c**656e67746849000473697a654c0007** >> 6d656d656e746f71007e0001787000**00000000000000000493e000000038** >> 000000437372003c636f6d2e63612e**63686f7275732e74696d6573657269** >> 65732e6e756d657269632e4e756d62**65725061636b696e674f7574707574**244d656d656e746f40a3b5b30 >> dd1f4770c000078707704000000037**37200106a6176612e7574696c2e426** >> 9745365746efd887e3934ab2102000**15b0004626974737400025b4a78707** >> 57200025b4a782004b512b17593020**00078700000000413c39ed68e90205** >> 300000000000124f80000000000000**0000000000000000000787371007e0** >> 0077704000000007371007e0009757**1007e000c000000080202020202020** >> 202020202020202020202020202020**202020202020202020202020202020** >> 202020202020202020202020202020**202020202000000000000000278')"**; SQL >> statement: >> >> DELETE FROM timeseries.metric_data WHERE end_time < ? LIMIT 1000 >> [90112-168] >> >> at org.h2.message.DbException.**getJdbcSQLException(** >> DbException.java:329) >> >> at org.h2.message.DbException.**get(DbException.java:169) >> >> at org.h2.message.DbException.**get >> >> -- > 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/awQR6dY5ZPY/unsubscribe?hl=en > . > To unsubscribe from this group and all its topics, 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?hl=en. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- 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?hl=en-US. For more options, visit https://groups.google.com/groups/opt_out.
