Laravel Error 1406: Data Too Long for Column, and Why It Is Your Friend

One day the article form in our admin panel started returning a 500 error. Only for some articles, not all. The log said this:

SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'searchable' at row 1

The fix took five minutes. But the lesson is worth a full post, because two important decisions are hidden behind this error that most tutorials skip.

What the error says#

It is simple: the value you want to write into a column is longer than the length defined for that column.

In our case the searchable column was varchar(191) and was filled from the title plus the excerpt. The panel form accepted up to 500 characters of excerpt. Any article with a long excerpt went over the column limit.

Finding the real column#

The error message gives the column name, but it is not always clear which table it belongs to. See the structure with this command:

SHOW CREATE TABLE articles;

Or just the suspect columns:

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM information_schema.COLUMNS
WHERE TABLE_NAME = 'articles';

And to see how long the longest existing value is:

SELECT MAX(CHAR_LENGTH(excerpt)) FROM articles;

Why you do not get the error on your own machine#

This part matters more than the error itself.

MySQL has a setting called sql_mode. If STRICT_TRANS_TABLES is in it, the server raises an error on invalid data. If it is not, the server silently truncates the value, logs a warning and carries on.

See the current value:

SELECT @@sql_mode;

Now imagine strict mode was off. What would have happened?

• The article would be saved.
• You would see no error.
• The search column would be saved half-filled.
• Months later someone would ask "why can't this article be found in search?" and you would have no clue.

The rule we kept from this episode: STRICT_TRANS_TABLES is our friend here. A loud error is better than a silent corruption. If it is on for your server, do not turn it off; if it is off, turn it on in your development environment so you see these bugs sooner.

In Laravel you can set it explicitly in config/database.php:

'mysql' => [
    // ...
    'strict' => true,
],

The fix: which column, which type#

You have three options, and the wrong choice costs you.

The selection rule is simple: look at the limit the form validation allows, not at your own guess. If the form accepts up to 500 characters, the column cannot be 191. The two must always stay in sync.

The corrective migration in Laravel:

Schema::table('articles', function (Blueprint $table) {
    $table->mediumText('searchable')->change();
});

The real trap: why the column was 191 in the first place#

The number 191 is not random. On utf8mb4 tables each character takes up to four bytes, and the old index limit was 767 bytes. 767 divided by 4 is 191. So this number was chosen only to fit in the index. If you are not familiar with this story, the post on error 1071 explains exactly that.

Now the right question is: what is that index even good for?

In our case, the searchable column was only used by the query below:

SELECT * FROM articles WHERE searchable LIKE '%phrase%';

And this is the key point: a leading wildcard makes the index useless. For LIKE '%x%' MySQL cannot use a B-Tree index, because it does not know where in the tree to start. So that index had never been used even once since day one, yet it had limited the column length.

See it yourself with EXPLAIN:

EXPLAIN SELECT * FROM articles WHERE searchable LIKE '%test%';

If you see NULL in the key column and ALL in type, the index was ignored.

So the right fix was: drop the index, do not shorten the column.

Schema::table('articles', function (Blueprint $table) {
    $table->dropIndex('articles_searchable_index');
    $table->mediumText('searchable')->change();
});

The rule: before shortening a column to respect an index limit, ask whether that index is really used by your queries. If not, drop the index, not the column.

If you really need fast search#

Dropping the index means accepting a full table scan. For a few thousand rows that is fine; for a few hundred thousand it is not.

The options, in order of cost:

1. Limit search to the title and excerpt and leave out the full text. Simple, but the results get weaker.
2. A prefix index on the beginning part of the column. Useful only when you do not use a leading wildcard.
3. Full-text search. It has serious limits for non-English languages; we wrote why in multilingual search in MySQL.
4. A separate search engine. Usually not available on shared hosting.

We combined option one with a separate full-text column, and it is more than enough for the current volume. What matters is making the decision consciously, rather than shrinking the column out of fear of the error.

Sibling errors that get confused#

There are three MySQL errors with similar symptoms and completely different causes. Diagnosing correctly is half the way.

Error 1366 is especially familiar to anyone working with non-Latin text or emoji, and its symptom looks like this:

SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xF0\x9F\x99\x82...'

Those bytes are usually an emoji. If the column is utf8 — not utf8mb4 — four-byte characters such as emoji do not fit. utf8 in MySQL is a misleading name and supports at most three bytes.

Check the table's character set:

SELECT TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE();

If you see anything other than utf8mb4, that is the cause. The fix is converting the table, but keep in mind that this conversion increases the byte length of strings and can take you from 1366 to 1071. These three errors are a chain.

What the user should see#

The discussion so far was technical, but there is a user experience point too: error 1406 should never reach the user.

If your form accepts up to 500 characters and the column holds 500 characters, there is no problem. But if validation has no limit, the user sees a 500 error page that says nothing about what to do.

So put the limit in validation too:

'excerpt' => ['nullable', 'string', 'max:500'],

And show it in the form too — a simple character counter, or at least the maxlength attribute on the field. Three layers in agreement: the UI, validation, the database column. If these three match, this error never reaches the user.

A note on derived columns#

The searchable column that caused all this had an important property: it was derived. It was built from other data, not filled in directly by the user.

Derived columns have two specific risks:

1. Their length is not predictable. When a column is built by combining two other fields, its limit equals the sum of their limits — not the limit of one of them. We had forgotten exactly that.

2. They are filled from only one path. If a record is created or restored somewhere else and does not go through that path, the derived column stays empty. Once, a soft-deleted row was missed in a backfill and came back without a value when it was restored.

The rule we kept for good: every backfill on a table with soft deletes must include the deleted rows too.

Article::withTrashed()->chunkById(200, function ($rows) {
    foreach ($rows as $row) {
        // rebuild the derived column
    }
});

The full path after changing a column#

One point that is easy to forget: changing the column type in a migration is only half the job. Follow the whole path:

• The migration
• The model's fillable array
• The request validation rules
• The assignment method in the controller

If you enlarged a column but the validation rule still enforces the old limit, the user still sees an error message — only this time from Laravel, not from MySQL.

Migrating a table that has data#

Changing a column type on an empty table is painless. On a table with data there are a few points.

1. Does changing a column type in Laravel need a package? In current Laravel versions the change method works without extra dependencies. If you have an old project and got an error about a missing package, check this.

2. Changing the type rewrites the column's other attributes. If the column was nullable and you do not write that in the new migration, after the change it is no longer nullable and empty rows throw errors. Write all attributes again:

$table->mediumText('searchable')->nullable()->change();

3. On a large table, altering the structure locks the table. For a few thousand rows it finishes in a fraction of a second, but on large tables it can halt the site for a while. If you have a big table, do this during low-traffic hours.

4. Always have a way back. Write the down method for real, not empty:

public function down(): void
{
    Schema::table('articles', function (Blueprint $table) {
        $table->string('searchable', 191)->nullable()->change();
    });
}

Note, though, that rolling back to a smaller column on data that went over the limit gives the same 1406 error. A rollback is not always free, and it is better to know this before you run it.

Summary#

• Error 1406 means the data is longer than the column limit.
• If you are not getting this error, strict mode is probably off and your data is silently truncated. That is worse.
• Before shrinking a column, ask whether the index that imposed the limit is used at all.
• The column length and the form's validation limit must always match.