kriolo_developer's avatar

SQLSTATE[HY000]: General error: 1366 Incorrect string value

Hello guys,

I'm almost pulling my hair over here after trying to solve this problem for hours. The thing is, whenever I try to run my database seeder, I keep getting this error telling me I have a incorrect string value.

[Illuminate\Database\QueryException]                                         
  SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xE3\xB3nica'  
   for column 'nome' at row 1 (SQL: insert into `utilizadores` (`nome`, `apel  
  ido`, `email`, `password`, `verificado`, `token_verificacao`, `id_unidade`,  
   `atualizado_em`, `criado_em`) values (ver�nica, pinto, [email protected]  
  t, $2y$10$24vkB6XXGBsEQgsVQo8MOuw9PGHlu1lEMGoan8PP/8imeW80hWmMW, 1, , 1, 20  
  17-07-21 01:40:49, 2017-07-21 01:40:49))                                     
                                                                                                                                                  
  [Doctrine\DBAL\Driver\PDOException]                                          
  SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xE3\xB3nica'  
   for column 'nome' at row 1                                                  
                                                                                                                                                  
  [PDOException]                                                               
  SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xE3\xB3nica'  
   for column 'nome' at row 1

And this is the factory for the model:

$factory->define(Utilizador::class, function (Faker\Generator $faker) {
    $faker = Faker\Factory::create('pt_PT');
    static $password;

    $unidades = Unidade::all()->pluck('id')->toArray();

    return [
        'nome' => $faker->firstName,
        'apelido' => $faker->lastName,
        'email' => $faker->unique()->safeEmail,
        'password' => $password ?: $password = bcrypt('123456'),
        'verificado' => $verificado = $faker->randomElement([Utilizador::UTILIZADOR_VERIFICADO, Utilizador::UTILIZADOR_NAO_VERIFICADO]),
        'token_verificacao' => $verificado == Utilizador::UTILIZADOR_VERIFICADO ? null : Utilizador::generateVerificationCode(),
        'id_unidade' => $faker->randomElement($unidades),
    ];
});

As you can see, it looks like it is related to encoding. But I've set my charset to utf8mb4 and my collation to utf8mb4_unicode_ci.

Back in Laravel 5.3 I didn't face this kind of problem while using utf8 and utf8_unicode_ci.

0 likes
6 replies
jlrdw's avatar

Make sure the mysql version you are using supports utf8mb4.

kriolo_developer's avatar

I am using MySQL version 5.7.14. I've made sure the database charset is set to utfomb4 and collation to utf8mb4_unicode_ci. I've also made sure the table's have those charset and collation as well.

kriolo_developer's avatar

This MUST be a problem with Laravel and the way it's inserting the data into the database with db:seed, because I can add utf8mb4 data normally with sql queries directly usind a MySQL Console.

kriolo_developer's avatar

Just tried inserting the data directly using the DB Facace, and it works.

DB::insert("INSERT INTO utilizadores(nome, apelido, email, password, token_verificacao, id_unidade, criado_em, atualizado_em) VALUES (?, ?, ?, ?, ?, ?, ?, ?)", [
            'josé',
            'amândio',
            '[email protected]',
            '$2y$10QhVfdMiZKjWl4dImkrMJvOC.uVbKmeIb4lh4o3NyQ6KAbnfWRfMke',
            1,
            6,
            '2017-07-21 15:22:53',
            '2017-07-21 15:22:53'
        ]);

Then why doesn't it work when using the create method on the model or when using the save method? This is driving me insane. It's this type of bug that makes you want to shoot yourself.

kriolo_developer's avatar
kriolo_developer
OP
8 years ago
Best Answer
Level 1

Hey guys, I finally found out why my code was behaving like that. The thing is, I was using strtolower in a part of my code when that specific model was being updated:

public function setNomeAttribute($nome)
    {
        $this->attributes['nome'] = strtolower($nome);
    }

I got it working after changing it to mb_strtolower, that is the one that should be used when working with international strings.

public function setNomeAttribute($nome)
    {
        $this->attributes['nome'] = mb_strtolower($nome);
    }

This was the github answer that helped me:

visit https://stackoverflow.com/questions/26343876/laravel-utf-8-to-database

I hope it helps others that may be facing the same problem.

5 likes

Please or to participate in this conversation.