search

lbehnke / h2database

Automatically exported from code.google.com/p/h2database
0 stars 0 forks source link

Loop on h2 Startup, if h2 try to make new index over a table which have duplicate Entrys on primary unique Key #74

Closed GoogleCodeExporter closed 9 years ago

GoogleCodeExporter commented 9 years ago 
Hello

We are using h2 Version V1.1.109
IBM Java: V1.5.09
OS: Suse SLES V10.2

On the database there are duplicate Entry with the same unique primary key.
I have don't know how this was possible. The database still works fine, but
i can't delete the row.

One off my ideas to recover the problem, was to rebuild the index.

How reproduce the problem?
1. stop the database
2. delete index file
3. start database

After the start the database gets in to a loop.

The database is big 3GB. I have made a zip File.
You can download the database from the URL:
http://www.swissbyte.com/sysdb.zip (370BM)

Java Stack trace:

03-31 13:40:23 database: opening /disks/diskpool/opt/jane1/sysdb/systemdb
org.h2.jdbc.JdbcSQLException: Unique index or primary key violation:
PRIMARY_KEY_5  ON SYSTEMDB.SHEDULED_JOBS(SHEDULER_ID); SQL statement:
CREATE PRIMARY KEY ON SYSTEMDB.SHEDULED_JOBS(SHEDULER_ID) [23001-109]
    at org.h2.message.Message.getSQLException(Message.java:107)
    at org.h2.message.Message.getSQLException(Message.java:118)
    at org.h2.message.Message.getSQLException(Message.java:77)
    at org.h2.index.BaseIndex.getDuplicateKeyException(BaseIndex.java:160)
    at org.h2.index.BtreeLeaf.add(BtreeLeaf.java:67)
    at org.h2.index.BtreeNode.add(BtreeNode.java:105)
    at org.h2.index.BtreeNode.add(BtreeNode.java:105)
    at org.h2.index.BtreeIndex.add(BtreeIndex.java:223)
    at org.h2.table.TableData.addRowsToIndex(TableData.java:283)
    at org.h2.table.TableData.addIndex(TableData.java:211)
    at org.h2.command.ddl.CreateIndex.update(CreateIndex.java:90)
    at org.h2.engine.MetaRecord.execute(MetaRecord.java:85)
    at org.h2.engine.Database.open(Database.java:611)
    at org.h2.engine.Database.openDatabase(Database.java:223)
    at org.h2.engine.Database.<init>(Database.java:218)
    at org.h2.engine.Engine.openSession(Engine.java:57)
    at org.h2.engine.Engine.openSession(Engine.java:138)
    at org.h2.engine.Engine.getSession(Engine.java:118)
    at org.h2.server.TcpServerThread.run(TcpServerThread.java:120)
    at java.lang.Thread.run(Thread.java:810)
03-31 13:46:00 database: CREATE PRIMARY KEY ON
SYSTEMDB.SHEDULED_JOBS(SHEDULER_ID)
org.h2.jdbc.JdbcSQLException: Unique index or primary key violation:
PRIMARY_KEY_5  ON SYSTEMDB.SHEDULED_JOBS(SHEDULER_ID); SQL statement:
CREATE PRIMARY KEY ON SYSTEMDB.SHEDULED_JOBS(SHEDULER_ID) [23001-109]
    at org.h2.message.Message.getSQLException(Message.java:107)
    at org.h2.message.Message.getSQLException(Message.java:118)
    at org.h2.message.Message.getSQLException(Message.java:77)
    at org.h2.index.BaseIndex.getDuplicateKeyException(BaseIndex.java:160)
    at org.h2.index.BtreeLeaf.add(BtreeLeaf.java:67)
    at org.h2.index.BtreeNode.add(BtreeNode.java:105)
    at org.h2.index.BtreeNode.add(BtreeNode.java:105)
    at org.h2.index.BtreeIndex.add(BtreeIndex.java:223)
    at org.h2.table.TableData.addRowsToIndex(TableData.java:283)
    at org.h2.table.TableData.addIndex(TableData.java:211)
    at org.h2.command.ddl.CreateIndex.update(CreateIndex.java:90)
    at org.h2.engine.MetaRecord.execute(MetaRecord.java:85)
    at org.h2.engine.Database.open(Database.java:611)
    at org.h2.engine.Database.openDatabase(Database.java:223)
    at org.h2.engine.Database.<init>(Database.java:218)
    at org.h2.engine.Engine.openSession(Engine.java:57)
    at org.h2.engine.Engine.openSession(Engine.java:138)
    at org.h2.engine.Engine.getSession(Engine.java:118)
    at org.h2.server.TcpServerThread.run(TcpServerThread.java:120)
    at java.lang.Thread.run(Thread.java:810)
03-31 13:46:00 database: opening /disks/diskpool/opt/jane1/sysdb/systemdb
org.h2.jdbc.JdbcSQLException: Unique index or primary key violation:
PRIMARY_KEY_5  ON SYSTEMDB.SHEDULED_JOBS(SHEDULER_ID); SQL statement:
CREATE PRIMARY KEY ON SYSTEMDB.SHEDULED_JOBS(SHEDULER_ID) [23001-109]
    at org.h2.message.Message.getSQLException(Message.java:107)
    at org.h2.message.Message.getSQLException(Message.java:118)
    at org.h2.message.Message.getSQLException(Message.java:77)
    at org.h2.index.BaseIndex.getDuplicateKeyException(BaseIndex.java:160)
    at org.h2.index.BtreeLeaf.add(BtreeLeaf.java:67)
    at org.h2.index.BtreeNode.add(BtreeNode.java:105)
    at org.h2.index.BtreeNode.add(BtreeNode.java:105)
    at org.h2.index.BtreeIndex.add(BtreeIndex.java:223)
    at org.h2.table.TableData.addRowsToIndex(TableData.java:283)
    at org.h2.table.TableData.addIndex(TableData.java:211)
    at org.h2.command.ddl.CreateIndex.update(CreateIndex.java:90)
    at org.h2.engine.MetaRecord.execute(MetaRecord.java:85)
    at org.h2.engine.Database.open(Database.java:611)
    at org.h2.engine.Database.openDatabase(Database.java:223)
    at org.h2.engine.Database.<init>(Database.java:218)
    at org.h2.engine.Engine.openSession(Engine.java:57)
    at org.h2.engine.Engine.openSession(Engine.java:138)
    at org.h2.engine.Engine.getSession(Engine.java:118)
    at org.h2.server.TcpServerThread.run(TcpServerThread.java:120)
    at java.lang.Thread.run(Thread.java:810)

workaround:
Restore the old index File

From my point off view it is a defect.

But what is the right way to recover this situation

Thanks for your Help.

best regards
Thomas Feldmann

Original issue reported on code.google.com by thomas.f...@swissbyte.com on 31 Mar 2009 at 1:23

GoogleCodeExporter commented 9 years ago 
Hi,

You need to use the recovery tool to recover the database. The tools is 
available
from within the H2 Console (Tools / Recover) or as a command line tool
(org.h2.tools.Recover). This will create a .sql script that you can edit. You 
can
then remove the primary key index from this file (comment out the line CREATE 
PRIMARY
KEY ON SYSTEMDB.SHEDULED_JOBS) and run the script using the RunScript tool or
RUNSCRIPT command (creating a new database). You need to use another user name 
to
create the database than the one used in the SQL script, otherwise you get an
exception 'user already exists'. Then you can connect to the database and 
delete the
duplicate row, afterwards create the primary key.

The .trace.db file contains the following entries:
No space left on device; /disks/diskpool/opt/jane1/sysdb/systemdb.418473.log.db
This could be the reason for the corruption:
maybe H2 doesn't do the right thing after this problem. I need to test it.

The database was created with version 1.0.72 (SET CREATE_BUILD 72). Quite a few 
bugs
were fixed since then, maybe the database was broken before but it didn't show 
up
because the index was never recreated.

The log file number (418473) is very high. I would like to understand why.
Do you open and close the database a lot, or do you call CHECKPOINT a lot?

Regards,
Thomas

Original comment by thomas.t...@gmail.com on 3 Apr 2009 at 2:01

GoogleCodeExporter commented 9 years ago 
Hi

Thanks very much for your detailed answer.

I will try your recover guideline.

answer to your question:

We don't use checkpoint.
We don't open close the database very often.
The database is quite old and it runs for a long time.
We are using every day the h2 BACKUP command to make online backups.
Perhaps there is a Bug in your application.

best regards
Thomas

Original comment by thomas.f...@swissbyte.com on 6 Apr 2009 at 11:59

GoogleCodeExporter commented 9 years ago 
Hi Thomas

The high log file nummer is perhpaps a result from the startup loop

best regards
Thomas

Original comment by thomas.f...@swissbyte.com on 7 Apr 2009 at 8:09

GoogleCodeExporter commented 9 years ago 
Hi,

> The high log file nummer is perhpaps a result from the startup loop

Yes, that's possible...

> We are using every day the h2 BACKUP command to make online backups.

That's quite good, however there is a better way: use the command SCRIPT 
instead. See
http://www.h2database.com/html/grammar.html#script

Example: 

SCRIPT TO 'backup.zip' COMPRESSION ZIP

Original comment by thomas.t...@gmail.com on 13 Apr 2009 at 3:47

GoogleCodeExporter commented 9 years ago 
I'm setting the bug to 'Fixed' for now. Please re-open or create a new bug if 
you see
the problem again using a fresh database.

Regards,
Thomas

Original comment by thomas.t...@gmail.com on 27 Apr 2009 at 6:32