If you run into the following MySQL issue (note that the actual collations could be different than the ones here)
Illegal mix of collations (utf8mb4_general_ci,COERCIBLE), (utf8mb4_unicode_ci,COERCIBLE), (utf8mb4_unicode_ci,COERCIBLE) for operation 'replace' (1270)
Then it is very likely that it is a result of mixing collations in your MySQL database, thus, comparing strings of different collations or selecting combined data fails.
In the example above, the two different collations are utf8mb4_unicode_ci and utf8mb4_unicode_ci - but it could also be utf8_unicode_ci and utf8_general_ci, etc...
To fix this, we need to ensure that both collation match. One way we can do this is by changing the collation of one column to match the other.
Find the columns with inappropriate collation:
SHOW CREATE TABLE <table_name>;
Afterwards, you need to change the collation of the table to match that of the other table.
You can change the collation on each column:
ALTER TABLE <table_name> CHANGE <column_name> <data_type> CHARACTER SET <charset_name> COLLATE <collation_name>;
If you want to make a collation change table-wide:
ALTER TABLE <table_name> CONVERT TO CHARACTER SET <charset_name> COLLATE <collation_name>;
If you want to make a collation change database-wide:
ALTER DATABASE <database_name> CHARACTER SET <charset_name> COLLATE <collation_name>;