8000 [HttpFoundation] Fix PdoSessionHandler charset-collation mismatch with the Doctrine DBAL by samy-mahmoudi · Pull Request #63137 · symfony/symfony · GitHub
[go: up one dir, main page]
Skip to content

[HttpFoundation] Fix PdoSessionHandler charset-collation mismatch with the Doctrine DBAL #63137

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Merged
nicolas-grekas merged 1 commit into from
Jan 27, 2026
Merged

[HttpFoundation] Fix PdoSessionHandler charset-collation mismatch with the Doctrine DBAL #63137

nicolas-grekas merged 1 commit into from
Jan 27, 2026
+3 −4

Conversation

@samy-mahmoudi
Copy link
Contributor
@samy-mahmoudi samy-mahmoudi commented Jan 20, 2026 •
edited
Loading

Symfony version(s) affected

6.4 (and likely all versions since configureSchema() was introduced)

Description

Using the PdoSessionHandler with MySQL via the Doctrine DBAL and running make:migration result in an invalid migration under some conditions:

SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION 'utf8mb4_bin' is not valid for CHARACTER SET 'utf8mb3'

This occurs — in particular — because PdoSessionHandler::configureSchema() sets the collation to utf8mb4_bin but does not set the corresponding charset table option.

How to reproduce

  1. Configure the Doctrine DBAL with charset: utf8 (or leave it unset in DBAL 3.x):

    doctrine:
    dbal:
        charset: utf8

  2. Configure PdoSessionHandler for session storage:

    framework:
    session:
        handler_id: Symfony\Component\HttpFoundation\Session\Storage\Handler\PdoSessionHandler

services:
   Symfony\Component\HttpFoundation\Session\Storage\Handler\PdoSessionHandler:
      arguments:
         - 'mysql:host=%database_host%;port=%database_port%;dbname=%database_name%'
         - { db_username: '%database_user%', db_password: '%database_password%', lock_mode: 2 }

  3. Run php bin/console make:migration

  4. Inspect the generated migration — it contains an incompatible charset-collation combination:

    DEFAULT CHARACTER SET utf8 COLLATE `utf8mb4_bin`

  5. Run php bin/console doctrine:migrations:migrate

  6. Observe the failure in migration with the aforementioned charset-collation mismatch error.

Root Cause Analysis

In PdoSessionHandler::configureSchema():

case 'mysql':
    // ...
    $table->addOption('collate', 'utf8mb4_bin');  // <- Sets collation only
    $table->addOption('engine', 'InnoDB');
    break;

The method sets collate but not charset.

The utf8mb4_bin collation is bound to the utf8mb4 character set: no other character set produces a valid MySQL statement. While one might aim for generality and/or optimality by omitting the charset and letting MySQL infer it from the collation, the Doctrine DBAL may supply a charset from its configuration hierarchy, producing an invalid combination like:

DEFAULT CHARACTER SET utf8 COLLATE utf8mb4_bin

Charset Resolution Hierarchy

When building table options, the Doctrine DBAL resolves the charset in this order (the lower the rank, the higher the precedence):

Rank Source Description
1 $table->addOption('charset', ...) Per-table override (e.g., in configureSchema())
2 doctrine.dbal.default_table_options.charset Explicit Doctrine config
3 doctrine.dbal.charset Connection charset (copied to defaultTableOptions by AbstractSchemaManager::createSchemaConfig())
4 Hardcoded default 'utf8' DBAL 3.x only; removed in DBAL 4.x

Behavior Matrix: The Doctrine DBAL 3.x

In DBAL 3.x, if no charset is configured at any level, the hardcoded default 'utf8' is used and a DEFAULT CHARACTER SET clause is always emitted.

Case dbal.charset default_table_options.charset Handler sets charset? Effective Charset Collation MySQL Statement
1 not set not set No utf8 (hardcoded) utf8mb4_bin Invalid
2 not set not set Yes (utf8mb4) utf8mb4 utf8mb4_bin Valid
3 not set utf8mb4 No utf8mb4 utf8mb4_bin Valid
4 not set utf8mb4 Yes utf8mb4 utf8mb4_bin Valid
5 utf8 not set No utf8 (copied) utf8mb4_bin Invalid
6 utf8 not set Yes (utf8mb4) utf8mb4 utf8mb4_bin Valid
7 utf8 utf8mb4 No utf8mb4 utf8mb4_bin Valid
8 utf8 utf8mb4 Yes utf8mb4 utf8mb4_bin Valid
9 utf8mb4 not set No utf8mb4 (copied) utf8mb4_bin Valid
10 utf8mb4 not set Yes utf8mb4 utf8mb4_bin Valid

Behavior Matrix: The Doctrine DBAL 4.x

In DBAL 4.x, the hardcoded 'utf8' default was removed. If no charset is configured at any level, no DEFAULT CHARACTER SET clause is emitted, allowing MySQL to infer the charset from the collation.

Case dbal.charset default_table_options.charset Handler sets charset? Effective Charset Collation MySQL Statement
1 not set not set No none utf8mb4_bin Valid (MySQL infers)
2 not set not set Yes (utf8mb4) utf8mb4 utf8mb4_bin Valid
3 not set utf8mb4 No utf8mb4 utf8mb4_bin Valid
4 not set utf8mb4 Yes utf8mb4 utf8mb4_bin Valid
5 utf8 not set No utf8 (copied) utf8mb4_bin Invalid
6 utf8 not set Yes (utf8mb4) utf8mb4 utf8mb4_bin Valid
7 utf8 utf8mb4 No utf8mb4 utf8mb4_bin Valid
8 utf8 utf8mb4 Yes utf8mb4 utf8mb4_bin Valid
9 utf8mb4 not set No utf8mb4 (copied) utf8mb4_bin Valid
10 utf8mb4 not set Yes utf8mb4 utf8mb4_bin Valid

Summary

DBAL Version Bug Cases Description
3.x Cases 1 and 5 Hardcoded default + Charset propagation from connection
4.x Case 5 only Charset propagation from connection

Proposed Solution

Since utf8mb4_bin is bound to utf8mb4, explicitly setting the charset ensures valid MySQL in all cases:

$table->addOption('charset', 'utf8mb4');
$table->addOption('collate', 'utf8mb4_bin');

Workarounds

Users can work around this by either:

  1. Configuring default_table_options in the Doctrine DBAL to set a compatible charset:

    doctrine:
    dbal:
        default_table_options:
            charset: utf8mb4

  2. Manually editing the generated migration to change the charset to utf8mb4

  3. Using $handler->createTable() directly instead of make:migration (this method uses raw SQL and lets MySQL infer the charset from the collation)

Related Issues

Additional Context

  • The utf8mb4 charset has been the recommended MySQL default since MySQL 5.5.3 (2010) and is required for full Unicode support (including emojis).
  • The Symfony documentation shows SQL examples with COLLATE utf8mb4_bin but does not mention the Doctrine charset requirement.
  • The createTable() method in PdoSessionHandler uses raw SQL (COLLATE utf8mb4_bin without explicit CHARACTER SET) and works correctly because MySQL infers the charset. The configureSchema() method, however, goes through the Doctrine DBAL which may add an explicit CHARACTER SET clause.

@carsonbot carsonbot added this to the 6.4 milestone Jan 20, 2026
@symfony symfony deleted a comment from carsonbot Jan 22, 2026
@nicolas-grekas
Copy link
Member
nicolas-grekas commented Jan 22, 2026

Hello, thanks for the PR!

I dug the git history a bit and found that this collation thing has been introduced in #10931 for the createTable method.

This makes me wonder about two things:

  • The request in the createTable method must also be updated.
  • Isn't the correct fix to simply remove the collation thing? I don't get why it's needed. Can you give it a try on your use case?

About the PR itself, the description above misses the table that's in the default template (it was displayed before your replaced it by your own content, see .github/PULL_REQUEST_TEMPLATE.md file).

About the current PR description, it's obviously generated by an LLM, and WAY to long for anyone to pay attention. Please try to improve this and find a description that goes straight to the point.

@nicolas-grekas nicolas-grekas force-pushed the fix-a-bug-in-the-pdo-session-handler branch from e3869f7 to 9c8d85b Compare January 27, 2026 15:05
nicolas-grekas
nicolas-grekas approved these changes Jan 27, 2026
View reviewed changes
Copy link
Member
@nicolas-grekas nicolas-grekas left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

I went ahead with my suggestion.

@nicolas-grekas
Copy link
Member
nicolas-grekas commented Jan 27, 2026

Thank you @samy-mahmoudi.

@nicolas-grekas nicolas-grekas merged commit 668123e into symfony:6.4 Jan 27, 2026
9 of 12 checks passed
This was referenced Jan 28, 2026
Merged
Merged
Merged
Merged
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

@nicolas-grekas nicolas-grekas nicolas-grekas approved these changes

@jderusse jderusse Awaiting requested review from jderusse jderusse is a code owner

Assignees

No one assigned

Labels

HttpFoundation Status: Reviewed

Projects

None yet

Milestone

6.4

Development

Successfully merging this pull request may close these issues.

3 participants

@samy-mahmoudi @nicolas-grekas @carsonbot
0