JSON in longtext in mariabd returns "Could not convert database value to Doctrine type json"

user_6614155 0
Viewed 2950

Symfony 5, I write my User entity code accordingly with:
https://symfony.com/doc/current/security.html#denying-access-roles-and-other-authorization

and I use mariadb

public function getRoles(): array
    {
        $roles[] = $this->roles;
        // guarantee every user at least has ROLE_USER
        $roles[] = 'ROLE_USER';

        return array_unique($roles);
    }

and I added manually an user with role ROLE_ADMIN.

I don't understand why it returns the error:

Could not convert database value "ROLE_ADMIN" to Doctrine Type json

symfony mariadb doctrine php
2 Answers

You get that error because you are setting the value to something that's an invalid JSON. If you are going to modify your DB directly, you should put a valid value for a JSON type. In this case it would be something like:

["ROLE_ADMIN"]

instead of:

ROLE_ADMIN

Additionally, you picked the incorrect column type. While JSON is an alias for LONGTEXT on MariaDb, on JSON typed columns a constraint is added. As explained in the docs you linked:

In order to ensure that a a valid json document is inserted, the JSON_VALID function can be used as a CHECK constraint. This constraint is automatically included for types using the JSON alias from MariaDB 10.4.3.

This would have alerted you that the data you had entered manually was not a valid JSON document.

There is absolutely no need of changing the default getRoles() method you show. That one would work perfectly fine, if you are using a JSON type for the property as the error shows.

user_14265390

As I wrote, I use MariaDB. On MariaDB help, we can read : look https://mariadb.com/kb/en/json-data-type/

So I try to replace in my entity (src/Entity/User.php)

public function getRoles(): array
    {
        $roles[] = json_encode($this->roles);
        …

but it is a bad way as @yivi wrote, the first error is at value in db : be careful also if you define manually, like me, an user in database, that you enter for example ["ROLE_ADMIN"] an not ROLE_ADMIN which could returns an other error Notice: Array to string conversion because I have a second error :

$roles[] = $this->roles;
//instead of
$roles = $this->roles;

Stupid typing error

user_66141550
Related