Problems After Database Migration

[tobias]

Hi there,

I keep getting the following error when trying to rebuild.php

[2018-11-16 08:32:30] Espo.ERROR: Fault to rebuild database schema. Details: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' [] []
[2018-11-16 08:32:30] Espo.ERROR: Uncaught Exception Espo\Core\Exceptions\Error: "Error while rebuilding database. See log file for details." at D:\home\site\wwwroot\crm\application\Espo\Core\DataManager.php line 102 {"exception":"[object] (Espo\\Core\\Exceptions\\Error(code: 500): Error while rebuilding database. See log file for details. at D:\\home\\site\\wwwroot\\crm\\application\\Espo\\Core\\DataManager.php:102)"} []

App wise, I'm getting an error 500 and the log reads:

[2018-11-16 08:36:35] Espo.ERROR: Uncaught Exception PDOException: "SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='" at D:\home\site\wwwroot\crm\application\Espo\ORM\DB\Mapper.php line 73 {"exception":"[object] (PDOException(code: HY000): SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' at D:\\home\\site\\wwwroot\\crm\\application\\Espo\\ORM\\DB\\Mapper.php:73)"} []

I've checked the charset and collation and it fully matches the settings with the old database. Hence, I expect this to be a collation issue with one (or more) specific columns.

Any idea, how to find out what column may cause this issue?

---

EDIT:

I've checked the collations of all columns for both utf8_unicode_ci and utf8_general_ci:

SELECT table_schema, table_name, count(column_name), character_set_name, collation_name
FROM information_schema.columns
WHERE collation_name = 'utf8_unicode_ci'
AND table_schema = 'crm'
GROUP BY table_name
ORDER BY table_schema, table_name,ordinal_position;

Result:

• utf8_general_ci: 130 columns in 55 tables
• utf8_unicode_ci: 345 columns in 74 tables

Why this mismatch and what columns to look out for, used in queries with string comparison that leads to the errors stated above?

[tobias]

Hi there, any idea what to check? I'm sorry to push again, but we need Espo to be up and running again as soon as possible.

[tobias]

Ok, got it working. Anyone else struggling with the collation issue, try this:

SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;') FROM information_schema.TABLES WHERE TABLE_SCHEMA = '[your_schema_name]';

And then execute the generated query.