Understanding the Problem

One of the most challenging issues with Apache Derby is database corruption, which can manifest as:

• Unexpected SQLNonTransientConnectionException errors.
• Data inconsistency when running queries.
• Issues when starting the database, such as Database not found errors.

These issues often stem from improper shutdown procedures, disk failures, or concurrency conflicts in multi-threaded applications.

Root Causes and Diagnosis

Improper Shutdown

Apache Derby requires a clean shutdown using:

DriverManager.getConnection("jdbc:derby:;shutdown=true");

If this step is skipped, especially in embedded mode, the database can remain in an inconsistent state.

Uncommitted Transactions

Derby uses write-ahead logging, but uncommitted transactions during a crash can cause corruption. Check transaction logs for uncommitted changes:

SELECT XID, STATUS FROM SYSCS_DIAG.TRANSACTION_TABLE;

Multi-Threading Issues

Apache Derby is not thread-safe by default. If accessed concurrently without proper synchronization, data corruption may occur. Ensure safe multi-threaded access by using a connection pool like Apache Commons DBCP.

Fixing and Preventing Database Corruption

Running Database Recovery

If corruption is detected, restart Derby in recovery mode:

java -Dderby.system.home=/path/to/db org.apache.derby.tools.ij

Then run:

CALL SYSCS_UTIL.SYSCS_CHECKPOINT_DATABASE();

Enabling Auto-Recovery

Derby supports auto-recovery by setting:

derby.storage.recoverOnBoot=true

This ensures recovery on startup if an unclean shutdown occurred.

Using Connection Pools

To prevent concurrency issues, use a connection pool instead of direct JDBC connections:

BasicDataSource ds = new BasicDataSource();
ds.setUrl("jdbc:derby:myDB;create=true");
ds.setMinIdle(5);
ds.setMaxIdle(10);
ds.setMaxOpenPreparedStatements(100);

Regular Backups

Schedule regular database backups using:

CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE('/backup/location');

Conclusion

Database corruption in Apache Derby is often due to improper shutdowns, uncommitted transactions, and concurrency issues. Following best practices such as clean shutdowns, auto-recovery, connection pooling, and regular backups helps maintain database integrity.

FAQs

1. How do I know if my Apache Derby database is corrupted?

Check for startup errors, missing tables, or failed queries. Run SYSCS_UTIL.SYSCS_CHECKPOINT_DATABASE() to detect corruption.

2. Can I recover a corrupted Apache Derby database?

Yes. Restart Derby with recovery enabled and run a full backup restore if needed.

3. Why does my database fail to start after a crash?

Uncommitted transactions or an improper shutdown can leave Derby in an inconsistent state. Ensure that derby.storage.recoverOnBoot=true is set.

4. How can I prevent Derby from becoming unresponsive in multi-threaded applications?

Use a connection pool instead of managing direct connections, and avoid sharing a single connection across multiple threads.

5. What is the best way to back up Apache Derby?

Use the built-in system procedure SYSCS_UTIL.SYSCS_BACKUP_DATABASE() to create consistent backups.