CODEWITHSUNDEEP
mysqlstored-proceduresparameter-passingmysql-5.1
Jeromy French
Jeromy French

Reputation: 12121

why does a parameter passed into a stored procedure cause MySQL error 1267?

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?

Upvotes: 1

Views: 847

Answers (1)

Rahul
Rahul

Reputation: 77896

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

http://forums.mysql.com/read.php?103,265345,265579

Upvotes: 4

Related Questions