Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation ‘=’

by

The default collation for stored procedure parameters is utf8_general_ci and you can’t mix collations, so you have four options:

Option 1: add COLLATE to your input variable:

SET @rUsername = ‘aname’ COLLATE utf8_unicode_ci; -- COLLATE added
CALL updateProductUsers(@rUsername, @rProductID, @rPerm);

Option 2: add COLLATE to the WHERE clause:

CREATE PROCEDURE updateProductUsers(
    IN rUsername VARCHAR(24),
    IN rProductID INT UNSIGNED,
    IN rPerm VARCHAR(16))
BEGIN
    UPDATE productUsers
        INNER JOIN users
        ON productUsers.userID = users.userID
        SET productUsers.permission = rPerm
        WHERE users.username = rUsername COLLATE utf8_unicode_ci -- COLLATE added
        AND productUsers.productID = rProductID;
END

Option 3: add it to the IN parameter definition (pre-MySQL 5.7):

CREATE PROCEDURE updateProductUsers(
    IN rUsername VARCHAR(24) COLLATE utf8_unicode_ci, -- COLLATE added
    IN rProductID INT UNSIGNED,
    IN rPerm VARCHAR(16))
BEGIN
    UPDATE productUsers
        INNER JOIN users
        ON productUsers.userID = users.userID
        SET productUsers.permission = rPerm
        WHERE users.username = rUsername
        AND productUsers.productID = rProductID;
END

Option 4: alter the field itself:

ALTER TABLE users CHARACTER SET utf8 COLLATE utf8_general_ci;

Unless you need to sort data in Unicode order, I would suggest altering all your tables to use utf8_general_ci collation, as it requires no code changes, and will speed sorts up slightly.

UPDATE: utf8mb4/utf8mb4_unicode_ci is now the preferred character set/collation method. utf8_general_ci is advised against, as the performance improvement is negligible. See https://stackoverflow.com/a/766996/1432614

More Related Contents:

  1. Stored procedure that Automatically delete rows older than 7 days in MYSQL
  2. Can I create view with parameter in MySQL?
  3. Pass array to MySQL stored routine
  4. Connect By Prior Equivalent for MySQL
  5. How to raise an error within a MySQL function
  6. MySQL stored procedure vs function, which would I use when?
  7. Looping Over Result Sets in MySQL
  8. mysql dynamic query in stored procedure
  9. MySQL : transaction within a stored procedure
  10. Writing optional parameters within stored procedures in MySQL? [duplicate]
  11. Dynamic table names in stored procedure function
  12. List of Stored Procedures/Functions Mysql Command Line
  13. passing LIMIT as parameters to MySQL sproc
  14. mysql stored procedure that calls itself recursively
  15. MySQL triggers cannot update rows in same table the trigger is assigned to. Suggested workaround?
  16. How do you debug MySQL stored procedures?
  17. MySQL Stored Procedure Error Handling
  18. MySQL error #2014 – Commands out of sync; you can’t run this command now
  19. MySQL Stored Procedures not working with SELECT (basic question)
  20. use a variable for table name in mysql sproc
  21. Split a string and loop through values in MySQL stored procedure
  22. How to schedule a stored procedure in MySQL
  23. My SQL Dynamic query execute and get ouput into a variable in stored procedure
  24. MySQL : When stored procedure parameter name is the same as table column name
  25. How to use Table output from stored MYSQL Procedure
  26. How do I write an SP in phpMyAdmin (MySQL)?
  27. Creating MYSQL Procedure in Laravel 4 Migrations
  28. is it possible to call a sql script from a stored procedure in another sql script?
  29. Mysql stored procedure don’t take table name as parameter
  30. How to pass a variable to a IN clause?

Leave a Comment