Issue Bug Closed

#110891Reports "MySQL Database Character Set" check reports false positive for columns with explicit CHARACTER SET/COLLATE

Steffen Matthes 10/02/26, 12:05 updated 10/19/26, 20:40

Description

I’m not a TYPO3 core maintainer, just ran into this as an integrator while
debugging a “Check Failed” I couldn’t explain. The patch below is a rough
idea of how this could be fixed — happy to be told if there’s a reason
this is more involved than it looks (e.g. performance of parsing all
ext_tables.sql on every report run, or an existing plan to address this
differently).

Problem

(see attached screenshot: “Reports” module / Install Tool showing the “MySQL Database Character Set” check with status “Check Failed”)
`TYPO3\CMS\Reports\Report\Status\ConfigurationStatus::getMysqlDatabaseUtf8Status()`
reports a “Check Failed” error (“illegal mix of collations”) for ANY column whose
actual collation differs from `$GLOBALS[‘TYPO3_CONF_VARS’][‘DB’][‘Connections’][‘Default’][‘defaultTableOptions’]`,
regardless of whether that deviation is intentional.

This is a false positive for every column that explicitly declares its own
CHARACTER SET/COLLATE in ext_tables.sql – which is an officially supported
mechanism since https://docs.typo3.org/c/typo3/cms-core/main/en-us/Changelog/13.4.x/Important-106508-RespectColumnCharacterSetAndCollationInExt_tablessql.html (“Respect column CHARACTER SET and COLLATE in
ext_tables.sql”). Core’s own `sys_refindex.hash` is an example
(`CHARACTER SET ascii COLLATE ascii_bin`, deliberately chosen to save index
space), so the check fails on every TYPO3 v13.4+/v14 installation with a
non-empty reference index – independent of any third-party extension.

Since https://docs.typo3.org/c/typo3/cms-core/main/en-us/Changelog/13.4.x/Important-106508-RespectColumnCharacterSetAndCollationInExt_tablessql.html made this a documented, supported pattern for extension authors
too, any extension using it will now also trigger this false positive.

Steps to reproduce

1. Fresh TYPO3 v13.4.35 / v14 install, default connection
`defaultTableOptions.collation = ‘utf8mb4_general_ci’`.
2. Rebuild the reference index (populates sys_refindex).
3. Open Backend module “Reports” > tab “Status Report”, or check
EXT:reports’ ConfigurationStatus output.

Expected behavior

Columns that explicitly declare their own CHARACTER SET/COLLATE in
ext_tables.sql (core or extension) and whose live DB collation matches that
declaration should NOT be reported as a mixed-collation problem – only
genuinely unexpected mismatches (e.g. a table imported with a wrong charset)
should.

Suggested fix

Read the intended column-level collation from the already-parsed
ext_tables.sql definitions (same source “Analyze Database Structure” uses)
and exclude matching columns from the error:

--- a/typo3/sysext/reports/Classes/Report/Status/ConfigurationStatus.php
+++ b/typo3/sysext/reports/Classes/Report/Status/ConfigurationStatus.php
@@
 use Doctrine\DBAL\Platforms\MariaDBPlatform as DoctrineMariaDBPlatform;
 use Doctrine\DBAL\Platforms\MySQLPlatform as DoctrineMySQLPlatform;
 use TYPO3\CMS\Backend\Routing\UriBuilder;
 use TYPO3\CMS\Backend\Utility\BackendUtility;
 use TYPO3\CMS\Core\Cache\Backend\MemcachedBackend;
 use TYPO3\CMS\Core\Core\Environment;
 use TYPO3\CMS\Core\Database\ConnectionPool;
+use TYPO3\CMS\Core\Database\Schema\Parser\Parser as SqlStatementParser;
+use TYPO3\CMS\Core\Database\Schema\SqlReader;
 use TYPO3\CMS\Core\Localization\LanguageService;
 use TYPO3\CMS\Core\Registry;
 use TYPO3\CMS\Core\Type\ContextualFeedbackSeverity;
@@
                     if (isset($tableOptions['collation'])) {
                         $collationConstraint = $queryBuilder->expr()->neq('collation_name', $queryBuilder->quote($tableOptions['collation']));
                     } elseif (isset($tableOptions['charset'])) {
                         $collationConstraint = $queryBuilder->expr()->notLike('collation_name', $queryBuilder->quote($tableOptions['charset'] . '%'));
                     }

                     $queryBuilder = $connection->createQueryBuilder();
-                    $wrongCollationColumnsFound = $queryBuilder->select('collation_name')
+                    $wrongCollationColumns = $queryBuilder->select('table_name', 'column_name', 'collation_name')
                         ->from('information_schema.columns')
                         ->where(
                             $queryBuilder->expr()->and(
                                 $queryBuilder->expr()->eq('table_schema', $queryBuilder->quote($connection->getDatabase())),
                                 $collationConstraint
                             )
                         )
-                        ->setMaxResults(1)
-                        ->executeQuery();
+                        ->executeQuery()
+                        ->fetchAllAssociative();

-                    if ($wrongCollationColumnsFound->rowCount() > 0) {
+                    $explicitCollations = $this->getColumnsWithExplicitCollation();
+                    $unexpectedWrongCollationColumns = array_filter(
+                        $wrongCollationColumns,
+                        static function (array $row) use ($explicitCollations): bool {
+                            $key = strtolower($row['table_name']) . '.' . strtolower($row['column_name']);
+                            // A column that intentionally declares its own CHARACTER SET/COLLATE
+                            // in ext_tables.sql (e.g. core's sys_refindex.hash, see #106508) is not
+                            // a mixed collation problem, as long as the database matches it.
+                            return !isset($explicitCollations[$key]) || $explicitCollations[$key] !== $row['collation_name'];
+                        }
+                    );
+
+                    if ($unexpectedWrongCollationColumns !== []) {
                         $message = sprintf($this->getLanguageService()->sL('LLL:EXT:reports/Resources/Private/Language/locallang_reports.xlf:status_MysqlDatabaseCharacterSet_MixedCollations'), $charset);
                         $severity = ContextualFeedbackSeverity::ERROR;
                         $statusValue = $this->getLanguageService()->sL('LLL:EXT:reports/Resources/Private/Language/locallang_reports.xlf:status_checkFailed');
                     }
                 }
             }
         } else {
             $message = $this->getLanguageService()->sL('LLL:EXT:reports/Resources/Private/Language/locallang_reports.xlf:status_MysqlDatabaseCharacterSet_Ok');
         }

         return GeneralUtility::makeInstance(
             ReportStatus::class,
             $this->getLanguageService()->sL('LLL:EXT:reports/Resources/Private/Language/locallang_reports.xlf:status_MysqlDatabaseCharacterSet'),
             $statusValue,
             $message,
             $severity
         );
     }
+
+    /**
+     * Collects "tablename.columnname" => collation for all columns that declare
+     * an explicit CHARACTER SET/COLLATE directly in their ext_tables.sql definition
+     * (see Core Changelog #106508). Those are intentional overrides and must not
+     * be treated as a "mixed collations" problem above.
+     *
+     * @return array<string, string> Keys are lower-cased "tablename.columnname".
+     */
+    protected function getColumnsWithExplicitCollation(): array
+    {
+        $sqlReader = GeneralUtility::makeInstance(SqlReader::class);
+        $parser = GeneralUtility::makeInstance(SqlStatementParser::class);
+        $statements = $sqlReader->getCreateTableStatementArray($sqlReader->getTablesDefinitionString());
+
+        $explicitCollations = [];
+        foreach ($statements as $statement) {
+            try {
+                $tables = $parser->parse($statement);
+            } catch (\Throwable) {
+                // Statement validity is enforced during schema migration, not here.
+                continue;
+            }
+            foreach ($tables as $table) {
+                foreach ($table->getColumns() as $column) {
+                    if ($column->hasPlatformOption('collation')) {
+                        $key = strtolower($table->getName()) . '.' . strtolower($column->getName());
+                        $explicitCollations[$key] = $column->getPlatformOption('collation');
+                    }
+                }
+            }
+        }
+        return $explicitCollations;
+    }
 }

Verified against TYPO3 v13.4.35 (vendor/typo3/cms-reports, vendor/typo3/cms-core)
and a local DDEV MySQL 8.0 instance: `information_schema.columns` only ever
flagged `sys_refindex.hash` (ascii_bin) on an otherwise clean utf8mb4_general_ci
database, confirming this triggers independent of any installed third-party
extension.

Reviews 0

No change resolves or names this issue in its commit message, and no comment links one.

Files 1

History 4

  1. Oliver Bartsch commented and changed status New Closed <span data-processor="relativetime" data-value="2026-10-19T20:40:52+00:00" > 10/19/26, 20:40 </span>

    Hey,

    thanks for the detailed analysis and the patch suggestion. I can confirm the behaviour on main. Since #106508, sys_refindex.hash is deliberately defined as CHARACTER SET ascii COLLATE ascii_bin. ConfigurationStatus::getMysqlDatabaseUtf8Status(), however, still compares every column only against defaultTableOptions. So the status fails as soon as the database schema has been updated, no matter which extensions are installed.

    This is already tracked in #106617, which is under review, so I’m closing this one as a duplicate. Please follow up there. Your notes are a useful addition. The fix should consider the expected per-column and per-table collation from ext_tables.sql, ideally using the same logic as “Analyze Database Structure”. Your sketch covers columns with an explicit COLLATE, but not table-level overrides or columns that declare only a CHARACTER SET.

    Thanks, Oli