SYSERROR -9008 BD Illegal record

[Former Member]

Performing a database structure check result in an error message:

2009-02-24 18:54:33 0x106C 53022 B*TREE Check Table: 5602 (Root)

2009-02-24 18:54:33 0x106C 53022 B*TREE Check Table: 81004 (Root)

2009-02-24 18:54:51 0x106C ERR 53370 B*TREE Sum record len <> bottom: 8180

2009-02-24 18:54:51 0x106C ERR 53370 B*TREE Corrupted data page: 20278

2009-02-24 18:54:51 0x106C ERR 53272 B*TREE Parent data pageNo: 2521

2009-02-24 18:54:51 0x106C ERR 53000 B*TREE 0D000000000000001C2C000000000000

2009-02-24 18:54:51 0x106C ERR 53000 B*TREE Table Root 2085

2009-02-24 18:54:51 0x1690 ERR 53019 CHECK Base error: illegal_record

2009-02-24 18:54:51 0x1690 ERR 53019 CHECK Root pageNo: 2085

2009-02-24 18:54:51 0x106C 53022 B*TREE Check Table: 15180 (Root)

...

How can we identify and repair the illegal record?

select * from roots where root in ( 2085, 2521 81004)

returns anything.

Up to now we have no problems with this. Database works fine.

The problem seems to exist already some time, because the backups we checked

also seem to have this problem.

(Version is now 7.6.06 - but the error is already in the backups taken with the previous release)

Any help appreciated,

regards

Bernhard Schiefer

[lbreddemann]

Hi Bernd,

could you run a "CHECK DATA WITH UPDATE" in Admin mode?

Either you ran the queries against the root table with the wrong user or the roots are really not in the database anymore, but not yet correctly cleaned up by the garbage collector.

regards,

Lars

[Former Member]

Both checks return the same error.

But I checked now: select * from roots

- I used the dba-User and it seems to return only dba tables.

I checked also the other users and now found the table with root=2085 .

But how can I repair the illegal record?

[lbreddemann]

HI Bernhard,

if the corrupted root is indeed a table, you now need to have a backup and recover the database.

There is no 'repair' of corrupt tables. The data in the page is lost.

regards,

Lars

[Former Member]

Hi Lars,

all the backups show the same error after reloading

(despite checking backup says : check backup has completed successfully)

I now tried:

CREATE TABLE <new_tab> AS SELECT * FROM <corrupt_tab>

The check for the <new_tab> is ok!

SELECT COUNT(*) returns the same value for both tables.

(BTW Is there a diff-tool to compare the content of two tables?)

Do you think this is a good solution?

Regards,

Bernhard

[lbreddemann]

>

> Hi Lars,

>

> all the backups show the same error after reloading

> (despite checking backup says : check backup has completed successfully)

Hmm... can you provide us a download with this backup?

Sounds pretty weired...

Anyhow: CHECK BACKUP is not the same as CHECK DATA. It does only check if all pages in the backup are actually readable and whether the page checksums are OK.

CHECK DATA does a lot more here, so it is possible that you don't get an error message for CHECK BACKUP here.

> I now tried:

>

> CREATE TABLE <new_tab> AS SELECT * FROM <corrupt_tab>

That shouldn't have worked if there is a corruption...

> The check for the <new_tab> is ok!

Did you run CHECK TABLE or CHECK DATA after copying the table?

> SELECT COUNT(*) returns the same value for both tables.

> (BTW Is there a diff-tool to compare the content of two tables?)

Nope - there is no DIFF-TOOL, at least not with the standard MaxDB tool.

But you may just join the tables with all columns - if you receive less than all entries, there are deviations.

> Do you think this is a good solution?

If the application data is fine with that - sure.

Anyhow it remains obscure why the error message was given, if all data is readable.

For that, the backup would be useful...

regards,

Lars

ps.

If this is for a SAP installation - just open a message for this to get analyzed in more detail.

[Former Member]

Hi Lars,

the CHECK TABLE was fine with new_tab, so we decided to:

DROP TABLE <corrupt_tab>

and

RENAME TABLE <new_tab> TO <corrupt_tab>

(additionally recreated all indexes)

And now everything seems to be ok.

CHECK DATA for the DB now returns "successfully finished"

I'm sorry that we can't send the backup to analyze this phenomena, but it contains sensitive data of our students.

Regards,

Bernhard

[lbreddemann]

Hi Bernd,

is it possible that you provide the .bad or .cor files that (should have) been created?

I'm pretty sure that our development (that's the SAP MaxDB development) is quite interested in figuring out why there was a check data error but no problem during data access.

regards,

Lars