DEV Community

Cover image for Unknown character set index for field received from server
Faizan
Faizan

Posted on Originally published at headsntail.com

Unknown character set index for field received from server

#mysql #database #tutorial

java.sql.SQLException: Unknown character set index for field '255' received from server. If your app suddenly throws this after moving to MySQL 8, the database isn't broken. Your client is too old to understand the collation the server is sending back.

The "unknown character set index for field received from server" error means MySQL sent a collation ID (usually 255, which is utf8mb4_0900_ai_ci) that your driver doesn't know about. Fix it by upgrading the MySQL driver. If you can't, switch the server and your tables to utf8mb4_unicode_ci.

Why unknown character set index for field received from server happens

Every column in a result set comes with metadata, including a numeric collation ID. MySQL 8.0 made utf8mb4_0900_ai_ci the default collation for utf8mb4, and its ID is 255.

Older drivers carry a hardcoded lookup table of collation IDs. That table ends before 255. So when the driver reads the field metadata, it finds an ID it has never seen and gives up.

You'll typically hit it in these situations:

  • An old Java app using a legacy mysql-connector-java 5.1.x jar, pointed at a fresh MySQL 8 server
  • A PHP 7.x build from before mysqlnd learned about the new collations
  • A Docker setup where you bumped the image from mysql:5.7 to mysql:8 and changed nothing else

To reproduce it, take any old client, connect to a default MySQL 8 install, and run SELECT on a table created with default settings. It fails on the first string column.

Error variations you might see instead

The same root cause shows up with different wording depending on the client:

  • Connector/J, at connect time: Unknown initial character set index '255' received from server. Initial client character set can be forced via the 'characterEncoding' property.
  • Connector/J, at query time: Unknown character set index for field '255' received from server.
  • PHP mysqlnd: Server sent charset (255) unknown to the client. Please, report to the developers

Occasionally the number is something else, like 224 or 246, on very old drivers. The fix is the same.

Step 1: Confirm the server is sending collation 255

Log in with the mysql CLI and check what the server defaults to:

SHOW VARIABLES LIKE 'collation_%';
SHOW VARIABLES LIKE 'character_set_server';
Enter fullscreen mode Exit fullscreen mode

If collation_server or collation_database shows utf8mb4_0900_ai_ci, you've found the source. You can confirm the ID mapping directly:

SELECT ID, COLLATION_NAME
FROM information_schema.COLLATIONS
WHERE ID = 255;
Enter fullscreen mode Exit fullscreen mode

Step 2: Upgrade the MySQL driver (the real fix)

This is the fix I'd go for first. Changing collations is a workaround; a current driver simply understands ID 255.

For Java with Maven, replace the old mysql:mysql-connector-java artifact with the current one. If you're on Spring Boot, leave the version out and let Boot manage it:

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <scope>runtime</scope>
</dependency>
Enter fullscreen mode Exit fullscreen mode

Gradle equivalent:

runtimeOnly 'com.mysql:mysql-connector-j'
Enter fullscreen mode Exit fullscreen mode

Not on Spring Boot? Pick the latest version from Maven Central and pin it. Also update the driver class if your config still says com.mysql.jdbc.Driver; the current one is com.mysql.cj.jdbc.Driver. If Spring then complains about the datasource, see this fix for BeanCreationException with database type NONE on MySQL.

For PHP, check your version and mysqlnd build:

php -v
php -i | grep -i mysqlnd
Enter fullscreen mode Exit fullscreen mode

On Windows (XAMPP), use php -i | findstr mysqlnd. If you're on an old PHP 7.x release, upgrading PHP is the clean fix. Current PHP versions handle MySQL 8 collations without any extra config.

Step 3: If you can't upgrade, change the server default collation

Sometimes the client is a vendor app or a legacy service you can't touch. In that case, make the server stop sending 255.

Edit my.cnf (often /etc/mysql/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf on Linux, my.ini on Windows):

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
Enter fullscreen mode Exit fullscreen mode

Restart MySQL:

sudo systemctl restart mysql
Enter fullscreen mode Exit fullscreen mode

For Docker Compose, pass the flags on the command line instead:

services:
  db:
    image: mysql:8
    command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
Enter fullscreen mode Exit fullscreen mode

Gotcha: this only changes defaults for new objects. Tables you already created keep utf8mb4_0900_ai_ci, and their columns can still report 255. That's why most people who "fixed the config" still see the error. Do Step 4.

Step 4: Convert existing tables away from utf8mb4_0900_ai_ci

Find the affected tables:

SELECT TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_db'
  AND TABLE_COLLATION = 'utf8mb4_0900_ai_ci';
Enter fullscreen mode Exit fullscreen mode

Change the database default, then generate the ALTER statements instead of typing them by hand:

ALTER DATABASE your_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

SELECT CONCAT('ALTER TABLE `', TABLE_NAME,
              '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_db'
  AND TABLE_COLLATION = 'utf8mb4_0900_ai_ci';
Enter fullscreen mode Exit fullscreen mode

Copy the output and run it. Back up first. CONVERT TO rebuilds each table, which takes time and locks on large tables, so don't do it on production at noon. The two collations also compare some characters differently, so a unique index can occasionally throw a duplicate-key error during conversion. Test on a copy.

Step 5: Set the charset explicitly in your connection settings

Pin the connection collation so the client and server agree from the first handshake.

JDBC URL:

jdbc:mysql://localhost:3306/your_db?characterEncoding=UTF-8&connectionCollation=utf8mb4_unicode_ci
Enter fullscreen mode Exit fullscreen mode

Laravel, in config/database.php under the mysql connection:

'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
Enter fullscreen mode Exit fullscreen mode

Laravel already defaults to these, but check that nothing in .env or a custom connection overrides them. If your connection parameters are confusing in plain PHP, this breakdown of the host parameter in mysqli_connect() covers the basics.

One opinion: adding characterEncoding=UTF-8 alone to an old driver is a weak fix. It can get you past the "initial" variant at connect time, but the "for field" variant comes from table metadata, so it often comes straight back on the first query.

Step 6: Verify the fix

Check that no columns still use the 0900 collation:

SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_db'
  AND COLLATION_NAME = 'utf8mb4_0900_ai_ci';
Enter fullscreen mode Exit fullscreen mode

You want Empty set. Then restart your app and run the query that failed before. If you're on Laravel, a quick php artisan migrate:status hits the database and is a fast smoke test. If seeding breaks after the collation change, this guide on migrate:fresh --seed not seeding into MySQL may help.

For future migrations, keep the collation consistent so new tables don't drift back to the server default. If you'd rather have someone check your setup directly, you can contact HeadsNTail.

FAQ

What is character set index 255 in MySQL?

It's the numeric ID of the utf8mb4_0900_ai_ci collation, the default for utf8mb4 in MySQL 8.0. Drivers written before MySQL 8 don't have it in their lookup table, which is why they reject it.

Why did this error start after upgrading to MySQL 8?

MySQL 5.7 defaulted to collations your old driver already knew. MySQL 8 changed the default to utf8mb4_0900_ai_ci, so any table created on the new server, or imported without an explicit collation, now sends ID 255 to the client.

Is utf8mb4_unicode_ci worse than utf8mb4_0900_ai_ci?

utf8mb4_0900_ai_ci follows a newer Unicode collation standard, so sorting and comparison can be more accurate for some languages. For most English or mixed-language web apps, utf8mb4_unicode_ci works fine. Still, upgrading the driver and keeping the MySQL 8 default is the better long-term choice.

Does adding characterEncoding=UTF-8 to the JDBC URL fix it?

Sometimes, for the "Unknown initial character set index" variant raised at connect time. It usually doesn't fix the "for field" variant, because that ID comes from the column metadata. Upgrade the connector or convert the tables.

If you see "unknown character set index for field received from server" again later, check whether a new table was created with the server default, then rerun the Step 6 query to find it.

Originally published at headsntail.com. Free tools for PHP, Laravel and cPanel work: headsntail.com/tools.

Top comments (0)