Support of Multi-Valued index from MySQL 8

[anaelChardan]

Q	A
Version	2.10.1

Support Question

Do you know if you support the use of multi-valued indexes from MySQL 8, maybe you do and I don't create them the right way ;)

$this->addSql('CREATE INDEX IDX_board_customers ON TBL_Board ((CAST(customers as CHAR(36) ARRAY)));');

But when the Schema is introspected it throws an exception due to column name which is null.

By example:

php bin/console d:s:u --dump-sql

In Index.php line 83:

  Expecting a string as Index Column

Thanks! ;)

[morozov]

Most likely, DBAL doesn't support this.

[stefan-messagebird]

Any idea if this might be supported at some point, or what would be the workaround to use this MySQL 8 feature. For me it's also failing at just reading existing tables not created with the migration:

In Index.php line 91:
                                      
  Expecting a string as Index Column  
                                      
 Doctrine\DBAL\Schema\Index->_addColumn() at /var/www/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/Index.php:73
 Doctrine\DBAL\Schema\Index->__construct() at /var/www/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:907
 Doctrine\DBAL\Schema\AbstractSchemaManager->_getPortableTableIndexesList() at /var/www/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/MySqlSchemaManager.php:102
 Doctrine\DBAL\Schema\MySqlSchemaManager->_getPortableTableIndexesList() at /var/www/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:188
 ... 
  ProxyManagerGeneratedProxy\__PM__\Doctrine\DBAL\Schema\Schema\Generated2956515c6fa4907395c219a41aa0ac4f->createTable() at /var/www/src/Infrastructure/Db/Migrations/Version20200210121248.php:20
 App\Infrastructure\Db\Migrations\Version20200210121248->up() at /var/www/vendor/doctrine/migrations/lib/Doctrine/Migrations/Version/DbalExecutor.php:167

Existing table that is probably breaking it:

CREATE TABLE iBreakStuff
(
    id          INT AUTO_INCREMENT NOT NULL,
    json_array  JSON     DEFAULT NULL,
    created_at  DATETIME DEFAULT NOT NULL,
    updated_at  DATETIME DEFAULT NOT NULL,
    PRIMARY KEY (id),
    INDEX json_array_ids( (CAST(json_array->>'$[*].id' AS CHAR(36) ARRAY)) )
) DEFAULT CHARACTER SET utf8mb4
  COLLATE `utf8mb4_unicode_ci`
  ENGINE = InnoDB
;

[morozov]

Any idea if this might be supported at some point

There's currently no plan for that.

what would be the workaround to use this MySQL 8 feature.

It fails because currently the "columns" property of an index is not nullable but the above index has NULL as columns. This needs to be fixed first.

The other thing is that the "expression" property of the index above is introspected as:

cast(json_unquote(json_extract(`json_array`,_utf8mb4'$[*].id')) as char(36) array)

A proper comparison of the original and introspected versions of the expression may be challenging.

[stefan-messagebird]

Thanks for the fast reply. I've managed for now to go around it by avoiding to use the $schema in migrations altogether, and just use:

$this->addSql('CREATE ... ');

[colinmollenhour]

I wouldn't be in a huge rush to use this MySQL feature, it causes segfaults. Reported and verified in MySQL bug #108663 but after 10 months there is still no word on when it will be fixed..