Laravel migrate rollback error, Data too long for column

Question

I am using Laravel 5.3, and the migration is really awesome to control db development.

My question is when I change the column type from string to text, everything is working well. But after user save data which length longer than 255(varchar). Then my migrate can't rollback. It will say Data too long for my column. Would like to ask everyone how to solve this problem?

=========================================================

Schema::table('tbname', function(Blueprint $table)
{
    $table->text('value')->change();
});

Schema::table('tbname', function(Blueprint $table)
{
    $table->string('value')->change();
});

=========================================================

Seeder:

$records = [
    [
        'description' => 'The description is longer than 255. The description is longer than 255. The description is longer than 255. The description is longer than 255. The description is longer than 255. The description is longer than 255. The description is longer than 255. The description is longer than 255. The description is longer than 255. The description is longer than 255. The description is longer than 255. '
    ],
    [
        'description' => 'The description is longer than 255. The description is longer than 255. The description is longer than 255. The description is longer than 255. The description is longer than 255. The description is longer than 255. The description is longer than 255. The description is longer than 255. The description is longer than 255. The description is longer than 255. The description is longer than 255. '
    ]
];

foreach ($records as $index => $record) {
    Desc::create($record);
}

Need more details here. Please add the migration code and the table description from the DB to the question. — linuxartisan
If you are using MySQL version < 5.7 then this error recently I was also come up with this error. You can find solution here — Ahmed Khan
Any fix for this issue? I have the same exact issue on L5.4 during rollback. — Oluwatobi Samuel Omisakin
@OmisakinOluwatobi I have updated my answer to provide a little more explanation, as well as a potential workaround. — patricus

patricus patricus · Accepted Answer · 2017-03-10T08:03:37

You have a text field that has more than 255 characters in it. In your rollback, you're attempting to change the field to varchar(255). At this point, MySQL has a decision to make: either truncate the data to fit into 255 characters, or throw an error.

If using MySQL in strict mode, MySQL will throw an error. If not using strict mode, MySQL will truncate the data without error.

Laravel 5.3 was changed to use strict mode by default.

If you want to stop getting this error, you can disable strict mode by setting 'strict' => false in your config/database.php file.

Please note that while this will suppress the error, there are consequences. The data that previously caused this error will now just be truncated without any error.

If you only want to disable strict mode for the rollback, you can create a second database connection with strict mode disabled, and the tell your migration to use that connection for the rollback. Remember, though, this will truncate all data in this field longer than 255 characters.

config:

'connections' => [
    'mysql' => [
        /* normal connection information */
        'strict' => true,
    ],
    'mysql-rollback' => [
        /* normal connection information */
        'strict' => false,
    ],
],

migration:

public function down()
{
    Schema::connection('mysql-rollback')->table('tbname', function(Blueprint $table) {
        $table->string('value')->change();
    });
}

Laravel migrate rollback error, Data too long for column

2 Answers