The Library

Related Documents

Illegal Mix of Collations

Length: 240 words; Published: October 21, 2024; Updated: October 21, 2024; Category: Schema & SQL; Type: Troubleshooting

A mismatch in collations used in incoming replication and the table structure in the node itself causes an error.

Scenario

You get an error such as the one below:

2021-03-25T16:09:32.856092+01:00 37 [ERROR] [MY-010584] [Repl] Slave SQL for channel ‘’: Error ‘Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,EXPLICIT) for operation ‘=’' on query. Default database: ‘farao_achmea_hdod_a’. Query: ’UPDATE tblDocument SET LockedBy = NULL , LockedDT = NULL , LockExpirationDT = NULL WHERE LockedBy = NAME_CONST(‘inLockedBy’,_latin1'AchmeaHdodEnrichFaraoFile:123646188' COLLATE ‘latin1_swedish_ci’)‘, Error_code: MY-001267
2021-03-25T16:09:32.879162+01:00 37 [ERROR] [MY-010584] [Repl] Slave SQL for channel ‘’: Node has dropped from cluster, Error_code: MY-001047 <<<

This error is likely caused by a mismatch in collations used in incoming replication and the table structure in the node itself.

For example, the following WHERE clause may cause the error:

WHERE 'A' COLLATE utf8_general_ci,IMPLICIT = 'A' COLLATE latin1_swedish_ci,EXPLICIT

Work-Arounds & Solution

To mitigate such errors, there are a couple of things you can do.

  1. Modify the table structure to use the utf8_general_ci collation, to match the incoming data:

    ALTER TABLE tblDocument
CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

  2. Modify the replication source to use the latin1_swedish_ci collation to match the table structure.

  3. Use an explicit COLLATE clause in the replication query to convert the incoming data:

    UPDATE tblDocument
SET LockedBy = NULL, LockedDT = NULL, LockExpirationDT = NULL
WHERE LockedBy = NAME_CONST(‘inLockedBy’,_latin1'AchmeaHdodEnrichFaraoFile:123646188' COLLATE ‘utf8_general_ci’);

  4. Consider converting the entire database to use a consistent character set and collation, preferably utf8mb4 with an appropriate collation, such as utf8mb4_unicode_ci.

Related Documents