Laravel Error Message : 1406 Data too long for column

Posted on

This is an article which is showing a message generated in the process of inserting record in a table exist located in a database used by a Laravel web-based application development. The error is shown in the following output of page which is shown as follows :

Illuminate \ Database \ QueryException (22001)
SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'state' at row 1 (SQL: insert into `users` (`name`, `email`, `password`, `first_name`, `last_name`, `city`, `state`, `phone`, `updated_at`, `created_at`) values (mike, mike.rowling@gmail.com, $2y$10$Mh4tYElfB11u1foRi0ZIm.Tq9ex.ygU6sLtorw9CPjiMaszgUFvWy, Mike, Rowling, Boston, New York, xxxxxxxx, 2017-11-07 07:27:58, 2017-11-07 07:27:58))

Another information is also shown as follows which is extracted from a file named laravel.log located in the storage/logs folder :

[2017-11-07 07:29:38] local.ERROR: SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'state' at row 1 (SQL: insert into `users` (`name`, `email`, `password`, `first_name`, `last_name`, `city`, `state`, `phone`, `updated_at`, `created_at`) values (mike, mike.rowling@gmail.com, $2y$10$l/hRf2UH7qiiy7r9SnRsIuMg3hO.Spe3Mh1toM32ozpW4BmhsFp3m, Mike, Rowling, Boston, New York, xxxxxxxx, 2017-11-07 07:29:38, 2017-11-07 07:29:38)) {"exception":"[object] (Illuminate\\Database\\QueryException(code: 22001): SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'state' at row 1 (SQL: insert into `users` (`name`, `email`, `password`, `first_name`, `last_name`, `city`, `state`, `phone`, `updated_at`, `created_at`) values (mike, mike.rowling@gmail.com, $2y$10$l/hRf2UH7qiiy7r9SnRsIuMg3hO.Spe3Mh1toM32ozpW4BmhsFp3m, Mike, Rowling, Boston, New York, xxxxxxxx, 2017-11-07 07:29:38, 2017-11-07 07:29:38)) at /var/www/html/laravel/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664, PDOException(code: 22001): SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'state' at row 1 at /var/www/html/laravel/vendor/laravel/framework/src/Illuminate/Database/Connection.php:458)
[stacktrace]

The problem is in the column named ‘state’ which is already generated as part of a table. The state column only defined for storing 2 characters. But in the above insert process, the data which is passed and it is trying to be stored is more than 2 characters in length. It is shown in the above as an example is ‘New York’ as the entry filled in the column ‘state’. Since it is more than 2 characters, it will generate an error that “Data too long for column ‘state’ at row”. To resolve the problem , just change the column definition to have larger character definition. It can be done by two methods as shown below :

1. Using directly access to MySQL Database Server

alter table users change state state(100);

2. Using migration script file available which can be executed using php artisan tool available inside the Laravel web-based application project. It shown in the following snippet code inside the migration script file handled for creating users table :

Schema::create('users', function (Blueprint $table) {
$table->increments('id');
$table->string('name');
$table->string('email')->unique();
$table->string('password');
$table->string('first_name',50);
$table->string('last_name',50);
$table->string('city',100)->nullable();
$table->string('state',100)->nullable();
$table->string('phone',12)->nullable();
$table->rememberToken();
$table->timestamps();
});

One thought on “Laravel Error Message : 1406 Data too long for column

Leave a Reply