When writing a migration, people might forget to set the correct collation for newly created tables/columns.
It would be good if there is a way to set the default collection for this because I'm certain this is going to come back to haunt us at some point.
Observed here: #26923 (comment) (db_structure vs doctrine migration approach).
From what I see we want utf8_bin and not utf8_unicode_ci
@DeepDiver1975 @butonic what do you think ?
Or will we even change the collation for emojis ?
I found this article https://florian.ec/articles/mysql-doctrine-utf8/ that says that Doctrine doesn't allow setting a global collation and expects it to be passed for every call.
I couldn't find a quick solution. Maybe we need to somehow mangle the SchemaConfig before running the migrations but I can't seem to find a way to access it from our MigrationService.
Or maybe as the FAQ says we should properly use SET NAMES, but I thought we had this in our connection already. Maybe Doctrine is using a different connection class, not the one containing our overrides ?
Looks like we've been setting it explicitly within the helpers so far:
lib/private/DB/MDB2SchemaReader.php|122| $table->addOption('collate', 'utf8_bin');
lib/private/Repair/Collation.php|66| $query = $this->connection->prepare('ALTER TABLE `' . $table . '` CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;');
lib/private/Repair/Collation.php|81| " AND (COLLATION_NAME <> 'utf8_bin' OR CHARACTER_SET_NAME <> 'utf8')" .
lib/private/Setup/MySQL.php|61| $query = "CREATE DATABASE IF NOT EXISTS `$name` CHARACTER SET utf8 COLLATE utf8_bin;";