Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation '='

Question

When im running the follwing procedure

call clean_email('johnny@c.com');

I'm getting the error Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation '='

I tried changing collations for table applications but still, the error doesn't resolve. What can I do to resolve this? Do I need to change the procedure?

CREATE DEFINER=`root`@`%` PROCEDURE `clean_email`(_email_ varchar(128))
this_proc : BEGIN
    declare _appId                bigint unsigned;
    /*TEMP*/  declare _resumeId    bigint unsigned;
    DECLARE done INT DEFAULT FALSE;

 

    DECLARE cursor_i CURSOR FOR 
        /*TEMP*/  (SELECT appId, null as "resumeId" FROM _appendix WHERE lower(`value`) = lower(_email_) AND lower(`key`)="applicantemail")
        -- LATER -- andOr /*TEMP*/  UNION
        -- LATER -- (SELECT id 
        -- LATER -- andOr /*TEMP*/, resumeId
        -- LATER -- FROM cbax_application WHERE lower(`name`)  =  lower(_email_))
        ;
    /*TEMP*/ DECLARE cursor_old CURSOR FOR select id from applications where 
        /*TEMP*/ CASE WHEN formData is null OR trim(formData)="" THEN false ELSE 
        /*TEMP*/ lower(JSON_UNQUOTE(JSON_EXTRACT(CONVERT(formData using utf8mb4),'$.candidateParams.ApplicantEmail'))) = lower(_email_) END;
     
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
    BEGIN
        GET DIAGNOSTICS CONDITION 1 @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
        ROLLBACK;
        select "Failed" as "status", @p1 as "sql_state", @p2 as "message_text";
    END;
    
    -- ---------------------
    -- Input Validations --
    IF(_email_ IS NULL or instr(_email_,"@") < 2) THEN
        select "Failed" as "status", 0 as "sql_state", "Email id is required." as "message_text";
        LEAVE this_proc;
    END IF;
    -- END Input Validations --
    -- ------------------------
    
    START TRANSACTION;
        OPEN cursor_i;
        read_loop: LOOP
            FETCH cursor_i INTO _appId
            /*TEMP*/, _resumeId
            ;
            IF done THEN
                LEAVE read_loop;
            END IF;
    
            DELETE FROM cbax_application WHERE id = _appId;
            DELETE FROM cbax_application_blob WHERE appId = _appId;
            DELETE FROM job_info WHERE appId = _appId;
            DELETE FROM `resume` WHERE id = _resumeId;
            -- LATER -- DELETE FROM _values WHERE appId = _appId;
            -- LATER -- DELETE FROM _letter WHERE appId = _appId;
            -- LATER -- DELETE FROM _history WHERE appId = _appId;
            
            /*TEMP*/ DELETE FROM _appendix WHERE appId = _appId;
        END LOOP;
        CLOSE cursor_i;
        
        /*START TEMP*/
            SET done = FALSE;
            OPEN cursor_old;
            old_loop: LOOP
                FETCH cursor_old INTO _appId;
                IF done THEN
                    LEAVE old_loop;
                END IF;
        
                DELETE FROM applications WHERE id = _appId;
                DELETE FROM _values WHERE appId = _appId;
                DELETE FROM_letter WHERE appId = _appId;
                DELETE FROM _history WHERE appId = _appId;
            END LOOP;
            CLOSE cursor_old;
        /*END TEMP*/
    
        select "Success" as "status";
    COMMIT;
END

Answer 1

SQL is picky when it comes to the interaction of charset and collation. This is also in MySQL Collation: latin1_swedish_ci Vs utf8_general_ci .

Change the CHARSET to utf8_swedish_ci : DEFAULT CHARACTER SET = utf8_swedish_ci

When using 'incompatible' charsets and collations one tries to compare 'apples with pears' :

A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set.

source : https://dev.mysql.com/doc/refman/8.0/en/charset-general.html

Answer 2

Do SHOW CREATE PROCEDURE clean_email; but look at the extra columns other than the body of the code. One of them says the CHARACTER SET used when creating the proc. It probably says latin1.

To change that,

SET NAMES utf8mb4;   -- assuming this is desired
DROP PROCEDURE clean_emaill;
and recreate the procedure