If you ever get the dreaded "Illegal mix of collation" error you may want to convert all your tables to the same collation to fix it. Note that simply changing the tables in phpmyadmin will not actually change all the fields.
There are scripts available to do this, most notably here. However, in my case, the script required a ton of changes, and eventually started kicking up errors that defied fixing.
So, this is how I did it - taking advantage of the fact a mysql backup is a plain text file that can be edited. I found the original idea here.
In the following, as I recall I was mostly just trying to change the latin1_general_ci to utf8_general_ci. Obviously you'll need to modify the Search & Replace with your own needs. I can imagine this method would not work to change two completely different charsets like say, Arabic & Latin (but I really have no idea).
This is not a comprehensive tutorial, it's just my own plan that I wanted to post before I delete it. All the usual disclaimers apply, especially as I'm a complete dummy with all things PHP and Mysql.
There are scripts available to do this, most notably here. However, in my case, the script required a ton of changes, and eventually started kicking up errors that defied fixing.
So, this is how I did it - taking advantage of the fact a mysql backup is a plain text file that can be edited. I found the original idea here.
In the following, as I recall I was mostly just trying to change the latin1_general_ci to utf8_general_ci. Obviously you'll need to modify the Search & Replace with your own needs. I can imagine this method would not work to change two completely different charsets like say, Arabic & Latin (but I really have no idea).
This is not a comprehensive tutorial, it's just my own plan that I wanted to post before I delete it. All the usual disclaimers apply, especially as I'm a complete dummy with all things PHP and Mysql.
set default collation in phpmyadmin
close the forum
download a mysql backup
extract, edit: replace all
latin1
with
utf8
compress, upload to server
open Putty, use:
gunzip < mydb.sql.gz | mysql -h 127.0.0.1 -u dbusername -p mydb
test forum
reopen forum
close the forum
download a mysql backup
extract, edit: replace all
latin1
with
utf8
compress, upload to server
open Putty, use:
gunzip < mydb.sql.gz | mysql -h 127.0.0.1 -u dbusername -p mydb
test forum
reopen forum