Invalid datetime format: 1366 Incorrect string value

Question

I'm getting this error:

SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xBD Inch...' for column 'column-name' at row 1

My database, table, and column have the format utf8mb4_unicode_ci also column-name is type text and NULL.

This is the value of the column-name

[column-name] => Some text before 11 ▒ and other text after, and after.

However I wait that laravel adds quotes to column's values, because the values are separated by commas (,). It should be as follow:

[column-name] => 'Some text before 11 ▒ and other text after, and after.'

See below the Schema

    Schema::create('mws_orders', function (Blueprint $table) {
        $table->string('custom-id');
        $table->string('name');
        $table->string('description')->nullable();
        $table->string('comment')->nullable();
        $table->integer('count')->nullable();
        $table->text('column-name')->nullable();
        $table->timestamps();

        $table->primary('custom-id');
    });

I have been looking for on google but not any solution, yet.

Anyone has an idea how to solve this issue?

I'm using Laravel 5.5 and MariaDB 10.2.11.

Solution 1

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

Solution 2

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.

Solution 3

If mb-convert-encoding or utf8-encode are not solving this problem for you, check if you're only using string functions in their multibyte variants.

e.g. Instead of substr you must use mb_substr

Doc reference here: Multibyte String Functions

Written for future readers who might end up with my same problem :)

Solution 4

BD is the latin1 (and several others) encoding for ½ (one-half). The error message talks about storing that in a datetime. So, it sounds like there are at least two errors --

• mismatch of CHARACTER SETs
• poorly formulated query

You show us something about the CREATE TABLE, but why would "inches" be involved in that?

Solution 5

Just change database configuration (charset & collation) in

config/database.php

to:

'connections' => [
        'mydb' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST'),
            'database' => env('DB_DATABASE'),
            'username' => env('DB_USERNAME'),
            'password' => env('DB_PASSWORD'),
            'charset'   => 'utf8mb4',              // **for emoticons**
            'collation' => 'utf8mb4_unicode_ci',   // **for emoticons**
        ],
]

View more solutions

Comments

[gvd]

The problem is really relate to missing quotes, I took the raw query, add quotes to each value and run directly in the database, and inserted correctly. Now it turns to another question: why Laravel is missing quotes and how solve it?

[aynber]

Laravel is not missing quotes. It's using PDO, prepared statements and parameter binding, which bypasses the need for quotes.

[Rick James]

Let's see the generated SQL.

[danielkelly_io]

just adding the utf8_encode() method did it for me. Quoting everything literally saved my values with quotes