CODEWITHSUNDEEP
mysql
Mark
Mark

Reputation: 314

Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT)

Can anyone help me on how could i fix this error

Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'locate'

I have executed my stored procedure on the server1 and it's work fine, but it will throw an error on the server2. I've checked all the tables and stored procedure on both servers and it's all the same.

Can anyone help me on what is this problem and the resolution for it.

Here is the stored procedure.

DELIMITER $$

DROP PROCEDURE IF EXISTS `UPDATEPASSWORD` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `UPDATEPASSWORD`(IN empn CHAR(9), IN oldpassword VARCHAR(255), IN newpassword VARCHAR(255))
main:BEGIN
DECLARE diction INT;
DECLARE prevpass INT;
DECLARE len INT;
DROP TABLE IF EXISTS  passwordstatus;
CREATE TEMPORARY TABLE passwordstatus
(
    `passwordstatus` VARCHAR(127)
);

PREPARE stmt1 FROM "select count(seqid) into @diction 
                    from dictionary where instr(?,word)>0 or instr(REVERSE(?),REVERSE(word))>0 ";
SET @a = newpassword;
SET @b = newpassword;
EXECUTE stmt1 USING @a,@b;
DEALLOCATE PREPARE stmt1;
IF @diction > 0 THEN
    START TRANSACTION;
    PREPARE stmt1 FROM "INSERT INTO passwordstatus VALUES (?)";
    SET @a = "PASSWORD CONTAINS COMMONLY USED WORDS";
    EXECUTE stmt1 USING @a;
    DEALLOCATE PREPARE stmt1;
    COMMIT; 
    SELECT * FROM passwordstatus;
    LEAVE main;
END IF;

 PREPARE stmt1 FROM "select count(seqid) into @prevpass 
                    from prevpass where (instr(sha1(?),passwd)>0 or instr(REVERSE(sha1(?)),REVERSE(passwd))>0)and empno = ?";
SET @a = newpassword;
SET @b =empn;
EXECUTE stmt1 USING @a, @a, @b;
DEALLOCATE PREPARE stmt1;
IF @prevpass > 0 THEN
    START TRANSACTION;
    PREPARE stmt1 FROM "INSERT INTO passwordstatus VALUES (?)";
    SET @a = "PASSWORD CONTAINS PREVIOUSLY USED PASSWORDS";
    EXECUTE stmt1 USING @a;
    DEALLOCATE PREPARE stmt1;
    COMMIT;      
    SELECT * FROM passwordstatus;
    LEAVE main;
END IF;

PREPARE STMT1 FROM " SELECT COUNT(seqid) INTO @prevpass FROM prevpass where empno = ? ";
SET @a = empn;
EXECUTE STMT1 USING @a;
DEALLOCATE PREPARE STMT1;
START TRANSACTION;
IF @prevpass = 6 THEN
    PREPARE STMT1 FROM "DELETE FROM prevpass WHERE EMPNO = ? ORDER BY seqid LIMIT 1";
    SET @a = empn;
    EXECUTE STMT1 USING @a;
    DEALLOCATE PREPARE STMT1;
END IF;
PREPARE STMT FROM " UPDATE emppass SET passwd = sha1(?),lastupdate = now() WHERE empno = ? ";
SET @a = newpassword;
SET @b = empn;
EXECUTE STMT USING @a,@b;
DEALLOCATE PREPARE STMT;
PREPARE STMT1 FROM " INSERT INTO prevpass (empno,passwd,createdate) VALUES (?,sha1(?),now())";
SET @a = empn;
SET @b = newpassword;
EXECUTE STMT1 USING @a,@b;
DEALLOCATE PREPARE STMT1;
PREPARE stmt1 FROM "INSERT INTO passwordstatus VALUES (?)";
    SET @a = "PASSWORD UPDATED SUCCESSFULLY";
    EXECUTE stmt1 USING @a;
    DEALLOCATE PREPARE stmt1;
COMMIT;
SELECT * FROM passwordstatus;

END $$

DELIMITER ;

Upvotes: 1

Views: 3046

Answers (2)

Bryan
Bryan

Reputation: 1241

try to have a BINARY command before each parameter.

SHA1(BINARY ?)
BINARY passwd

Upvotes: 3

rMX
rMX

Reputation: 1090

Post query you are executing.

As i can see now, you can do this:

Check table collation of tables on both servers:

select table_name, table_collation
  from information_schema.tables
 where table_schema = database();

And check the collation of columns:

select *
from information_schema.columns
where table_schema = database() 
  and collation_name is not null
  and collation_name not like 'utf8%';

Possibly this could help you.

Upvotes: 2

Related Questions