How to fix “Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='”

Question

While executing a stored procedure in MySQL on my remove server as below:

CREATE OR REPLACE PROCEDURE `SetNextPage`(
    IN `inRefNo` varchar(30) COLLATE utf8_general_ci,
    IN `inStage` varchar(40) COLLATE utf8_general_ci,
    IN `inRedirectTo` varchar(50) COLLATE utf8_general_ci,
    IN `inSurveyType` varchar(14) COLLATE utf8_general_ci
)
BEGIN
    IF inSurveyType = 'preinspection' THEN
        UPDATE preinspections SET Stage = inStage, RedirectTo = inRedirectTo WHERE RefNo = inRefNo;
    ELSE
        UPDATE surveys SET Stage = inStage, RedirectTo = inRedirectTo WHERE RefNo = inRefNo;
    END IF;
END

I am receiving error message as:

Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

Here's a screenshot of tables in my database with query as below:

SELECT TABLE_NAME, TABLE_COLLATION
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="mydatabase" AND TABLE_TYPE="BASE TABLE";

This table quick_tables.bck is just stuck in there because of .bck in its name. But it is not being anywhere in my code.

Here's the snapshot of system variables with an SQL query as below:

SHOW VARIABLES WHERE Variable_name LIKE ("character%") OR Variable_name LIKE ("Collation%");

Variable_name Value
character_set_client utf8
character_set_connection utf8
character_set_database utf8
character_set_filesystem binary
character_set_results utf8
character_set_server latin1
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets/
collation_connection utf8_unicode_ci
collation_database utf8_general_ci
collation_server latin1_swedish_ci

Being on remote server with Shared Hosting, I cannot change environmental variables.

What changes should I make to make my stored procedure to run?

Rick James Rick James · Accepted Answer · 2019-12-24T20:25:17

Do SHOW CREATE PROCEDURE SetNextPage \G

You will probably find that the character set and/or collation in effect when you defined the proc was not what you want now.

Either change the collation of your connection, or drop the proc, SET NAMES, and re-CREATE the proc.

For general use, utf8_genera_ci is the least 'good', followed by utf8_unicode_ci, then utf8_unicode_520_ci. There is an even better one when you get to MySQL 8.0.

You can change the SESSION variables for your connection.

Since there is a difference between the settings in the Proc and the connection, one or the other needs changing.

To change the proc, DROP it, SET NAMES utf8 COLLATE utf8_unicode_520_ci; re-CREATE it.

To change the connection, see what the API provides in the connection. Or do a SET like above.

How to fix “Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='”

2 Answers