MySQL Collation Repair: Case Study - Repairing a Production Database

Still need help?

The Atlassian Community is here for you.

Ask the community

This document is part of the guide on How to Fix the Collation and Character Set of a MySQL Database. Please refer to that page for more information.

Table of Contents

In this Document

Before Proceeding

Before proceeding, ensure that you:

  • Have shut down Confluence
  • Have completed a full database backup

You may also wish to apply these changes in a test environment before applying them to production.

Introduction

This guide will set up a Confluence 3.5 instance against a MySQL Database with the incorrect collation and character set. We'll go through the process of Upgrading Confluence to the most recent version (at time of writing, version 5.6) while maintaining the integrity of data in the database. This should hopefully give system Administrators more context on how to run the queries provided throughout the How to Fix the Collation and Character Set of a MySQL Database guide.

Goals

We'll create a new installation of Confluence 3.5.17 against a broken MySQL database. Additionally, we'll also create some content with international character that can cause problems during upgrades (due to the character set used):

  1. A user with an accented character - José
  2. A page with the title "Let's have a ¡Celebración!"

Throughout the upgrade process, we'll remedy the errors in the upgrade process that refer to the database configuration, as well as ensuring the content is correct.

Installing Confluence 3.5.17

Creating the Database

First, we'll create a database with the incorrect character set and encoding:

CREATE DATABASE `conf-broken` CHARACTER SET = 'latin1' COLLATE = 'latin1_swedish_ci';

Installing Confluence

Confluence 3.5.17 can be found at the Confluence Download Archives. Refer to Installing Confluence Standalone for more information. Once Confluence is ready, access it at http://localhost:8080/

Things to Note:

  • The MySQL driver is included in this version of Confluence.
  • Ensure that you select "Production Installation", and that you use a JDBC connection.
  • If you require a license, you can either generate an Evaluation License or a Developer License.

Creating Content

Once you've connected to the Database, Confluence will ask you if you'd like to import any content. Select "Example Site >>" so we have a few pages to work with.

Setting up the System Administrator

Confluence will then ask you for account details for your Admin user. Let's create the user José, with a password and email of your choosing. Ensure that the user name and display name are both set as José:

Creating a new page

Now that you've set up your admin account, it's time to create a page within Confluence:

  1. Click the "Add" button, and select "Page"
  2. Set the title and the content of the page to "Let's have a ¡Celebración!"
  3. Click "Save"
You should end up with something resembling this - note that the title and content is all displayed correctly (despite using the incorrect settings):

Verifying the contents of the database

At this point, let's take a moment to verify that the contents of the database are stored correctly - we'll want to verify this later. Run the following query against your database:

SELECT user_name FROM cwd_user;

You should see that José is displayed correctly in the database - there are no incorrect or invalid characters. Despite using the incorrect collation and character set, the accented character displays correctly.

This is due to two reasons:

  1. While the accented character is available, not all characters are available in the latin1 character set. Most characters are available in utf8.
  2. Different collations sort and behave differently. To ensure a consistent experience, we recommend the use of a single collation - utf8_bin

Upgrading Confluence to version 5.0.3

The next step in upgrading Confluence is to move to 5.0.3. This version ensures that we get all the necessary upgrade tasks run before moving to newer versions of Confluence, as some of the upgrade tasks are removed. As before, you can download Confluence 5.0.3 from the Download Archives. Please see Upgrading Confluence for more information.

It's also important to ensure that your database is configured correctly, in accordance with the Database Setup for MySQL for your version of Confluence. After reading the setup notes, it's clear that our Database isn't in UTF8 format as required by the documentation. Let's fix that:

ALTER DATABASE `conf-broken` CHARACTER SET = 'utf8' COLLATE = 'utf8_bin';

Once you've changed the database collation, proceed with Upgrading Confluence to 5.0.3. After the upgrade has completed, check that your page still displays correctly:

Upgrading Confluence to version 5.6.3

Because Confluence 5.6.3 doesn't come with a driver for MySQL, you'll need to obtain the correct driver from the MySQL Website. Please see Database JDBC Drivers for more information.

Now, let's attempt to visit Confluence. Depending on your specific environment, you may see one of two errors.

If you were logged in before the upgrade

Confluence will attempt to log you in. However, you might receive an error similar to the following:

Click here to expand...
org.springframework.jdbc.BadSqlGrammarException: Hibernate operation: Unable to perform find; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'this.external_id' in 'field list'
	org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:97)
	org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
	org.springframework.orm.hibernate.HibernateAccessor.convertJdbcAccessException(HibernateAccessor.java:364)
	org.springframework.orm.hibernate.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:351)
	org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:375)
	org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:337)
	com.atlassian.crowd.embedded.hibernate2.HibernateUserDao.internalFindUser(HibernateUserDao.java:486)
	com.atlassian.crowd.embedded.hibernate2.HibernateUserDao.internalFindByName(HibernateUserDao.java:463)
	com.atlassian.crowd.embedded.hibernate2.HibernateUserDao.findByName(HibernateUserDao.java:443)
	com.atlassian.confluence.user.crowd.CachedCrowdUserDao$1.apply(CachedCrowdUserDao.java:78)
	com.atlassian.confluence.user.crowd.CachedCrowdUserDao$1.apply(CachedCrowdUserDao.java:72)
	com.atlassian.confluence.cache.option.OptionalReadThroughCache.get(OptionalReadThroughCache.java:35)
	com.atlassian.confluence.user.crowd.CachedCrowdUserDao.findUser(CachedCrowdUserDao.java:140)
	com.atlassian.confluence.user.crowd.CachedCrowdUserDao.findByName(CachedCrowdUserDao.java:162)
	sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
	sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	java.lang.reflect.Method.invoke(Method.java:606)
	org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
	org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
	org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
	com.sun.proxy.$Proxy45.findByName(Unknown Source)
	com.atlassian.crowd.directory.AbstractInternalDirectory.findUserByName(AbstractInternalDirectory.java:150)
	com.atlassian.crowd.directory.AbstractInternalDirectory.findUserByName(AbstractInternalDirectory.java:61)
	com.atlassian.crowd.manager.directory.DirectoryManagerGeneric.findUserByName(DirectoryManagerGeneric.java:298)
	com.atlassian.crowd.manager.application.ApplicationServiceGeneric.findUserByName(ApplicationServiceGeneric.java:289)
	com.atlassian.crowd.embedded.core.CrowdServiceImpl.getUser(CrowdServiceImpl.java:93)
	sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
	sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	java.lang.reflect.Method.invoke(Method.java:606)
	org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
	org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
	org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
	com.sun.proxy.$Proxy50.getUser(Unknown Source)
	com.atlassian.crowd.embedded.atlassianuser.EmbeddedCrowdUserManager.getUser(EmbeddedCrowdUserManager.java:107)
	com.atlassian.confluence.user.ConfluenceUserManager.getUser(ConfluenceUserManager.java:63)
	sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
	sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	java.lang.reflect.Method.invoke(Method.java:606)
	org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
	org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
	org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
	com.sun.proxy.$Proxy51.getUser(Unknown Source)
	bucket.user.DefaultUserAccessor.getUser(DefaultUserAccessor.java:163)
	com.atlassian.confluence.user.DefaultUserAccessor.getUserByName(DefaultUserAccessor.java:221)
	sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
	sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	java.lang.reflect.Method.invoke(Method.java:606)
	org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
	org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
	com.atlassian.spring.interceptors.SpringProfilingInterceptor.invoke(SpringProfilingInterceptor.java:16)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
	com.sun.proxy.$Proxy61.getUserByName(Unknown Source)
	com.atlassian.confluence.user.ConfluenceAuthenticator.getUser(ConfluenceAuthenticator.java:135)
	com.atlassian.confluence.user.ConfluenceAuthenticator.getUser(ConfluenceAuthenticator.java:30)
	com.atlassian.seraph.auth.DefaultAuthenticator.getUserFromCookie(DefaultAuthenticator.java:440)
	com.atlassian.seraph.auth.DefaultAuthenticator.getUser(DefaultAuthenticator.java:330)
	com.atlassian.confluence.user.ConfluenceAuthenticator.getUser(ConfluenceAuthenticator.java:141)
	com.atlassian.seraph.filter.SecurityFilter.doFilter(SecurityFilter.java:138)
	com.atlassian.confluence.web.filter.ConfluenceSecurityFilter.doFilter(ConfluenceSecurityFilter.java:27)
	com.atlassian.seraph.filter.BaseLoginFilter.doFilter(BaseLoginFilter.java:148)
	com.atlassian.plugin.servlet.filter.IteratingFilterChain.doFilter(IteratingFilterChain.java:46)
	com.atlassian.plugin.servlet.filter.ServletFilterModuleContainerFilter.doFilter(ServletFilterModuleContainerFilter.java:77)
	com.atlassian.plugin.servlet.filter.ServletFilterModuleContainerFilter.doFilter(ServletFilterModuleContainerFilter.java:63)
	com.atlassian.johnson.filters.AbstractJohnsonFilter.doFilter(AbstractJohnsonFilter.java:41)
	com.atlassian.plugin.servlet.filter.IteratingFilterChain.doFilter(IteratingFilterChain.java:46)
	com.atlassian.plugin.servlet.filter.ServletFilterModuleContainerFilter.doFilter(ServletFilterModuleContainerFilter.java:77)
	com.atlassian.plugin.servlet.filter.ServletFilterModuleContainerFilter.doFilter(ServletFilterModuleContainerFilter.java:63)
	com.atlassian.confluence.web.filter.DebugFilter.doFilter(DebugFilter.java:50)
	com.atlassian.core.filters.AbstractHttpFilter.doFilter(AbstractHttpFilter.java:31)
	com.atlassian.confluence.web.ConfluenceJohnsonFilter.handleError(ConfluenceJohnsonFilter.java:41)
	com.atlassian.johnson.filters.AbstractJohnsonFilter.doFilter(AbstractJohnsonFilter.java:63)
	org.springframework.orm.hibernate.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:170)
	com.atlassian.spring.filter.FlushingSpringSessionInViewFilter.doFilterInternal(FlushingSpringSessionInViewFilter.java:29)
	org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
	com.atlassian.confluence.util.ConfluenceErrorFilter.doFilter(ConfluenceErrorFilter.java:29)
	com.atlassian.util.profiling.filters.ProfilingFilter.doFilter(ProfilingFilter.java:99)
	com.atlassian.confluence.core.datetime.RequestTimeThreadLocalFilter.doFilter(RequestTimeThreadLocalFilter.java:43)
	com.atlassian.core.filters.cache.AbstractCachingFilter.doFilter(AbstractCachingFilter.java:33)
	com.atlassian.core.filters.AbstractHttpFilter.doFilter(AbstractHttpFilter.java:31)
	com.atlassian.plugin.servlet.filter.IteratingFilterChain.doFilter(IteratingFilterChain.java:46)
	com.atlassian.plugin.servlet.filter.ServletFilterModuleContainerFilter.doFilter(ServletFilterModuleContainerFilter.java:77)
	com.atlassian.plugin.servlet.filter.ServletFilterModuleContainerFilter.doFilter(ServletFilterModuleContainerFilter.java:63)
	com.atlassian.confluence.web.filter.validateparam.RequestParamValidationFilter.doFilter(RequestParamValidationFilter.java:58)
	com.atlassian.core.filters.AbstractHttpFilter.doFilter(AbstractHttpFilter.java:31)
	com.atlassian.confluence.web.filter.TranslationModeFilter.doFilter(TranslationModeFilter.java:44)
	com.atlassian.core.filters.AbstractHttpFilter.doFilter(AbstractHttpFilter.java:31)
	com.atlassian.confluence.plugin.servlet.filter.ActionContextCleanUp.doFilter(ActionContextCleanUp.java:73)
	com.atlassian.confluence.web.filter.LanguageExtractionFilter.doFilter(LanguageExtractionFilter.java:53)
	com.atlassian.core.filters.AbstractHttpFilter.doFilter(AbstractHttpFilter.java:31)
	com.atlassian.confluence.util.RequestCacheThreadLocalFilter.doFilter(RequestCacheThreadLocalFilter.java:32)
	com.atlassian.confluence.web.filter.ResponseOutputStreamFilter.doFilter(ResponseOutputStreamFilter.java:25)
	com.atlassian.core.filters.AbstractHttpFilter.doFilter(AbstractHttpFilter.java:31)
	com.atlassian.core.filters.encoding.AbstractEncodingFilter.doFilter(AbstractEncodingFilter.java:41)
	com.atlassian.core.filters.AbstractHttpFilter.doFilter(AbstractHttpFilter.java:31)
	com.atlassian.core.filters.HeaderSanitisingFilter.doFilter(HeaderSanitisingFilter.java:44)
	com.atlassian.confluence.servlet.FourOhFourErrorLoggingFilter.doFilter(FourOhFourErrorLoggingFilter.java:71)
	com.atlassian.confluence.web.filter.DebugFilter.doFilter(DebugFilter.java:50)
	com.atlassian.core.filters.AbstractHttpFilter.doFilter(AbstractHttpFilter.java:31)

root cause
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'this.external_id' in 'field list'
	sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
	sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	java.lang.reflect.Constructor.newInstance(Constructor.java:526)
	com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
	com.mysql.jdbc.Util.getInstance(Util.java:386)
	com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
	com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4187)
	com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4119)
	com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
	com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
	com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2815)
	com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
	com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2322)
	com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
	net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:89)
	net.sf.hibernate.loader.Loader.getResultSet(Loader.java:880)
	net.sf.hibernate.loader.Loader.doQuery(Loader.java:273)
	net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:138)
	net.sf.hibernate.loader.Loader.doList(Loader.java:1063)
	net.sf.hibernate.loader.Loader.list(Loader.java:1048)
	net.sf.hibernate.loader.CriteriaLoader.list(CriteriaLoader.java:118)
	net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:3675)
	net.sf.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:238)
	net.sf.hibernate.impl.CriteriaImpl.uniqueResult(CriteriaImpl.java:385)
	com.atlassian.crowd.embedded.hibernate2.HibernateUserDao$2.doInHibernate(HibernateUserDao.java:491)
	org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:370)
	org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:337)
	com.atlassian.crowd.embedded.hibernate2.HibernateUserDao.internalFindUser(HibernateUserDao.java:486)
	com.atlassian.crowd.embedded.hibernate2.HibernateUserDao.internalFindByName(HibernateUserDao.java:463)
	com.atlassian.crowd.embedded.hibernate2.HibernateUserDao.findByName(HibernateUserDao.java:443)
	com.atlassian.confluence.user.crowd.CachedCrowdUserDao$1.apply(CachedCrowdUserDao.java:78)
	com.atlassian.confluence.user.crowd.CachedCrowdUserDao$1.apply(CachedCrowdUserDao.java:72)
	com.atlassian.confluence.cache.option.OptionalReadThroughCache.get(OptionalReadThroughCache.java:35)
	com.atlassian.confluence.user.crowd.CachedCrowdUserDao.findUser(CachedCrowdUserDao.java:140)
	com.atlassian.confluence.user.crowd.CachedCrowdUserDao.findByName(CachedCrowdUserDao.java:162)
	sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
	sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	java.lang.reflect.Method.invoke(Method.java:606)
	org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
	org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
	org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
	com.sun.proxy.$Proxy45.findByName(Unknown Source)
	com.atlassian.crowd.directory.AbstractInternalDirectory.findUserByName(AbstractInternalDirectory.java:150)
	com.atlassian.crowd.directory.AbstractInternalDirectory.findUserByName(AbstractInternalDirectory.java:61)
	com.atlassian.crowd.manager.directory.DirectoryManagerGeneric.findUserByName(DirectoryManagerGeneric.java:298)
	com.atlassian.crowd.manager.application.ApplicationServiceGeneric.findUserByName(ApplicationServiceGeneric.java:289)
	com.atlassian.crowd.embedded.core.CrowdServiceImpl.getUser(CrowdServiceImpl.java:93)
	sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
	sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	java.lang.reflect.Method.invoke(Method.java:606)
	org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
	org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
	org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
	com.sun.proxy.$Proxy50.getUser(Unknown Source)
	com.atlassian.crowd.embedded.atlassianuser.EmbeddedCrowdUserManager.getUser(EmbeddedCrowdUserManager.java:107)
	com.atlassian.confluence.user.ConfluenceUserManager.getUser(ConfluenceUserManager.java:63)
	sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
	sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	java.lang.reflect.Method.invoke(Method.java:606)
	org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
	org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
	org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
	com.sun.proxy.$Proxy51.getUser(Unknown Source)
	bucket.user.DefaultUserAccessor.getUser(DefaultUserAccessor.java:163)
	com.atlassian.confluence.user.DefaultUserAccessor.getUserByName(DefaultUserAccessor.java:221)
	sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
	sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	java.lang.reflect.Method.invoke(Method.java:606)
	org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
	org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
	com.atlassian.spring.interceptors.SpringProfilingInterceptor.invoke(SpringProfilingInterceptor.java:16)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
	com.sun.proxy.$Proxy61.getUserByName(Unknown Source)
	com.atlassian.confluence.user.ConfluenceAuthenticator.getUser(ConfluenceAuthenticator.java:135)
	com.atlassian.confluence.user.ConfluenceAuthenticator.getUser(ConfluenceAuthenticator.java:30)
	com.atlassian.seraph.auth.DefaultAuthenticator.getUserFromCookie(DefaultAuthenticator.java:440)
	com.atlassian.seraph.auth.DefaultAuthenticator.getUser(DefaultAuthenticator.java:330)
	com.atlassian.confluence.user.ConfluenceAuthenticator.getUser(ConfluenceAuthenticator.java:141)
	com.atlassian.seraph.filter.SecurityFilter.doFilter(SecurityFilter.java:138)
	com.atlassian.confluence.web.filter.ConfluenceSecurityFilter.doFilter(ConfluenceSecurityFilter.java:27)
	com.atlassian.seraph.filter.BaseLoginFilter.doFilter(BaseLoginFilter.java:148)
	com.atlassian.plugin.servlet.filter.IteratingFilterChain.doFilter(IteratingFilterChain.java:46)
	com.atlassian.plugin.servlet.filter.ServletFilterModuleContainerFilter.doFilter(ServletFilterModuleContainerFilter.java:77)
	com.atlassian.plugin.servlet.filter.ServletFilterModuleContainerFilter.doFilter(ServletFilterModuleContainerFilter.java:63)
	com.atlassian.johnson.filters.AbstractJohnsonFilter.doFilter(AbstractJohnsonFilter.java:41)
	com.atlassian.plugin.servlet.filter.IteratingFilterChain.doFilter(IteratingFilterChain.java:46)
	com.atlassian.plugin.servlet.filter.ServletFilterModuleContainerFilter.doFilter(ServletFilterModuleContainerFilter.java:77)
	com.atlassian.plugin.servlet.filter.ServletFilterModuleContainerFilter.doFilter(ServletFilterModuleContainerFilter.java:63)
	com.atlassian.confluence.web.filter.DebugFilter.doFilter(DebugFilter.java:50)
	com.atlassian.core.filters.AbstractHttpFilter.doFilter(AbstractHttpFilter.java:31)
	com.atlassian.confluence.web.ConfluenceJohnsonFilter.handleError(ConfluenceJohnsonFilter.java:41)
	com.atlassian.johnson.filters.AbstractJohnsonFilter.doFilter(AbstractJohnsonFilter.java:63)
	org.springframework.orm.hibernate.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:170)
	com.atlassian.spring.filter.FlushingSpringSessionInViewFilter.doFilterInternal(FlushingSpringSessionInViewFilter.java:29)
	org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
	com.atlassian.confluence.util.ConfluenceErrorFilter.doFilter(ConfluenceErrorFilter.java:29)
	com.atlassian.util.profiling.filters.ProfilingFilter.doFilter(ProfilingFilter.java:99)
	com.atlassian.confluence.core.datetime.RequestTimeThreadLocalFilter.doFilter(RequestTimeThreadLocalFilter.java:43)
	com.atlassian.core.filters.cache.AbstractCachingFilter.doFilter(AbstractCachingFilter.java:33)
	com.atlassian.core.filters.AbstractHttpFilter.doFilter(AbstractHttpFilter.java:31)
	com.atlassian.plugin.servlet.filter.IteratingFilterChain.doFilter(IteratingFilterChain.java:46)
	com.atlassian.plugin.servlet.filter.ServletFilterModuleContainerFilter.doFilter(ServletFilterModuleContainerFilter.java:77)
	com.atlassian.plugin.servlet.filter.ServletFilterModuleContainerFilter.doFilter(ServletFilterModuleContainerFilter.java:63)
	com.atlassian.confluence.web.filter.validateparam.RequestParamValidationFilter.doFilter(RequestParamValidationFilter.java:58)
	com.atlassian.core.filters.AbstractHttpFilter.doFilter(AbstractHttpFilter.java:31)
	com.atlassian.confluence.web.filter.TranslationModeFilter.doFilter(TranslationModeFilter.java:44)
	com.atlassian.core.filters.AbstractHttpFilter.doFilter(AbstractHttpFilter.java:31)
	com.atlassian.confluence.plugin.servlet.filter.ActionContextCleanUp.doFilter(ActionContextCleanUp.java:73)
	com.atlassian.confluence.web.filter.LanguageExtractionFilter.doFilter(LanguageExtractionFilter.java:53)
	com.atlassian.core.filters.AbstractHttpFilter.doFilter(AbstractHttpFilter.java:31)
	com.atlassian.confluence.util.RequestCacheThreadLocalFilter.doFilter(RequestCacheThreadLocalFilter.java:32)
	com.atlassian.confluence.web.filter.ResponseOutputStreamFilter.doFilter(ResponseOutputStreamFilter.java:25)
	com.atlassian.core.filters.AbstractHttpFilter.doFilter(AbstractHttpFilter.java:31)
	com.atlassian.core.filters.encoding.AbstractEncodingFilter.doFilter(AbstractEncodingFilter.java:41)
	com.atlassian.core.filters.AbstractHttpFilter.doFilter(AbstractHttpFilter.java:31)
	com.atlassian.core.filters.HeaderSanitisingFilter.doFilter(HeaderSanitisingFilter.java:44)
	com.atlassian.confluence.servlet.FourOhFourErrorLoggingFilter.doFilter(FourOhFourErrorLoggingFilter.java:71)
	com.atlassian.confluence.web.filter.DebugFilter.doFilter(DebugFilter.java:50)
	com.atlassian.core.filters.AbstractHttpFilter.doFilter(AbstractHttpFilter.java:31)

If you weren't logged into Confluence

You'll see a bootstrap failure, similar to the following:

While the failures in both scenarios are completely different, they're caused by the same issue - the collation and character sets for tables in the database are inconsistent

How MySQL Handles Collation and Character Sets

When you create a database, table, or column, you have the ability to specify the character set and collation. If none is explicitly set during creation, the object will use that specified of its parent - or continue along upwards until the explicitly defined character set and collation is found. In this particular case, we created a database with latin1 encoding. In preparation for our upgrade to Confluence 5, we adjusted the collation on the database as suggested by the documentation.

The result was that when new tables were created in Confluence 5, they used the database's character set and encoding - in this case, utf8. We now have an inconsistent database - some tables use utf8 while others use latin1.

In Confluence 5.5, we introduced bootstrap checks to ensure that Confluence wouldn't upgrade an inconsistent database, as it can cause problems with incorrectly formatted data, among other things.

Repairing the Database

At this point, we need to ensure that our database is consistent in the character set and collations that it uses. Since utf8 is the recommended character set, we'll be using that.

Since we know our database is using the right collation (we set it earlier), we'll be able to start from MySQL Collation Repair: Table Level Changes. Ensure Confluence has been shut down, and that you have a backup of your database before proceeding.

These results are supplied for reference only, and may differ from your installation

Query Results

Let's identify the tables that are having problems:

SELECT T.TABLE_NAME, C.CHARACTER_SET_NAME, C.COLLATION_NAME
FROM information_schema.TABLES AS T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` AS C
WHERE C.collation_name = T.table_collation
AND T.table_schema = 'conf-broken'
AND
(
    C.CHARACTER_SET_NAME != 'utf8'
    OR
    C.COLLATION_NAME != 'utf8_bin'
);
Click here to expand...
TABLE_NAME CHARACTER_SET_NAME COLLATION_NAME
ATTACHMENTDATA latin1 latin1_swedish_ci
ATTACHMENTS latin1 latin1_swedish_ci
BANDANA latin1 latin1_swedish_ci
BODYCONTENT latin1 latin1_swedish_ci
CLUSTERSAFETY latin1 latin1_swedish_ci
CONFANCESTORS latin1 latin1_swedish_ci
CONFVERSION latin1 latin1_swedish_ci
CONTENT latin1 latin1_swedish_ci
CONTENT_LABEL latin1 latin1_swedish_ci
CONTENT_PERM latin1 latin1_swedish_ci
CONTENT_PERM_SET latin1 latin1_swedish_ci
DECORATOR latin1 latin1_swedish_ci
EXTRNLNKS latin1 latin1_swedish_ci
FOLLOW_CONNECTIONS latin1 latin1_swedish_ci
IMAGEDETAILS latin1 latin1_swedish_ci
INDEXQUEUEENTRIES latin1 latin1_swedish_ci
KEYSTORE latin1 latin1_swedish_ci
LABEL latin1 latin1_swedish_ci
LINKS latin1 latin1_swedish_ci
NOTIFICATIONS latin1 latin1_swedish_ci
OS_PROPERTYENTRY latin1 latin1_swedish_ci
PAGETEMPLATES latin1 latin1_swedish_ci
PLUGINDATA latin1 latin1_swedish_ci
SPACEGROUPPERMISSIONS latin1 latin1_swedish_ci
SPACEGROUPS latin1 latin1_swedish_ci
SPACEPERMISSIONS latin1 latin1_swedish_ci
SPACES latin1 latin1_swedish_ci
TRACKBACKLINKS latin1 latin1_swedish_ci
TRUSTEDAPP latin1 latin1_swedish_ci
TRUSTEDAPPRESTRICTION latin1 latin1_swedish_ci
cwd_app_dir_group_mapping latin1 latin1_swedish_ci
cwd_app_dir_mapping latin1 latin1_swedish_ci
cwd_app_dir_operation latin1 latin1_swedish_ci
cwd_application latin1 latin1_swedish_ci
cwd_application_address latin1 latin1_swedish_ci
cwd_application_attribute latin1 latin1_swedish_ci
cwd_directory latin1 latin1_swedish_ci
cwd_directory_attribute latin1 latin1_swedish_ci
cwd_directory_operation latin1 latin1_swedish_ci
cwd_group latin1 latin1_swedish_ci
cwd_group_attribute latin1 latin1_swedish_ci
cwd_membership latin1 latin1_swedish_ci
cwd_user latin1 latin1_swedish_ci
cwd_user_attribute latin1 latin1_swedish_ci
cwd_user_credential_record latin1 latin1_swedish_ci
external_entities latin1 latin1_swedish_ci
external_members latin1 latin1_swedish_ci
groups latin1 latin1_swedish_ci
hibernate_unique_key latin1 latin1_swedish_ci
local_members latin1 latin1_swedish_ci
os_group latin1 latin1_swedish_ci
os_user latin1 latin1_swedish_ci
os_user_group latin1 latin1_swedish_ci
remembermetoken latin1 latin1_swedish_ci
users latin1 latin1_swedish_ci

Next, we'll generate our repair script:

SELECT CONCAT('ALTER TABLE ',  table_name, ' CHARACTER SET utf8 COLLATE utf8_bin;')
FROM information_schema.TABLES AS T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` AS C
WHERE C.collation_name = T.table_collation
AND T.table_schema = 'conf-broken'
AND
(
    C.CHARACTER_SET_NAME != 'utf8'
    OR
    C.COLLATION_NAME != 'utf8_bin'
);

Each row is a query we run in bulk. You can export these results to a .sql file, and run those queries against your database. After running the repair queries, there are no more tables in the database that have the incorrect collation.

Click here to expand...
CONCAT('ALTER TABLE ',  table_name, ' CHARACTER SET utf8 COLLATE utf8_bin;')
ALTER TABLE ATTACHMENTDATA CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE ATTACHMENTS CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE BANDANA CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE BODYCONTENT CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE CLUSTERSAFETY CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE CONFANCESTORS CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE CONFVERSION CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE CONTENT CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE CONTENT_LABEL CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE CONTENT_PERM CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE CONTENT_PERM_SET CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE DECORATOR CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE EXTRNLNKS CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE FOLLOW_CONNECTIONS CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE IMAGEDETAILS CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE INDEXQUEUEENTRIES CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE KEYSTORE CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE LABEL CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE LINKS CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE NOTIFICATIONS CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE OS_PROPERTYENTRY CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE PAGETEMPLATES CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE PLUGINDATA CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE SPACEGROUPPERMISSIONS CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE SPACEGROUPS CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE SPACEPERMISSIONS CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE SPACES CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE TRACKBACKLINKS CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE TRUSTEDAPP CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE TRUSTEDAPPRESTRICTION CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE cwd_app_dir_group_mapping CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE cwd_app_dir_mapping CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE cwd_app_dir_operation CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE cwd_application CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE cwd_application_address CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE cwd_application_attribute CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE cwd_directory CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE cwd_directory_attribute CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE cwd_directory_operation CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE cwd_group CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE cwd_group_attribute CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE cwd_membership CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE cwd_user CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE cwd_user_attribute CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE cwd_user_credential_record CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE external_entities CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE external_members CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE groups CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE hibernate_unique_key CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE local_members CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE os_group CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE os_user CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE os_user_group CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE remembermetoken CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE users CHARACTER SET utf8 COLLATE utf8_bin;

Now, let's identify any columns that aren't using the correct collation:

SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'conf-broken'
AND
(
    CHARACTER_SET_NAME != 'utf8'
    OR
    COLLATION_NAME != 'utf8_bin'
);
Click here to expand...
TABLE_NAME COLUMN_NAME CHARACTER_SET_NAME COLLATION_NAME
ATTACHMENTS TITLE latin1 latin1_swedish_ci
ATTACHMENTS CONTENTTYPE latin1 latin1_swedish_ci
ATTACHMENTS CREATOR latin1 latin1_swedish_ci
ATTACHMENTS LASTMODIFIER latin1 latin1_swedish_ci
ATTACHMENTS ATTACHMENT_COMMENT latin1 latin1_swedish_ci
BANDANA BANDANACONTEXT latin1 latin1_swedish_ci
BANDANA BANDANAKEY latin1 latin1_swedish_ci
BANDANA BANDANAVALUE latin1 latin1_swedish_ci
BODYCONTENT BODY latin1 latin1_swedish_ci
CONFVERSION VERSIONTAG latin1 latin1_swedish_ci
CONTENT CONTENTTYPE latin1 latin1_swedish_ci
CONTENT TITLE latin1 latin1_swedish_ci
CONTENT CREATOR latin1 latin1_swedish_ci
CONTENT LASTMODIFIER latin1 latin1_swedish_ci
CONTENT VERSIONCOMMENT latin1 latin1_swedish_ci
CONTENT CONTENT_STATUS latin1 latin1_swedish_ci
CONTENT MESSAGEID latin1 latin1_swedish_ci
CONTENT DRAFTPAGEID latin1 latin1_swedish_ci
CONTENT DRAFTSPACEKEY latin1 latin1_swedish_ci
CONTENT DRAFTTYPE latin1 latin1_swedish_ci
CONTENT USERNAME latin1 latin1_swedish_ci
CONTENT PLUGINKEY latin1 latin1_swedish_ci
CONTENT PLUGINVER latin1 latin1_swedish_ci
CONTENT_LABEL OWNER latin1 latin1_swedish_ci
CONTENT_LABEL LABELABLETYPE latin1 latin1_swedish_ci
CONTENT_PERM CP_TYPE latin1 latin1_swedish_ci
CONTENT_PERM USERNAME latin1 latin1_swedish_ci
CONTENT_PERM GROUPNAME latin1 latin1_swedish_ci
CONTENT_PERM CREATOR latin1 latin1_swedish_ci
CONTENT_PERM LASTMODIFIER latin1 latin1_swedish_ci
CONTENT_PERM_SET CONT_PERM_TYPE latin1 latin1_swedish_ci
DECORATOR SPACEKEY latin1 latin1_swedish_ci
DECORATOR DECORATORNAME latin1 latin1_swedish_ci
DECORATOR BODY latin1 latin1_swedish_ci
EXTRNLNKS CONTENTTYPE latin1 latin1_swedish_ci
EXTRNLNKS URL latin1 latin1_swedish_ci
EXTRNLNKS CREATOR latin1 latin1_swedish_ci
EXTRNLNKS LASTMODIFIER latin1 latin1_swedish_ci
FOLLOW_CONNECTIONS FOLLOWER latin1 latin1_swedish_ci
FOLLOW_CONNECTIONS FOLLOWEE latin1 latin1_swedish_ci
IMAGEDETAILS MIMETYPE latin1 latin1_swedish_ci
INDEXQUEUEENTRIES HANDLE latin1 latin1_swedish_ci
KEYSTORE ALIAS latin1 latin1_swedish_ci
KEYSTORE TYPE latin1 latin1_swedish_ci
KEYSTORE ALGORITHM latin1 latin1_swedish_ci
KEYSTORE KEYSPEC latin1 latin1_swedish_ci
LABEL NAME latin1 latin1_swedish_ci
LABEL OWNER latin1 latin1_swedish_ci
LABEL NAMESPACE latin1 latin1_swedish_ci
LINKS DESTPAGETITLE latin1 latin1_swedish_ci
LINKS DESTSPACEKEY latin1 latin1_swedish_ci
LINKS CREATOR latin1 latin1_swedish_ci
LINKS LASTMODIFIER latin1 latin1_swedish_ci
NOTIFICATIONS USERNAME latin1 latin1_swedish_ci
NOTIFICATIONS CREATOR latin1 latin1_swedish_ci
NOTIFICATIONS LASTMODIFIER latin1 latin1_swedish_ci
NOTIFICATIONS CONTENTTYPE latin1 latin1_swedish_ci
OS_PROPERTYENTRY entity_name latin1 latin1_swedish_ci
OS_PROPERTYENTRY entity_key latin1 latin1_swedish_ci
OS_PROPERTYENTRY string_val latin1 latin1_swedish_ci
OS_PROPERTYENTRY text_val latin1 latin1_swedish_ci
PAGETEMPLATES TEMPLATENAME latin1 latin1_swedish_ci
PAGETEMPLATES TEMPLATEDESC latin1 latin1_swedish_ci
PAGETEMPLATES LABELS latin1 latin1_swedish_ci
PAGETEMPLATES CONTENT latin1 latin1_swedish_ci
PAGETEMPLATES CREATOR latin1 latin1_swedish_ci
PAGETEMPLATES LASTMODIFIER latin1 latin1_swedish_ci
PAGETEMPLATES PLUGINKEY latin1 latin1_swedish_ci
PAGETEMPLATES MODULEKEY latin1 latin1_swedish_ci
PLUGINDATA PLUGINKEY latin1 latin1_swedish_ci
PLUGINDATA FILENAME latin1 latin1_swedish_ci
SPACEGROUPPERMISSIONS PERMTYPE latin1 latin1_swedish_ci
SPACEGROUPPERMISSIONS PERMGROUPNAME latin1 latin1_swedish_ci
SPACEGROUPPERMISSIONS PERMUSERNAME latin1 latin1_swedish_ci
SPACEGROUPS SPACEGROUPNAME latin1 latin1_swedish_ci
SPACEGROUPS SPACEGROUPKEY latin1 latin1_swedish_ci
SPACEGROUPS LICENSEKEY latin1 latin1_swedish_ci
SPACEGROUPS CREATOR latin1 latin1_swedish_ci
SPACEGROUPS LASTMODIFIER latin1 latin1_swedish_ci
SPACEPERMISSIONS PERMTYPE latin1 latin1_swedish_ci
SPACEPERMISSIONS PERMGROUPNAME latin1 latin1_swedish_ci
SPACEPERMISSIONS PERMUSERNAME latin1 latin1_swedish_ci
SPACEPERMISSIONS CREATOR latin1 latin1_swedish_ci
SPACEPERMISSIONS LASTMODIFIER latin1 latin1_swedish_ci
SPACES SPACENAME latin1 latin1_swedish_ci
SPACES SPACEKEY latin1 latin1_swedish_ci
SPACES CREATOR latin1 latin1_swedish_ci
SPACES LASTMODIFIER latin1 latin1_swedish_ci
SPACES SPACETYPE latin1 latin1_swedish_ci
SPACES SPACESTATUS latin1 latin1_swedish_ci
TRACKBACKLINKS CONTENTTYPE latin1 latin1_swedish_ci
TRACKBACKLINKS URL latin1 latin1_swedish_ci
TRACKBACKLINKS TITLE latin1 latin1_swedish_ci
TRACKBACKLINKS BLOGNAME latin1 latin1_swedish_ci
TRACKBACKLINKS EXCERPT latin1 latin1_swedish_ci
TRACKBACKLINKS CREATOR latin1 latin1_swedish_ci
TRACKBACKLINKS LASTMODIFIER latin1 latin1_swedish_ci
TRUSTEDAPP NAME latin1 latin1_swedish_ci
TRUSTEDAPPRESTRICTION TYPE latin1 latin1_swedish_ci
TRUSTEDAPPRESTRICTION restriction latin1 latin1_swedish_ci
cwd_app_dir_group_mapping group_name latin1 latin1_swedish_ci
cwd_app_dir_mapping allow_all latin1 latin1_swedish_ci
cwd_app_dir_operation operation_type latin1 latin1_swedish_ci
cwd_application application_name latin1 latin1_swedish_ci
cwd_application lower_application_name latin1 latin1_swedish_ci
cwd_application active latin1 latin1_swedish_ci
cwd_application description latin1 latin1_swedish_ci
cwd_application application_type latin1 latin1_swedish_ci
cwd_application credential latin1 latin1_swedish_ci
cwd_application_address remote_address latin1 latin1_swedish_ci
cwd_application_address remote_address_binary latin1 latin1_swedish_ci
cwd_application_attribute attribute_value latin1 latin1_swedish_ci
cwd_application_attribute attribute_name latin1 latin1_swedish_ci
cwd_directory directory_name latin1 latin1_swedish_ci
cwd_directory lower_directory_name latin1 latin1_swedish_ci
cwd_directory active latin1 latin1_swedish_ci
cwd_directory description latin1 latin1_swedish_ci
cwd_directory impl_class latin1 latin1_swedish_ci
cwd_directory lower_impl_class latin1 latin1_swedish_ci
cwd_directory directory_type latin1 latin1_swedish_ci
cwd_directory_attribute attribute_value latin1 latin1_swedish_ci
cwd_directory_attribute attribute_name latin1 latin1_swedish_ci
cwd_directory_operation operation_type latin1 latin1_swedish_ci
cwd_group group_name latin1 latin1_swedish_ci
cwd_group lower_group_name latin1 latin1_swedish_ci
cwd_group active latin1 latin1_swedish_ci
cwd_group local latin1 latin1_swedish_ci
cwd_group description latin1 latin1_swedish_ci
cwd_group group_type latin1 latin1_swedish_ci
cwd_group_attribute attribute_name latin1 latin1_swedish_ci
cwd_group_attribute attribute_value latin1 latin1_swedish_ci
cwd_group_attribute attribute_lower_value latin1 latin1_swedish_ci
cwd_user user_name latin1 latin1_swedish_ci
cwd_user lower_user_name latin1 latin1_swedish_ci
cwd_user active latin1 latin1_swedish_ci
cwd_user first_name latin1 latin1_swedish_ci
cwd_user lower_first_name latin1 latin1_swedish_ci
cwd_user last_name latin1 latin1_swedish_ci
cwd_user lower_last_name latin1 latin1_swedish_ci
cwd_user display_name latin1 latin1_swedish_ci
cwd_user lower_display_name latin1 latin1_swedish_ci
cwd_user email_address latin1 latin1_swedish_ci
cwd_user lower_email_address latin1 latin1_swedish_ci
cwd_user credential latin1 latin1_swedish_ci
cwd_user_attribute attribute_name latin1 latin1_swedish_ci
cwd_user_attribute attribute_value latin1 latin1_swedish_ci
cwd_user_attribute attribute_lower_value latin1 latin1_swedish_ci
cwd_user_credential_record password_hash latin1 latin1_swedish_ci
external_entities name latin1 latin1_swedish_ci
external_entities type latin1 latin1_swedish_ci
groups groupname latin1 latin1_swedish_ci
os_group groupname latin1 latin1_swedish_ci
os_user username latin1 latin1_swedish_ci
os_user passwd latin1 latin1_swedish_ci
remembermetoken username latin1 latin1_swedish_ci
remembermetoken token latin1 latin1_swedish_ci
users name latin1 latin1_swedish_ci
users password latin1 latin1_swedish_ci
users email latin1 latin1_swedish_ci
users fullname latin1 latin1_swedish_ci

Once again, we'll generate our repair script - firstly for varchar columns:

SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, '(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET UTF8 COLLATE utf8_bin;')
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'conf-broken'
AND DATA_TYPE = 'varchar'
AND
(
    CHARACTER_SET_NAME != 'utf8'
    OR
    COLLATION_NAME != 'utf8_bin'
);

As before, execute each of these queries against your database.

Click here to expand...
CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, '(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET UTF8 COLLATE utf8_bin;')
ALTER TABLE `ATTACHMENTS` MODIFY `TITLE` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `ATTACHMENTS` MODIFY `CONTENTTYPE` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `ATTACHMENTS` MODIFY `CREATOR` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `ATTACHMENTS` MODIFY `LASTMODIFIER` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `ATTACHMENTS` MODIFY `ATTACHMENT_COMMENT` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `BANDANA` MODIFY `BANDANACONTEXT` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `BANDANA` MODIFY `BANDANAKEY` varchar(100) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `CONFVERSION` MODIFY `VERSIONTAG` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `CONTENT` MODIFY `CONTENTTYPE` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `CONTENT` MODIFY `TITLE` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `CONTENT` MODIFY `CREATOR` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `CONTENT` MODIFY `LASTMODIFIER` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `CONTENT` MODIFY `CONTENT_STATUS` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `CONTENT` MODIFY `MESSAGEID` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `CONTENT` MODIFY `DRAFTPAGEID` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `CONTENT` MODIFY `DRAFTSPACEKEY` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `CONTENT` MODIFY `DRAFTTYPE` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `CONTENT` MODIFY `USERNAME` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `CONTENT` MODIFY `PLUGINKEY` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `CONTENT` MODIFY `PLUGINVER` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `CONTENT_LABEL` MODIFY `OWNER` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `CONTENT_LABEL` MODIFY `LABELABLETYPE` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `CONTENT_PERM` MODIFY `CP_TYPE` varchar(10) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `CONTENT_PERM` MODIFY `USERNAME` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `CONTENT_PERM` MODIFY `GROUPNAME` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `CONTENT_PERM` MODIFY `CREATOR` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `CONTENT_PERM` MODIFY `LASTMODIFIER` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `CONTENT_PERM_SET` MODIFY `CONT_PERM_TYPE` varchar(10) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `DECORATOR` MODIFY `SPACEKEY` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `DECORATOR` MODIFY `DECORATORNAME` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `EXTRNLNKS` MODIFY `CONTENTTYPE` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `EXTRNLNKS` MODIFY `URL` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `EXTRNLNKS` MODIFY `CREATOR` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `EXTRNLNKS` MODIFY `LASTMODIFIER` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `FOLLOW_CONNECTIONS` MODIFY `FOLLOWER` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `FOLLOW_CONNECTIONS` MODIFY `FOLLOWEE` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `IMAGEDETAILS` MODIFY `MIMETYPE` varchar(30) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `INDEXQUEUEENTRIES` MODIFY `HANDLE` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `KEYSTORE` MODIFY `ALIAS` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `KEYSTORE` MODIFY `TYPE` varchar(32) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `KEYSTORE` MODIFY `ALGORITHM` varchar(32) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `LABEL` MODIFY `NAME` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `LABEL` MODIFY `OWNER` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `LABEL` MODIFY `NAMESPACE` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `LINKS` MODIFY `DESTPAGETITLE` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `LINKS` MODIFY `DESTSPACEKEY` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `LINKS` MODIFY `CREATOR` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `LINKS` MODIFY `LASTMODIFIER` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `NOTIFICATIONS` MODIFY `USERNAME` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `NOTIFICATIONS` MODIFY `CREATOR` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `NOTIFICATIONS` MODIFY `LASTMODIFIER` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `NOTIFICATIONS` MODIFY `CONTENTTYPE` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `OS_PROPERTYENTRY` MODIFY `entity_name` varchar(125) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `OS_PROPERTYENTRY` MODIFY `entity_key` varchar(200) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `OS_PROPERTYENTRY` MODIFY `string_val` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `PAGETEMPLATES` MODIFY `TEMPLATENAME` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `PAGETEMPLATES` MODIFY `TEMPLATEDESC` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `PAGETEMPLATES` MODIFY `LABELS` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `PAGETEMPLATES` MODIFY `CREATOR` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `PAGETEMPLATES` MODIFY `LASTMODIFIER` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `PAGETEMPLATES` MODIFY `PLUGINKEY` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `PAGETEMPLATES` MODIFY `MODULEKEY` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `PLUGINDATA` MODIFY `PLUGINKEY` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `PLUGINDATA` MODIFY `FILENAME` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `SPACEGROUPPERMISSIONS` MODIFY `PERMTYPE` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `SPACEGROUPPERMISSIONS` MODIFY `PERMGROUPNAME` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `SPACEGROUPPERMISSIONS` MODIFY `PERMUSERNAME` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `SPACEGROUPS` MODIFY `SPACEGROUPNAME` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `SPACEGROUPS` MODIFY `SPACEGROUPKEY` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `SPACEGROUPS` MODIFY `CREATOR` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `SPACEGROUPS` MODIFY `LASTMODIFIER` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `SPACEPERMISSIONS` MODIFY `PERMTYPE` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `SPACEPERMISSIONS` MODIFY `PERMGROUPNAME` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `SPACEPERMISSIONS` MODIFY `PERMUSERNAME` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `SPACEPERMISSIONS` MODIFY `CREATOR` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `SPACEPERMISSIONS` MODIFY `LASTMODIFIER` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `SPACES` MODIFY `SPACENAME` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `SPACES` MODIFY `SPACEKEY` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `SPACES` MODIFY `CREATOR` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `SPACES` MODIFY `LASTMODIFIER` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `SPACES` MODIFY `SPACETYPE` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `SPACES` MODIFY `SPACESTATUS` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `TRACKBACKLINKS` MODIFY `CONTENTTYPE` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `TRACKBACKLINKS` MODIFY `URL` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `TRACKBACKLINKS` MODIFY `TITLE` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `TRACKBACKLINKS` MODIFY `BLOGNAME` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `TRACKBACKLINKS` MODIFY `EXCERPT` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `TRACKBACKLINKS` MODIFY `CREATOR` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `TRACKBACKLINKS` MODIFY `LASTMODIFIER` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `TRUSTEDAPP` MODIFY `NAME` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `TRUSTEDAPPRESTRICTION` MODIFY `TYPE` varchar(32) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `TRUSTEDAPPRESTRICTION` MODIFY `restriction` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_app_dir_group_mapping` MODIFY `group_name` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_app_dir_operation` MODIFY `operation_type` varchar(32) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_application` MODIFY `application_name` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_application` MODIFY `lower_application_name` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_application` MODIFY `description` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_application` MODIFY `application_type` varchar(32) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_application` MODIFY `credential` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_application_address` MODIFY `remote_address` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_application_address` MODIFY `remote_address_binary` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_application_attribute` MODIFY `attribute_name` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_directory` MODIFY `directory_name` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_directory` MODIFY `lower_directory_name` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_directory` MODIFY `description` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_directory` MODIFY `impl_class` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_directory` MODIFY `lower_impl_class` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_directory` MODIFY `directory_type` varchar(32) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_directory_attribute` MODIFY `attribute_name` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_directory_operation` MODIFY `operation_type` varchar(32) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_group` MODIFY `group_name` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_group` MODIFY `lower_group_name` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_group` MODIFY `description` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_group` MODIFY `group_type` varchar(32) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_group_attribute` MODIFY `attribute_name` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_group_attribute` MODIFY `attribute_value` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_group_attribute` MODIFY `attribute_lower_value` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_user` MODIFY `user_name` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_user` MODIFY `lower_user_name` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_user` MODIFY `first_name` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_user` MODIFY `lower_first_name` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_user` MODIFY `last_name` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_user` MODIFY `lower_last_name` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_user` MODIFY `display_name` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_user` MODIFY `lower_display_name` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_user` MODIFY `email_address` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_user` MODIFY `lower_email_address` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_user` MODIFY `credential` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_user_attribute` MODIFY `attribute_name` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_user_attribute` MODIFY `attribute_value` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_user_attribute` MODIFY `attribute_lower_value` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_user_credential_record` MODIFY `password_hash` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `external_entities` MODIFY `name` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `external_entities` MODIFY `type` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `groups` MODIFY `groupname` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `os_group` MODIFY `groupname` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `os_user` MODIFY `username` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `os_user` MODIFY `passwd` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `remembermetoken` MODIFY `username` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `remembermetoken` MODIFY `token` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `users` MODIFY `name` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `users` MODIFY `password` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `users` MODIFY `email` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `users` MODIFY `fullname` varchar(255) CHARACTER SET UTF8 COLLATE utf8_bin;

We'll also generate our repair script for non varchar columns:

SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, ' CHARACTER SET UTF8 COLLATE utf8_bin;')
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'conf-broken'
AND DATA_TYPE != 'varchar'
AND
(
    CHARACTER_SET_NAME != 'utf8'
    OR
    COLLATION_NAME != 'utf8_bin'
);
Click here to expand...
CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, ' CHARACTER SET UTF8 COLLATE utf8_bin;')
ALTER TABLE `BANDANA` MODIFY `BANDANAVALUE` mediumtext CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `BODYCONTENT` MODIFY `BODY` longtext CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `CONTENT` MODIFY `VERSIONCOMMENT` mediumtext CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `DECORATOR` MODIFY `BODY` mediumtext CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `KEYSTORE` MODIFY `KEYSPEC` text CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `OS_PROPERTYENTRY` MODIFY `text_val` mediumtext CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `PAGETEMPLATES` MODIFY `CONTENT` mediumtext CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `SPACEGROUPS` MODIFY `LICENSEKEY` text CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_app_dir_mapping` MODIFY `allow_all` char CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_application` MODIFY `active` char CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_application_attribute` MODIFY `attribute_value` text CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_directory` MODIFY `active` char CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_directory_attribute` MODIFY `attribute_value` text CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_group` MODIFY `active` char CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_group` MODIFY `local` char CHARACTER SET UTF8 COLLATE utf8_bin;
ALTER TABLE `cwd_user` MODIFY `active` char CHARACTER SET UTF8 COLLATE utf8_bin;

At this point, our database is sufficiently repaired, so we can start Confluence. All of the identifying queries should now return zero rows. Let's go ahead and check our sample page:

Optional: Fixing incorrectly encoded content

You may come across content that hasn't been encoded correctly. This is because MySQL can define a character set and collation at the connection level, as well as at the server level. These character set and collation levels are defined in several server variables, and are explained in Appendix A - Character Set and Collation Variables.

Because the location of your incorrectly encoded content may vary, the examples are limited. Please see MySQL Collation Repair: Column Level Encoding Issues for more information about a suitable query to use to re-encode data.

Appendix A: Character Set and Collation variables

character_set_server and collation_server:

These are the default character set and collation used by the server - by default, they're set to latin1 and latin1_swedish_ci, respectively. This means that any database will use these character set and collation, unless specified during the creation process.

character_set_connection and collation_connection:

These are the character set and collation that will be used in the connection. If they're not explicitly set, they'll use the same values as defined by character_set_server and collation_server respectively. 

To determine what settings your server is running, use the following queries:

SHOW VARIABLES LIKE 'collation%';
SHOW VARIABLES LIKE '%char%';

To set these variables permanently, please visit our guide on Configuring Database Character Encoding.

Last modified on Feb 19, 2016

Was this helpful?

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