Corrupted database

[Gabor 'Morc' KORMOS]

  Hi,

  I saw a post just the other day detailing how to fix a CRC error
although this does not cover my problem because the corruption does not
surface during boot rather on query. I use Sonar which uses Derby
embedded, but unfortunately one of the versions which is known to
corrupt data, 10.3.1.4.
  OK, so the problem is that on query the Debry instance just shuts down
detecting the CRC error. I used a hex editor to fix the CRC (it's in the
first page of the file) but then I get another error (see below). Could
someone help me fix this even by sending her/him the whole database (not
huge, 240MB uncompressed)?

  Thanks,

  Gabor 'Morc' Kormos.

2009-06-17 09:52:59.011 GMT Thread[DRDAConnThread_9,5,derby.daemons]
(XID = 151484434), (SESSIONID = 2), (DATABASE = sonar), (DRDAID =
NF000001.GCDA-4269129726538145963{4}), Failed Statement is: select *
from project_measures
java.lang.IllegalArgumentException: Bit position 1 is outside the legal
range
    at
org.apache.derby.iapi.services.io.FormatableBitSet.checkPosition(Unknown
Source)
    at org.apache.derby.iapi.services.io.FormatableBitSet.isSet(Unknown
Source)
    at
org.apache.derby.impl.store.raw.data.AllocExtent.getPageStatus(Unknown
Source)
    at
org.apache.derby.impl.store.raw.data.AllocationCache.getPageStatus(Unknown
Source)
    at
org.apache.derby.impl.store.raw.data.FileContainer.pageValid(Unknown Source)
    at
org.apache.derby.impl.store.raw.data.FileContainer.getUserPage(Unknown
Source)
    at
org.apache.derby.impl.store.raw.data.FileContainer.getPage(Unknown Source)
    at
org.apache.derby.impl.store.raw.data.BaseContainerHandle.getPage(Unknown
Source)
    at
org.apache.derby.impl.store.access.heap.HeapConglomerateFactory.readConglomerate(Unknown
Source)
    at
org.apache.derby.impl.store.access.RAMAccessManager.conglomCacheFind(Unknown
Source)
    at
org.apache.derby.impl.store.access.RAMTransaction.findExistingConglomerate(Unknown
Source)
    at
org.apache.derby.impl.store.access.RAMTransaction.openStoreCost(Unknown
Source)
    at
org.apache.derby.impl.sql.compile.CompilerContextImpl.getStoreCostController(Unknown
Source)
    at
org.apache.derby.impl.sql.compile.FromBaseTable.getStoreCostController(Unknown
Source)
    at
org.apache.derby.impl.sql.compile.FromBaseTable.estimateCost(Unknown Source)
    at
org.apache.derby.impl.sql.compile.OptimizerImpl.estimateTotalCost(Unknown
Source)
    at
org.apache.derby.impl.sql.compile.OptimizerImpl.costBasedCostOptimizable(Unknown
Source)
    at
org.apache.derby.impl.sql.compile.OptimizerImpl.costOptimizable(Unknown
Source)
    at
org.apache.derby.impl.sql.compile.FromBaseTable.optimizeIt(Unknown Source)
    at
org.apache.derby.impl.sql.compile.ProjectRestrictNode.optimizeIt(Unknown
Source)
    at
org.apache.derby.impl.sql.compile.OptimizerImpl.costPermutation(Unknown
Source)
    at org.apache.derby.impl.sql.compile.SelectNode.optimize(Unknown Source)
    at
org.apache.derby.impl.sql.compile.DMLStatementNode.optimizeStatement(Unknown
Source)
    at
org.apache.derby.impl.sql.compile.CursorNode.optimizeStatement(Unknown
Source)
    at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
    at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
    at
org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown
Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown
Source)
    at
org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
    at
org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
    at
org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown Source)
    at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown
Source)
    at
org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
    at
org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
    at
org.apache.derby.impl.drda.DRDAStatement.prepareStatementJDBC3(Unknown
Source)
    at org.apache.derby.impl.drda.DRDAStatement.prepare(Unknown Source)
    at org.apache.derby.impl.drda.DRDAStatement.explicitPrepare(Unknown
Source)
    at org.apache.derby.impl.drda.DRDAConnThread.parsePRPSQLSTT(Unknown
Source)
    at org.apache.derby.impl.drda.DRDAConnThread.processCommands(Unknown
Source)
    at org.apache.derby.impl.drda.DRDAConnThread.run(Unknown Source)

[Derby-3]

Hi,

I don't know if just going in to the database with a hex editor is going to
really fix your problem...

The issue of corrupted indexes and corrupted databases will continually
plague javaDB/Derby because of its market niche and design. Cloudscape was
designed as a lightweight database and now people are using it beyond its
initial design. (If you think about it, you can only fit so much in to a
small footprint...)

I really think that those left of Sun, soon to be Oracle, that are
supporting JavaDB, should consider the development of such tool(s). The
reason I suggest this core team is that they are the ones who would benefit
the most from the tool(s). (They are paid to support JavaDB and it would
make their lives easier, plus they tend to know more about the inner
workings of javaDB.)

But hey! Its just a thought. ;-)

-Mikey

> -----Original Message-----
> From: Gabor 'Morc' KORMOS [mailto:morc@...]
> Sent: Wednesday, June 17, 2009 4:55 AM
> To: derby-user@...
> Subject: Corrupted database
>
>   Hi,
>
>   I saw a post just the other day detailing how to fix a CRC error
> although this does not cover my problem because the corruption does not
> surface during boot rather on query. I use Sonar which uses Derby
> embedded, but unfortunately one of the versions which is known to
> corrupt data, 10.3.1.4.
>   OK, so the problem is that on query the Debry instance just shuts down
> detecting the CRC error. I used a hex editor to fix the CRC (it's in the
> first page of the file) but then I get another error (see below). Could
> someone help me fix this even by sending her/him the whole database (not
> huge, 240MB uncompressed)?
>
>   Thanks,
>
>   Gabor 'Morc' Kormos.
>
> 2009-06-17 09:52:59.011 GMT Thread[DRDAConnThread_9,5,derby.daemons]
> (XID = 151484434), (SESSIONID = 2), (DATABASE = sonar), (DRDAID =
> NF000001.GCDA-4269129726538145963{4}), Failed Statement is: select *
> from project_measures
> java.lang.IllegalArgumentException: Bit position 1 is outside the legal
> range
>     at
> org.apache.derby.iapi.services.io.FormatableBitSet.checkPosition(Unknown
> Source)
>     at org.apache.derby.iapi.services.io.FormatableBitSet.isSet(Unknown
> Source)
>     at
> org.apache.derby.impl.store.raw.data.AllocExtent.getPageStatus(Unknown
> Source)
>     at
> org.apache.derby.impl.store.raw.data.AllocationCache.getPageStatus(Unknown
> Source)
>     at
> org.apache.derby.impl.store.raw.data.FileContainer.pageValid(Unknown
> Source)
>     at
> org.apache.derby.impl.store.raw.data.FileContainer.getUserPage(Unknown
> Source)
>     at
> org.apache.derby.impl.store.raw.data.FileContainer.getPage(Unknown Source)
>     at
> org.apache.derby.impl.store.raw.data.BaseContainerHandle.getPage(Unknown
> Source)
>     at
> org.apache.derby.impl.store.access.heap.HeapConglomerateFactory.readConglo
> merate(Unknown
> Source)
>     at
> org.apache.derby.impl.store.access.RAMAccessManager.conglomCacheFind(Unkno
> wn
> Source)
>     at
> org.apache.derby.impl.store.access.RAMTransaction.findExistingConglomerate
> (Unknown
> Source)
>     at
> org.apache.derby.impl.store.access.RAMTransaction.openStoreCost(Unknown
> Source)
>     at
> org.apache.derby.impl.sql.compile.CompilerContextImpl.getStoreCostControll
> er(Unknown
> Source)
>     at
> org.apache.derby.impl.sql.compile.FromBaseTable.getStoreCostController(Unk
> nown
> Source)
>     at
> org.apache.derby.impl.sql.compile.FromBaseTable.estimateCost(Unknown
> Source)
>     at
> org.apache.derby.impl.sql.compile.OptimizerImpl.estimateTotalCost(Unknown
> Source)
>     at
> org.apache.derby.impl.sql.compile.OptimizerImpl.costBasedCostOptimizable(U
> nknown
> Source)
>     at
> org.apache.derby.impl.sql.compile.OptimizerImpl.costOptimizable(Unknown
> Source)
>     at
> org.apache.derby.impl.sql.compile.FromBaseTable.optimizeIt(Unknown Source)
>     at
> org.apache.derby.impl.sql.compile.ProjectRestrictNode.optimizeIt(Unknown
> Source)
>     at
> org.apache.derby.impl.sql.compile.OptimizerImpl.costPermutation(Unknown
> Source)
>     at org.apache.derby.impl.sql.compile.SelectNode.optimize(Unknown
> Source)
>     at
> org.apache.derby.impl.sql.compile.DMLStatementNode.optimizeStatement(Unkno
> wn
> Source)
>     at
> org.apache.derby.impl.sql.compile.CursorNode.optimizeStatement(Unknown
> Source)
>     at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown
> Source)
>     at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
>     at
> org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInt
> ernalStatement(Unknown
> Source)
>     at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown
> Source)
>     at
> org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
>     at
> org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
>     at
> org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown Source)
>     at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown
> Source)
>     at
> org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
> Source)
>     at
> org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
> Source)
>     at
> org.apache.derby.impl.drda.DRDAStatement.prepareStatementJDBC3(Unknown
> Source)
>     at org.apache.derby.impl.drda.DRDAStatement.prepare(Unknown Source)
>     at org.apache.derby.impl.drda.DRDAStatement.explicitPrepare(Unknown
> Source)
>     at org.apache.derby.impl.drda.DRDAConnThread.parsePRPSQLSTT(Unknown
> Source)
>     at org.apache.derby.impl.drda.DRDAConnThread.processCommands(Unknown
> Source)
>     at org.apache.derby.impl.drda.DRDAConnThread.run(Unknown Source)

[Kathey Marsden]

Gabor 'Morc' KORMOS wrote:

>  Hi,
>
>  I saw a post just the other day detailing how to fix a CRC error
> although this does not cover my problem because the corruption does
> not surface during boot rather on query. I use Sonar which uses Derby
> embedded, but unfortunately one of the versions which is known to
> corrupt data, 10.3.1.4.
>  OK, so the problem is that on query the Debry instance just shuts
> down detecting the CRC error. I used a hex editor to fix the CRC (it's
> in the first page of the file) but then I get another error (see
> below). Could someone help me fix this even by sending her/him the
> whole database (not huge, 240MB uncompressed)?

You might start with a consistency check to determine what table/index
is corrupt: http://wiki.apache.org/db-derby/DatabaseConsistencyCheck

Do this on your original corrupted database, not the one that you
changed with the hex editor.
Hopefully it is an index that you can drop and recreate.

Thanks

Kathey

[Gabor 'Morc' KORMOS]

On 17/06/2009 16:36, Kathey Marsden wrote:

> Gabor 'Morc' KORMOS wrote:
>>  Hi,
>>
>>  I saw a post just the other day detailing how to fix a CRC error
>> although this does not cover my problem because the corruption does
>> not surface during boot rather on query. I use Sonar which uses Derby
>> embedded, but unfortunately one of the versions which is known to
>> corrupt data, 10.3.1.4.
>>  OK, so the problem is that on query the Debry instance just shuts
>> down detecting the CRC error. I used a hex editor to fix the CRC (it's
>> in the first page of the file) but then I get another error (see
>> below). Could someone help me fix this even by sending her/him the
>> whole database (not huge, 240MB uncompressed)?
> You might start with a consistency check to determine what table/index
> is corrupt: http://wiki.apache.org/db-derby/DatabaseConsistencyCheck
>
> Do this on your original corrupted database, not the one that you
> changed with the hex editor.
> Hopefully it is an index that you can drop and recreate.
>
> Thanks
>
> Kathey

   Hi Kathey,

   I'm passed this, I just forgot to include this info. Anyhow I ran the two queries on that page although the first does not produce any output except this:

ERROR XSDG2: DERBY SQL error: SQLCODE: -1, SQLSTATE: XSDG2, SQLERRMC: Invalid checksum on Page Page(0,Container(0, 2384)), expected=3,281,399,563, on-disk version=3,090,892,683, page dump follows: Hex dump:

   I found a mailing list thread that detailed how to find out whether it's an index or table, but based on that I could not determine if it's an index or table. The two queries suggested were these (updated with the number from the error message):

SELECT C.CONGLOMERATENUMBER, C.CONGLOMERATENAME FROM SYS.SYSCONGLOMERATES C WHERE CONGLOMERATENUMBER=2384;
SELECT C.CONGLOMERATENUMBER, T.TABLENAME FROM SYS.SYSCONGLOMERATES C, SYS.SYSTABLES T  WHERE C.CONGLOMERATENAME=T.TABLEID AND C.CONGLOMERATENUMBER=2384;

   These produce these outputs respectively:

CONGLOMERATENUMBER  |CONGLOMERATENAME
-----------------------------------------------------------------------------------------------------------------------------------------------------
2384                |83ba410f-011d-aeeb-11a5-ffffd602c73a

CONGLOMERATENUMBER  |TABLENAME

-----------------------------------------------------------------------------------------------------------------------------------------------------
2384                |PROJECT_MEASURES

   This table does have an index, but even trying to drop it results in the same error as above. I tried to figure out the file structure from the source code and fix it with little luck, but I'm clear that this page is the file header containing some vital info. Anyhow dropping the table is not an option, we'd like to preserve as much data as possible and only this table/file is corrupted. An important question though: since this Sonar version uses a known data corrupting Derby version does that help at all or this corruption is not the one that was caused by the bug?

   Thanks,

   Morc.

[Mike Matrigali]

Unfortunately it looks like your corruption is in page 0 of a regular
table (not the index).  This is the worst case, as page 0 contains a
bunch of information necessary to access the rest of the table.  From the
subsequent errors you got after patching the code to ignore the
catch of the page being corrupted, it looks like the bit map on
the page is bad.

In addition to normal metadata about the table, page 0 also includes
the first part of the bit map for the table which tells the table
which pages are allocated or not.



Gabor 'Morc' KORMOS wrote:

> On 17/06/2009 16:36, Kathey Marsden wrote:
>> Gabor 'Morc' KORMOS wrote:
>>>  Hi,
>>>
>>>  I saw a post just the other day detailing how to fix a CRC error
>>> although this does not cover my problem because the corruption does
>>> not surface during boot rather on query. I use Sonar which uses Derby
>>> embedded, but unfortunately one of the versions which is known to
>>> corrupt data, 10.3.1.4.
>>>  OK, so the problem is that on query the Debry instance just shuts
>>> down detecting the CRC error. I used a hex editor to fix the CRC
>>> (it's in the first page of the file) but then I get another error
>>> (see below). Could someone help me fix this even by sending her/him
>>> the whole database (not huge, 240MB uncompressed)?
>> You might start with a consistency check to determine what table/index
>> is corrupt: http://wiki.apache.org/db-derby/DatabaseConsistencyCheck
>>
>> Do this on your original corrupted database, not the one that you
>> changed with the hex editor.
>> Hopefully it is an index that you can drop and recreate.
>>
>> Thanks
>>
>> Kathey
>
>   Hi Kathey,
>
>   I'm passed this, I just forgot to include this info. Anyhow I ran the
> two queries on that page although the first does not produce any output
> except this:
>
> ERROR XSDG2: DERBY SQL error: SQLCODE: -1, SQLSTATE: XSDG2, SQLERRMC:
> Invalid checksum on Page Page(0,Container(0, 2384)),
> expected=3,281,399,563, on-disk version=3,090,892,683, page dump
> follows: Hex dump:
>
>   I found a mailing list thread that detailed how to find out whether
> it's an index or table, but based on that I could not determine if it's
> an index or table. The two queries suggested were these (updated with
> the number from the error message):
>
> SELECT C.CONGLOMERATENUMBER, C.CONGLOMERATENAME FROM
> SYS.SYSCONGLOMERATES C WHERE CONGLOMERATENUMBER=2384;
> SELECT C.CONGLOMERATENUMBER, T.TABLENAME FROM SYS.SYSCONGLOMERATES C,
> SYS.SYSTABLES T  WHERE C.CONGLOMERATENAME=T.TABLEID AND
> C.CONGLOMERATENUMBER=2384;
>
>   These produce these outputs respectively:
>
> CONGLOMERATENUMBER  |CONGLOMERATENAME
> -----------------------------------------------------------------------------------------------------------------------------------------------------
>
> 2384                |83ba410f-011d-aeeb-11a5-ffffd602c73a
>
> CONGLOMERATENUMBER  |TABLENAME
>
> -----------------------------------------------------------------------------------------------------------------------------------------------------
>
> 2384                |PROJECT_MEASURES
>
>   This table does have an index, but even trying to drop it results in
> the same error as above. I tried to figure out the file structure from
> the source code and fix it with little luck, but I'm clear that this
> page is the file header containing some vital info. Anyhow dropping the
> table is not an option, we'd like to preserve as much data as possible
> and only this table/file is corrupted. An important question though:
> since this Sonar version uses a known data corrupting Derby version does
> that help at all or this corruption is not the one that was caused by
> the bug?
>
>   Thanks,
>
>   Morc.
>

[Gabor 'Morc' KORMOS]

   Mike,

   You seem to know more than me about the structure. Is there a way you
think to reconstructs page 0 given the rest of the file is intact?

   Thanks,

   Morc.

On 6/17/2009 20:20, Mike Matrigali wrote:

> Unfortunately it looks like your corruption is in page 0 of a regular
> table (not the index).  This is the worst case, as page 0 contains a
> bunch of information necessary to access the rest of the table.  From the
> subsequent errors you got after patching the code to ignore the
> catch of the page being corrupted, it looks like the bit map on
> the page is bad.
>
> In addition to normal metadata about the table, page 0 also includes
> the first part of the bit map for the table which tells the table
> which pages are allocated or not.
>
>
>
> Gabor 'Morc' KORMOS wrote:
>> On 17/06/2009 16:36, Kathey Marsden wrote:
>>> Gabor 'Morc' KORMOS wrote:
>>>>  Hi,
>>>>
>>>>  I saw a post just the other day detailing how to fix a CRC error
>>>> although this does not cover my problem because the corruption does
>>>> not surface during boot rather on query. I use Sonar which uses
>>>> Derby embedded, but unfortunately one of the versions which is known
>>>> to corrupt data, 10.3.1.4.
>>>>  OK, so the problem is that on query the Debry instance just shuts
>>>> down detecting the CRC error. I used a hex editor to fix the CRC
>>>> (it's in the first page of the file) but then I get another error
>>>> (see below). Could someone help me fix this even by sending her/him
>>>> the whole database (not huge, 240MB uncompressed)?
>>> You might start with a consistency check to determine what
>>> table/index is corrupt:
>>> http://wiki.apache.org/db-derby/DatabaseConsistencyCheck
>>>
>>> Do this on your original corrupted database, not the one that you
>>> changed with the hex editor.
>>> Hopefully it is an index that you can drop and recreate.
>>>
>>> Thanks
>>>
>>> Kathey
>>
>>   Hi Kathey,
>>
>>   I'm passed this, I just forgot to include this info. Anyhow I ran
>> the two queries on that page although the first does not produce any
>> output except this:
>>
>> ERROR XSDG2: DERBY SQL error: SQLCODE: -1, SQLSTATE: XSDG2, SQLERRMC:
>> Invalid checksum on Page Page(0,Container(0, 2384)),
>> expected=3,281,399,563, on-disk version=3,090,892,683, page dump
>> follows: Hex dump:
>>
>>   I found a mailing list thread that detailed how to find out whether
>> it's an index or table, but based on that I could not determine if
>> it's an index or table. The two queries suggested were these (updated
>> with the number from the error message):
>>
>> SELECT C.CONGLOMERATENUMBER, C.CONGLOMERATENAME FROM
>> SYS.SYSCONGLOMERATES C WHERE CONGLOMERATENUMBER=2384;
>> SELECT C.CONGLOMERATENUMBER, T.TABLENAME FROM SYS.SYSCONGLOMERATES C,
>> SYS.SYSTABLES T  WHERE C.CONGLOMERATENAME=T.TABLEID AND
>> C.CONGLOMERATENUMBER=2384;
>>
>>   These produce these outputs respectively:
>>
>> CONGLOMERATENUMBER  |CONGLOMERATENAME
>> -----------------------------------------------------------------------------------------------------------------------------------------------------
>>
>> 2384                |83ba410f-011d-aeeb-11a5-ffffd602c73a
>>
>> CONGLOMERATENUMBER  |TABLENAME
>>
>> -----------------------------------------------------------------------------------------------------------------------------------------------------
>>
>> 2384                |PROJECT_MEASURES
>>
>>   This table does have an index, but even trying to drop it results in
>> the same error as above. I tried to figure out the file structure from
>> the source code and fix it with little luck, but I'm clear that this
>> page is the file header containing some vital info. Anyhow dropping
>> the table is not an option, we'd like to preserve as much data as
>> possible and only this table/file is corrupted. An important question
>> though: since this Sonar version uses a known data corrupting Derby
>> version does that help at all or this corruption is not the one that
>> was caused by the bug?
>>
>>   Thanks,
>>
>>   Morc.

[Gabor 'Morc' KORMOS]

  Gals and guys.

  Anyone adventurous enough to help me with this problem? Even a yes/no
to the question whether it's possible and maybe some pointers how to
reconstruct page 0 would be a lot of help I think.

  Thanks,

  Morc.

On 17/06/2009 20:27, Gabor 'Morc' Kormos wrote:

>   Mike,
>
>   You seem to know more than me about the structure. Is there a way
> you think to reconstructs page 0 given the rest of the file is intact?
>
>   Thanks,
>
>   Morc.
>
> On 6/17/2009 20:20, Mike Matrigali wrote:
>> Unfortunately it looks like your corruption is in page 0 of a regular
>> table (not the index).  This is the worst case, as page 0 contains a
>> bunch of information necessary to access the rest of the table.  From
>> the
>> subsequent errors you got after patching the code to ignore the
>> catch of the page being corrupted, it looks like the bit map on
>> the page is bad.
>>
>> In addition to normal metadata about the table, page 0 also includes
>> the first part of the bit map for the table which tells the table
>> which pages are allocated or not.
>>
>>
>>
>> Gabor 'Morc' KORMOS wrote:
>>> On 17/06/2009 16:36, Kathey Marsden wrote:
>>>> Gabor 'Morc' KORMOS wrote:
>>>>>  Hi,
>>>>>
>>>>>  I saw a post just the other day detailing how to fix a CRC error
>>>>> although this does not cover my problem because the corruption
>>>>> does not surface during boot rather on query. I use Sonar which
>>>>> uses Derby embedded, but unfortunately one of the versions which
>>>>> is known to corrupt data, 10.3.1.4.
>>>>>  OK, so the problem is that on query the Debry instance just shuts
>>>>> down detecting the CRC error. I used a hex editor to fix the CRC
>>>>> (it's in the first page of the file) but then I get another error
>>>>> (see below). Could someone help me fix this even by sending
>>>>> her/him the whole database (not huge, 240MB uncompressed)?
>>>> You might start with a consistency check to determine what
>>>> table/index is corrupt:
>>>> http://wiki.apache.org/db-derby/DatabaseConsistencyCheck
>>>>
>>>> Do this on your original corrupted database, not the one that you
>>>> changed with the hex editor.
>>>> Hopefully it is an index that you can drop and recreate.
>>>>
>>>> Thanks
>>>>
>>>> Kathey
>>>
>>>   Hi Kathey,
>>>
>>>   I'm passed this, I just forgot to include this info. Anyhow I ran
>>> the two queries on that page although the first does not produce any
>>> output except this:
>>>
>>> ERROR XSDG2: DERBY SQL error: SQLCODE: -1, SQLSTATE: XSDG2,
>>> SQLERRMC: Invalid checksum on Page Page(0,Container(0, 2384)),
>>> expected=3,281,399,563, on-disk version=3,090,892,683, page dump
>>> follows: Hex dump:
>>>
>>>   I found a mailing list thread that detailed how to find out
>>> whether it's an index or table, but based on that I could not
>>> determine if it's an index or table. The two queries suggested were
>>> these (updated with the number from the error message):
>>>
>>> SELECT C.CONGLOMERATENUMBER, C.CONGLOMERATENAME FROM
>>> SYS.SYSCONGLOMERATES C WHERE CONGLOMERATENUMBER=2384;
>>> SELECT C.CONGLOMERATENUMBER, T.TABLENAME FROM SYS.SYSCONGLOMERATES
>>> C, SYS.SYSTABLES T  WHERE C.CONGLOMERATENAME=T.TABLEID AND
>>> C.CONGLOMERATENUMBER=2384;
>>>
>>>   These produce these outputs respectively:
>>>
>>> CONGLOMERATENUMBER  |CONGLOMERATENAME
>>> -----------------------------------------------------------------------------------------------------------------------------------------------------
>>>
>>> 2384                |83ba410f-011d-aeeb-11a5-ffffd602c73a
>>>
>>> CONGLOMERATENUMBER  |TABLENAME
>>>
>>> -----------------------------------------------------------------------------------------------------------------------------------------------------
>>>
>>> 2384                |PROJECT_MEASURES
>>>
>>>   This table does have an index, but even trying to drop it results
>>> in the same error as above. I tried to figure out the file structure
>>> from the source code and fix it with little luck, but I'm clear that
>>> this page is the file header containing some vital info. Anyhow
>>> dropping the table is not an option, we'd like to preserve as much
>>> data as possible and only this table/file is corrupted. An important
>>> question though: since this Sonar version uses a known data
>>> corrupting Derby version does that help at all or this corruption is
>>> not the one that was caused by the bug?
>>>
>>>   Thanks,
>>>
>>>   Morc.
>
>

[Bryan Pendleton]

>  Anyone adventurous enough to help me with this problem? Even a yes/no
> to the question whether it's possible and maybe some pointers how to
> reconstruct page 0 would be a lot of help I think.

Well, anything's possible, since it's open source software and so you can
change it and make it do what you need. However, this doesn't sound like
a very easy thing to do. You've definitely exhausted all possible sources
of backups?

Here's some fairly high-level information about page formats:
http://db.apache.org/derby/papers/pageformats.html

My first thought would be that if you could make page 0 appear to look
as though ALL other pages in the conglomerate were "in-use", so that it
seemed to have no pages marked "available", then you could try opening
the database and reading all the data out.

So you'd like the FreePages bitmap to be empty, for this recovery scenario.

thanks,

bryan

[Mike Matrigali]

Along these lines, if you don't want to hack the code I would try the
following (note I have not ever tried this, so have no idea if it will
work - I thought about this for awhile and looked at the headers and
could not come up with why it would not work).  For this you will need
to know the seg0 file associated with
the table you are trying to recover and the seg0 file you will create
for a new dummy table.

o Shutdown the db you are working with cleanly with shutdown=true, make
   an offline copy of it and only work on the copy.
o In another db create a new table that has the same ddl as the original
   table - i will call this dummy table.  The most important part of
   this is that the new table has the
   same page size as the table you are trying to recover.  If you use
the same ddl and you didn't set any page size properties when you
created the original table then the page size should match.  The size and
   structure of the allocation part of page 0 is different for each of
the 4 supported page sizes (2k, 4k, 8k, 32k) - basically there is a
fixed header and "the rest" is used for the allocation page.  Thus the
bigger the page size the more pages the allocation bit map on page 0
controls.
o now insert as many rows as necessary into the dummy table such that it
is as big as the table you are trying to recover.  The goal here is to
get the allocation map in page 0 to mark all the pages as allocated and
in use.
o now shutdown cleanly (ie. shutdown=true) - if you don't do this then
   the changes may only be in the log and not in the seg0 file.
o now with both db's shutdown cleanly, copy page 0 from the dummy file
over page 0 of the copied database table that you are trying to recover
and try booting, and checking the table.
On a unix system I think this can easily be done with one or two dd
commands, let me know if you need more info.  Again do this only while
db's are shutdown cleanly otherwise all sorts of recovery problems may
happen.
o And of course after you do this you should run the consistency checker
to see what else may be corrupted, note the consistency checker does not
check everything.  So I usually recommend the only safe thing to do when
using this kind of data corruption mining is to select the recovered
data out of the bad db and then insert into a newly created good db
otherwise the corruptions may lurk around and bite you later.  The
checker is not perfect, it mostly does a good job of checking that the
index tree's are consistent internally and that they are consistent with
the base tables.  For instance I don't think it even reads data in base
that is not needed to check the indexes.

/mikem

Bryan Pendleton wrote:

>>  Anyone adventurous enough to help me with this problem? Even a yes/no
>> to the question whether it's possible and maybe some pointers how to
>> reconstruct page 0 would be a lot of help I think.
>
> Well, anything's possible, since it's open source software and so you can
> change it and make it do what you need. However, this doesn't sound like
> a very easy thing to do. You've definitely exhausted all possible sources
> of backups?
>
> Here's some fairly high-level information about page formats:
> http://db.apache.org/derby/papers/pageformats.html
>
> My first thought would be that if you could make page 0 appear to look
> as though ALL other pages in the conglomerate were "in-use", so that it
> seemed to have no pages marked "available", then you could try opening
> the database and reading all the data out.
>
> So you'd like the FreePages bitmap to be empty, for this recovery scenario.
>
> thanks,
>
> bryan
>
>

[Gabor 'Morc' KORMOS]

  Hi Mike,

  Brilliant idea! I'll give it a try and I appreciate your help very
much! Although there's a problem, that is I don't know how many records
there are in the table, but I'll insert until I reach the same file
size. If I understand Sonar correctly this table is never deleted from
or updated, just inserted and queried. I let you know how it goes and
also for others to have an answer whether this solution works or not.

  Regards,

  Morc.

On 23/06/2009 01:36, Mike Matrigali wrote:

> Along these lines, if you don't want to hack the code I would try the
> following (note I have not ever tried this, so have no idea if it will
> work - I thought about this for awhile and looked at the headers and
> could not come up with why it would not work).  For this you will need
> to know the seg0 file associated with
> the table you are trying to recover and the seg0 file you will create
> for a new dummy table.
>
> o Shutdown the db you are working with cleanly with shutdown=true, make
>   an offline copy of it and only work on the copy.
> o In another db create a new table that has the same ddl as the original
>   table - i will call this dummy table.  The most important part of
>   this is that the new table has the
>   same page size as the table you are trying to recover.  If you use
> the same ddl and you didn't set any page size properties when you
> created the original table then the page size should match.  The size and
>   structure of the allocation part of page 0 is different for each of
> the 4 supported page sizes (2k, 4k, 8k, 32k) - basically there is a
> fixed header and "the rest" is used for the allocation page.  Thus the
> bigger the page size the more pages the allocation bit map on page 0
> controls.
> o now insert as many rows as necessary into the dummy table such that
> it is as big as the table you are trying to recover.  The goal here is to
> get the allocation map in page 0 to mark all the pages as allocated and
> in use.
> o now shutdown cleanly (ie. shutdown=true) - if you don't do this then
>   the changes may only be in the log and not in the seg0 file.
> o now with both db's shutdown cleanly, copy page 0 from the dummy file
> over page 0 of the copied database table that you are trying to recover
> and try booting, and checking the table.
> On a unix system I think this can easily be done with one or two dd
> commands, let me know if you need more info.  Again do this only while
> db's are shutdown cleanly otherwise all sorts of recovery problems may
> happen.
> o And of course after you do this you should run the consistency
> checker to see what else may be corrupted, note the consistency
> checker does not
> check everything.  So I usually recommend the only safe thing to do when
> using this kind of data corruption mining is to select the recovered
> data out of the bad db and then insert into a newly created good db
> otherwise the corruptions may lurk around and bite you later.  The
> checker is not perfect, it mostly does a good job of checking that the
> index tree's are consistent internally and that they are consistent with
> the base tables.  For instance I don't think it even reads data in base
> that is not needed to check the indexes.
>
> /mikem
>
> Bryan Pendleton wrote:
>>>  Anyone adventurous enough to help me with this problem? Even a
>>> yes/no to the question whether it's possible and maybe some pointers
>>> how to reconstruct page 0 would be a lot of help I think.
>>
>> Well, anything's possible, since it's open source software and so you
>> can
>> change it and make it do what you need. However, this doesn't sound like
>> a very easy thing to do. You've definitely exhausted all possible
>> sources
>> of backups?
>>
>> Here's some fairly high-level information about page formats:
>> http://db.apache.org/derby/papers/pageformats.html
>>
>> My first thought would be that if you could make page 0 appear to look
>> as though ALL other pages in the conglomerate were "in-use", so that it
>> seemed to have no pages marked "available", then you could try opening
>> the database and reading all the data out.
>>
>> So you'd like the FreePages bitmap to be empty, for this recovery
>> scenario.
>>
>> thanks,
>>
>> bryan
>>
>>
>

[Gabor 'Morc' KORMOS]

  As said here's the update on this. I tried to fill up the datafile to
the same size as the corrupted file, but it turned out that records are
deleted from that table. Anyhow after copying page 0 the corrupted file
sprung back to life and there were records in the table although I have
no idea if they were the records which were active before the corruption
or contained deleted records too. Anyhow it was not necessary to recover
the data, so I did not spend more time on trying. I'm sure with
relatively little effort and by studying page/file structure available
on the website one could reconstruct page 0 and recover the data.

  Regards,

  Morc.

On 23/06/2009 11:09, Gabor 'Morc' KORMOS wrote:

>  Hi Mike,
>
>  Brilliant idea! I'll give it a try and I appreciate your help very
> much! Although there's a problem, that is I don't know how many
> records there are in the table, but I'll insert until I reach the same
> file size. If I understand Sonar correctly this table is never deleted
> from or updated, just inserted and queried. I let you know how it goes
> and also for others to have an answer whether this solution works or not.
>
>  Regards,
>
>  Morc.
>
> On 23/06/2009 01:36, Mike Matrigali wrote:
>> Along these lines, if you don't want to hack the code I would try the
>> following (note I have not ever tried this, so have no idea if it will
>> work - I thought about this for awhile and looked at the headers and
>> could not come up with why it would not work).  For this you will
>> need to know the seg0 file associated with
>> the table you are trying to recover and the seg0 file you will create
>> for a new dummy table.
>>
>> o Shutdown the db you are working with cleanly with shutdown=true, make
>>   an offline copy of it and only work on the copy.
>> o In another db create a new table that has the same ddl as the original
>>   table - i will call this dummy table.  The most important part of
>>   this is that the new table has the
>>   same page size as the table you are trying to recover.  If you use
>> the same ddl and you didn't set any page size properties when you
>> created the original table then the page size should match.  The size
>> and
>>   structure of the allocation part of page 0 is different for each of
>> the 4 supported page sizes (2k, 4k, 8k, 32k) - basically there is a
>> fixed header and "the rest" is used for the allocation page.  Thus the
>> bigger the page size the more pages the allocation bit map on page 0
>> controls.
>> o now insert as many rows as necessary into the dummy table such that
>> it is as big as the table you are trying to recover.  The goal here
>> is to
>> get the allocation map in page 0 to mark all the pages as allocated and
>> in use.
>> o now shutdown cleanly (ie. shutdown=true) - if you don't do this then
>>   the changes may only be in the log and not in the seg0 file.
>> o now with both db's shutdown cleanly, copy page 0 from the dummy file
>> over page 0 of the copied database table that you are trying to recover
>> and try booting, and checking the table.
>> On a unix system I think this can easily be done with one or two dd
>> commands, let me know if you need more info.  Again do this only while
>> db's are shutdown cleanly otherwise all sorts of recovery problems may
>> happen.
>> o And of course after you do this you should run the consistency
>> checker to see what else may be corrupted, note the consistency
>> checker does not
>> check everything.  So I usually recommend the only safe thing to do when
>> using this kind of data corruption mining is to select the recovered
>> data out of the bad db and then insert into a newly created good db
>> otherwise the corruptions may lurk around and bite you later.  The
>> checker is not perfect, it mostly does a good job of checking that the
>> index tree's are consistent internally and that they are consistent with
>> the base tables.  For instance I don't think it even reads data in base
>> that is not needed to check the indexes.
>>
>> /mikem
>>
>> Bryan Pendleton wrote:
>>>>  Anyone adventurous enough to help me with this problem? Even a
>>>> yes/no to the question whether it's possible and maybe some
>>>> pointers how to reconstruct page 0 would be a lot of help I think.
>>>
>>> Well, anything's possible, since it's open source software and so
>>> you can
>>> change it and make it do what you need. However, this doesn't sound
>>> like
>>> a very easy thing to do. You've definitely exhausted all possible
>>> sources
>>> of backups?
>>>
>>> Here's some fairly high-level information about page formats:
>>> http://db.apache.org/derby/papers/pageformats.html
>>>
>>> My first thought would be that if you could make page 0 appear to look
>>> as though ALL other pages in the conglomerate were "in-use", so that it
>>> seemed to have no pages marked "available", then you could try opening
>>> the database and reading all the data out.
>>>
>>> So you'd like the FreePages bitmap to be empty, for this recovery
>>> scenario.
>>>
>>> thanks,
>>>
>>> bryan