Invalid datetime format: 1366 Incorrect string value

I ran into similar problems with Laravel 5.5 and MariaDB 10.2. When storing some user input t into a varchar column, an exception:

SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xE2\x80\x86y\xE2\x80...' for column 'comment' at row 1

will be thrown.

Since this only happened on the stage server but not on local dev server, I compared the collation and charset of underlining table, it turns out database and table on stage server use latin1 while local dev server uses utf8mb4.

The problem was solved by changing database and table collation and char set to utf8mb4 and utf8mb4_unicode_ci.

ALTER DATABASE <db_name> CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

For anyone who runs into this problem, please check collation and char set of your database and table. Chances are Laravel app is encoding with utf8 while databases uses something else.

I solved it, encoding to uft-8 all string columns that generated this error before insert. For example, the column that generated the error was column-name, I encoded as show bellow. Also I found other column with the same error, I used this solution, too.

$data [
//key=>values 
];

$myModel = new MyModel(); 

$data['column-name'] = DB::connection()->getPdo()->quote(utf8_encode($data['column-name']));

$myModel->insert($data);