Inhaltsverzeichnis
Bevor Sie fortfahren
Bevor Sie fortfahren, stellen Sie sicher, dass:
- Confluence abgeschaltet ist
- Ein vollständiges Datenbank Backup vorliegt
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):
- Einen Benutzer mit einem akzentuierten Zeichen - José
- 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:
- Der MySQL-Treiber ist in dieser Confluence-Version bereits inkludiert
- Stellen Sie sicher, dass Sie "Production Installation" ausgewählt haben und Sie die JDBC-Verbindung verwenden
- Wenn Sie eine Lizenz benötigen können Sie entweder eine Evaluationslizenz oder eine Entwicklerlizenz generieren
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:
- Klicken Sie auf den Button "Erstellen" und wählen Sie "Seite" aus.
- Wählen Sie als Seitentitel und -Inhalt folgendes: "Let's have a ¡Celebración!".
- 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:
- 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.
- 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: 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