Tweet about this on TwitterShare on FacebookShare on Google+Share on StumbleUponShare on TumblrShare on RedditPin on PinterestEmail this to someone
Illegal mix of collations

MySql Logo

Once again I started playing with Laravel as over the past few days as it has been changed a lot from version 3 to 4, of course very good improvements. So I created few database migrations and things worked great. Then I wanted to write some complex mysql query so I thought using a stored procedure instead of Eloquent so that I can fine tune it to my expectations. First thing I started doing is writing the sql query in MySQL workbench. I wanted to have ‘where’ clause comparison with one of the variable I declare. But immediately it gave me “Illegal mix of collations” error after searching the net for a while I was able to sort it out. I thought anyone new to mysql will face this issue sooner or later, so I decided to write this post to help them out.

Collation in related to database means the set of rules governing how to compare and/or sort characters. Database must have clear idea of which collation is used in order to provide the support for sorting and compare (ex: order by or where clause). As long as you use same application (or programming code) or if you’re using columns already created in these clauses or sorting database doesn’t have any issue in working on them because they are of same collation. In my case I used Laravel migrations to create and seed the tables so we don’t have any issue fetching data from Laravel PHP code. But when you use MySql command line or MySql Workbench then the application we use to work with data is different.

How to Re-Create ‘Illegal mix of collations’ Error

If you’re reading this article that means you have already recreated it so I think you already have table and data which can cause this issue when accessing from command line or MySql Workbench. To illustrates the solution I have already created sample table and added few rows of string data.

As you can see we have a table with one column which is a type of varchar(25).  Assume we created this using Laravel migrations. We have also added few rows as below.

This will be just a simplified example but assume you have extremely large set of tables and you need to write complex query which has ‘where clauses’ at multiple levels and you need to have comparison with one of the variable you’re passing in. Check following example.

As you can see we have done everything right here but MySql complaining about “Illegal mix of collations” error. First question is how do we know which collation is used by table and which is used by our variable. Because we don’t want to change database or table collation just to check our query temporarily. We just need to make this work for now in order to test this query.

How to Check the Collation of MySql Table

This is very simple only this you have to do is run following command on your console (or MySql Workbench). Then check the collation field at the end.

Above output may not clear as the data is too much and row is wrapped into next line but I think you can see the Collation column header and “utf8_unicode_ci” under that. This means our ‘test’ table is using “utf8_unicode_ci” collation but according to previous error then our parameter text data must be of  “utf8_general_ci” collation. So then we need to convert our variable to what database expects.

Fixing the Problem

Simply convert your variable assignment with following, then you will see no issue using it in the query where clause.

 

Tweet about this on TwitterShare on FacebookShare on Google+Share on StumbleUponShare on TumblrShare on RedditPin on PinterestEmail this to someone