I have a Confluence Wiki and it's basic database that comes with it is HSQLDB. We don't use that in production Wiki but in internal coding tip and documentation Wiki.
But unfortenately Server's diskspace was low and so HSQLDB was crashed. Yes, that was partly our own fault.
But in directory where HSQLDB resides there where several files:
So confluencedb.script.new was wanished and confluencedb.log's size was tiny. Before Manager actions it was 40 Mb.
When testing these files in Confluence Wiki, modifications before crash day were lost.
Before this operation I wrote above, I opened confluence.script.new using Notepad++ and noticed there was newest modifications. But after using hsqldb's Manager I couldn't find those modifications in confluencedb.script file.
I ask this question here because this is HSQLDB related question not Confluence.
Can anyone tell me what are the phases to repair database when there is file.script and file.script.new files?
I didn't find anything when Googling internet which would tell this kind of crash and how to solve it.
I would be very pleased to get information how to manage this situation and get database working again.
Thanks for all,
Michael
Last edit: Michael Laant 2015-10-01
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
The database in Confluence has memory tables which keep all the data in memory. Naturally when the database gets large it uses all the allocated JVM memory and cannot continue to operate. When you open such a database outside Confluence, you should increase the -Xmx setting to allow loading all the data. Without this the data may not fully load from the confluence.log file.
To reduce memory usage, you don't need to move to PostgreSQL or any other database engine. All you have to do is change the large tables to CACHED (disk) tables. Open the database in DatabaseManager and execute this command for the largest tables only:
SET TABLE <large table name> TYPE CACHED
Then execute this:
CHECKPOINT
You will see a new confluence.data file appear and a smaller confluence.script file. The database can then be used in Confluence and the data can grow ten times or more without error.
Last edit: Fred Toussi 2015-10-01
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
confluencedb.log file is missing. I opened confluencedb.log file using editor and noticed that there were dates and Image references day before Crash. But after running Manager confluencedb.data didn't contain any of those dates and Image references.
So, what could went wrong that dates and Images were wanished. I didn't found them .script file aither.
Best Regards,
Michael
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Before opening with DatabaseManager, make sure the modified=yes property setting is in the .properties file.
When you open with DatabaseManager, append ";hsqldb.full_log_replay=true" to the database path. This should stop processing the .log when it hits an error and allow you to manually repair the .log file until those lines with the new dates have been processed.
After successful processing, there will be no .log file.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hello,
I have a Confluence Wiki and it's basic database that comes with it is HSQLDB. We don't use that in production Wiki but in internal coding tip and documentation Wiki.
But unfortenately Server's diskspace was low and so HSQLDB was crashed. Yes, that was partly our own fault.
But in directory where HSQLDB resides there where several files:
confluencedb.script
confluencedb.script.new
confluencedb.properties
confluencedb.log
confluencedb.lobs
Every one of those files has content.
But now we would like repair database into better shape and then move it into Postgres.
I tried to use hsqldb.jar file's Manger to repair that database. After that there was
following files left:
confluencedb.script
confluencedb.properties
confluencedb.log
confluencedb.lobs
So confluencedb.script.new was wanished and confluencedb.log's size was tiny. Before Manager actions it was 40 Mb.
When testing these files in Confluence Wiki, modifications before crash day were lost.
Before this operation I wrote above, I opened confluence.script.new using Notepad++ and noticed there was newest modifications. But after using hsqldb's Manager I couldn't find those modifications in confluencedb.script file.
I ask this question here because this is HSQLDB related question not Confluence.
Can anyone tell me what are the phases to repair database when there is file.script and file.script.new files?
I didn't find anything when Googling internet which would tell this kind of crash and how to solve it.
I would be very pleased to get information how to manage this situation and get database working again.
Thanks for all,
Michael
Last edit: Michael Laant 2015-10-01
The database in Confluence has memory tables which keep all the data in memory. Naturally when the database gets large it uses all the allocated JVM memory and cannot continue to operate. When you open such a database outside Confluence, you should increase the -Xmx setting to allow loading all the data. Without this the data may not fully load from the confluence.log file.
To reduce memory usage, you don't need to move to PostgreSQL or any other database engine. All you have to do is change the large tables to CACHED (disk) tables. Open the database in DatabaseManager and execute this command for the largest tables only:
Then execute this:
You will see a new confluence.data file appear and a smaller confluence.script file. The database can then be used in Confluence and the data can grow ten times or more without error.
Last edit: Fred Toussi 2015-10-01
Thousend thanks, Fred!
I will try this tomorrow in my environment.
Best Regards,
Michael
Still one question, where can find those table names that need SET TABLE <large table="" name=""> TYPE CACHED?
Best regards
Michael
Just look at the .script file and see which tables have a lot of text in INSERT INTO ... lines. I think there are a couple of very large tables.
Last edit: Fred Toussi 2015-10-01
Hello,
I tried to recover database files using Manager and SET TABLE <table_name" TYPE CACHED and after that making CHECKPOINT.
Result files were following (original Crash ones were on the top of this topic)
confluencedb.data
confluencedb.lobs
confluencedb.properties
confluencedb.script
confluencedb.log file is missing. I opened confluencedb.log file using editor and noticed that there were dates and Image references day before Crash. But after running Manager confluencedb.data didn't contain any of those dates and Image references.
So, what could went wrong that dates and Images were wanished. I didn't found them .script file aither.
Best Regards,
Michael
Before opening with DatabaseManager, make sure the modified=yes property setting is in the .properties file.
When you open with DatabaseManager, append ";hsqldb.full_log_replay=true" to the database path. This should stop processing the .log when it hits an error and allow you to manually repair the .log file until those lines with the new dates have been processed.
After successful processing, there will be no .log file.