orm: Schema Update not working from json_array to json with DBAL 3

Bug Report

Q	A
BC Break	yes
Version	2.10.0

Summary

The deprecated json_array type is not longer supported but its actually not possible to upgrade in 2.10.0 the database from json_array to json.

Current behavior

After upgrade to doctrine/orm I did replace all json_array with json but when now running:

bin/console doctrine:schema:update --dump-sql

The the FieldRegistry fails as the database still has the comment (DC2Type:json_array) in it and is so mapped to the json_array type.

How to reproduce

Create a Entity with json_array update doctrine/orm to 2.10.0 change type from json_array to json and run:

bin/console doctrine:schema:update --dump-sql

In Exception.php line 125:

  [Doctrine\DBAL\Exception]
  Unknown column type "json_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 \Doct
  rine\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 infor
  mation.


Exception trace:
  at /Users/alexanderschranz/Documents/Sulu/sulu-develop.localhost/vendor/sulu/sulu/vendor/doctrine/dbal/src/Exception.php:125
 Doctrine\DBAL\Exception::unknownColumnType() at /Users/alexanderschranz/Documents/Sulu/sulu-develop.localhost/vendor/sulu/sulu/vendor/doctrine/dbal/src/Types/TypeRegistry.php:37
 Doctrine\DBAL\Types\TypeRegistry->get() at /Users/alexanderschranz/Documents/Sulu/sulu-develop.localhost/vendor/sulu/sulu/vendor/doctrine/dbal/src/Types/Type.php:143
 Doctrine\DBAL\Types\Type::getType() at /Users/alexanderschranz/Documents/Sulu/sulu-develop.localhost/vendor/sulu/sulu/vendor/doctrine/dbal/src/Schema/MySQLSchemaManager.php:228
 Doctrine\DBAL\Schema\MySQLSchemaManager->_getPortableTableColumnDefinition() at /Users/alexanderschranz/Documents/Sulu/sulu-develop.localhost/vendor/sulu/sulu/vendor/doctrine/dbal/src/Schema/AbstractSchemaManager.php:884
 Doctrine\DBAL\Schema\AbstractSchemaManager->_getPortableTableColumnList() at /Users/alexanderschranz/Documents/Sulu/sulu-develop.localhost/vendor/sulu/sulu/vendor/doctrine/dbal/src/Schema/AbstractSchemaManager.php:195
 Doctrine\DBAL\Schema\AbstractSchemaManager->listTableColumns() at /Users/alexanderschranz/Documents/Sulu/sulu-develop.localhost/vendor/sulu/sulu/vendor/doctrine/dbal/src/Schema/AbstractSchemaManager.php:308
 Doctrine\DBAL\Schema\AbstractSchemaManager->listTableDetails() at /Users/alexanderschranz/Documents/Sulu/sulu-develop.localhost/vendor/sulu/sulu/vendor/doctrine/dbal/src/Schema/MySQLSchemaManager.php:334
 Doctrine\DBAL\Schema\MySQLSchemaManager->listTableDetails() at /Users/alexanderschranz/Documents/Sulu/sulu-develop.localhost/vendor/sulu/sulu/vendor/doctrine/dbal/src/Schema/AbstractSchemaManager.php:293
 Doctrine\DBAL\Schema\AbstractSchemaManager->listTables() at /Users/alexanderschranz/Documents/Sulu/sulu-develop.localhost/vendor/sulu/sulu/vendor/doctrine/dbal/src/Schema/AbstractSchemaManager.php:1132
 Doctrine\DBAL\Schema\AbstractSchemaManager->createSchema() at /Users/alexanderschranz/Documents/Sulu/sulu-develop.localhost/vendor/sulu/sulu/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/SchemaTool.php:955
 Doctrine\ORM\Tools\SchemaTool->createSchemaForComparison() at /Users/alexanderschranz/Documents/Sulu/sulu-develop.localhost/vendor/sulu/sulu/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/SchemaTool.php:931
 Doctrine\ORM\Tools\SchemaTool->getUpdateSchemaSql() at /Users/alexanderschranz/Documents/Sulu/sulu-develop.localhost/vendor/sulu/sulu/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/Console/Command/SchemaTool/UpdateCommand.php:79
 Doctrine\ORM\Tools\Console\Command\SchemaTool\UpdateCommand->executeSchemaCommand() at /Users/alexanderschranz/Documents/Sulu/sulu-develop.localhost/vendor/sulu/sulu/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/Console/Command/SchemaTool/AbstractCommand.php:47
 Doctrine\ORM\Tools\Console\Command\SchemaTool\AbstractCommand->execute() at /Users/alexanderschranz/Documents/Sulu/sulu-develop.localhost/vendor/sulu/sulu/vendor/doctrine/doctrine-bundle/Command/Proxy/UpdateSchemaDoctrineCommand.php:40
 Doctrine\Bundle\DoctrineBundle\Command\Proxy\UpdateSchemaDoctrineCommand->execute() at /Users/alexanderschranz/Documents/Sulu/sulu-develop.localhost/vendor/sulu/sulu/vendor/symfony/console/Command/Command.php:299
 Symfony\Component\Console\Command\Command->run() at /Users/alexanderschranz/Documents/Sulu/sulu-develop.localhost/vendor/sulu/sulu/vendor/symfony/console/Application.php:996
 Symfony\Component\Console\Application->doRunCommand() at /Users/alexanderschranz/Documents/Sulu/sulu-develop.localhost/vendor/sulu/sulu/vendor/symfony/framework-bundle/Console/Application.php:96
 Symfony\Bundle\FrameworkBundle\Console\Application->doRunCommand() at /Users/alexanderschranz/Documents/Sulu/sulu-develop.localhost/vendor/sulu/sulu/vendor/symfony/console/Application.php:295
 Symfony\Component\Console\Application->doRun() at /Users/alexanderschranz/Documents/Sulu/sulu-develop.localhost/vendor/sulu/sulu/vendor/symfony/framework-bundle/Console/Application.php:82
 Symfony\Bundle\FrameworkBundle\Console\Application->doRun() at /Users/alexanderschranz/Documents/Sulu/sulu-develop.localhost/vendor/sulu/sulu/vendor/symfony/console/Application.php:167
 Symfony\Component\Console\Application->run() at /Users/alexanderschranz/Documents/Sulu/sulu-develop.localhost/vendor/sulu/sulu/src/Sulu/Bundle/TestBundle/Resources/app/console:27

Expected behavior

An upgrade form json_array to json should work and the doctype comment be removed by using:

ALTER TABLE se_role_settings CHANGE value value JSON NOT NULL; -- no comment

About this issue

Original URL
State: open
Created 3 years ago
Reactions: 5
Comments: 17 (14 by maintainers)

Commits related to this issue

Add doctrine/dbal to project composer.json As discussed in https://github.com/doctrine/orm/issues/9078 when entities utilize data mappings which are provided by the dbal lib it is expected behavior t... — committed to chasen/orm by chasen 3 years ago
Add doctrine/dbal to project composer.json (#9152) As discussed in https://github.com/doctrine/orm/issues/9078 when entities utilize data mappings which are provided by the dbal lib it is expected be... — committed to doctrine/orm by chasen 3 years ago

Most upvoted comments

Let’s not collect more me-toos in here. We know about it, we know the cause, we know the workarounds. If you want to contribute to the solution, please feel free comment or open a PR.

To recap:

The breaking change was introduced with DBAL 3.0. Whenever you pull a major version upgrade of a package, you need to expect breaking changes and test thoroughly.
The ORM right now relies on DBAL for the type system. If DBAL removed a type, it’s unavailable to the ORM as well.
The ORM relies on DBAL to produce a schema diff, with the known limitation that all the types the current database uses have to be available.
ORM does not force a DBAL upgrade: DBAL 2 is still supported and probably will be for some time.

tl;dr: If you use deprecated DBAL types, don’t upgrade DBAL before you have migrated away from them.

This is a community driven library. If you have an idea how to create a smoother upgrade path, please open a PR to either DBAL or ORM and we discuss it. If you want to to express that this issue affects you, feel free to 👍🏻 the issue description.

derrabus on Oct 26, 2021

I also today reach this issue, but i fixed it using this migration:


<?php declare(strict_types=1);

namespace DoctrineMigrations;

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


final class Version20220529152449 extends AbstractMigration
{
    public function up(Schema $schema): void
    {
        $query = "
        SELECT c.column_name,
                   pgd.description,
                   c.table_schema,
                   c.table_name
            FROM pg_catalog.pg_statio_all_tables as st
            inner join pg_catalog.pg_description pgd on (pgd.objoid=st.relid)
            inner join information_schema.columns c on (pgd.objsubid=c.ordinal_position and c.table_schema=st.schemaname and c.table_name=st.relname)
            WHERE pgd.description = :desc
        ";

        $data = $this->connection
            ->executeQuery($query, ["desc" => '(DC2Type:json_array)'])
            ->fetchAllAssociative();

        foreach ($data as $column) {
            $this->addSql("
                COMMENT ON COLUMN {$column['table_schema']}.{$column['table_name']}.{$column['column_name']} IS NULL;
            ");
        }
    }

    public function down(Schema $schema): void
    {
        // no way back!
    }
}

bordeux on May 31, 2022

Can you register the json_array type manually to use the JsonType class for it? Since you only need it for the migration, registering the type manually could do the trick.

beberlei on Oct 12, 2021

That makes sense. Thanks for all of the explanation. If I get a few minutes later today, ill see if I can take a stab at updating the getting started docs to better call this out to help others avoid similar issues.

chasen on Oct 26, 2021