upgrade from confluence 5.6 to 6.13 fails with org.hibernate.exception.SQLGrammarException

Question

Hi, I have upgraded confluence 5.6 to 6.13. I already fixed a number of errors before, such as setting org.hibernate.dialect.H2Dialect in confluence.cfg.xml, changing the database and HTTP threads, JVM memory requirements etc. We have an embedded database (I think), at least conf/server.xml does not list any of mysql, oracle, microsoft or postgresql " " After upgrading and the above mentioned fixes, I now get the following error message in the webbrowser: " Upgrade failed. Please consult the system logs for details. You will need to fix these problems, restore your database and confluence home directory to the pre upgrade state. Then retry the upgrade. Upgrade error message: Upgrade task com.atlassian.confluence.upgrade.upgradetask.RelationConstraintsPreSchemaUpgradeTask@311518cd failed during the SCHEMA_UPGRADE phase due to: StatementCallback; bad SQL grammar [SELECT CONSTRAINTS.TABLE_NAME, CONSTRAINT_NAME, COLUMNS.COLUMN_NAME FROM INFORMATION_SCHEMA.CONSTRAINTS, INFORMATION_SCHEMA.COLUMNS WHERE COLUMNS.TABLE_NAME = 'USERCONTENT_RELATION' AND CONSTRAINT_TYPE = 'UNIQUE' AND COLUMNS.TABLE_NAME = CONSTRAINTS.TABLE_NAME AND COLUMN_LIST REGEXP concat('^', COLUMNS.COLUMN_NAME, ',|,', COLUMNS.COLUMN_NAME, ',|,', COLUMNS.COLUMN_NAME, '$|^', COLUMNS.COLUMN_NAME, '$')]; nested exception is java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: CONSTRAINTS 2019-10-11 20:57:16 error Unable to start up Confluence. Fatal error during startup sequence: confluence.lifecycle.core:mananagedjobs (Start and stop the Managed Scheduled Jobs) 2019-10-11 20:59:12 fatal could not prepare statement; SQL [select schedulerr0_.id as id1_51_, schedulerr0_.job_id as job_id2_51_, schedulerr0_.start_time as start_ti3_51_, schedulerr0_.duration as duration4_51_, schedulerr0_.outcome as outcome5_51_, schedulerr0_.message as message6_51_ from scheduler_run_details schedulerr0_ where schedulerr0_.job_id=? order by schedulerr0_.start_time desc limit ?]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement " I would appreciate any help. Thanks a lot - Michael

Shannon S · Answer

Michael,

Thank you for confirming that!

It's definitely possible to continue to run Confluence on the evaluation database, but it's not recommended as there would be no way to recover the data should it become corrupt, and it can cause other issues with performance. The particular error you are running into is a common issue with the evaluation database and is solved by migrating to a supported production database.

Regards,

Shannon

Michael Hiller · Answer

OK, thanks, I'll give that a try.

btw: We have been using the build-in H2 database for almost a decade now without any problems. Therefore I assumed it would also work with upgraded confluence versions (as it did in the past).

Shannon S · Answer

Hello Michael,

Thank you for contacting us about this.

The Confluence Embedded H2 database is only meant to be used during evaluation, and is not supported in the case of upgrade.

My recommendation would be to first roll back your upgrade by restoring the last site backup you made prior to the upgrade. Next step would then be to migrate away from the H2 database to a supported database.

Once you confirm the migration worked, then you can proceed with your upgrade.

Let me know if you have any trouble with that!

Regards,

Shannon