Full or Incremental Phase Error: Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation

Scenarios

During a full or incremental migration or synchronization, an error is reported, and the log information is as follows: Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation

Possible Causes

The sorting rule of the source MySQL 5.* character set utf8mb4 is utf8mb4_general_ci, and that of the destination GaussDB(for MySQL) character set utf8mb4 is utf8mb4_0900_ai_ci. An error is reported, indicating that the sorting rules are inconsistent.

Solution

• Solution 1
  1. Run the SQL statement in the destination database to change the character set sorting rule of the corresponding column to utf8mb4_0900_ai_ci. For example, to change the character set sorting rule of column c1 in table test_collation_1 to utf8mb4_0900_ai_ci, run the following command:

      ALTER TABLE test_collation_1 MODIFY COLUMN c1 VARCHAR(16) COLLATE utf8mb4_0900_ai_ci;

  2. In the task list, locate the target task and click Resume in the Operation column to resume the task.
• Solution 2
  1. Delete all columns containing collate utf8mb4_general_ci from the source database table.
  2. In the task list, locate the target task and click Reset in the Operation column to reset the task. Alternatively, create a DRS task again.
• Solution 3
  1. Run the SQL statement in the destination database to change the character set sorting rule of the destination database to utf8mb4_0900_ai_ci.

     SET GLOBAL default_collation_for_utf8mb4='utf8mb4_general_ci';

  2. In the task list, locate the target task and click Reset in the Operation column to reset the task. Alternatively, create a DRS task again.