Upgrade to Confluence with MySQL Fails Due to java.sql.SQLException: Incorrect string value

Still need help?

The Atlassian Community is here for you.

Ask the community

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

  1. Rollback Confluence (with its database) to pre-upgrade state
  2. Check the MySQL repair guide to fix the charset, encoding and the storage of your database.
  3. Backup the fixed database.
  4. Re-run the upgrade again

Last modified on Mar 30, 2016

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.