MySQL Error: Illegal Mix of Collations on Imported Database After Script Update
I was doing some testing before upgrading a vBulletin 3.8.4 board to vB 4.0.1, for that I downloaded the MySQL database of the forum using phpMyAdmin, created a new database on a different hosting account & imported the database there. Next I run the upgrade script & all worked fine, till I accessed my upgraded testing forum & tried to open some pages, I got the following MySQL error:
Database error in vBulletin 4.0.1:
MySQL Error : Illegal mix of collations (cp1251_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='
Error Number : 1267
I thought 1st the error was caused by the MySQL version (4.1.X) or by a bug in vBulletin itself, or by the "character set" I have chosen to export & then import the database in phpMyAdmin. That’s why I exported / imported the DB several times using different "character sets" & restarted the upgrade process, even setup a 2nd testing site on a different server; all with no success.
The real problem is in the "default collation" & it’s very simple to fix
The problem wouldn’t have happened if I run the upgrade script on my original database, it happened because the blank database where I imported my backup, has a different setting than the original one. You may have this same problem if you move to a new host.
To avoid the problem, you need to check your original database in phpMyAdmin. In the Structure tab see what is the collation for tables in the Collation column. In mine for example it’s cp1251_general_ci.
Now, open the Operations tab. In the bottom you can change the default collation type for the database, you probably will find it’s the same collation you saw in the Collation column we saw above, keep it like that.
Now go open the other database (that gave you the error) in phpMyAdmin. in the Collation column you will see that there are two different collation types (in my case cp1251_general_ci & latin1_swedish_ci).
Select all the tables & choose "Drop" to empty the database.
Now go to the Structure tab. The default collation type should be different than that in your original DB (in my case latin1_swedish_ci). Change it to match it (I changed it to cp1251_general_ci).
Now import the DB again & rerun your upgrade script. It should give you no errors this time, because the new tables created by the script will be written using the same collation type in the old tables.