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

Question

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

Answer 1

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.