character encoding - MySQL Error Code: 1267. Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation 'locate'

Question

I have a MySQL 8 database schema which was originally created with the incorrect collation of "utf8mb4_0900_ai_ci".

When importing tables from another database, compiling the views and running queries against them, I get the error: Error Code: 1267. Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation 'locate'

I then converted collation for the entire database and tables

SELECT @@collation_database; utf8mb4_general_ci

By running SHOW TABLE STATUS FROM <database>; I can confirm that all tables in the database have the correct collation of utf8mb4_general_ci but the error persists.

What should I do to resolve this?

Answer 1

I finally resolved by recreating a few functions that were called as part of the view.