Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

Laravel query fails: conversion from one collation into another is impossible

ErrorSQLSTATE[HY000]: General error: 3988 Conversion from collation utf8_unicode_ci into utf8mb4_0900_ai_ci impossible for parameter

Short answer

MySQL is comparing or binding a value whose character set cannot be converted into the one on the other side. Match them: set the collation in config/database.php to the one your tables use, or convert old tables with ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4.

  • Applies to Laravel 12, PHP 8.4, MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Compare the collation the connection uses with the collation of the table the query touches.

    SHOW VARIABLES LIKE 'collation%';
    SHOW CREATE TABLE users;
  2. 2Match what you see, then jump to its fix

    1. 1SHOW VARIABLES shows collation_connection as one value and the table was created with another.The Laravel connection collation differs from the tables'Fix
    2. 2SHOW CREATE TABLE lists CHARSET=utf8 and COLLATE=utf8_unicode_ci, while newer tables use utf8mb4.An old table still uses the legacy utf8 character setFix
    3. 3Only a single raw query fails, and you cannot convert the tables yet.One query must compare values of different collationsFix

What does the error look like?

  • SQLSTATE[HY000]: General error: 3988 Conversion from collation utf8_unicode_ci into utf8mb4_0900_ai_ci impossible for parameter

Cause 1 of 3

The Laravel connection collation differs from the tables'

1Check

The client tells the server which character set and collation to use when it connects; MySQL then sets collation_connection from that. Laravel sends the charset and collation from the mysql block of config/database.php.

php artisan tinker --execute="print_r(config('database.connections.mysql.collation'));"

2Fix

Set charset and collation in the connection to match your tables, using the values you saw in SHOW CREATE TABLE. The Laravel skeleton ships utf8mb4 and utf8mb4_unicode_ci.

// config/database.php, inside 'mysql'
'charset' => env('DB_CHARSET', 'utf8mb4'),
'collation' => env('DB_COLLATION', 'utf8mb4_unicode_ci'),

php artisan config:clear

3Verify

Reconnect and read the session value, then repeat the failing query.

SHOW VARIABLES LIKE 'collation_connection';

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

An old table still uses the legacy utf8 character set

1Check

A join or comparison between a legacy column and a utf8mb4 value mixes character sets.

SHOW CREATE TABLE users;

2Fix

Convert the table, and any other table the query joins, to utf8mb4. This rewrites the table, so take a backup first and run it off-peak.

ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

3Verify

SHOW CREATE TABLE now shows utf8mb4 and the query runs.

SHOW CREATE TABLE users;

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 3

One query must compare values of different collations

1Check

A COLLATE clause overrides the default collation for one comparison.

SELECT * FROM users WHERE email = 'a@example.com' COLLATE utf8mb4_unicode_ci;

2Fix

Add COLLATE to that expression in the raw query as a stopgap, then plan the table conversion.

DB::select("SELECT * FROM users WHERE email = ? COLLATE utf8mb4_unicode_ci", ['a@example.com']);

3Verify

Run the same query again; it returns rows instead of error 3988.

SELECT COUNT(*) FROM users WHERE email = 'a@example.com' COLLATE utf8mb4_unicode_ci;

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Create every table with the same charset and collation as the connection in config/database.php.
  • Check the collation of tables from a restored dump before running migrations on them.
  • Keep one collation across a database and put it in version control.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Databases

Laravel bulk insert fails with 'too many placeholders'

SQLSTATE[HY000]: General error: 1390 Prepared statement contains too many placeholders

Laravel sends one placeholder per value, so one insert of many rows times many columns, or a whereIn with a huge list, creates a statement MySQL refuses.

3 causes, each with a fix

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.