Mysql – SQL Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,NUMERIC) for operation

collationMySQLmysql-error-1064

Total NOOB trying to solve this error. I recently switched servers and suddenly a table / sql query which used to work perfectly is now displaying the following error:

Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,NUMERIC) for > operation '<>' SQL=SELECT…

I have a structure in the table which includes TIME fields and INT fields. When I switch the time field datatype to INT and not TIME the error disappears. Unfortunately so does the time data (it displays 0:00).

The SQL Query looks like:

SELECT
    OverallRank,
    First,
    Last,
    Affiliate,
    Part1Number,
    Part1NumberRank,
    Part2Number,
    Part2NumberRank,
    DATE_FORMAT(Part1Time, '%i:%s') as Part1Time, 
    Part1TimeRank,
    Part3Number,
    Part3NumberRank,
    AgeGroup
FROM
    (SELECT
        First,
        Last,
        Affiliate,
        AgeGroup,
        Part1Number,
        Part2Number,
        Part1Time,
        Part3Number,
        Part1NumberRank,
        Part2NumberRank,
        Part1TimeRank,
        Part3NumberRank,
        (Part1NumberRank + Part2NumberRank + Part1TimeRank + Part3NumberRank) AS Total,
        @overallrank:=CASE WHEN @total <> (Part1NumberRank + Part2NumberRank + Part1TimeRank + Part3NumberRank) THEN @overallrank+1 ELSE @overallrank+0 END AS OverallRank,
        @total:=(Part1NumberRank + Part2NumberRank + Part1TimeRank + Part3NumberRank) AS TTL
    FROM
        (SELECT @overallrank:=0) overallrank,
        (SELECT @total:=0) ttl,
            (SELECT
                First,
                Last,
                Affiliate,
                AgeGroup,
                Part1Number,
                Part2Number,
                Part1Time,
                Part3Number,
                Part1NumberRank,
                Part2NumberRank,
                Part1TimeRank,
                @rank4:=CASE WHEN @Part3Number <> Part3Number THEN @rank4+1 ELSE @rank4+0 END AS Part3NumberRank,
                @Part3Number:=Part3Number AS P4
            FROM
                (SELECT @rank4:=0) r4,
                (SELECT @Part3Number:=0) p4,
                (SELECT
                    First,
                    Last,
                    Affiliate,
                    AgeGroup,
                    Part1Number,
                    Part2Number,
                    Part1Time,
                    Part3Number,
                    Part1NumberRank,
                    Part2NumberRank,
                    @rank3:=CASE WHEN @Part1Time <> Part1Time THEN @rank3+1 ELSE @rank3+0 END AS Part1TimeRank,
                    @Part1Time:=Part1Time AS P3
                FROM
                    (SELECT @rank3:=0) r3,
                    (SELECT @Part1Time:=0) p3,
                    (SELECT
                        First,
                        Last,
                        Affiliate,
                        AgeGroup,
                        Part1Number,
                        Part2Number,
                        Part1Time,
                        Part3Number,
                        Part1NumberRank,
                        @rank2:=CASE WHEN @Part2Number <> Part2Number THEN @rank2+1 ELSE @rank2+0 END AS Part2NumberRank,
                        @Part2Number:=Part2Number AS P2
                    FROM
                        (SELECT @rank2:=0) r2,
                        (SELECT @Part2Number:=0) p2,
                        (SELECT
                            First,
                            Last,
                            Affiliate,
                            AgeGroup,
                            Part1Number,
                            Part2Number,
                            Part1Time,
                            Part3Number,
                            @rank1:=CASE WHEN @Part1Number <> Part1Number THEN @rank1+1 ELSE @rank1+0 END AS Part1NumberRank,
                            @Part1Number:=Part1Number AS P1
                        FROM
                            (SELECT @rank1:=0) r1,
                            (SELECT @Part1Number:=0) p1,
                            (SELECT
                                    *
                            FROM 
                                #__results
                            WHERE
                                EventName = '2011EoSummer' AND
                Gender = {$REQUEST:Gender} AND
                ({$REQUEST:Age} = 'Overall' OR AgeGroup = {$REQUEST:Age})
                ORDER BY 
                                Part1Number DESC
                            ) T1
                        ) T2
                    ORDER BY
                            Part2Number DESC
                    ) T3
                    ORDER BY
                            Part1Time ASC
            ) T4
            ORDER BY
                Part3Number DESC
        ) T5
        ORDER BY 
            Total ASC
    ) T6

Any help would be GREATLY appreciated

I tried to run the query in my PHPMyADMIN and got the following:

1267 – Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,NUMERIC) for operation '<>'

Best Answer

This is a issue which was solved by my hosting company... here was their response after being contacted.

It appears that the database you are currently using defaulted to using the server's default information. This can happen during an account move, or through php configurations. We have prepared a script that you can run to quickly change the collation of any database. Please see the following for more information:

http://www.inmotionhosting.com/support/website/databases/how-to-convert-a-database-to-utf-8

Once the collation is updated, you should then be able to run your query without any errors.

Related Solutions

Mysql – Illegal mix of collations error in MySql

Here's how to check which columns are the wrong collation:

SELECT table_schema, table_name, column_name, character_set_name, collation_name

FROM information_schema.columns

WHERE collation_name = 'latin1_general_ci'

ORDER BY table_schema, table_name,ordinal_position;

And here's the query to fix it:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET latin1 COLLATE 'latin1_swedish_ci';

Link

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

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

1267 – Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,NUMERIC) for operation '<>'

Best Answer

Related Solutions

Mysql – Illegal mix of collations error in MySql

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

Related Topic