Situation:

You installed Drupal core using a third-party installer. Then, you installed or enabled more modules to add more features to your site.

You encounter illegal mix of collation errors involving latin1_swedish_ci and utf8_general_ci collations.
For example, whenever you perform a search using a Chinese keyword, you encounter an illegal mix of collation error.

You realised that about 40 tables in the Drupal database have been created with latin1_swedish_ci collation.
And, Drupal created the rest of the tables with utf8_general_ci collation.

Solution:

Manual Method (30 mins to 1 hr)

1. Put your site to offline mode.

2. Backup all your databases (Important).

3. Select phpMyAdmin.

4. Select the correct Drupal database.

5. Modify table collation:

• Locate table with latin1_swedish_ci collation
• Select Structure (Index finger pointing icon)
• Select Operations tab
• Change collation to utf8_general_ci
• Go

6. Modify field collation:

* Some fields have collations, some do not have collation. Do not modify those fields without collation.

• (Select Structure tab)
• Locate field with latin1_swedish_ci collation
• Select Change (Pencil icon)
• Change collation to utf8_general_ci
• Save
• Repeat step 6 to modify other fields with latin1_swedish_ci collation to utf8_general_ci collation.

7. Repeat steps 4 to 6 to modify other tables with latin1_swedish_ci collation to utf8_general_ci collation.

8. Put your site to online mode.

9. Make sure that your site is functioning normally.

Easier Method (2 mins to 10 mins)

After making the mistake of installing Fantastico, there is an easier way to correct the collation of all tables:

Create a new php file on the server with the following and then run it:

$db = mysql_connect('localhost','myuser_mydbuser','mypassword');
if(!$db) echo "Cannot connect to the database - incorrect details";
mysql_select_db('myuser_mydbname'); $result=mysql_query('show tables');
while($tables = mysql_fetch_array($result)) {
foreach ($tables as $key => $value) {
mysql_query("ALTER TABLE $value COLLATE utf8_general_ci");
}}
echo "The collation of your database has been successfully changed!";

Make sure to substitute in the above script:

- myuser_mydbname with your database name;
- myuser_mydbuser with your mysql username;
- mypassword with your password for the mysql user;