Upgrading Confluence will throw an error about Bad SQL Grammar Nested Exception
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Symptoms
After upgrading Confluence from 3.4.x or an older version to 3.5.x or a newer version, the following error appears.
org.springframework.jdbc.BadSqlGrammarException: Hibernate operation:
Unable to perform find; bad SQL grammar []; nested exception is
java.sql.SQLException: Invalid object name 'cwd_application'.
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:107)
caused by: java.sql.SQLException: Invalid object name 'cwd_application'.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368) Additionally, if a user tries to log in, a similar error can occur but this time pointing to the CWD_USER table
Cause
The CWD_* tables weren't created properly in the upgrade process.
In 3.5.x, new user management was introduced using Crowd architecture to manage users in Confluence and implementing user directories in order to facilitate user management and integration between applications.
There are some known causes for that:
The database from an older version was imported directly into 3.5.x or newer Confluence version database and thus, the upgrade automated steps didn't run as expected.
The build number (<confluence-home>/confluence.cfg.xml) from the current version of Confluence is incorrect and this won't trigger the upgrade steps.
The primary keys on the database do not exist. Can occur more often in SQL server but could also happen in other databases.
Resolution for cause 1
Shutdown Confluence
Rollback Confluence to the previous version before the upgrade attempt. (Restore the database backup and home_folder backup)
Proceed with the upgrade steps exactly as in the upgrade documentation.
Resolution for cause 2
Rollback your database to a point pre-upgrade.
Run this query to remove the rows of the extra buildnumbers:
Note: Where ? is the CONFVERSIONID of all extra buildnumbers.
Always back up your data before making any database modifications. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
DELETE FROM CONFVERSION WHERE CONFVERSIONID IN (?,?,...);Be sure that the correct buildnumber of your current Confluence version is set into your confluence.cfg.xml file, which is located at Confluence home.
Try to proceed with the upgrade again.
Resolution for cause 3
Shutdown Confluence
Rollback Confluence to the previous version before the upgrade attempt. (Restore the database backup and home_folder backup)
Create an XML backup and import it into a new Confluence instance with the same version.
Proceed with the upgrade steps exactly as in the upgrade documentation.
Was this helpful?