Allow %s_bin in SQL field definitions

[ghost]

Comment by @fritzmg
January 4th, 2018, 09:29 GMT

Btw. why do all the alias fields use a hardcoded utf8mb4_bin collation now anyway? Even if you set

doctrine:
    dbal:
        connections:
            default:
                default_table_options:
                    charset: utf8
                    collate: utf8_unicode_ci

the collation of these fields would still be utf8mb4_bin.

[leofeyer]

Because those fields are not case-insensitive. But I agree that we should allow %s_bin and replace %s with the configured charset.

[aschempp]

Please use the SQL definition as array, that should probably automatically fix it ;-)

[leofeyer]

Really? How is this supposed to work?

[fritzmg]

Please use the SQL definition as array, that should probably automatically fix it ;-)

Even if you use the array, you'd still have to hard code the collation, wouldn't you?

@leofeyer what about using

varchar(128) BINARY NOT NULL default ''

instead of

varchar(128) COLLATE utf8mb4_bin NOT NULL default ''

[aschempp]

Really? How is this supposed to work?

http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/schema-representation.html#column

'sql' => ['type' => 'binary_string', 'length' => 16, 'fixed' => true]

[leofeyer]

Nope, the schema update tries to generate a varbinary() field then instead of a varchar() binary field.

[leofeyer]

@fritzmg's suggestion varchar(128) BINARY works, however the schema tool does not explicitly handle this case it seems.

[aschempp]

Is there a difference between the two? According to Stackoverflow I would say we actually do want varbinary?

[leofeyer]

No, according to exactly the post you have linked to, we do not want varbinary. We want varchar(255) binary, which

causes the binary collation for the column character set to be used

[leofeyer]

Why you should not map varchar() binary to varbinary(): https://bugs.mysql.com/bug.php?id=16668

[leofeyer]

Since we cannot make Doctrine handle the BINARY flag, there are two possible solutions:

1. Continue to use COLLATE utf8mb4_bin, which is potentially incompatible if someone changes the DB charset. (They would have to adjust the DCA files, too.)

2. Allow to use the BINARY flag in DCA files and handle it in the DcaSchemaProvider. This works fine for DCA files but not for ORM entities. (So it would be a new known limitation.)

@contao/developers What is your favorite solution?

[aschempp]

But we are not storing a binary string, we are storing binary data, are we not? According to that link:

VARCHAR BINARY
When BINARY values are stored, they are right-padded with the pad value to the specified length.

vs.

VARBINARY
There is no padding on insert, and no bytes are stripped on select. All bytes are significant in comparisons.

VARBINARY sounds more reasonable for me?

[leofeyer]

We are storing a binary string (e.g. tl_page.alias). We are also storing binary data (e.g. tl_content.singleSRC) but that has nothing to do with this issue.

[leofeyer]

See #1300.