Issue Bug Under Review Quiet

#106617MySQL Database Character Set | respect defaultTableOptions -> collation

Claus Harup 04/25/25, 09:17 updated 10/02/26, 15:54

Description

Problem

Having TYPO3 v.13 and mariaDB 10.11 with DB config:

'DB' => [
        'Connections' => [
            'Default' => [
                'charset' => 'utf8mb4',
                'dbname' => 'xxxxxxxxxxxx',
                'defaultTableOptions' => [
                    'charset' => 'utf8mb4',
                    'collation' => 'utf8mb4_unicode_ci',
                ],
                'driver' => 'mysqli',
                'host' => 'db',
                'password' => 'xxxxxxxxxxxxxxxxxxxx',
                'port' => 3306,
                'user' => 'xxxxx',
            ],
        ],
    ],

When I access “Reports” module in backend I get:

Check Failed

> Your default database is set to create tables with character set “utf8mb4_unicode_ci”, but contains tables or columns with different collations. Please fix these tables to avoid “illegal mix of collations” errors.

Cause of the problem

The reason is that sys_refindex hash’s collation is set to ascii_bin – set by the “Analyze Database Structure” tool.

clipboard-202511130843-mfqjh.png

Reviews 1

Files 3

History 1317

  1. Josef Glatz changed attached clipboard-202511130843-mfqjh.png description <span data-processor="relativetime" data-value="2025-11-13T07:43:22+00:00" > 11/13/25, 07:43 </span>

  2. Josef Glatz commented <span data-processor="relativetime" data-value="2025-11-13T07:44:51+00:00" > 11/13/25, 07:44 </span>

    • TYPO3 14.x-dev also affected
    • What about extending the Checks to allow the exact actual state?
  3. Eric Harrer commented and changed attached reports-columnCollationWhitelist.diff <span data-processor="relativetime" data-value="2025-11-13T08:32:46+00:00" > 11/13/25, 08:32 </span>

    In a recent project, I solved this by adjusting the check. Since I didn’t know exactly how to determine the actual state from ext_tables.sql, I made it configurable.

    /*
     * Add column collation white list
     */
    $GLOBALS['TYPO3_CONF_VARS']['DB']['reports']['columnCollationWhitelist'] = [
        'sys_refindex' => [
            'hash' => ['ascii_bin'],
        ],
    ];

    EXT:reports/Classes/Report/Status/ConfigurationStatus.php has been patched with the attached diff.

    Perhaps that will help for now, but taking the actual state into account would be more elegant than this approach.

  4. Dmitry Dulepov commented <span data-processor="relativetime" data-value="2026-01-30T14:59:10+00:00" > 01/30/26, 14:59 </span>

    I would rather fix the column. There is no point to make it different from the rest. Any joins on this column will make conversions inside the database. Any queries will also run (useless) convertion from ascii to utfmb4 because the connection uses utf8mb4. Marking the column as ascii does not improve speed too becase it is ascii anyway and utf8mb4 will still store ascii as normal ascii (1 byte per char) and it adds more conversions on each query.

    Simple solution to the issue:

    - hash varchar(32) DEFAULT '' NOT NULL CHARACTER SET ascii COLLATE ascii_bin,
    + hash varchar(32) DEFAULT '' NOT NULL,
  5. S P commented <span data-processor="relativetime" data-value="2026-02-25T07:59:47+00:00" > 02/25/26, 07:59 </span>

    I agree with both:

    • Dmitry is totally right about this specific column: remove the explicit sys_refindex.hash collation and let it just use the default.
    • But, of course, it also can totally be valid to have different collations on specific columns. So the proposed check should happen in any case. Otherwise on such custom fields (with then valid custom collations) this (then invalid) error will then just pop up again.
  6. Gerrit Code Review pushed patch set 1 for main to change 94660 status Accepted Under Review <span data-processor="relativetime" data-value="2026-07-02T15:03:20+00:00" > 07/02/26, 15:03 </span>

  7. Jan Loderhose commented and changed attached typo3-reports-collation-check.diff <span data-processor="relativetime" data-value="2026-07-28T14:53:45+00:00" > 07/28/26, 14:53 </span>

    Hi, thanks a lot for picking this up!

    I tested André’s change 94660 on a TYPO3 v14.3.5 site with MariaDB and defaultTableOptions.collation = utf8mb4_unicode_ci. Before the patch, five columns were reported:

    • sys_refindex.hash — ascii_bin, declared that way in core’s ext_tables.sql
    • be_users.user_settings, form_definition.configuration, tx_scheduler_task.parameters, tx_scheduler_task.execution_details — all utf8mb4_bin, all json

    After the patch, sys_refindex.hash and be_users.user_settings were correctly exempted, but the other three were still reported. As far as I can tell, the reason is that the declarations come from SqlReader::getTablesDefinitionString(), which only sees ext_tables.sql, whereas those three exist in TCA only (type => json) and get added later by DefaultTcaSchema::enrich(), where JsonFieldType becomes Types::JSON. Concretely: EXT:form’s ext_tables.sql defines no form_definition table at all, and EXT:scheduler’s only has serialized_task_object and serialized_executions. So the JsonType check never gets a chance to see those columns.

    The practical effect is that on a stock v14 install the report would stay red even with the patch applied, which might make the fix look like it didn’t work.

    Would it make sense to take the expected schema from SchemaMigrator::parseCreateTableStatements() instead? That already does SqlReader plus the DefaultTcaSchema enrichment and hands back finished Table objects, so the JsonType check would cover TCA-only columns too, and the report would stay aligned with whatever the Database Analyzer actually creates. It would need the method to become public — I’m not sure how you feel about that, given SchemaMigrator is @internal; the alternative of injecting DefaultTcaSchema and TcaSchemaFactory into the report and repeating the enrichment steps seemed more likely to drift over time, but you’ll have a better sense of that than I do.

    I’ve attached the version I ended up testing — a standalone diff against v14.3.5 containing 94660’s production changes (including the charset/collation guard fix in TableBuilder) plus that one change. With it, all five columns stopped being reported here, and an unfiltered information_schema query confirmed there was nothing else deviating in the database.

    It’s what worked on my install rather than a considered proposal, so please use it however it’s helpful.