Why you should not use the default HSQLDB backend with Confluence · Jul 19, 09:18 PM
Confluence clearly states on their web site that users should not use Confluence with the default HSQLDB backend in production. However, they fail to list any reason why HSQLDB should not be used. This may lead some people (like me) into thinking that HSQLDB will be ok for the long term since they do not list any reasons for their statement.
So we stuck with the HSQLDB back end for our Wiki. It was bad. After a few months we did have some data integrity problems (fortunately, with records that had been deleted), but even this was enough to break the built-in Hibernate-based backups and force us to migrate.
The migration was hard too. Why:
- Atlassian does not publish decent tools on their site (unlike other companies that build their products on Confluence) to help you easily migrate away from HSQLDB to other databases
- For MySQL, Atlassian recommends using the MySQL migration tools to migrate from HSQLDB to MySQL; these tools work very well but have a java heap limit of 384 MB, so only smaller databases can be migrated using this toolkit.
- If any data integrity problems occur and a foreign key relationship is corrupted within the data in the database, the built-in Hibernate-based backups will no longer work as Hibernate will abort at the first referential integrity constraint it finds.
- Data integrity problems meant we had to:
- Create the MySQL database with no constraints
- Migrate from the embedded HSQLDB to a standalone instance so we could access the data with JDBC
- Write custom scripts to dump data from JDBC
- Munge the SQL INSERT data to convert from java Unicode to MySQL Unicode encoding
- Import all records back into the MySQL DB
- Re-add constraints one at a time
Some of the challenges we ran into:
- Our database well exceeded 384 MB rendering the MySQL migration tools useless
- HSQLDB uses java escape sequences for UTF-8 characters; we ended up using java code to convert those sequences from the HSQLDB script file to binary for our MySQL import file as MySQL wants to have either \x encoded sequences or raw binary.
- Had to migrate from the embedded version of HSQLDB to the standalone version to allow for our hand-written migration code to be able to access the HSQLDB instance via JDBC.
I really wish Atlassian would list some of these potential problems on their site, had they given more than just ‘do not use it’ we would have listened :).
— Max Schubert
Comment
How come I get 'no server suitable for synchronization found' from my NTP client when the server is returning a valid NTP response to the client? Nagios 3 Enterprise Network Monitoring Book