Only put collation on join columns, never charset
#9652
Conversation
The fix introduced in doctrine#9636 now breaks when working with integer relations. Putting `charset` and `collation` on a text join column works fine, but when the column is an integer, MySQL won't accept it: ```sql CREATE TABLE table_name ( // .. user_id INT CHARACTER SET utf8 NOT NULL COLLATE `utf8_unicode_ci` // .. ) ``` produces this error: ``` Query 1 ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHARACTER SET utf8 NOT NULL COLLATE `utf8_unicode_ci`, type VARCHAR(255) NOT NUL' at line 1 ``` Luckily the solution is simple. The charset is not needed as MySQL automatically derives that from the `collation` when specified. It's also clever enough to ignore `collation` when the type is an integer. Therefore the following works fine: ```sql CREATE TABLE table_name ( // .. user_id INT NOT NULL COLLATE `utf8_unicode_ci`, string_user_id VARCHAR(255) NOT NULL COLLATE `utf8_unicode_ci`, // .. ) ```
|
Unfortunately, this still breaks when working with contract_id BINARY(16) NOT NULL COLLATE `utf8_unicode_ci`
# Query 1 ERROR: COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'binary' |
|
Is there a way to know which column types do support collation? Maybe we can only set the collation when the type is |
|
@ruudk maybe we should revert this instead? It's already bad that the ORM has to know about charsets and collations, IMO it would be worse if it has to know about MySQL types. Maybe this should be somehow fixed in DBAL instead? |
|
We can do that. Maybe I can create a PR that adds support for |
|
PR for revert: #9654 |
That might make sense since it already exists on |
|
Your future contribution might also fix #4286 |
|
Closing in favour of #9655 |
The fix introduced in #9636 now breaks when working with integer relations.
Putting
charsetandcollationon a text join column works fine, but when thecolumn is an integer, MySQL won't accept it:
produces this error:
Luckily the solution is simple. The charset is not needed as MySQL automatically derives that from
the
collationwhen specified. It's also clever enough to ignorecollationwhen the type is an integer.Therefore the following works fine:
/cc @greg0ire