Update from Confluence Server 7.10.2 to 7.11 Fails

[europoint]

From hub.docker.com, using the Confluence-Server:latest pull.

An upgrade from 7.10.2 to 7.11 fails: The front end reports:

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

The atlassian-confluence.log reports:

Caused by: java.sql.SQLSyntaxErrorException: Unknown column 'this_.external_id' in 'field list'
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
...
2021-02-06 10:44:31,827 ERROR [perm-delta-cache-receiver] [engine.jdbc.spi.SqlExceptionHelper] logExceptions Unknown column 'this_.external_id' in 'field list'
2021-02-06 10:44:31,828 WARN [perm-delta-cache-receiver] [confluence.impl.hibernate.ConfluenceHibernateTransactionManager] doRollback Performing rollback. Transactions:
->[PluginReadWriteTx]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT (Session #633154150)
2021-02-06 10:44:31,829 ERROR [perm-delta-cache-receiver] [gatekeeper.evaluator.cache.CacheUpdateReceiver] error Failed to initialize evaluator cache
org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet

Unable to upgrade as a result.

[Alexis Robert]

Hi @europoint , 

there is an old KB on Atlassian documentation that references this error : https://confluence.atlassian.com/confkb/unknown-column-this-external_id-when-upgrading-to-confluence-5-2-5-or-above-on-mysql-427623662.html

Are you using a MySQL database by any chance ? If yes I'd try the recommended fix from Atlassian.

Let me know if this helps, 

--Alexis

[europoint]

@Alexis RobertThank you for finding that KB!

Unfortunately, it hasn't resolved the issue. The schema, table and columns are all configured for UTF8 for my Confluence database

[europoint]

I retried it by taking a backup of the database and converting it to utf8mb4 character set and collation, then starting up 7.11 and got the same issue.

I also explicitly stated the character set and collation in the JDBC connection, whether using utf8 or utf8mb4, but the error is the same.

->[PluginReadWriteTx]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT (Session #1270354319)
2021-02-13 14:54:14,760 ERROR [perm-delta-cache-receiver] [gatekeeper.evaluator.cache.CacheUpdateReceiver] error Failed to initialize evaluator cache
org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet

It seems I am stuck at 7.10.2 unless anyone is able to identify the resolution?

[Alexis Robert]

Hi @europoint , 

from another thread with MS SQL Server, looks like it could be because of permissions from the database user.

Can you try checking the permissions, and if that fails create a new user with full privileges on Confluence tables ?

[europoint]

Thanks @Alexis Robert for digging around for me again.

The user had full rights to all my Atlassian DBs. I dropped grants for everything except my Confluence DB, and also gave the user Global privileges, but the same error occurs. I had read something about it potentially being a conflict between access to more than one schema, but evidently that also wasn't the problem.

[Alexis Robert]

It might just be an issue with the latest Docker image then, you should maybe wait for the next version then. 

[europoint]

No answer, but I believe I have found the root cause:

Confluence 7.11 attempts to upgrade the schema for the Confluence database. I am currently using MySQL and have Bitbucket, Jira, Confluence and Crowd all on the same database server and the same user to connect to all of these db instances.

The error in question is below:

2021-02-18 08:12:17,362 ERROR [Catalina-utility-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:157)
at com.atlassian.confluence.plugin.PluginFrameworkContextListener.launchUpgrades(PluginFrameworkContextListener.java:122)
at com.atlassian.confluence.plugin.PluginFrameworkContextListener.contextInitialized(PluginFrameworkContextListener.java:82)
at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4716)
at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5177)
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1384)
at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1374)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: com.atlassian.config.ConfigurationException: Cannot update schema
at com.atlassian.confluence.impl.core.persistence.hibernate.schema.ConfluenceSchemaHelper.validateSchemaUpdateIfNeeded(ConfluenceSchemaHelper.java:85)
at com.atlassian.confluence.upgrade.impl.DefaultUpgradeManager.validateSchemaUpdateIfNeeded(DefaultUpgradeManager.java:73)
at com.atlassian.confluence.upgrade.AbstractUpgradeManager.upgrade(AbstractUpgradeManager.java:138)
... 13 more
Caused by: org.hibernate.tool.schema.extract.spi.SchemaExtractionException: More than one table found in namespace (, ) : cwd_synchronisation_status
at org.hibernate.tool.schema.extract.internal.InformationExtractorJdbcDatabaseMetaDataImpl.processTableResults(InformationExtractorJdbcDatabaseMetaDataImpl.java:491)
at org.hibernate.tool.schema.extract.internal.InformationExtractorJdbcDatabaseMetaDataImpl.getTable(InformationExtractorJdbcDatabaseMetaDataImpl.java:264)
at org.hibernate.tool.schema.extract.internal.DatabaseInformationImpl.getTableInformation(DatabaseInformationImpl.java:111)
at org.hibernate.tool.schema.internal.IndividuallySchemaMigratorImpl.performTablesMigration(IndividuallySchemaMigratorImpl.java:69)
at com.atlassian.confluence.impl.hibernate.ConfluenceHibernateSchemaManagementTool$3.performTablesMigration(ConfluenceHibernateSchemaManagementTool.java:110)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.performMigration(AbstractSchemaMigrator.java:207)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:114)
at org.hibernate.tool.hbm2ddl.SchemaUpdate.execute(SchemaUpdate.java:94)
at org.hibernate.tool.hbm2ddl.SchemaUpdate.execute(SchemaUpdate.java:62)
at com.atlassian.confluence.impl.core.persistence.hibernate.schema.ConfluenceSchemaHelper.validateSchemaUpdateIfNeeded(ConfluenceSchemaHelper.java:82)
... 15 more
2021-02-18 08:12:17,364 ERROR [Catalina-utility-1] [atlassian.confluence.plugin.PluginFrameworkContextListener] launchUpgrades 1 errors were encountered during upgrade:
2021-02-18 08:12:17,364 ERROR [Catalina-utility-1] [atlassian.confluence.plugin.PluginFrameworkContextListener] launchUpgrades 1: Cannot update schema

 Looking at the various databases, both Crowd and Jira have a cwd_synchronisation_status table.

I removed the grants from both Jira and Crowd for the user, and retried, but then got an error stating that the user was denied access to the table.

I then reinstated the grants just for the Crowd database, and got the same error as displayed in the code above.

I also tested by creating a completely new user for the Confluence db instance, and still received the same error.

There appears to be an issue in the schema upgrade for Confluence which does not limit itself to the Confluence db instance. I'm not sure if this is a specific issue with MySQL or with the schema upgrade itself.

[Alexis Robert]

Hi @europoint , 

looking at the latest error message you provided, I think this KB could apply : https://confluence.atlassian.com/confkb/upgrade-failed-application-will-not-start-com-atlassian-config-configurationexception-cannot-update-schema-947849026.html

It has the same error message as you have, so you could use the workaround provided.

Otherwise, I would advise getting in touch with atlassian support : http://getsupport.atlassian.com/

[europoint]

@Alexis RobertI would like to thank you for your perseverence in trying to resolve my issue: it's very much appreciated.

In the end I resorted to drastic measures:

1. Take a backup of the Confluence database.
2. Deploy a Docker container for MySQL.
3. Restore the Confluence database backup to the new MySQL server.
4. Deploy a Docker container of the latest version of Confluence against the new MySQL server.

This then carried out the upgrade without issue. The next steps were:

1. Take a backup of the new Confluence instance.
2. Restore the new backup to the original MySQL server.
3. Delete the Docker containers for MySQL and the latest version of Confluence.
4. Redeploy my Atlassian Docker stack with the latest version of Confluence against the original MySQL server.

I suspect the issue is down to the upgrade script assuming that the Confluence database is the only database on the server, and searching across the entire db namespace. It failed because it identified a table in my Bitbucket database. By doing an upgrade on the Confluence database in an isolated MySQL server instance, it therefore had no other databases it could look at.

I believe this is a defect in the upgrade process: It's entirely valid to have multiple Atlassian products using the same MySQL server instance, and using the same database user to access them.

[Alexis Robert]

Good to know that you managed to carry out your upgrade ! 

If this is an issue with the Docker images, maybe @Dave Chevell  can help.

[serge calderara]

Hello @Alexis Robert I get the same issue.

I was runing confluene datacenter 7.10 on SQL server without any issue.
Then I download confluence 7.12.0 package and update necessary config file then start the instance.

I get the same sql error what was initially reported by @europoint 

Any idea ?