About the recovery of h2 database

[linrong jiang]

My confluence is using H2 databases, After an unexpected power outage I can't connect to my confluence, I also failed to link the database with DBVisualizer. It feels like it should be h2db.mv. db file is corrupted. Sadly I don't have a backup file, I repair the database with java -cp h2*.jar org.h2.tools.Recover, and then execute java -cp h2*.jar org.h2.tools.RunScript with the generated .sql file. I used DBVisualizer to connect the newly generated .db file successfully, I can see the tables and data inside, but after replacing the .db file with the new h2db, the http 404 error .mv.db file appears.Guys can anyone tell me what to do？

[Nic Brough -Adaptavist-]

The h2 database is prone to catastiophic failure, on any form of system error (a crash or having the process killed without shutdown), as well as if it gets large.

This is exactly why all the docs say "for demo/dev/test purposes" - it's on;y usable for a system you don't care about losing.

The failure is usually simply that the data on the fisk gets truncated part way through, so you lose a huge chunk of the database, but it can be garage bytes written to it as well.

The recover tools can repair a truncation in the sense of they can work out how to end the loops to make the file readable again.  But it can't recover the lost data, that's gone.

I suspect that's what has happened to your database.  The repair might be a success as far as h2 is concerned, but a chunk of our data is gone, and Jira can't work without it.

[linrong jiang]

That's too bad, I need to see if the database lost data can be recovered

[Nic Brough -Adaptavist-]

What backups have you been taking?  Copies of the home directory and/or regular XML exports are the only two that might have all your data in them.

Look into 

<Jira home>/database (in your backup file sets)

<Jira home>/backup (on the Jira server - the latest file in there, if it's broadly the same size as the others, is the one to work with)

[linrong jiang]

Both my jira and confluence use the H2 database, but they are separated. Now the jira service is normal. My jira database had the same problem before but there are backup files that can be restored. Now the confluence database is damaged and there is no such thing as backup file,I only have the Confluence/database/h2db.mv.db file

[Nic Brough -Adaptavist-]

Yes, they will be separate - the h2 database is physically a set of files in the <application home>/database directory.  The installers create them when you ask it to use the embedded database.

Without a backup, your Confluence has gone I'm afraid.  There's no way to repair content that just isn't thre in a h2db.mv.db file.

[Benjamin]

Hi @linrong jiang ,

H2 is primarily for evaluation. You may want to consider moving to Postgres or mysql down the line. 

Would recommend contacting Atlassian support if your instance is licensed.

https://support.atlassian.com/contact/#/

-Ben

[Pramodh M]

Hi @linrong jiang 

Welcome to the Community!!

Do you have XML backup files in place?

It will be in backups folder if its automatic backup in home directory.

You can setup new instance with same version and restore the data along with attachments

Let me know

Thanks,
Pramodh

[linrong jiang]

I don't have a backup file. If I can restore this time, I must set up automatic backup, and I have to migrate the database. The H2 database is not stable enough.

[Pramodh M]

Yes I agree, consider moving to the prod database!!