CODEWITHSUNDEEP
phpmysqllaraveltexttypes
Sergej Fomin
Sergej Fomin

Reputation: 2002

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

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

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...

Upvotes: 44

Views: 160418

Answers (14)

rodrigoserafim
rodrigoserafim

Reputation: 23

You shouldn't change the column type if it doesn't make sense, for example, if it is a name, don't change the column type, instead, you should try a verification on the request, example:

 public function store(Request $request)
    {
        $request->validate([
            'name' => 'max:255',
        ]);
    }

with this, Laravel automatically returns an error message: "The name must not be greater than 255 characters."

But, if it is a bigger thing, like a comment, or something else that requires a lot characters, on that occasion, you should change the column type to text() or longText(), example:

return new class extends Migration
{
    public function up()
    {
        Schema::table('table_name', function (Blueprint $table) {
            $table->text('column_name')->change();
        });
    }

    public function down()
    {
        Schema::table('table_name', function (Blueprint $table) {
            $table->string('column_name')->change();
        });
    }
};

and then, run php artisan migrate.

Upvotes: 0

UrbanwarfareStudios
UrbanwarfareStudios

Reputation: 360

I came across this error recently and I just wanted to share this in case someone else get this issue to save you some time.

If you are constructing your SQL to be inserted and you seperate it over serveral lines to make it legible - it's possible to end up with a newline within the value of the varchar.

MySQL will see this and treat the content like TEXT rather than a string and will throw this error even if the number of characters in your value is less than 255.

Upvotes: 0

user13493837
user13493837

Reputation:

When I received this error, it was because existing data in the table did not conform to the new length. So I just increased the length and it worked.

I was trying:

 $table->string('name', 10)->unique()->change();

To change the column from TinyText and make it unique. But one column had 12 characters. So I made it this:

$table->string('name', 20)->unique()->change();

Upvotes: 0

Opemipo Alomaja
Opemipo Alomaja

Reputation: 21

$table->text('body'); <===== change the string to text

  1. string can only support 256 character
  2. text support more

Upvotes: 0

Petr Sobotka
Petr Sobotka

Reputation: 733

In my case it was connected to the update of MariaDB.

What helped:

In Mariadb config file (linux machine) /etc/mysql/mariadb.conf.d/50-server.cnf in the section [mariadb] I added this line:

sql_mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Restart MariaDB and the exception is gone.

Upvotes: 0

hacknull
hacknull

Reputation: 415

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();
    });
}

Upvotes: 0

Rohit Kothari
Rohit Kothari

Reputation: 11

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

Upvotes: 1

Nitish Nain
Nitish Nain

Reputation: 11

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

Upvotes: 1

Ing Oscar MR
Ing Oscar MR

Reputation: 779

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

Upvotes: 7

Guvanch Hojamov
Guvanch Hojamov

Reputation: 346

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

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

Upvotes: 2

Abi Raj Nijanandi
Abi Raj Nijanandi

Reputation: 41

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

Upvotes: 4

JDK Ben
JDK Ben

Reputation: 281

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.

Upvotes: 28

Pramodya Abeysinghe
Pramodya Abeysinghe

Reputation: 1210

change the type of column fromstring to text.

Then run a migrate refresh using php artisan migrate:refesh

Upvotes: 2

Alexey Mezenin
Alexey Mezenin

Reputation: 163748

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();
});

Upvotes: 31

Related Questions