SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xB2{_#h<...' for column `my_database`.`user`.`password` at row 1

[Clem-D]

Symfony FOSUserBundle versions:
$ php5.6 /home/cdelestre/Downloads/composer.phar info | grep user friendsofsymfony/user-bundle dev-master 315f837 Symfony FOSUserBundle

Description of the problem including expected versus actual behavior:
Basically this is the error I get :

SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xB2{_#h<...' for column my_database.user.password at row 1

It seems to be only happened for "user.password" columns, for example firstname can contain accent and everything's fine. Plus I can do this for example : setPlainPassword('FooB@r!!!€');
and it works as well.

Of course I googled this error and it appear I should change "utf8" to "utf8mb4" into my database (I'm using mariaDB : Server version: 10.3.15-MariaDB-1:10.3.15+maria~stretch mariadb.org binary distribution )
So I did by changing /etc/mysql/my.cnf file which looks like that :

!includedir /etc/mysql/conf.d/

[client]
default-character-set = utf8mb4

[mysqld]
init_connect = 'SET character_set_system = utf8mb4'
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[mysql]
default-character-set = utf8mb4
socket=/run/mysqld/mysqld.sock

So the SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%'; command gives me this output :

+--------------------------+--------------------+
| Variable_name | Value |
+--------------------------+--------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| collation_connection | utf8mb4_unicode_ci |
| collation_database | utf8mb4_unicode_ci |
| collation_server | utf8mb4_unicode_ci |
+--------------------------+--------------------+

I even changed doctrine.dbal.charset: UTF8 to

doctrine:
dbal:
charset: utf8mb4
default_table_options:
charset: utf8mb4
collate: utf8mb4_unicode_ci

into app/config/config.yml file but it doesn't work neither...

Is there a way to bitepass the "salt" step ?
Why it's not working at it should be..?
It seems I've tried everything I can and I'm becoming crazy, any help would be appreciate :)

Steps to reproduce:

1. Just create a new user with a form, without providing any password. And persist them then flush with entity manager.

[Clem-D]

Wow OK I've found a kind of "quick fix" by changing a method in my User entity.

I've used to use a method called "setPassword" in the constructor :

/**
     * Constructor
     */
    public function __construct()
    {
      parent::__construct();
      $this->sites = new ArrayCollection();
      $this->wps = new ArrayCollection();
      $this->requests = new ArrayCollection();
      $this->creation_date = new \DateTime('now');
      $this->demands = new ArrayCollection();
      $this->files = new ArrayCollection();
      $this->entities = new ArrayCollection();
      $this->password = $this->random_str(32);
    }


    /**
     * Generate a random string, using a cryptographically secure
     * pseudorandom number generator (random_int)
     *
     * For PHP 7, random_int is a PHP core function
     * For PHP 5.x, it's rand function !
     *
     * @param int $length      How many characters do we want?
     * @param string $keyspace A string of all possible characters
     *                         to select from
     * @return string
     */
    function random_str($length, $keyspace = ' 0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ_-^@+*/#&)}({!/:;?,€%$µ§£<>²')
    {
        $str = '';
        $max = mb_strlen($keyspace, '8bit') - 1;
        for ($i = 0; $i < $length; ++$i) {
            $str .= $keyspace[rand(0, $max)];
        }
        return $str;
    }

I updated setPassword by changing $keyspace = ' 0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ_-^@+*/#&)}({!/:;?,€%$µ§£<>²' to
$keyspace = ' 0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'
I guess it's less secure but at least it works so far...

So maybe I should keep it like this and going back to "old" (or "regular") symfony and mariaDB config what do you think guys ?

EDIT :
When I write I've used to it's because my code works like a charm on 2 centOS server (dev and prod) which use the following mariaDB version :

prod : Server version: 10.2.22-MariaDB-log Source distribution
dev : Server version: 10.1.31-MariaDB MariaDB Server

[stof]

the password field contains the hashed password, for which there is no reason to have accents. I guess that what you wanted to do was to generate a random plain password instead.

[Clem-D]

Since 2019 I don't exactly remember the problem but I guess I needed to generate a random password without actually knowing the password myself. Anyway I used it for a good reason ;)

There were no accents at all in the previous pattern.