Update to DBAL 4.x.x

[mrbinr]

Symfony FOSUserBundle versions: 4.0.0

Description of the problem including expected versus actual behavior:

I have update my project which use Symfony 6.4 with composer update and DBAL has been updated

...
Upgrading doctrine/dbal (3.9.1 => 4.2.1)
...

Provide logs (if relevant):

Doctrine\DBAL\Types\Exception\UnknownColumnType: Unknown column type "array" requested.
Any Doctrine type that you  use has to be registered with \Doctrine\DBAL\Types\Type::addType(). 
You can get a list of all the known types with \Doctrine\DBAL\Types\Type::getTypesMap(). 
If this error occurs during database introspection then you might have forgotten to  register 
all database types for a Doctrine Type.
Use AbstractPlatform#registerDoctrineTypeMapping() or have your custom types implement 
Type#getMappedDatabaseTypes(). If the type name is empty you might have a problem with the cache 
or forgot some mapping information.

After investigation the problem seems to be linked to roles property and it cannot being overridden with AttributeOverride.

[iamBunny]

While a change to support this might be coming, i personally doubt it will be changed

For now, your options are:

• Using a custom field type for Array that you can specify in Doctrine config, which i do not recommend
• Migrating from using FosUserBundle (recommended, even suggested by FOS in readme of this bundle) and using Symfony’s native User support that comes with Symfony Security. Migrating will save you headaches in the future.

[TheDoctor0]

If someone wants to still proceed with adding a custom field type:

1. Create SerializedArrayType.php

<?php

namespace App\Doctrine\Type;

use Doctrine\DBAL\Platforms\AbstractPlatform;
use Doctrine\DBAL\Types\Type;

final class SerializedArrayType extends Type
{
    public const NAME = 'serialized_array';

    public function getSQLDeclaration(array $column, AbstractPlatform $platform): string
    {
        return $platform->getClobTypeDeclarationSQL($column);
    }

    public function convertToPHPValue($value, AbstractPlatform $platform): mixed
    {
        if ($value === null || $value === '') {
            return [];
        }

        $unserialized = @unserialize($value);
        if ($unserialized === false && $value !== 'b:0;') {
            throw new \UnexpectedValueException("Could not unserialize value: " . var_export($value, true));
        }

        return $unserialized;
    }

    public function convertToDatabaseValue($value, AbstractPlatform $platform): ?string
    {
        if ($value === null) {
            return null;
        }

        return serialize($value);
    }

    public function getName(): string
    {
        return self::NAME;
    }

    public function requiresSQLCommentHint(AbstractPlatform $platform): bool
    {
        return true;
    }
}

2. In your config/packages/doctrine.yaml add:

doctrine:
    dbal:
        types:
            serialized_array: App\Doctrine\Type\SerializedArrayType

3. Use like this:

use Doctrine\DBAL\Types\Types;
use Doctrine\ORM\Mapping as ORM;
use App\Doctrine\Type\SerializedArrayType;

#[ORM\Column(type: SerializedArrayType::NAME)]
private $roles = [];

[veewee]

A workaround is to alias array to json and transform the data to JSON instead:

doctrine:
    dbal:
        types:
            array: Doctrine\DBAL\Types\JsonType

<?php

declare(strict_types=1);

namespace DoctrineMigrations;

use Doctrine\DBAL\Schema\Schema;
use Doctrine\Migrations\AbstractMigration;

final class Version20260106091828 extends AbstractMigration
{
    public function getDescription(): string
    {
        return 'Update array types to use JSON';
    }

    public function up(Schema $schema): void
    {
        $this->write('Starting migration of array columns to JSON...');

        $this->migrateArrayColumn('users', 'roles', 'id');
        // You can add extra table / columns here.

        $this->write('Migration complete!');
    }

    public function down(Schema $schema): void
    {
        $this->skipIf(true, 'Not implemented.');
    }

    private function migrateArrayColumn(string $table, string $column, string $idColumn): void
    {
        $this->write("Migrating {$table}.{$column}...");

        // Fetch all rows with data in this column
        $rows = $this->connection->fetchAllAssociative(
            "SELECT {$idColumn}, {$column} FROM {$table} WHERE {$column} IS NOT NULL"
        );

        if (empty($rows)) {
            $this->write("  No data to migrate in {$table}.{$column}");
            return;
        }

        $migrated = 0;
        $alreadyJson = 0;
        $errors = 0;

        foreach ($rows as $row) {
            $id = $row[$idColumn];
            $data = $row[$column];

            // Check if it's already JSON (in case migration is run multiple times)
            if ($this->isJson($data)) {
                $alreadyJson++;
                continue;
            }

            // Try to unserialize the PHP serialized data
            $unserialized = @unserialize($data);

            // Check if unserialization failed (and it's not the serialized 'false' value)
            if ($unserialized === false && $data !== serialize(false)) {
                $this->write("  Warning: Could not unserialize row {$id}, skipping");
                $errors++;
                continue;
            }

            // Convert to JSON
            $jsonData = json_encode([...$unserialized]);

            if ($jsonData === false) {
                $this->write("  Warning: Could not JSON encode row {$id}, skipping");
                $errors++;
                continue;
            }

            // Update the database
            $this->connection->update(
                $table,
                [$column => $jsonData],
                [$idColumn => $id]
            );

            $migrated++;
        }

        $this->write("  {$table}.{$column}: {$migrated} migrated, {$alreadyJson} already JSON, {$errors} errors");
    }

    private function isJson(string $string): bool
    {
        if (empty($string)) {
            return false;
        }

        json_decode($string);
        return json_last_error() === JSON_ERROR_NONE;
    }
}