Error Code: 1267. Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)

Question

I have the following MySQL function to check for all the words exists in a string.

CREATE DEFINER=`root`@`localhost` FUNCTION `ALL_WORDS`(s TEXT, m TEXT) RETURNS int(11)
BEGIN
DECLARE j INT DEFAULT 1;
DECLARE k INT DEFAULT 1;
DECLARE c INT;
--
SET s=TRIM(s);
--
IF s<>'' AND m<>'' THEN
--
SET s=CONCAT(' ', UCASE(s) , ' ' );
SET m=UCASE(m);
--
SET c=PARSECOUNT(m,' ');
--
 a:LOOP
--
   IF j=c+1 OR INSTR(s,CONCAT(' ',PARSE(m,' ',j),' ')) =0 THEN
--
     LEAVE a;
--
   END IF;
--
   SET j=j+1;
--
 END LOOP;
--
SET k=(j=c+1);
--
END IF;
--
RETURN k;
--
END

When I run I get the following response

Error Code: 1267. Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'instr'

Do you get the error message when you run the code to define the function or when you use the function later? — Álvaro González, Jun 08 '18 at 15:24
It was working fine with previous version of server now using 5.7.22. I had to add a CAST. — Stephen Bouffe, Jun 08 '18 at 16:37
IF j=c+1 OR INSTR( CAST(s AS CHAR),CAST(CONCAT(' ',PARSE(m,' ',j),' ') AS CHAR)) =0 THEN — Stephen Bouffe, Jun 08 '18 at 16:37
I see... Do you get the error message when you run the code to define the function or when you use the function later? — Álvaro González, Jun 08 '18 at 16:37
My educated guess is that the two arguments come from columns that have different and incompatible collations. Unless your company is based in Sweden I see little reason to use a Swedish collation so I presume that latin1 is an overlook—you possibly forgot to set UTF-8 explicitly and it defaulted to server default. — Álvaro González, Jun 09 '18 at 13:20
CREATE DEFINER=`root`@`localhost` FUNCTION `PARSE`(s TEXT, d TEXT, i INT) RETURNS text CHARSET utf8 BEGIN -- IF i<=PARSECOUNT(s,d) AND i>0 THEN -- RETURN SUBSTRING_INDEX(SUBSTRING_INDEX(s,d,i),d,-1); -- ELSE -- RETURN ''; -- END IF; -- END — Stephen Bouffe, Jun 11 '18 at 08:26

Error Code: 1267. Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)

0 Answers0