Errors with MySQL 8 need documenting

[byte]

Sometimes we see errors similar to:

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 <<<

We know that this is likely caused by a mismatch in collations used in incoming replication and the table structure in the node itself. We don't know if an upgrade changed some columns or something.

We know Oracle is documenting stuff similar to this: https://support.oracle.com/knowledge/Oracle%20Database%20Products/2582168_1.html

We should be documenting this as well, because the Oracle site isn't free for others to read up on

[kirjaamo]

@byte Maybe we could add something to Troubleshooting Articles in the Knowledge Base. Do you have access to the Oracle documentation? If you do, can you copy the article or the needed info to me?

[byte]

@byte Maybe we could add something to Troubleshooting Articles in the Knowledge Base. Do you have access to the Oracle documentation? If you do, can you copy the article or the needed info to me?

i do not have such access. but troubleshooting would be a good idea

[kirjaamo]

@byte What do you think of these two suggestions for a troubleshooting article draft:

1.

Illegal mix of collations

Occasionally, you may see an error like 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 may cause the error:

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

To solve this, specify a shared collation for the two columns within the query. See below for an example COLLATE clause:

SELECT * FROM table ORDER BY key COLLATE utf8_general_ci,IMPLICIT;

2.

Illegal mix of collations

Occasionally, you may see an error like 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 <<<

An "illegal mix of collations" occurs when an expression compares two strings of different collations but of equal coercibility and the coercibility rules cannot help to resolve the conflict.

To solve this problem:

• Change the collation of one (or both) of the strings so that they match and there is no longer any ambiguity, or
• Force one string to not be coercible.