問題

When trying to upgrade Confluence from version 5.8.10 to any newer release, for example, 5.9.x or 6.x, a database error regarding an unknown column is logged and then the upgrade path is locked. 

During the upgrade process, below error appears. It might also appear when trying to restore the instance to its previous version, the one used before the upgrade.

caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)
caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'spacepermi0_.PERMALLUSERSSUBJECT' in 'field list'
    at sun.reflect.GeneratedConstructorAccessor281.newInstance(Unknown Source)

2019-08-16 00:02:51,530 ERROR [Catalina-utility-2] [atlassian.confluence.plugin.PluginFrameworkContextListener] launchUpgrades Upgrade failed, application will not start: Upgrade task com.atlassian.confluence.upgrade.upgradetask.PopulateLowerFieldsUpgradeTask@147e3137 failed during the SCHEMA_UPGRADE phase due to: StatementCallback; bad SQL grammar [update CONTENT set LOWERTITLE = lower(TITLE) where (TITLE is not null) and CONTENTID >= 98305 and CONTENTID <= 720898]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'LOWERTITLE' in 'field list'
com.atlassian.confluence.upgrade.UpgradeException: Upgrade task com.atlassian.confluence.upgrade.upgradetask.PopulateLowerFieldsUpgradeTask@147e3137 failed during the SCHEMA_UPGRADE phase due to: StatementCallback; bad SQL grammar [update CONTENT set LOWERTITLE = lower(TITLE) where (TITLE is not null) and CONTENTID >= 98305 and CONTENTID <= 720898]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'LOWERTITLE' in 'field list'
	at com.atlassian.confluence.upgrade.AbstractUpgradeManager.executeUpgradeStep(AbstractUpgradeManager.java:236)
	at com.atlassian.confluence.upgrade.AbstractUpgradeManager.runSchemaUpgradeTasks(AbstractUpgradeManager.java:192)
	at com.atlassian.confluence.upgrade.AbstractUpgradeManager.upgrade(AbstractUpgradeManager.java:142)
...
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'LOWERTITLE' in 'field list'
	at sun.reflect.GeneratedConstructorAccessor297.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.Util.getInstance(Util.java:408)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3976)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3912)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2482)
	at com.mysql.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1552)
	at com.mysql.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2607)
	at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1480)
	at com.mchange.v2.c3p0.impl.NewProxyStatement.executeUpdate(NewProxyStatement.java:410)
	at org.springframework.jdbc.core.JdbcTemplate$1UpdateStatementCallback.doInStatement(JdbcTemplate.java:532)
	at org.springframework.jdbc.core.JdbcTemplate$1UpdateStatementCallback.doInStatement(JdbcTemplate.java:529)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:408)

原因

As the tables suffering from the error have only been introduced on newer versions of Confluence, when the upgrade process fails, the database does not have the needed table to be referenced by the application. The documented causes at the moment that might cause this issue are the ones below. 

1. An expired license.
2. Bad collation settings on your database.
3. Hyphen characters in the database name.

診断

環境

• This might happen on Confluence Server only and in any environment, but has been seen the most when using MySQL as the database. 
• Interesting to note that the column name in the error log might change, it depends on the version we are upgrading from and the version we are upgrading to.

Diagnostic Steps

• A migration attempt from version 5.x to a newer version didn't work. During the process a hibernate exception related to a failure when extracting a set of results happened.
• When trying to restore the instance to the original version that it used to have before the upgrade, it keeps failing with the same error when trying to start it up.

• Run below queries on your database, if they return results, proceed to Resolution - Collation Settings.

  Queries to Identify Collation Issues

  USE information_schema;
  
  SELECT schema_name, default_character_set_name, default_collation_name FROM schemata WHERE schema_name = 'confluence' AND (default_character_set_name <> 'utf8' OR default_collation_name <> 'utf8_bin');
  
  SELECT table_name, table_collation, engine FROM tables WHERE table_schema = '<DATABASE_NAME>' AND table_collation <> 'utf8_bin';
  
  SELECT table_name, column_name, character_set_name, collation_name FROM columns WHERE table_schema = '<DATABASE_NAME>' and data_type in ('varchar', 'text') and (character_set_name <> 'utf8' OR collation_name <> 'utf8_bin');

• Check Confluence logs to see if after the upgrade process started below message was logged. If it appeared, proceed to Workaround - Expired License.

  License Issue - Error Log

  2017-06-09 10:03:47,155 ERROR [localhost-startStop-1] [confluence.upgrade.impl.DefaultUpgradeManager] runUpgradePrerequisites Current license is not valid: SUPPORT_EXPIRED
  
  2017-06-09 10:03:47,824 ERROR [localhost-startStop-1] [atlassian.confluence.plugin.PluginFrameworkContextListener] launchUpgrades 1: <p>Confluence support and upgrades for your current license have expired.</p><p>Please access <a href="http://my.atlassian.com">My Atlassian</a> as the license contact and purchase a license renewal for Confluence. If you have a new license, please enter it on this <a href="$CONTEXT/fixonly/fixlicense.action">page</a> and <b>restart</b>.</p><p>Alternatively, you can continue using Confluence by rolling back to a previous version. To do this you must perform a clean installation of Confluence of the version that needs to be restored then restore the confluence home directory to its pre-upgrade state. No database restorations need to be made.</p>

• Check whether your database name contains a hyphen character (e.g. confluence-5.7)

Workaround - Expired License

The expired license issue might require a workaround to get your instance working again. First, ensure that the license in question is indeed valid prior proceeding with next steps. In case it is not, a renewal needs to take place. In case it is, proceed.

• Revert your instance to the version that was in place before the upgrade.
• Ensure all is up and running after the rollback.
• Access MyAtlassian, look for you latest SEN and expand it. Copy your license key.
• Open the following URL at your browser by replacing it with your Confluence base one: http://<confluence_base_url>/fixonly/fixlicense.action
• The prompts on this page will help you to update your license key to the latest one.
• Once that is done, you will need to restart Confluence.
• After the restart, upgrade Confluence again and it should go through just fine.

Resolution - Collation Settings

If at least one object in the Confluence database does not have the UTF-8 encoding and/or UTF-8_BIN collation, then the SQL Grammar Exception might occur.

• Check How to Fix the Collation and Character Set of your Database.

Resolution - Database name containing a hyphen character

• Revert your instance to the version that was in place before the upgrade.
• Ensure all is up and running after the rollback.
• Shutdown Confluence and work with your DBA to rename or migrate the Confluence database to a database name that does not contain the hyphen character
• Make sure that the Confluence DB user has the relevant read/write permissions on the new database name
• Update <confluence-home>/confluence.cfg.xml to point to the new database name without the hyphen character
• After the restart, upgrade Confluence again and it should go through just fine