Apache Derby is a usually a strong database but sometimes it might get corrupted.

This is what worked for me after many embedded  derby databases became corrupted because we finished the free space on the partition (yes, shame on us).

To make things worse, we didn’t always have a recent backup of the db, which just grew the complexity of our problem.

The Errors we got were XSDG2 or XJ040

After discovering that some database were corrupted because the clients were trying to write data onto the database but the space on the devices was finished (error XJ040), we soon noticed that the databases corrupted themselves in three different ways.

The first one didn’t even allow us to access/boot the embedded database and gave this error

------------ BEGIN SHUTDOWN ERROR STACK -------------

ERROR XSDG2: Invalid checksum on Page Page(0,Container(0, 3632)), expected=1.828.510.840, on-disk version=3.463.751.691, page dump follows: Hex dump:

In the other situation the database was accessible but some table were inconsistent.

ERROR X0X62: Inconsistency found between table 'APP.T1' and index
'T1_I'. Error when trying to retrieve row location '(1,6)' from the
table. The full index key,including the row location, is '{ 1, (1,6) }'.
The suggested corrective action is to recreate the index.

The third one was a mix, because the database was accessible, but once we tried using a specific table, that table would make the connection crash (dunno if that’s the right term to use), making impossible every action.

Our first problem was: how do we get to boot the embedded db again?

How to gain access to Derby even if it won’t boot

This is an “hack” that worked many times for us.
If the database has some dirt transaction that are still pending in its internal logs then it might not even boot.

So in these cases the best way to access again the database is to replace the internal “log” folder with the one from an empty derby database.

This allowed us to get into the database with the ij utilities.

java -cp "derby.jar;derbytools.jar" org.apache.derby.tools.ij
ij> connect 'jdbc:derby:C:/tmp/private/data';

Checking the table consistency

Now that we accessed the db we checked if every table was ok with this command.

SELECT schemaname || '.' || tablename as TableName,SYSCS_UTIL.SYSCS_CHECK_TABLE(schemaname, tablename) AS OK FROM sys.sysschemas s, sys.systables t WHERE s.schemaid = t.schemaid   and t.tabletype = 'T'   ;

This command usually ended in 3 possibilities

1. One or more tables were inconsistent
2. The connection crashed when trying to check one table (usually the one that was writing when the space on device ended)
3. Everything was ok (yes… sometimes things go well 😀 )

Case 1: Inconsistency – error X0X62

When we got the X0X62 error all we had to do is to call the SYSCS_COMPRESS_TABLE on the table and everything was instantly fixed.

ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP','TABLE_NAME',1);

Case 2: Crash !

When the table crashed, we first re-executed the sql to know the exact table name by looking at the last table that didn’t have any error.

SELECT schemaname || '.' || tablename as TableName AS OK FROM sys.sysschemas s, sys.systables t WHERE s.schemaid = t.schemaid   and t.tabletype = 'T'   ;

Then we had only 2 options

1. If we had a recent backup we could replace the corresponding _.dat_ table file and have the db working again.
2. If we _didn’t_ have a recent backup we had to read the data of the corrupted table and reimport into a new clean database together with the non-corrupted one. Sounds complex but in fact it’s quite easy.

Replacing the corrupted table _.dat_ file from one of your backups 

To replace the .dat file with one of your backups all you have to do is discover what’s the right one.

To do that just execute this script (taken from the Derby Wiki )
notice: you need derby 10.10, if not I advice you to upgrade a copy of the db just to execute this, and then substitute the .dat on the original

CREATE FUNCTION bigintToHexString(hexString bigint)     RETURNS VARCHAR(16)     PARAMETER STYLE JAVA NO SQL     LANGUAGE JAVA      EXTERNAL NAME 'java.lang.Long.toHexString';

--select that will tell you which .dat file belongs to which table
select  CAST(TABLENAME as varchar(32)) as DB_table, CAST('C' ||  bigintToHexString (CONGLOMERATENUMBER)  ||  '.dat' as varchar(12)) as file, isindex from SYS.SYSCONGLOMERATES a, SYS.SYSTABLES b  where a.TABLEID = b.TABLEID AND b.TABLETYPE <> 'S' order by file;

Once you got the name, just replace it and you should be good to go.

I know it’s not the perfect solution but it worked well for us since the tables were usually enough fresh so we didn’t lose any data by using the backup.

How to read the data if you don’t have a backup

We had corrupted database we didn’t know how to recover because the connection kept crashing, making it impossible to work on them or recover data from the corrupted table.

Searching on internet I found this jira bug that was talking about dumping data from a corrupted db .

Following these instruction (that I copy just for future references in case the original goes down)

1.-Download lastest derby bin.
2.- Add DerbyRecovery-0.0.1-SNAPSHOT.jar to the ij classpath
3.- Run dataFileVTI.sql (remember to change the route to the database in the connect string)
3.- enter ij and connect to the database using upgrade=true
4.- use call SYSCS_UTIL.SYSCS_REGISTER_TOOL('customTool',true,'RawDBReader', 'CONTROL','RAW_','c:db','','APP',null); -> change the db route, password, bootpassword and schema
5.- check with select * from raw_app.table1;

we were able to read the data from the table! Awesome.
Once we got into this step this is what we’ve done

• We created a db with all the non-corrupted data. You can use a simple tool like SQLSquirrel to transfer the data between the two database, or any other import-export procedure you might like.
  Remember: don’t try to copy the corrupted table, or the connection will crash.
• Since the fix in the jira didn’t create a real table, we could only export the data by using the _SYSCS_UTIL.SYSCS_EXPORT_QUERY_ so we exported to a file and then reimported with a bit of work into the new db (don’t forget to recreate the table 😉 )

That’s it, it took a long time but it taught us many lessons, both in terms of backing up and how to handle this emergency situations.

Closing line

One thing I’ve discovered throughout this process of fixing corrupted databases was how well done was the Derby wiki.
Whenever you’re in trouble or you want some more info, it’s worth taking a look.