SQLSTATE[HY000]: General error: 1270 Illegal mix of collations (utf8_general_ci,COERCIBLE), (utf8_unicode_ci,COERCIBLE), (utf8_unicode_ci,COERCIBLE) for operation 'replace'

[internethering]

I got the same error like issue #222. If I want to create a directory or want to synchronise the files, I get:

An exception occurred while executing 'SELECT UNHEX(REPLACE(UUID(), '-', '')) AS uuid UNION ALL SELECT UNHEX(REPLACE(UUID(), '-', '')) AS uuid UNION ALL SELECT UNHEX(REPLACE(UUID(), '-', '')) AS uuid UNION ALL SELECT UNHEX(REPLACE(UUID(), '-', '')) AS uuid UNION ALL SELECT UNHEX(REPLACE(UUID(), '-', '')) AS uuid UNION ALL SELECT UNHEX(REPLACE(UUID(), '-', '')) AS uuid UNION ALL SELECT UNHEX(REPLACE(UUID(), '-', '')) AS uuid UNION ALL SELECT UNHEX(REPLACE(UUID(), '-', '')) AS uuid UNION ALL SELECT UNHEX(REPLACE(UUID(), '-', '')) AS uuid UNION ALL SELECT UNHEX(REPLACE(UUID(), '-', '')) AS uuid':

SQLSTATE[HY000]: General error: 1270 Illegal mix of collations (utf8_general_ci,COERCIBLE), (utf8_unicode_ci,COERCIBLE), (utf8_unicode_ci,COERCIBLE) for operation 'replace'

Same error at three different installations. So I tried a brand new 4.8.4 installation by contao-manager.phar.php – same error:

I could solve the problem for me by editing vendor/contao/core-bundle/src/Resources/contao/library/Contao/Database.php:

--- vendor/contao/core-bundle/src/Resources/contao/library/Contao/Database.php.orig     2019-10-23 02:23:00.397801108 +0200
+++ vendor/contao/core-bundle/src/Resources/contao/library/Contao/Database.php  2019-10-23 02:21:28.848200099 +0200
@@ -677,6 +677,7 @@

                if (empty($ids))
                {
+            $this->resConnection->exec('SET collation_connection = ' . Config::get('dbCollation'). ';');
                        $statement = $this->resConnection->executeQuery(implode(' UNION ALL ', array_fill(0, 10, "SELECT UNHEX(REPLACE(UUID(), '-', '')) AS uuid")));
                        $ids = $statement->fetchAll(\PDO::FETCH_COLUMN);
                }

some informations about my system:

php 7.3 dev-db/mariadb 10.4.8

[Toflar]

Does "brand new" mean you installed it from scratch also with an empty database? Or did you try to import an existing database?

[leofeyer]

@internethering /ping