`AbstractSchemaManager#_getPortableTableColumnList()` cannot process functional indexes defined in MySQL

Ocramius commented 2 years ago

Bug Report

Q	A
Version	3.3.2

Summary

When using functional MySQL indexes, the AbstractSchemaManager crashes when instantiating a new Index for which the columns could not be identified.

How to reproduce

Given following DDL:

CREATE TABLE product (
  id BINARY(16) NOT NULL,
  -- ...
  custom_fields JSON NOT NULL,
  PRIMARY KEY(id)
);

CREATE INDEX IDX_SOME_SUBFIELD
    ON `product`
    (
        (JSON_VALUE(custom_fields, '$.mySubfield' RETURNING CHAR(255)))
    );

Running schema introspection tools lea

$ bin/console doctrine:schema:update --dump-sql  -vvv
In Index.php line 83:

  [TypeError]                                                                                                                                  
  Doctrine\DBAL\Schema\Index::_addColumn(): Argument #1 ($column) must be of type string, null given, called in /srv/share/vendor/doctrine/db  
  al/src/Schema/Index.php on line 72                                                                                                           

Exception trace:
  at /srv/share/vendor/doctrine/dbal/src/Schema/Index.php:83
 Doctrine\DBAL\Schema\Index->_addColumn() at /srv/share/vendor/doctrine/dbal/src/Schema/Index.php:72
 Doctrine\DBAL\Schema\Index->__construct() at /srv/share/vendor/doctrine/dbal/src/Schema/AbstractSchemaManager.php:1072
 Doctrine\DBAL\Schema\AbstractSchemaManager->_getPortableTableIndexesList() at /srv/share/vendor/doctrine/dbal/src/Schema/MySQLSchemaManager.php:104
 Doctrine\DBAL\Schema\MySQLSchemaManager->_getPortableTableIndexesList() at /srv/share/vendor/doctrine/dbal/src/Schema/AbstractSchemaManager.php:228
 Doctrine\DBAL\Schema\AbstractSchemaManager->listTableIndexes() at /srv/share/vendor/doctrine/dbal/src/Schema/AbstractSchemaManager.php:328
 Doctrine\DBAL\Schema\AbstractSchemaManager->listTableDetails() at /srv/share/vendor/doctrine/dbal/src/Schema/MySQLSchemaManager.php:336
 Doctrine\DBAL\Schema\MySQLSchemaManager->listTableDetails() at /srv/share/vendor/doctrine/dbal/src/Schema/AbstractSchemaManager.php:306
 Doctrine\DBAL\Schema\AbstractSchemaManager->listTables() at /srv/share/vendor/doctrine/dbal/src/Schema/AbstractSchemaManager.php:1229
 Doctrine\DBAL\Schema\AbstractSchemaManager->createSchema() at /srv/share/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/SchemaTool.php:966
 Doctrine\ORM\Tools\SchemaTool->createSchemaForComparison() at /srv/share/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/SchemaTool.php:937
 Doctrine\ORM\Tools\SchemaTool->getUpdateSchemaSql() at /srv/share/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/Console/Command/SchemaTool/UpdateCommand.php:79
 Doctrine\ORM\Tools\Console\Command\SchemaTool\UpdateCommand->executeSchemaCommand() at /srv/share/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/Console/Command/SchemaTool/AbstractCommand.php:46
 Doctrine\ORM\Tools\Console\Command\SchemaTool\AbstractCommand->execute() at /srv/share/vendor/doctrine/doctrine-bundle/Command/Proxy/UpdateSchemaDoctrineCommand.php:40
 Doctrine\Bundle\DoctrineBundle\Command\Proxy\UpdateSchemaDoctrineCommand->execute() at /srv/share/vendor/symfony/console/Command/Command.php:291
 Symfony\Component\Console\Command\Command->run() at /srv/share/vendor/symfony/console/Application.php:1007
 Symfony\Component\Console\Application->doRunCommand() at /srv/share/vendor/symfony/framework-bundle/Console/Application.php:96
 Symfony\Bundle\FrameworkBundle\Console\Application->doRunCommand() at /srv/share/vendor/symfony/console/Application.php:299
 Symfony\Component\Console\Application->doRun() at /srv/share/vendor/symfony/framework-bundle/Console/Application.php:82
 Symfony\Bundle\FrameworkBundle\Console\Application->doRun() at /srv/share/vendor/symfony/console/Application.php:171
 Symfony\Component\Console\Application->run() at /srv/share/bin/console:29

The trace is a bit noisy because of this happening in a symfony application, but the last frames are clear.

This happens here:

https://github.com/doctrine/dbal/blob/35eae239ef515d55ebb24e9d4715cad09a4f58ed/src/Schema/AbstractSchemaManager.php#L1066-L1073

At this location, $data['columns'][0] will be null for the index defined above

Current behaviour

See above crash - a hard crash is probably to be avoided.

Expected behaviour

Perhaps letting it silently ignore the index? :thinking:

In my application, I will likely come up with a workaround that skips this specific index through an event subscriber attached to \Doctrine\DBAL\Events::onSchemaIndexDefinition, but it is only a local workaround.

morozov commented 2 years ago

At a high level, this is similar to https://github.com/doctrine/dbal/issues/4470: the DBAL crashes upon encountering a database object that it doesn't understand, while it has a quite limited understanding of the supported platforms.

From the design standpoint, in both of these cases, the DBAL might fall back to using some generic types for such objects (i.e. columns and indices). It should operate lower-level raw definitions that won't likely allow comparison but will retain the original properties of the objects.

YosraHamza commented 1 year ago

Any updates here? and when it is expected to be fixed? I'm using a Laravel admin panel (Backpack v5) and they are using the package in the core operations

Ocramius commented 1 year ago

@YosraHamza this is open source: it is fixed when YOU fix it :P

YosraHamza commented 1 year ago

@Ocramius I agree with you. I saw the open discussion and related issues so I thought it might be resolved soon. If not, I will give it a try for sure.

lukasm91 commented 1 year ago

Really not saying this is the proper solution (open to suggestion, but I am looking at this project the first time, so it is likely just some workaround, I am neither a PHP expert nor into databases).

https://github.com/lukasm91/dbal/tree/fix_invalid_columns

This is just skipping indices that have an index with unknown columns. I ran into this with a typo3 application with a sqlite backend.

ES-Six commented 1 year ago

Just encountered this exact same issue.

Here is how to reproduce :

Applyed the following migration by running php bin/console doctrine:migrations:migrate :

<?php

declare(strict_types=1);

namespace DoctrineMigrations;

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

/**
 * Auto-generated Migration: Please modify to your needs!
 */
final class Version20230120105840 extends AbstractMigration
{
    public function getDescription(): string
    {
        return '';
    }

    public function up(Schema $schema): void
    {
        // this up() migration is auto-generated, please modify it to your needs
        $this->addSql("ALTER TABLE company ADD INDEX idx_siret ((  cast(regexp_replace(cast(`corporate_identifier` as char charset utf8mb4),_utf8mb4'[^0-9]+',_utf8mb4'') as unsigned)   ))");
    }

    public function down(Schema $schema): void
    {
        // this down() migration is auto-generated, please modify it to your needs
        $this->addSql('DROP INDEX idx_siret ON company');
    }
}

Then I ran php bin/console doctrine:migrations:diff.

And this error happened : Doctrine\DBAL\Schema\Index::_addColumn(): Argument #1 ($column) must be of type string, null given, called in /var/ www/vendor/doctrine/dbal/src/Schema/Index.php on line 72

This error occured because the _addColumn function take a string parameter but mysql functionnal indexes has no column at all, so null is passed to this function and dbal crash.

doctrine / dbal