Upgrade to Confluence with MySQL Fails Due to java.sql.SQLException: Incorrect string value
Symptoms
This KB only applies if your Confluence is hooked to MySQL database
Upgrading to Confluence 4.x fails. You'll get prompted with the following error when trying to access Confluence instance:
Unable to start up Confluence. Fatal error during startup sequence: confluence.lifecycle.core:pluginframeworkdependentupgrades (Run all the upgrades that require the plugin framework to be available) - com.atlassian.confluence.content.render.xhtml.migration.exceptions.MigrationException: java.util.concurrent.ExecutionException: org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not update: com.atlassian.confluence.core.BodyContent#3310833; uncategorized SQLException for SQL []; SQL state [HY000]; error code [1366]; Incorrect string value: '\xEF\xBF\xBD\x09In...' for column 'BODY' at row 1; nested exception is java.sql.SQLException: Incorrect string value: '\xEF\xBF\xBD\x09In...' for column 'BODY' at row 1
The following appears in the atlassian-confluence.log
:
2012-06-29 13:44:14,761 ERROR [WikiToXhtmlMigration:thread-2] [sf.hibernate.util.JDBCExceptionReporter] logExceptions Incorrect string value: '\xEF\xBF\xBD\x09In...' for column 'BODY' at row 1
2012-06-29 13:44:14,761 ERROR [WikiToXhtmlMigration:thread-2] [sf.hibernate.impl.SessionImpl] execute Could not synchronize database state with session
2012-06-29 13:44:17,666 FATAL [main] [atlassian.config.lifecycle.LifecycleManager] panicAndShutdown Unable to start up Confluence. Fatal error during startup sequence: confluence.lifecycle.core:pluginframeworkdependentupgrades (Run all the upgrades that require the plugin framework to be available) - com.atlassian.confluence.content.render.xhtml.migration.exceptions.MigrationException: java.util.concurrent.ExecutionException: org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not update: [com.atlassian.confluence.core.BodyContent#3310833]; uncategorized SQLException for SQL []; SQL state [HY000]; error code [1366]; Incorrect string value: '\xEF\xBF\xBD\x09In...' for column 'BODY' at row 1; nested exception is java.sql.SQLException: Incorrect string value: '\xEF\xBF\xBD\x09In...' for column 'BODY' at row 1
com.atlassian.confluence.content.render.xhtml.migration.exceptions.MigrationException: java.util.concurrent.ExecutionException: org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not update: [com.atlassian.confluence.core.BodyContent#3310833]; uncategorized SQLException for SQL []; SQL state [HY000]; error code [1366]; Incorrect string value: '\xEF\xBF\xBD\x09In...' for column 'BODY' at row 1; nested exception is java.sql.SQLException: Incorrect string value: '\xEF\xBF\xBD\x09In...' for column 'BODY' at row 1
at com.atlassian.confluence.content.render.xhtml.migration.DefaultWikiToXhtmlSiteMigrator.migrateSite(DefaultWikiToXhtmlSiteMigrator.java:167)
at com.atlassian.confluence.upgrade.upgradetask.WikiToXhtmlMigrationUpgradeTask.doDeferredUpgrade(WikiToXhtmlMigrationUpgradeTask.java:40)
at com.atlassian.confluence.upgrade.PluginFrameworkDependentUpgrader.runDeferredUpgradeTasks(PluginFrameworkDependentUpgrader.java:56)
at com.atlassian.confluence.upgrade.PluginFrameworkDependentUpgrader.startup(PluginFrameworkDependentUpgrader.java:25)
at com.atlassian.config.lifecycle.DefaultLifecycleManager.startUp(DefaultLifecycleManager.java:40)
at com.atlassian.config.lifecycle.LifecycleServletContextListener.contextInitialized(LifecycleServletContextListener.java:17)
.....
Caused by: java.util.concurrent.ExecutionException: org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not update: [com.atlassian.confluence.core.BodyContent#3310833]; uncategorized SQLException for SQL []; SQL state [HY000]; error code [1366]; Incorrect string value: '\xEF\xBF\xBD\x09In...' for column 'BODY' at row 1; nested exception is java.sql.SQLException: Incorrect string value: '\xEF\xBF\xBD\x09In...' for column 'BODY' at row 1
at java.util.concurrent.FutureTask$Sync.innerGet(Unknown Source)
at java.util.concurrent.FutureTask.get(Unknown Source)
at com.atlassian.confluence.content.render.xhtml.migration.DefaultWikiToXhtmlSiteMigrator.migrateSite(DefaultWikiToXhtmlSiteMigrator.java:148)
... 20 more
Caused by: org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not update: [com.atlassian.confluence.core.BodyContent#3310833]; uncategorized SQLException for SQL []; SQL state [HY000]; error code [1366]; Incorrect string value: '\xEF\xBF\xBD\x09In...' for column 'BODY' at row 1; nested exception is java.sql.SQLException: Incorrect string value: '\xEF\xBF\xBD\x09In...' for column 'BODY' at row 1
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
.....
Caused by: java.sql.SQLException: Incorrect string value: '\xEF\xBF\xBD\x09In...' for column 'BODY' at row 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1072)
.....
Cause
This is caused by an incorrect database character encoding, most commonly is set to latin1
. We only support UTF8 character encoding as described here.
Diagnosis
Check your Confluence database encoding by running this query (taken from here):
USE your_database_of_interest;
SELECT TABLE_SCHEMA,
TABLE_NAME,
CCSA.CHARACTER_SET_NAME AS DEFAULT_CHAR_SET,
COLUMN_NAME,
COLUMN_TYPE,
C.CHARACTER_SET_NAME
FROM information_schema.TABLES AS T
JOIN information_schema.COLUMNS AS C USING (TABLE_SCHEMA, TABLE_NAME)
JOIN information_schema.COLLATION_CHARACTER_SET_APPLICABILITY AS CCSA
ON (T.TABLE_COLLATION = CCSA.COLLATION_NAME)
WHERE TABLE_SCHEMA=SCHEMA()
AND C.DATA_TYPE IN ('enum', 'varchar', 'char', 'text', 'mediumtext', 'longtext')
ORDER BY TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME;
Resolution
- Rollback Confluence (with its database) to pre-upgrade state
- Check the MySQL repair guide to fix the charset, encoding and the storage of your database.
- Backup the fixed database.
Re-run the upgrade again