h2 database Unique index or primary key violation

h2 is a cool java sql db. it’s quick and handy. but not stable as other (non-java) db solutions.

sometimes “Unique index or primary key violation” is occured. probably inserting data in multiple threads cause this (though i set single thread mode). it’s critical, you have to fix this manually, not automatically.

in h2’s official site, this procedure is not well explained. org.h2.tools.Recover and org.h2.tools.RunScript will fix this problem.

1. org.h2.tools.Recover will generate *.sql backup file.
2. delete broken db
3. run org.h2.tools.RunScript. you will face some sql error.
4. comment the parts in *.sql. delete db and run RunScript again and again.

if you have a critical data, you have to be careful to fix sql file.

i’m testing h2 database’s memory usage.. it costs too much memory when indexing tables.. i have to choose between search performace and memory usage.. good luck to me~