Problem with dropping and recreating index

[Sanjeev Gour]

We are having an index on a table that results in error while deleting data from the table. The error says that it cannot find a row in the index. The following discussion we posted earlier has detailed explanation about it-

Discussion link

We are trying to work around this problem by dropping the index and recreate it when the error occurs something like this-
try{

ACQUIRE DISK LOCK
{

try{
    DELETE FROM TABLE WHERE ..... //this throws the index error that it cannot find the row in the index
}catch(Exception e){

    try{
       DROP INDEX      
       try{
           RECREATE THE INDEX
       }catch{
           LOG RECREATE FAILURE
       }
    }catch{
       LOG DROP FAILURE
}

finally{
    RELEASE DISC LOCK
}

The problem we are facing is that the DROP is showing any error but while creating the index we are seeing the following error-

org.h2.jdbc.JdbcSQLException: Timeout trying to lock table "METRIC_DATA"; SQL statement:
create index timeseries.idx_metric_data_start_time on timeseries.metric_data(start_time) [50200-168]

We are not sure how H2 is handling locking on the tables internally. We are using the following parameters in the JDBC url-

CACHE_TYPE=LRU;PAGE_SIZE=16384;MVCC=TRUE;DB_CLOSE_DELAY=-1

Any help to resolve this is much appreciated.

[Thomas Mueller]

Hi,

Of course the index should not get corrupt in the first place. If you have a simple, reproducible test case that reproduces the corrupt index (starting with a fresh database) with the latest version of H2, could you please post it?

Regards,

Thomas

--
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...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Sanjeev Gour]

Unfortunately we don't have a test case to reproduce this. I can however try to write a small program that would run for couple of days  and reproduce this, but I don't have any idea on how I can develop a unit test that fails consistently.

On a separate note, we tried this with the latest version of H2 and a fresh database and observed the index corruption.

Regards-
Sanjeev.

--
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/tlauEm_wDHY/unsubscribe?hl=en-US.
To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.

Visit this group at http://groups.google.com/group/h2-database?hl=en-US.

[Noel Grandin]

On 2013-06-13 09:31, Sanjeev Gour wrote:
> Unfortunately we don't have a test case to reproduce this. I can
> however try to write a small program that would run for couple of
> days and reproduce this, but I don't have any idea on how I can
> develop a unit test that fails consistently.
>
> On a separate note, we tried this with the latest version of H2 and a
> fresh database and observed the index corruption.
>

Your best bet is to stop using the MVCC option, is still has rough edges
and we plan to remove it eventually and replace it with MVStore.