CODEWITHSUNDEEP
phpmysqllaravelmariadblaravel-5.5
gvd
gvd

Reputation: 1592

Invalid datetime format: 1366 Incorrect string value

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.

Upvotes: 27

Views: 45542

Answers (7)

HTML
HTML

Reputation: 61

Change the character set:

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

Upvotes: 0

Zhiyong Li
Zhiyong Li

Reputation: 519

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.

Upvotes: 22

fatemeh sadeghi
fatemeh sadeghi

Reputation: 2583

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

Upvotes: 3

Y. Dabbous
Y. Dabbous

Reputation: 39

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

Upvotes: 2

Linuslabo
Linuslabo

Reputation: 1628

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

Upvotes: 6

gvd
gvd

Reputation: 1592

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

Upvotes: 22

Rick James
Rick James

Reputation: 142306

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?

Upvotes: 1

Related Questions