AndrewT Posted September 22, 2011 Share Posted September 22, 2011 Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '=' That is the exact error I get when I try to add a product to the cart. I added the testing code to the "add_to_cart" call, and the above error is displaying. $r = mysqli_query($dbc, "CALL add_to_cart('$uid','$sp_type',$pid,1)"); if(!$r) echo mysqli_error($dbc); What would be causing this? Obviously it is something that I am doing wrong, or else the forums would be blown up with posts asking this question. I Google'd it and got quite a few vague answers. Any ideas? If anyone needs anything else to figure out the problem (code snippets, explanations) just ask. Link to comment Share on other sites More sharing options...
Larry Posted September 23, 2011 Share Posted September 23, 2011 That means that the collation used to define the database, table, and column is different than the collation used to perform the query on the database, which is why MySQL can't do an equality test. I would confirm what collation and charset was used to define the database, table, and column in question and then confirm what charset the PHP script is communicating with MySQL using. Link to comment Share on other sites More sharing options...
AndrewT Posted September 23, 2011 Author Share Posted September 23, 2011 I will look in the information.schema and see what exactly all the table columns in that query are using for collation. How do I check to see what collation the PHP script is communicating in? I assumed it was utf8_general_ci. Link to comment Share on other sites More sharing options...
Larry Posted September 23, 2011 Share Posted September 23, 2011 The collation dictates how words are sorted and compared and such. That's in MySQL and isn't a PHP issue (here). You can determine the character set being used by PHP to communicate with MySQL by establishing that after connecting to the database. Link to comment Share on other sites More sharing options...
AndrewT Posted September 27, 2011 Author Share Posted September 27, 2011 I have checked all the columns and they are all set to utf8_general_ci. However when I run the procedure I still get the Illegal Mix of Collations error. Here is the exact code in the procedure: DELIMITER $$ CREATE PROCEDURE add_to_cart (uid CHAR(32), type VARCHAR(6), pid MEDIUMINT, qty TINYINT) BEGIN DECLARE cid INT; SELECT id INTO cid FROM carts WHERE user_session_id=uid AND product_type=type AND product_id=pid; IF cid > 0 THEN UPDATE carts SET quantity=quantity+qty, date_modified=NOW() WHERE id=cid; ELSE INSERT INTO carts (user_session_id, product_type, product_id, quantity) VALUES (uid, type, pid, qty); END IF; END$$ DELIMITER ; Sorry it took so long to get back, I was out of town for the weekend. Link to comment Share on other sites More sharing options...
Larry Posted September 27, 2011 Share Posted September 27, 2011 No need to apologize. The problem is not going to be in the procedure. Most likely the issue is the character set that PHP is using to communicate with MySQL, as I mentioned in my previous post. Link to comment Share on other sites More sharing options...
AndrewT Posted September 27, 2011 Author Share Posted September 27, 2011 Well I fixed the problem. The problem was that for whatever reason, the 'uid' was being read as utf8_unicode_ci instead of utf8_general_ci. So wherever it mentioned 'uid' I changed it to read CONVERT(uid USING utf8) COLLATE utf8_general_ci and it fixed it. Now the queries run just fine. Only problem is the cart stays empty haha >.> I will work on that problem for a while though before I ask for help. Thanks Larry! 1 Link to comment Share on other sites More sharing options...
Larry Posted September 27, 2011 Share Posted September 27, 2011 Okay. Kudos for figuring it out and thanks for sharing the solution. Link to comment Share on other sites More sharing options...
Jonathon Posted October 23, 2011 Share Posted October 23, 2011 Thanks for this Andrew, I needed this today! Link to comment Share on other sites More sharing options...
margaux Posted March 9, 2012 Share Posted March 9, 2012 I have come up against this issue as well and tried what Andrew T suggested (thanks). That seemed to work on the get_shopping_cart_contents stored procedure as I can now display the shopping cart but I get the same error when I try to remove an item or add an item to the cart Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_swedish_ci,IMPLICIT) for operation '=' This is a bit technical for me so apologies in advance for my stupid questions... Which collation is PHP using and which collation is MySQL using? In phpmyadmin it looks as if mysql defaults to utf_swedish but i have defined the tables and fields as utf_general? I'm not sure where to look to find out which collation PHP is using. Do I need to convert the collation for every instance of every variable in the stored procedures - e.g. $uid and $sp_type are strings and $pid and $qty are integers - is collation different on strings v numbers? Thanks for any suggestions! Link to comment Share on other sites More sharing options...
Larry Posted March 9, 2012 Share Posted March 9, 2012 The character set used by PHP is set in the database connection script. Check what columns use an equality comparison in that procedure, and then go into phpMyAdmin and make sure that both are using UTF8. This is only an issue for string columns. Link to comment Share on other sites More sharing options...
margaux Posted March 9, 2012 Share Posted March 9, 2012 I changed the collation in the connection script from utf8 to utf8_general_ci, then recreated the stored procedures and everything is now working nicely. Thanks for the help! Link to comment Share on other sites More sharing options...
Larry Posted March 9, 2012 Share Posted March 9, 2012 Great. Thanks for letting us know. Link to comment Share on other sites More sharing options...
Recommended Posts