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

Question

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 '<>'

Answer 1

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.

Answer 2

Apparently, there are columns/tables within your query that have different collations, being illegal for some operations.

Try changing the table/columns involved to a single collation.