Why do I get database schema errors while trying to perform a confluence update?

[Arnd P_]

Hi,

I'm using Confluence Server 6.9.3, MySQL 5.7, Oracle JDK 8.

I'm trying to upgrade Confluence Server from 6.9.3 to 6.10.1 but unfortunately Confluence always throws the same errors:

Pre-Upgrade recovery file generation failed. Please refer to https://confluence.atlassian.com/x/ropKGQ for possible solution.

2018-08-10 18:46:46

fatal

Failed to generate recovery file

The Confluence log says:

org.springframework.jdbc.BadSqlGrammarException: ConnectionCallback; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'confluence.AO_954A21_PUSH_NOTIFICATION_AO' doesn't exist

So I  set

-Dconfluence.upgrade.recovery.file.enabled=false

but now I have another database error saying:

Pre-upgrade validation failed. Check the Confluence application logs for more details. You'll need to fix these problems and restart Confluence before you can upgrade. Upgrade error message: Cannot update schema

2018-08-10 20:09:10

error

2018-08-10 20:09:10,422 ERROR [localhost-startStop-1] [engine.jdbc.spi.SqlExceptionHelper] logExceptions Table 'confluence.DIAGNOSTICS_ALERTS' doesn't exist
2018-08-10 20:09:10,429 ERROR [localhost-startStop-1] [atlassian.confluence.plugin.PluginFrameworkContextListener] launchUpgrades Upgrade failed, application will not start: com.atlassian.config.ConfigurationException: Cannot update schema
com.atlassian.confluence.upgrade.UpgradeException: com.atlassian.config.ConfigurationException: Cannot update schema
        at com.atlassian.confluence.upgrade.AbstractUpgradeManager.upgrade(AbstractUpgradeManager.java:133)
        at com.atlassian.confluence.plugin.PluginFrameworkContextListener.launchUpgrades(PluginFrameworkContextListener.java:126)
        at com.atlassian.confluence.plugin.PluginFrameworkContextListener.contextInitialized(PluginFrameworkContextListener.java:86)
        at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4627)
        at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5091)
        at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
        at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1427)
        at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1417)
        at java.util.concurrent.FutureTask.run(FutureTask.java:266)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)

Any ideas? Is DIAGNOSTICS_ALERT a table from the base system or is it related to a plugin?

I already deleted the plugin cache folders in my confluence home.

Cheers

Arnd

[AnnWorley · Accepted solution]

In my test instances, the diagnostics_alerts table exists in my Confluence 6.10 and above instances but not in my 6.9.x instances. It sounds like your database and home directory were not restored from back-up fully after the failed upgrades if the upgrade tasks are expecting that table.

To eliminate another possible issue, please make sure case sensitivity is not a problem: Upgrade fails 'java.sql.SQLException Table confluence.BANDANA doesn't exist' due to case sensitivity

[Arnd P_]

Hi Ann,

thanks for your answer!

I checked my database for collation, it is 'utf8_bin'. Furthermore I checked my database tables and DIAGNOSTICS_ALERT or diagnostics_altert don't exist.

Is there any chance that this can be fixed manually?

Unfortunately the mentioned table can't be found in the database diagram -> https://confluence.atlassian.com/doc/confluence-data-model-127369837.html#ConfluenceDataModel-Databasediagrams

I really expected the update mechanism of confluence to do a jdbc rollback to keep the database in an untouched stated after an error occurred.

I will try to set up an instance with the data from my backup before the upgrade obviously failed and try to find out what went wrong.  

Cheers

Arnd

[AnnWorley]

When an upgrade fails the database and Confluence "home" directories have to be manually restored from backup to get the upgrade to run again properly. 

[Arnd P_]

Hi Ann,

I used my backup and tried to upgrade again and now it's working. Unfortunately I had to use a backup from last week, before the upgrade obviously failed. - The DIAGNOSTIC_ALERTS and AO_954A21_PUSH_NOTIFICATION_AO table existed as .frm and .ibd files but the InnoDB system tablespace of my MySQL database was damaged, so the table was not accessible. 

Cheers 

Arnd

[AnnWorley]

Thank you so much for following up with the details! It should  be helpful for those in similar situations in the future. :)