Configuration for collation in default table options (collate/collation)

[aprat84]

In some places in this bundle and DBAL collate is used, in some other places collation is used. doctrine/dbal#5214

I need to put both in my config:

doctrine:
    dbal:
        default_table_options:
            charset: utf8mb4
            collation: utf8mb4_0900_ai_ci
            collate: utf8mb4_0900_ai_ci

If I only use collate (it worked before updating DBAL from 3.2.1 to 3.3.1), schema comparator, which uses collation config, detects changes and generates weird migrations when executing doctrine:migrations:diff command: empty up() and a down() full of useless ALTER TABLE.

If I only use collation, it creates new tables with utf8mb4_unicode_ci collation.

I know DBAL v4 won't force any charset/collation, but still the config documentation feels wrong...
https://github.com/doctrine/DoctrineBundle/blob/2.5.x/Resources/doc/configuration.rst

[greg0ire]

After doctrine/dbal#5246 is released, this bundle should be modified to use either collation or collate depending on the version of the DBAL.

UPD: it just got released

[dmaicher]

@greg0ire @aprat84 I actually still have this issue on dbal 3.3.2

I'm using

default_table_options:
    collate: utf8_general_ci

I also tried

default_table_options:
    collation: utf8_general_ci

and

default_table_options:
    collate: utf8_general_ci
    collation: utf8_general_ci

no luck. Always get those weird down() migrations like

ALTER TABLE address CHANGE street street LONGTEXT DEFAULT NULL COLLATE `utf8_unicode_ci`, ...

Is there a workaround?

[natewiebe13]

@dmaicher by updating dbal to 3.3.2 and changing the option from collate to collation (example 2 that you tried), I no longer get the weird migrations on down. Potentially there's another issue going on as well?

Here's my current dbal config:

doctrine:
    dbal:
        url: '%env(resolve:DATABASE_URL)%'
        server_version: '8.0'
        driver: 'pdo_mysql'
        charset: utf8mb4
        default_table_options:
            charset: utf8mb4
            collation: utf8mb4_unicode_ci

[dmaicher]

Ah it actually seems I'm using a mix of collations for different tables (some have utf8_general_ci and others utf8_unicode_ci) 😕 So somewhat the down() migrations are correct. However the up() part is empty...

[floviolleau]

Hi,

We have the same issue on a project. We have a mix of utf8_general_ci and utf8mb4_general_ci.

We tried all different combinations and we still face to the issue in 3.3.2.

We rolled-back to 3.3.0 and it is working.

We spotted
https://github.com/doctrine/dbal/milestone/108?closed=1

But the fix for 3.3.2 is not working for us

Any ideas?
Thanks

[aprat84]

With 3.3.2 there are no more weird migrations!

But, be aware: ConnectionFactory will configure both connection charset (utf8mb4) and, on MySQL/MariaDB, defaultTableOptions.collate (utf8mb4_unicode_ci) if they don't exist. ConnectionFactory.php:78

With the recent changes in DBAL 3.3.2, collate takes precedence over collation, so new tables are created with collation generated by ConnectionFactory (utf8mb4_unicode_ci) instead of what I have in defaultTableOptions (utf8mb4_0900_ai_ci).

So, for me, doctrine.dbal.charset and doctrine.dbal.default_table_options.collation are both mandatory:

doctrine:
    dbal:
        charset: utf8mb4
        default_table_options:
            charset: utf8mb4
            collation: utf8mb4_0900_ai_ci
            engine: InnoDB

Maybe a quick fix would be to check for both names:

if (!isset($params['defaultTableOptions']['collate']) && !isset($params['defaultTableOptions']['collation'])) {
    $params['defaultTableOptions']['collate'] = 'utf8mb4_unicode_ci';
}

And in the future, with DBAL v4, get rid of it.

[floviolleau]

Hi @aprat84,

Thanks for your quick reply. We put both and we still have the issue

Thanks

[greg0ire]

As mentioned earlier, I believe somebody should send a PR to the DoctrineBundle. collate has many occurrences: https://github.com/doctrine/DoctrineBundle/search?q=collate , and collation has none.

Everything that has been done for collate should be done for collation, and the collate code paths should be deprecated.

The logic should be: check for both options, give precedence to collation, and then, depending on the version of DBAL, use either collate or collation here:

DoctrineBundle/ConnectionFactory.php

Line 83 in 6e578fd

$params['defaultTableOptions']['collate'] = 'utf8mb4_unicode_ci';

[oleg-andreyev]

Will report a new bug

As for me bugs is located in: \Doctrine\DBAL\Platforms\AbstractPlatform::getColumnDeclarationSQL

            $charset = ! empty($column['charset']) ?
                ' ' . $this->getColumnCharsetDeclarationSQL($column['charset']) : '';

            $collation = ! empty($column['collation']) ?
                ' ' . $this->getColumnCollationDeclarationSQL($column['collation']) : '';

when fetching column information from database this two fields are populated in my case

utf8mb4 and utf8mb4_general_ci

but when doctrine builds schema from entity

this two fields are empty.

and later when \Doctrine\DBAL\Platforms\AbstractPlatform::getColumnDeclarationSQL is called, two strings do not match,