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('[email protected]');

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

What can I do to resolve this? Find the row(s) which causes the issue and use according COLLATE or CONVERT(). — Akina
i want to do it for entire table @Akina. Can you tell the command if I want to do it for the entire table. — art
If so then use ALTER TABLE and change column(s) collation. Ensure that this won't truncate/damage your data (backup!) — Akina
I don't understand which table and column are causing this @Akina — art
Create some debug table (for example, with one column of TEXT type). Add a lot of INSERT statements into your procedure between your working statements which inserts 1,2,3,... into debug table. Execute. Then look the data in the debug table - only values inserted before issue row will be found in it. This allows to determine the code row which causes the problem. Of course you may insert not only numbers, but the values of variables additionally too. — Akina

Rick James Rick James · Accepted Answer · 2021-02-05T18:49:17

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

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

2 Answers