Installing and upgrading help

Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

There is an argument to be had about whether the scripts should be able to cope with this however, they could cope with my particular flavour of the error if the script writers detected the unicode requirement and converted/sifted the tables.

In a spirit of being helpful rather than just blaming mysql and washing my hands  of it. Here goes.

1. utf8 comes in  flavours, "unicode" and "general". For most of us using Western European languages, general is probably fine. utf8_unicode is for languages with joint characters and such like where it might be important to have sorting work correctly for that language. When I started this process I did not know that and so I think I came across this problem because I chose unicode. (That is an assumption and not necessarily true).

2. The --skip-character-set switch on a mysqldump restore does not (or in my case didn't) correctly restore all data to utf8_unicode, It was certainly all set to utf8 but existing data may have been utf8_general, and so I think I ended up with  a mix of collations.--skip-character-set did not convert existing data

This may be because I do not know enough about switches or because mysqldump needs improving. One of many other possibilities is that my database was all general and new stuff from the upgrade was utf_unicode because of my my.cnf being set to unicode rather than general.

3. This bit of php code (from here http://drupal.org/node/298820).

<?php
// your connection
mysql_connect("localhost","username","password");
mysql_select_db("databasename");

// 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_unicode_ci");
echo $key . " =&gt; " . $table . " CONVERTED<br />";
}
}
?>

Will fix your database by converting all the tables to utf8_unicode properly. I applied this after I had done the sql restore using --skip-character-set from a sql dump imto a mysql with utf_unicode set up as the defaults in my.cnf.

To use this code,

a) In the code, change the login details for the database to whatever yours should be.

b) save it with the name convert.php (or <whatever you choose>.php into the root of your moodle.

c) Call the program  by visiting http://...<myMoodleSite>..../convert.php

d) Wait until loads of messages saying CONVERTED appear for each table.

Now continue with the normal upgrade process by visiting

http://...<myMoodleSite>.../