Derby database corrupted after delete

[openMonArch]

If CDO is used with

<property name="supportingAudits" value="false" /> <property name="supportingBranches" value="false" /> <property name="supportingUnits" value="false" />

delete of element can result in holes in LIST tables.
The result is a complete damaged database where all data is not usable anymore and CDO-Server does not startup.
The exception is:
org.eclipse.emf.cdo.util.CommitException: Rollback in DBStore: org.eclipse.net4j.db.DBException: java.sql.BatchUpdateException: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'I1680272084714_23' defined on '*_LIST'.

The original discussion: https://www.eclipse.org/forums/index.php/t/1112542/

We need some helpt to fix this error. Does anybody have any idea how to fix this?

[estepper]

Thank you very much for your test project. I've played the entire weekend and yesterday with it. First I struggled with the OSGi launch config, then with Derby. In the end I've taken your test code and stripped it down to an almost minimal test case with a normal Java main() method. I'm going to attach it for you in a minute. Within that class you can easily switch between Derby and H2. I've also fixed a bug (see issue #21) in CDO's Derby support, so that I finally could also run your test against a Derby DB, with up to 5000 element pair creations and up to 1000 element pair deletions, a dozen times. No single failure here!

Then I went back to your original test code and changed only as little as needed to make it runnable here. Then I was finally able to reproduce your problem!

After a while I developed a sense for what's going on and now I'm almost certain that the problem is caused by a bug in Derby itself. You can easily proof that yourself with the test class I'm going to attach. After each deleteTag() I execute checkTable() to see if the table currently contains a PK violation. And at some (always the same) point it does contain a PK violation. Of course, this SHOULD NOT be possible, no matter what CDO has been doing via JDBC!

I've also tested with Derby 10.16.1.1, exact same bug.
No problem with H2.

A few hints for your own analysis:

1. Execute the test case as a plain Java program (not OSGi).
2. The main() method starts the CDOServerBrowser on port 7777. When no commit is ongoing you can inspect the DB Tables or execute DB Queries.
3. Try these:

• select * from FILE_TAGS_LIST where CDO_SOURCE=1212 order by CDO_IDX
• select CDO_IDX, count(CDO_IDX) from FILE_TAGS_LIST where CDO_SOURCE=1212 group by CDO_IDX having count(CDO_IDX) <> 1

[estepper]

TestCases.zip

[openMonArch]

I'm glad that you could also see the error. It could really be a bug in Derby. We tested our tool with Postgres, and there we could not reproduce the error. So it could really be the case. But Postgres has a completely different transaction model compared to Derby. Therefore, it could be a kind of phantom read on the derby. We are thinking about isolation levels. As I understand it, the JDBC driver in CDO is set to TRANSACTION_READ_COMMITTED, which allows nonrepeatable reads and phantom reads . Could that be a direction we should investigate?

[estepper]

Unfortunately I'm not a guru in these DB / JDBC topics, but I definitely can't contradict your hypothesis about transaction isolation levels. Sorry I can't help more!

Please let me know when you think we can close this issue as resolved for CDO.

[estepper]

Do you have new insights here?

[openMonArch]

We have been quite busy with other things, but we are mitigating the bug to avoid mass deletions. We are now working on upgrading the Derby database to a newer version that supports Java 21. And we want to use Postregs as an alternative to Derby in the future. So the bug is still there. It appears to be an internal Derby problem. We have implemented a tool to fix the database when the bug occurs.