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.

Possible answer: https://stackoverflow.com/questions/11553124/character-encoding-fail-why-does-xbd-display-improperly-in-php-html — user1597430, Jan 15 '18 at 20:42
Looks like the column is supposed to be date time format, not a string. check your schema. — aynber, Jan 15 '18 at 20:43
The problem looks like other, Laravel is not enclosing into quotes. There are commas in my string. The insert error shows column's names with backtick, but column's values don't have quotes. — gvd, Jan 15 '18 at 20:53
Laravel uses parameter binding, which does not quote the values. Show your database schema and the exact code you're using. — aynber, Jan 15 '18 at 20:54
I'm using instance of my Model to insert $myModel = new MyModel(); $myModel->insert($data); when you talk about schema, do you refer to migration? — gvd, Jan 15 '18 at 20:57
Yes, that will work. Make sure you [edit] your question with the code, instead of putting it in a comment. — aynber, Jan 15 '18 at 20:59

score 21 · Accepted Answer · answered Jan 17 '18 at 21:09

21

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

answered Jan 17 '18 at 21:09

gvd

1,482
6
32
58

3

just adding the utf8_encode() method did it for me. Quoting everything literally saved my values with quotes – danielkelly_io Jan 29 '21 at 19:43

score 20 · Answer 2 · edited Mar 25 '22 at 16:14

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.

score 5 · Answer 3 · answered Jun 07 '20 at 12:37

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

score 2 · Answer 4 · answered Jul 26 '20 at 13:25

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**
        ],
]

fatemeh sadeghi · Answer 5 · 2021-05-12T08:26:29.320

follow this steps for solve problem

1- change CHARACTER and COLLATE`s table

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

2-change config /database.php file

 'mysql' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'unix_socket' => env('DB_SOCKET', ''),
        'charset' => 'utf8mb4',//****make sure these configs are set
        'collation' => 'utf8mb4_unicode_ci',//****make sure these configs are set
        'prefix' => '',
        'strict' => true,
        'engine' => null,
     
    ],

3- run this command to regenerate config cache

php artisan config:cache

score 1 · Answer 6 · answered Jan 15 '18 at 21:27

1

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?

answered Jan 15 '18 at 21:27

Rick James

135,179
13
127
222

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? – gvd Jan 15 '18 at 21:36
Laravel is not missing quotes. It's using PDO, prepared statements and parameter binding, which bypasses the need for quotes. – aynber Jan 16 '18 at 13:49
Let's see the _generated SQL_. – Rick James Jan 16 '18 at 14:35

Invalid datetime format: 1366 Incorrect string value

6 Answers6