Zeige mehr…

MySQL Kollationsreparatur: Fallstudie - Reparatur einer Produktionsdatenbank

Inhaltsverzeichnis

Bevor Sie fortfahren

Bevor Sie fortfahren, stellen Sie sicher, dass:

Es ist außerdem sinnvoller wenn Sie diese Änderugen zuerst in einer Testumgebung ausprobieren, bevor Sie die Schritte auf die Produktivumgebung übertragen.

Einleitung

In diesem Guide werden wir eine Confluence 3.5 Instanz mit einer MySQL-Datenbank, die eine falsche Kollation und einen falschen Zeichensatz hat, einrichten. Wir werden durch den Upgradeprozess zur neusten Confluence-Version gehen (zu diesem Zeitpunkt ist das die Version 5.6), während wir die Integrität der Datenbank beibehalten werden. Dies soll für Systemadministratoren eine weitere Hilfe sein die empfohlenen Abfragen zu machen, die hier beschrieben werden: How to Fix the Collation and Character Set of a MySQL Database

Die Ziele

Wir werden eine neue Confluence 3.5.17 Installation mit einer kaputten MySQL-Datenbank verbinden. Zusätzlich werden wir noch Inhalte mit internationalen Zeichen erstellen, die während Upgrades durchaus Probleme erzeugen können (wegen des verwendeten Zeichensatzes):

  1. Einen Benutzer mit einem akzentuierten Zeichen - José
  2. Eine Seite mit dem Titel "Let's have a ¡Celebración!"

Während des Upgrad-Prozesses werden wir die auftretenden Fehlermeldungen beheben, die sich auf die Datenbankkonfiguration beziehen und zugleich sicherstellen, dass die Inhalte korrekt sind.

Confluence 3.5.17 installieren

Die Datenbank erstellen

Als erstes müssen wir die Datenbank mit dem falschen Zeichensatz und der falschen Dekodierung erstellen:

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



Confluence installieren

Confluence 3.5.17 kann im Confluence Download-Archiv heruntergeladen werden. Lesen Sie für weitere Informationen die Dokumentation Installing Confluence Standalone. Sobald Confluence bereit ist, können Sie über diesen Link darauf zugreifen: http://localhost:8080/

Dinge, die es zu beachten gilt:



Inhalte erstellen

Sobald Sie an die Datenbank angebunden sind, werden Sie von Confluence gefragt ob Sie Inhalte importieren möchten. Wählen Sie "Beispielseite >>" aus, damit wir einige Seiten haben, mit welchen wir arbeiten können.

Den Systemadministrator erstellen

Als nächstes fragt Sie Confluence nach den Account-Details für Ihren Administrator. Lassen Sie uns den Benutzer José, mit einem Passwort und einer E-Mail Adresse Ihrer Wahl, anlegen. Stellen Sie sicher, dass der Benutzername und der angezeigte Name beides José ist:

Eine neue Seite erstellen

Nachdem Sie Ihren Admin-Account angelegt haben, ist es nun Zeit eine Seite in Confluence anzulegen:

  1. Klicken Sie auf den Button "Erstellen" und wählen Sie "Seite" aus.
  2. Wählen Sie als Seitentitel und -Inhalt folgendes: "Let's have a ¡Celebración!".
  3. Speichern Sie.

Sie sollten eine Seite erhalten die folgendermaßen aussieht - beachten Sie, dass der Titel und Inhalt Ihrer Seite korrekt angezeigt werden (obwohl die falschen Einstellungen verwendet werden):

Überprüfen Sie die Inhalte der Datenbank

Lassen Sie uns an dieser Stelle einen Moment Zeit dafür nehmen, die Inhalte der Datenbank auf Ihre Richtigkeit zu überprüfen. Starten Sie die folgende Abfrage in Ihrer Datenbank:

SELECT user_name FROM cwd_user;

Sie sollten José korrekt in Ihrer Datenbank sehen - es gibt keine falschen oder ungültigen Zeichen. Trotz der Tatsache, dass die falsche Kollation und der falsche Zeichensatz verwendet werden, wird das akzentuierte Zeichen richtig angezeigt.

Dies hat folgende beiden Gründe:

  1. Das akzentuierte Zeichen ist verfügbar, doch das ist nicht bei allen Zeichen im latin1 Zeichensatz der Fall. Die meisten Zeichen sind in utf8 verfügbar.
  2. Verschiedene Kollationen verhalten sich unterschiedlich. Um eine Konsistenz zu erreichen empfehlen wir Ihnen nur eine Kollation zu verwenden, nämlich utf8_bin

Confluence auf die Version 5.0.3 upgraden

Der nächste Schritt beim Upgrade von Confluence ist es zunächst auf die Version 5.0.3 upzugraden. Diese Version stellt nämlich sicher, dass wir alle notwendigen Upgradeaufgaben starten können, bevor wir zu neueren Confluence-Versionen upgraden. Wie zuvor, können Sie auch Confluence 5.0.3 aus dem Download-Archiv herunterladen. Für weitere Informationen lesen Sie sich bitte Upgrading Confluence durch.

Es ist ebenfalls wichtig, dass Ihre Datenbank korrekt konfiguriert ist und im Einklang mit dem Datenbank-Set-Up für MySQL für Ihre Confluence-Version steht. Nachdem wir die Set-Up-Dokumentation durchgelesen haben, stellen wir fest, dass unsere Datenbank nicht wie benötigt im UTF8-Format ist. Also fixen wir das gleich:

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

Nun ist die Datenbank-Kollation geändert und wir können mit dem Confluence 5.0.3 Upgrade fortfahren. Überprüfen Sie ob Ihre Seite nach dem Upgrade immer noch korrekt angezeigt wird:

Confluence auf die Version 5.6.3 upgraden

Confluence 5.6.3 kommt ohne einen MySQL-Treiber, aus diesem Grund müssen Sie zunächst den richtigen Treiber auf der MySQL-Webseite ermitteln. Lesen Sie für weitere Informationen die Dokumentation über Database JDBC Drivers.

Nun rufen wir Confluence auf. Je nach Ihrer Arbeitsumgebung erhalten Sie eventuell einen von zwei möglichen Fehlern.

Wenn Sie vor dem Upgrade noch eingeloggt waren

Confluence wird versuchen Sie erneut einzuloggen. Deshalb könnten Sie eine Fehlermeldung wie diese erhalten:

Klicken Sie hier, um zu erweitern...

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)

Wenn Sie nicht in Confluence eingeloggt waren

Dann erhalten Sie eine Urlader-Fehlermeldung, so ähnlich wie diese:

Obwohl die Fehler beider Szenarien von Grund auf unterschiedlich sind, haben Sie denselben Auslöser - die Kollation und die Zeichensätze in der Datenbank sind unbeständig.

Kollationen und Zeichensätze in MySQL

Wenn Sie eine Datenbank, eine Tabelle oder eine Spalte erstellen, haben Sie die Möglichkeit den Zeichensätz und die Kollation zu definieren. Wenn Sie das nicht tun, wird das Objekt die der übergeordneten Struktur übernehmen oder solange nach oben hin suchen, bis die Zeichensätze und die Kollation gefunden ist. In unserem Beispiel haben wir eine Datenbank mit der latin1 Kodierung. Als Vorbereitung für das Confluence 5 Upgrade haben wir die Kollation entsprechend unserer Dokumentation angepasst.

Das Ergebnis ist, dass bei der Erstellung neuer Tabellen in Confluence 5, die Zeichensätze und Kodierung der Datenbank übernommen werden - in diesem Fall utf8. Das bedeutet wir haben eine unbeständige Datenbank, weil manche Tabellen utf8 verwenden und andere wiederum latin1.

In Confluence 5.5 gibt es Bootstrap-Kontrollen, die sicherstellen, dass Confluence nicht mit einer unbeständigen Datenbank upgegradet wird, weil sonst neben falschen Formatierungen weitere Probleme auftreten können.

Reparatur der Datenbank

An diesem Punkt prüfen wir ob unsere Datenbank stetige Zeichensätze und Kollationen verwendet. Weil utf8 empfohlen ist, benutzen wir es.

Da wir aber wissen, dass unsere Datenbank die richtige Kollation verwenden (haben wir in einem vorherigen Schritt definiert), dürfen wir ab diesem Punkt beginnen: MySQL Kollationsreparatur: Änderungen auf Tabellen-Ebene. Sofern Sie das nicht schon getan haben, sollten Sie ein Backup von Ihrer Datenbank erstellen und anschließend Confluence abschalten, bevor Sie fortfahren.

Die folgenden Ergebnisse dienen nur als Veranschaulichung und können, je nach Ihrer Installation, abweichend sein.

Abfrage Ergebnis

Zur Identifizierung der fehlerhaften Tabellen:
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'
);

Klicken Sie hier, um zu erweitern...

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

Als nächstes generieren wir das Script zur Reparatur:
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'
);

All diese Abfragen starten wir per Masse. Sie können Sie sich als .sql-Datei exportieren und dann in Ihrer Datenbank ausführen. Nachdem diese Abfragen durchgelaufen sind, gibt es in der Datenbank keine Tabelle mehr, die die falsche Kollation verwendet.

Klicken Sie hier, um zu erweitern...

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;

Nun identifizieren wir die Spalten mit den falschen Kollationen:
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'
);

Klicken Sie hier, um zu erweitern...

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

Wie gewohnt erstellen wir jetzt das Script zur Reparatur - zuerst für die varchar Spalten:
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'
);

Führen Sie auch diese Abfragen in Ihrer Datenbank aus.

Klicken Sie hier, um zu erweitern...

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;

Für die nicht varchar Spalten erstellen wir ebenfalls ein Script zur Reparatur:
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'
);

Klicken Sie hier, um zu erweitern...

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;

An dieser Stelle ist die Datenbank ausreichend repariert, sodass wir Confluence nun wieder starten können. Alle Abfragen zur Identifizierung fehlerhafter Spalten sollten nun 0 Ergebnisse ergeben. Sehen wir uns nun unsere Beispielseite an:

Optional: Falsch dekodierte Inhalte fixen

Eventuell entdecken Sie Inhalte, die nicht korrekt dekodiert wurden. Das ist, weil MySQL die Zeichensätze und Kollationen auf der Verbindungsebene und der Serverebene definieren kann. Diese Zeichensatz- und Kollationsebenen werden in mehreren Servervariablen definiert und werden hier erklärt: Appendix A - Character Set and Collation Variables.

Die Orte der falsch dekodierten Inhalte können variieren. Lesen Sie deshalb bitte die Dokumentation MySQL Kollationsreparatur: Kodierprobleme auf Spalten-Ebene, um passende Abfragen für die erneute Kodierung der Daten zu erhalten.

Apendix A: Zeichensatz- und Kollationsvariablen

character_set_server und collation_server: character_set_connection und collation_connection:

Das sind die standardmäßig definierten Zeichensätze und Kollationen, die der Server verwendet:

  • latin1
  • latin1_swedish_ci.

Diese werden von jeder Datenbank verwendet, außer wenn während des Erstellungsprozesses etwas anderes spezifiziert wurde.

Das sind die Zeichensätze und die Kollation, die bei der Verbindung verwendet werden.

Wenn nicht explizit andere Werte definiert werden, dann werden die gleichen Werte wie bei character_set_server und collation_server verwendet.



Um herauszufinden welche Einstellungen Ihr Server hat, lassen Sie einfach die folgenden Abfragen laufen:

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

Wie Sie diese Variablen permanent definieren können, lesen Sie hier: Configuring Database Character Encoding.

Weiterführende Quellen

Fragen?

Besuchen Sie unsere Hilfeseite für weitere Informationen.

Zeige mehr…
Zeige mehr…