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

[Michael Hiller]

Hi,

I have upgraded confluence 5.6 to 6.13.
I already fixed a number of errors before, such as setting <property name="hibernate.dialect">org.hibernate.dialect.H2Dialect</property> 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
"
<Host name="localhost" debug="0" appBase="webapps" unpackWARs="true" autoDeploy="false" startStopThreads="4">
<Context path="" docBase="../confluence" debug="0" reloadable="false" useHttpOnly="true">
<!-- Logging configuration for Confluence is specified in confluence/WEB-INF/classes/log4j.properties -->
<Manager pathname=""/>
<Valve className="org.apache.catalina.valves.StuckThreadDetectionValve" threshold="60"/>
</Context>

<Context path="${confluence.context.path}/synchrony-proxy" docBase="../synchrony-proxy" debug="0"
reloadable="false" useHttpOnly="true">
<Valve className="org.apache.catalina.valves.StuckThreadDetectionValve" threshold="60"/>
</Context>
</Host>
"

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]

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

[Michael Hiller]

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]

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