Both of these stored procedures compile in my MySQL 5.1.73 server:
delimiter $$
CREATE PROCEDURE get_admins()
BEGIN
SELECT *
FROM Accounts
INNER JOIN LINK_Account_Status ON Accounts.account_id=LINK_Account_Status.account_id
AND LINK_Account_Status.active_ind=1
WHERE Accounts.active_ind=1
AND Accounts.`type`='admin';
END $$
delimiter ;
delimiter $$
CREATE PROCEDURE get_admins2(
IN p_type varchar(50)
)
BEGIN
SELECT *
FROM Accounts
INNER JOIN LINK_Account_Status ON Accounts.account_id=LINK_Account_Status.account_id
AND LINK_Account_Status.active_ind=1
WHERE Accounts.active_ind=1
AND Accounts.`type`=p_type;
END $$
delimiter ;
Executing CALL get_admins(); returns the results I expect.
Executing CALL get_admins2('admin'); errors:
Error Code: 1267. Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='
The observant responder will notice there is no functional difference between the two resulting queries. I have double-checked that Accounts.type is, indeed, a varchar(50) (even if it is unfortunately-named).
What in the Sam Hill is going on here?
You need to use COLLATE in your WHERE condition to solve this like below
delimiter $$
CREATE PROCEDURE get_admins2(
IN p_type varchar(50)
)
BEGIN
SELECT *
FROM Accounts
INNER JOIN LINK_Account_Status ON
Accounts.account_id=LINK_Account_Status.account_id
AND LINK_Account_Status.active_ind=1
WHERE Accounts.active_ind=1
AND Accounts.`type`=p_type COLLATE utf8_general_ci; /* <-- Here */
END $$
delimiter ;
You can as well add the COLLATION in parameter declaration itself ("As of [MySQL] 5.5.3, COLLATE can be used...") like:
delimiter $$
CREATE PROCEDURE get_admins2(
IN p_type varchar(50) COLLATE utf8_general_ci <-- Here
.....<rest of the code here>.....
EDIT:
After doing some search I found that, this issue may occur if your columns have different collation even though tables have same collation. See the below MySQL Forum post