#110891Reports "MySQL Database Character Set" check reports false positive for columns with explicit CHARACTER SET/COLLATE
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
-
Anton Danilov changed Related to #106617 <span data-processor="relativetime" data-value="2026-10-02T13:08:34+00:00" > 10/02/26, 13:08 </span>
-
Anton Danilov changed
Related to #106617<span data-processor="relativetime" data-value="2026-10-02T13:08:41+00:00" > 10/02/26, 13:08 </span> -
Anton Danilov changed Duplicates #106617 <span data-processor="relativetime" data-value="2026-10-02T13:08:48+00:00" > 10/02/26, 13:08 </span>
-
Oliver Bartsch commented and changed status
NewClosed <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.hashis deliberately defined asCHARACTER SET ascii COLLATE ascii_bin.ConfigurationStatus::getMysqlDatabaseUtf8Status(), however, still compares every column only againstdefaultTableOptions. 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