Sunbelt Computer Software

…h the Doctrine DBAL on MySQL

…ismatch with the Doctrine DBAL (samy-mahmoudi) This PR was merged into the 6.4 branch. Discussion ---------- [HttpFoundation] Fix PdoSessionHandler charset-collation mismatch with the Doctrine DBAL ## 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): ```yaml doctrine: dbal: charset: utf8 ``` 2. Configure `PdoSessionHandler` for session storage: ```yaml 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: ```sql 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()`: ```php 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: ```sql 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: ```php $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: ```yaml 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 - **#39969**: [Cache PdoAdapter has hard coded charset for creating cache table (mysql)](symfony/symfony#39969) ## 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](https://symfony.com/doc/current/session.html#mariadb-mysql) 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. Commits ------- 9c8d85bfe1d [HttpFoundation] Fix PdoSessionHandler charset-collation mismatch with the Doctrine DBAL on MySQL

* 6.4: [HttpClient] Fix dealing with truncated streams after headers arrived with CurlHttpClient [PropertyInfo] Fix DocBlock resolution for inherited promoted properties [HttpFoundation] Fix PdoSessionHandler charset-collation mismatch with the Doctrine DBAL on MySQL [HttpClient] Fix dealing with multiple levels of AsyncResponse decoration [Messenger] Only send UNLISTEN query if we are actively listening [RateLimiter] Persist state when consuming negative tokens

* 7.3: [DependencyInjection] Fix lazy proxy type resolution for decorated services [HttpClient] Fix dealing with truncated streams after headers arrived with CurlHttpClient [PropertyInfo] Fix DocBlock resolution for inherited promoted properties [HttpFoundation] Fix PdoSessionHandler charset-collation mismatch with the Doctrine DBAL on MySQL [HttpClient] Fix dealing with multiple levels of AsyncResponse decoration [Messenger] Only send UNLISTEN query if we are actively listening [RateLimiter] Persist state when consuming negative tokens

Sunbelt Computer Software

PL/B Language Development and Support

Navigation Menu

Repository navigation

Comparing changes

Open a pull request

Commits on Jan 27, 2026

This comparison is taking too long to generate.

Uh oh!