Laravel: String data, right truncated: 1406 Data too long for column

26
votes

I have a table with a column 'hotel'. The project is created in Laravel 5.4, so I used Migrations.

$table->string('hotel', 50);

This is MYSQL VARCHAR (50). It was working good, because when I was developing I used short hotel names like "HILTON NEW YORK 5"*.

Now the project is on production and customer asked why they can't input long hotel names. I've tested it with such a mock hotel name as "Long long long long long long long long long and very-very-very long hotel name 5 stars"

It gave me an error:

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

I've opened database in my Sequel Pro and changed it

  • first to VARCHAR (255)
  • then to TEXT

After each change I tested it with the same "Long long long long long long long long long and very-very-very long hotel name 5 starts" and get the same error (see above).

I've checked the type of column with

SHOW FIELDS FROM table_name

and it gave me

Field | Type

hotel | text

so the type of the field is 'text' indeed (65 535 characters).

Maybe it's somehow connected with Laravel Migration file (see above) where I set VARCHAR (50) in the beginning? But I can't re-run migration on production, because the table has data now.

Would appreciate any help.

UPDATE: I discovered that it actually saves that long hotel name in the DB. But user still gets this annoying mistake every time after submitting the form...

9
phpmysqllaraveltexttypes
You most certainly can create a migration to change the field type. As long as it's something similar it shouldn't be a problem. - Matthew Daly
As always, try it on your local copy just in case, but I don't see why it wouldn't work. - Matthew Daly
Hi, guys. Thank you. I've created a new migration as Alexey explained in his answer below and ran it (on local), but the erro is still shows up every time form is submitted....:( - Sergej Fomin

9 Answers

20
votes

You need to create a new migration, register it with composer du command and run php artisan migrate command to change type of the column:

Schema::table('the_table_name', function (Blueprint $table) {
    $table->string('hotel', 255)->change();
});
12
votes

On your local development, try changing the column type to:

$table->longText('columnName')

from your migration file. That solved it for me. But if you have gone live, then create a new migration just as Alexey has suggested and then use longText() column type.

3
votes

Change column's datatype from string to text and do not give length.

2
votes

I was storing pictures as base64 on a text colum so I got a SQL error:

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

I did my migration as 

$table->text('picture')

then I changed de column picture as:

$table->mediumText('picture')

I realiced that text column allows to store only 64 KB

TEXT: 65,535 characters - 64 KB MEDIUMTEXT: 16,777,215 - 16 MB LONGTEXT: 4,294,967,295 characters - 4 GB

For more info visit: understanding-strorage-sizes-for-mysql-text-data-types

1
votes

change the type of column fromstring to text.

Then run a migrate refresh using php artisan migrate:refesh

1
votes

Change column's datatype from string to text and do not give length.

$table->text('hotel')->change();
1
votes

Solution of this error "SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'hotel' at row 1" open Mysql and click on change button in the furent of that column and make length/values = 111

0
votes

Change data type of the column in the database to 'longtext' if 'text' data type not working

0
votes
public function up()
{
    Schema::table('news', function (Blueprint $table) {
        $table->text('content')->change();
    });
}

public function down()
{
    Schema::table('news', function (Blueprint $table) {
        $table->string('content', 65536)->change();
    });
}