user warning: Illegal mix of collations

This is not a Views issue, it says you have a sessions table with the wrong collation and you can't combine data from tables using different collations into the same query. Use something like PhpMyAdmin to see the collations for your tables and update them to the right collation (all should be utf8_general_ci). I don't remember exactly how to do that, Google around for instructions on how to do this if you can't figure it out.

Instead of doing this table by table in phpMyAdmin, you could also use this script:

http://dhani.web.id/index.php/2008/12/24/alter-all-tables-in-all-database/

mysql_connect("localhost","dbadminuser","dbadminpassword");
$dbs = mysql_query("SHOW DATABASES");
 
while ($baris = mysql_fetch_array($dbs))
{
foreach ($baris as $node => $db) {
mysql_select_db($db);
 
// convert code
$res = mysql_query("SHOW TABLES");
while ($row = mysql_fetch_array($res))
{
    foreach ($row as $key => $table)
    {
        mysql_query("ALTER TABLE " . $table . " CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci");
        echo $key . " =&gt; " . $table . " CONVERTED<br />";
    }
}
}}

1. create a new file in the root of your site; call it convert.php
2. copy and paste the above code into the file
3. alter the 'mysql_connect' values to match your database
4. save the file, and then access it in your browser: http://yoursite.com/convert.php
5. delete the file from your filesystem and voila!

Not sure if I'm missing something obvious, but I have had the same issue and have changed all collation settings to utf8_general_ci using the MySQL Administrator kit. All fields now have the right setting but I am still getting the warning from editing or cloning views?

One strange thing is phpMyAdmin which also shows all tables have in fact been set to utf8_general_ci but at the bottom (totals) row of the list of tables, the row heading for the Collation column has the caption utf8_unicode_ci? But this seem to me to be a co-incidence?..perhaps a phpMyAdmin bug or is it related to the warnings still appearing within drupal/views ?

Any help would be great.
Thanks

------
Found the problem... even though all table settings where set to utf8_general_ci, some table fields where still set to utf8_unicode_ci... sorting those out got rid of the collation warning

For what it's worth, I had this same error with a Fantastico De Luxe installation of 6.13.

I went in with PHPMyAdmin and edited the "sessions" table to set the sid, hostname and session fields to all be "utf8_general_ci". At first I was trying to set ALL of the fields in the "views_object_cache" table to the "utf8_general_ci" collation, but you can't do that with the integer field.

Anyway, in case someone is having the same problem, it was in my "sessions" table not the "views_object_cache" table. I just needed to read the whole SQL statement to see that the inner join was looking for another table.

This worked beautifully! Thank you.

I used convert.php and it solved this problem.

Worked perfectly.

I just ran into the same problem (again) and found this utility that changes the collation of all columns in all tables in a given database. Also, it takes the connection parameters as form input values --- so, no risk of saving (and forgetting to delete) you database connection string somewhere accessible in your site.

http://www.phoca.cz/documents/38-tools/154-how-to-change-collation-in-da...

The actual ZIPped PHP script can be downloaded here

http://www.phoca.cz/download/category/17-phoca-changing-collation-tool?d...

It's a PHP script which you upload onto your web server, call it through your web browser, give it your credentials and it does the collation conversion. Worked like a charm for me!

This can change the collation for you
http://drupal.org/project/dbtuner

I used the dbtuner. It returned an warning about offset type but fixed all of my collation issures.

Thanks.