Laravel query fails: conversion from one collation into another is impossible
SQLSTATE[HY000]: General error: 3988 Conversion from collation utf8_unicode_ci into utf8mb4_0900_ai_ci impossible for parameterShort 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.
Fix it fast
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;2Match what you see, then jump to its fix
- 1
SHOW VARIABLES shows collation_connection as one value and the table was created with another.The Laravel connection collation differs from the tables'Fix - 2
SHOW 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
Only a single raw query fails, and you cannot convert the tables yet.One query must compare values of different collationsFix
- 1
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:clear3Verify
Reconnect and read the session value, then repeat the failing query.
SHOW VARIABLES LIKE 'collation_connection';Confirmed by dev.mysql.com
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
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
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:
- Spotless LaundryAn on-demand laundry app: Flutter on a Laravel back end, with Firebase for live updates.
- EZ EstimaterConstruction quoting and job tracking for contractors, on a Laravel and Firebase stack.
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
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 fixDatabases
Laravel migration fails on managed MySQL because a table has no primary key
Add a primary key to the table or unset this variable to avoid this message.
The server has sql_require_primary_key switched on, so every CREATE TABLE or ALTER TABLE must leave the table with a primary key.
3 causes, each with a fixDatabases
PHP cannot connect to MySQL: the connection attempt failed
Error 2003
Error 2003 means the TCP connection to MySQL never opened.
4 causes, each with a fixDatabases
The client cannot reach the MySQL or MariaDB server through its Unix socket file
ERROR 2002 (HY000): Can't connect to local server through socket '/run/mysqld/mysqld.sock' (2) (MariaDB client wording)
Error 2002 means the client tried the Unix socket file named in the message and found nothing it could talk to.
4 causes, each with a fixDatabases
MySQL or MariaDB: Column count of mysql.proc is wrong, table probably corrupted
Error 1558: Column count of mysql.proc is wrong
The mysql.proc system table has a different number of columns than the running server expects.
3 causes, each with a fixDatabases
Saving an empty date from PHP into a MySQL DATETIME column fails
The column holds 0000-00-00 00:00:00 instead of NULL
An empty string is not NULL.
4 causes, each with a fix