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

This guide was written for Confluence 5.1, so may not be accurate for current Confluence versions, but it does provide a useful conceptual overview of how to repair a production database.


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

QueryResults

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_NAMECHARACTER_SET_NAMECOLLATION_NAME
ATTACHMENTDATAlatin1latin1_swedish_ci
ATTACHMENTSlatin1latin1_swedish_ci
BANDANAlatin1latin1_swedish_ci
BODYCONTENTlatin1latin1_swedish_ci
CLUSTERSAFETYlatin1latin1_swedish_ci
CONFANCESTORSlatin1latin1_swedish_ci
CONFVERSIONlatin1latin1_swedish_ci
CONTENTlatin1latin1_swedish_ci
CONTENT_LABELlatin1latin1_swedish_ci
CONTENT_PERMlatin1latin1_swedish_ci
CONTENT_PERM_SETlatin1latin1_swedish_ci
DECORATORlatin1latin1_swedish_ci
EXTRNLNKSlatin1latin1_swedish_ci
FOLLOW_CONNECTIONSlatin1latin1_swedish_ci
IMAGEDETAILSlatin1latin1_swedish_ci
INDEXQUEUEENTRIESlatin1latin1_swedish_ci
KEYSTORElatin1latin1_swedish_ci
LABELlatin1latin1_swedish_ci
LINKSlatin1latin1_swedish_ci
NOTIFICATIONSlatin1latin1_swedish_ci
OS_PROPERTYENTRYlatin1latin1_swedish_ci
PAGETEMPLATESlatin1latin1_swedish_ci
PLUGINDATAlatin1latin1_swedish_ci
SPACEGROUPPERMISSIONSlatin1latin1_swedish_ci
SPACEGROUPSlatin1latin1_swedish_ci
SPACEPERMISSIONSlatin1latin1_swedish_ci
SPACESlatin1latin1_swedish_ci
TRACKBACKLINKSlatin1latin1_swedish_ci
TRUSTEDAPPlatin1latin1_swedish_ci
TRUSTEDAPPRESTRICTIONlatin1latin1_swedish_ci
cwd_app_dir_group_mappinglatin1latin1_swedish_ci
cwd_app_dir_mappinglatin1latin1_swedish_ci
cwd_app_dir_operationlatin1latin1_swedish_ci
cwd_applicationlatin1latin1_swedish_ci
cwd_application_addresslatin1latin1_swedish_ci
cwd_application_attributelatin1latin1_swedish_ci
cwd_directorylatin1latin1_swedish_ci
cwd_directory_attributelatin1latin1_swedish_ci
cwd_directory_operationlatin1latin1_swedish_ci
cwd_grouplatin1latin1_swedish_ci
cwd_group_attributelatin1latin1_swedish_ci
cwd_membershiplatin1latin1_swedish_ci
cwd_userlatin1latin1_swedish_ci
cwd_user_attributelatin1latin1_swedish_ci
cwd_user_credential_recordlatin1latin1_swedish_ci
external_entitieslatin1latin1_swedish_ci
external_memberslatin1latin1_swedish_ci
groupslatin1latin1_swedish_ci
hibernate_unique_keylatin1latin1_swedish_ci
local_memberslatin1latin1_swedish_ci
os_grouplatin1latin1_swedish_ci
os_userlatin1latin1_swedish_ci
os_user_grouplatin1latin1_swedish_ci
remembermetokenlatin1latin1_swedish_ci
userslatin1latin1_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_NAMECOLUMN_NAMECHARACTER_SET_NAMECOLLATION_NAME
ATTACHMENTSTITLElatin1latin1_swedish_ci
ATTACHMENTSCONTENTTYPElatin1latin1_swedish_ci
ATTACHMENTSCREATORlatin1latin1_swedish_ci
ATTACHMENTSLASTMODIFIERlatin1latin1_swedish_ci
ATTACHMENTSATTACHMENT_COMMENTlatin1latin1_swedish_ci
BANDANABANDANACONTEXTlatin1latin1_swedish_ci
BANDANABANDANAKEYlatin1latin1_swedish_ci
BANDANABANDANAVALUElatin1latin1_swedish_ci
BODYCONTENTBODYlatin1latin1_swedish_ci
CONFVERSIONVERSIONTAGlatin1latin1_swedish_ci
CONTENTCONTENTTYPElatin1latin1_swedish_ci
CONTENTTITLElatin1latin1_swedish_ci
CONTENTCREATORlatin1latin1_swedish_ci
CONTENTLASTMODIFIERlatin1latin1_swedish_ci
CONTENTVERSIONCOMMENTlatin1latin1_swedish_ci
CONTENTCONTENT_STATUSlatin1latin1_swedish_ci
CONTENTMESSAGEIDlatin1latin1_swedish_ci
CONTENTDRAFTPAGEIDlatin1latin1_swedish_ci
CONTENTDRAFTSPACEKEYlatin1latin1_swedish_ci
CONTENTDRAFTTYPElatin1latin1_swedish_ci
CONTENTUSERNAMElatin1latin1_swedish_ci
CONTENTPLUGINKEYlatin1latin1_swedish_ci
CONTENTPLUGINVERlatin1latin1_swedish_ci
CONTENT_LABELOWNERlatin1latin1_swedish_ci
CONTENT_LABELLABELABLETYPElatin1latin1_swedish_ci
CONTENT_PERMCP_TYPElatin1latin1_swedish_ci
CONTENT_PERMUSERNAMElatin1latin1_swedish_ci
CONTENT_PERMGROUPNAMElatin1latin1_swedish_ci
CONTENT_PERMCREATORlatin1latin1_swedish_ci
CONTENT_PERMLASTMODIFIERlatin1latin1_swedish_ci
CONTENT_PERM_SETCONT_PERM_TYPElatin1latin1_swedish_ci
DECORATORSPACEKEYlatin1latin1_swedish_ci
DECORATORDECORATORNAMElatin1latin1_swedish_ci
DECORATORBODYlatin1latin1_swedish_ci
EXTRNLNKSCONTENTTYPElatin1latin1_swedish_ci
EXTRNLNKSURLlatin1latin1_swedish_ci
EXTRNLNKSCREATORlatin1latin1_swedish_ci
EXTRNLNKSLASTMODIFIERlatin1latin1_swedish_ci
FOLLOW_CONNECTIONSFOLLOWERlatin1latin1_swedish_ci
FOLLOW_CONNECTIONSFOLLOWEElatin1latin1_swedish_ci
IMAGEDETAILSMIMETYPElatin1latin1_swedish_ci
INDEXQUEUEENTRIESHANDLElatin1latin1_swedish_ci
KEYSTOREALIASlatin1latin1_swedish_ci
KEYSTORETYPElatin1latin1_swedish_ci
KEYSTOREALGORITHMlatin1latin1_swedish_ci
KEYSTOREKEYSPEClatin1latin1_swedish_ci
LABELNAMElatin1latin1_swedish_ci
LABELOWNERlatin1latin1_swedish_ci
LABELNAMESPACElatin1latin1_swedish_ci
LINKSDESTPAGETITLElatin1latin1_swedish_ci
LINKSDESTSPACEKEYlatin1latin1_swedish_ci
LINKSCREATORlatin1latin1_swedish_ci
LINKSLASTMODIFIERlatin1latin1_swedish_ci
NOTIFICATIONSUSERNAMElatin1latin1_swedish_ci
NOTIFICATIONSCREATORlatin1latin1_swedish_ci
NOTIFICATIONSLASTMODIFIERlatin1latin1_swedish_ci
NOTIFICATIONSCONTENTTYPElatin1latin1_swedish_ci
OS_PROPERTYENTRYentity_namelatin1latin1_swedish_ci
OS_PROPERTYENTRYentity_keylatin1latin1_swedish_ci
OS_PROPERTYENTRYstring_vallatin1latin1_swedish_ci
OS_PROPERTYENTRYtext_vallatin1latin1_swedish_ci
PAGETEMPLATESTEMPLATENAMElatin1latin1_swedish_ci
PAGETEMPLATESTEMPLATEDESClatin1latin1_swedish_ci
PAGETEMPLATESLABELSlatin1latin1_swedish_ci
PAGETEMPLATESCONTENTlatin1latin1_swedish_ci
PAGETEMPLATESCREATORlatin1latin1_swedish_ci
PAGETEMPLATESLASTMODIFIERlatin1latin1_swedish_ci
PAGETEMPLATESPLUGINKEYlatin1latin1_swedish_ci
PAGETEMPLATESMODULEKEYlatin1latin1_swedish_ci
PLUGINDATAPLUGINKEYlatin1latin1_swedish_ci
PLUGINDATAFILENAMElatin1latin1_swedish_ci
SPACEGROUPPERMISSIONSPERMTYPElatin1latin1_swedish_ci
SPACEGROUPPERMISSIONSPERMGROUPNAMElatin1latin1_swedish_ci
SPACEGROUPPERMISSIONSPERMUSERNAMElatin1latin1_swedish_ci
SPACEGROUPSSPACEGROUPNAMElatin1latin1_swedish_ci
SPACEGROUPSSPACEGROUPKEYlatin1latin1_swedish_ci
SPACEGROUPSLICENSEKEYlatin1latin1_swedish_ci
SPACEGROUPSCREATORlatin1latin1_swedish_ci
SPACEGROUPSLASTMODIFIERlatin1latin1_swedish_ci
SPACEPERMISSIONSPERMTYPElatin1latin1_swedish_ci
SPACEPERMISSIONSPERMGROUPNAMElatin1latin1_swedish_ci
SPACEPERMISSIONSPERMUSERNAMElatin1latin1_swedish_ci
SPACEPERMISSIONSCREATORlatin1latin1_swedish_ci
SPACEPERMISSIONSLASTMODIFIERlatin1latin1_swedish_ci
SPACESSPACENAMElatin1latin1_swedish_ci
SPACESSPACEKEYlatin1latin1_swedish_ci
SPACESCREATORlatin1latin1_swedish_ci
SPACESLASTMODIFIERlatin1latin1_swedish_ci
SPACESSPACETYPElatin1latin1_swedish_ci
SPACESSPACESTATUSlatin1latin1_swedish_ci
TRACKBACKLINKSCONTENTTYPElatin1latin1_swedish_ci
TRACKBACKLINKSURLlatin1latin1_swedish_ci
TRACKBACKLINKSTITLElatin1latin1_swedish_ci
TRACKBACKLINKSBLOGNAMElatin1latin1_swedish_ci
TRACKBACKLINKSEXCERPTlatin1latin1_swedish_ci
TRACKBACKLINKSCREATORlatin1latin1_swedish_ci
TRACKBACKLINKSLASTMODIFIERlatin1latin1_swedish_ci
TRUSTEDAPPNAMElatin1latin1_swedish_ci
TRUSTEDAPPRESTRICTIONTYPElatin1latin1_swedish_ci
TRUSTEDAPPRESTRICTIONrestrictionlatin1latin1_swedish_ci
cwd_app_dir_group_mappinggroup_namelatin1latin1_swedish_ci
cwd_app_dir_mappingallow_alllatin1latin1_swedish_ci
cwd_app_dir_operationoperation_typelatin1latin1_swedish_ci
cwd_applicationapplication_namelatin1latin1_swedish_ci
cwd_applicationlower_application_namelatin1latin1_swedish_ci
cwd_applicationactivelatin1latin1_swedish_ci
cwd_applicationdescriptionlatin1latin1_swedish_ci
cwd_applicationapplication_typelatin1latin1_swedish_ci
cwd_applicationcredentiallatin1latin1_swedish_ci
cwd_application_addressremote_addresslatin1latin1_swedish_ci
cwd_application_addressremote_address_binarylatin1latin1_swedish_ci
cwd_application_attributeattribute_valuelatin1latin1_swedish_ci
cwd_application_attributeattribute_namelatin1latin1_swedish_ci
cwd_directorydirectory_namelatin1latin1_swedish_ci
cwd_directorylower_directory_namelatin1latin1_swedish_ci
cwd_directoryactivelatin1latin1_swedish_ci
cwd_directorydescriptionlatin1latin1_swedish_ci
cwd_directoryimpl_classlatin1latin1_swedish_ci
cwd_directorylower_impl_classlatin1latin1_swedish_ci
cwd_directorydirectory_typelatin1latin1_swedish_ci
cwd_directory_attributeattribute_valuelatin1latin1_swedish_ci
cwd_directory_attributeattribute_namelatin1latin1_swedish_ci
cwd_directory_operationoperation_typelatin1latin1_swedish_ci
cwd_groupgroup_namelatin1latin1_swedish_ci
cwd_grouplower_group_namelatin1latin1_swedish_ci
cwd_groupactivelatin1latin1_swedish_ci
cwd_grouplocallatin1latin1_swedish_ci
cwd_groupdescriptionlatin1latin1_swedish_ci
cwd_groupgroup_typelatin1latin1_swedish_ci
cwd_group_attributeattribute_namelatin1latin1_swedish_ci
cwd_group_attributeattribute_valuelatin1latin1_swedish_ci
cwd_group_attributeattribute_lower_valuelatin1latin1_swedish_ci
cwd_useruser_namelatin1latin1_swedish_ci
cwd_userlower_user_namelatin1latin1_swedish_ci
cwd_useractivelatin1latin1_swedish_ci
cwd_userfirst_namelatin1latin1_swedish_ci
cwd_userlower_first_namelatin1latin1_swedish_ci
cwd_userlast_namelatin1latin1_swedish_ci
cwd_userlower_last_namelatin1latin1_swedish_ci
cwd_userdisplay_namelatin1latin1_swedish_ci
cwd_userlower_display_namelatin1latin1_swedish_ci
cwd_useremail_addresslatin1latin1_swedish_ci
cwd_userlower_email_addresslatin1latin1_swedish_ci
cwd_usercredentiallatin1latin1_swedish_ci
cwd_user_attributeattribute_namelatin1latin1_swedish_ci
cwd_user_attributeattribute_valuelatin1latin1_swedish_ci
cwd_user_attributeattribute_lower_valuelatin1latin1_swedish_ci
cwd_user_credential_recordpassword_hashlatin1latin1_swedish_ci
external_entitiesnamelatin1latin1_swedish_ci
external_entitiestypelatin1latin1_swedish_ci
groupsgroupnamelatin1latin1_swedish_ci
os_groupgroupnamelatin1latin1_swedish_ci
os_userusernamelatin1latin1_swedish_ci
os_userpasswdlatin1latin1_swedish_ci
remembermetokenusernamelatin1latin1_swedish_ci
remembermetokentokenlatin1latin1_swedish_ci
usersnamelatin1latin1_swedish_ci
userspasswordlatin1latin1_swedish_ci
usersemaillatin1latin1_swedish_ci
usersfullnamelatin1latin1_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 3, 2020

Was this helpful?

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