search

sunmingtao / sample-code

3 stars 4 forks source link

java.sql.SQLException: Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '=' #357

Closed sunmingtao closed 1 month ago

sunmingtao commented 1 month ago

Calling a MySQL function sees 

org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: java.sql.SQLException: Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '=' [statement:"select get_top_level_work_id_with_copy_role(:workId, :copyRole) as topLevelId", located:"select get_top_level_work_id_with_copy_role(:workId, :copyRole) as topLevelId", rewritten:"select get_top_level_work_id_with_copy_role(?, ?) as topLevelId", arguments:{ positional:{}, named:{copyRole:'fa',workId:44757416}, finder:[]}]
    at org.skife.jdbi.v2.SQLStatement.internalExecute(SQLStatement.java:1338)
    at org.skife.jdbi.v2.Query.fold(Query.java:173)
    at org.skife.jdbi.v2.Query.first(Query.java:273)
    at org.skife.jdbi.v2.Query.first(Query.java:264)
sunmingtao commented 1 month ago
  1. ALTER DATABASE amberdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  2. Restart the MySQL database server
  3. Drop and re-create the function without the need to append "COLLATE utf8mb4_unicode_ci"

Collection connection is irrelavant, even its value is "utf8mb4_0900_ai_ci". JDBC connection string value also doesn't need to change. (i.e no need to add connectionCollation=utf8mb4_general_ci)

Step 2. Restart the database server is a crucial step. Previously I didn't restart the database, and kept getting the "Illegal mix of collations" even after dropping and re-creating the function.

Do you know why restarting the database server is significant?

ChapGPT:

Restarting the MySQL database server is crucial after making significant changes to the database configuration, such as altering the default character set and collation. Here’s why:

Reasons for Restarting the MySQL Server

Caching and Session Variables:

MySQL server maintains various caches and session variables that might not immediately reflect changes made to the database's default settings. Restarting the server ensures that all sessions, including system and user sessions, are refreshed and use the new configuration settings.

Configuration Reload:

Some configuration changes, particularly those related to character sets and collations, require a full server restart to take effect. This is because certain settings are initialized at server startup and do not change for existing sessions.

Consistency Across Sessions:

Without restarting, existing sessions might continue to use the old collation settings, leading to inconsistencies when new sessions are created with the updated settings. Restarting ensures a consistent environment where all sessions adhere to the new collation settings. Flushing Internal Caches:

MySQL uses internal caches for various operations. These caches might hold old collation data. Restarting the server flushes these caches, ensuring that all operations use the updated collation settings.