Announcement

Collapse
No announcement yet.

Problems After Database Migration

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Problems After Database Migration

    Hi there,

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

    Code:
    [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:

    Code:
    [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:

    HTML Code:
    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?
    Last edited by tobias; 11-16-2018, 10:26 AM.

  • #2
    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.

    Comment


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

      HTML Code:
      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.

      Comment

      Working...
      X