I guess I blog a lot when I am stressed with technical problems…
So Unicode is great. Or is it? Something happened when my web hosting company upgraded MySQL from version 4.0.XX to 4.1.13-standard in last september. Back then I did some web searching and found that (…long story short, and probably inaccurate) in MySQL 4.0.XX all the data in the server was (or can be) stored as unicode (utf8) as default (or bite, and rendered to the users as unicode via web browser), but starting 4.1.XX each MySQL server can be set up to use whatever the character set that administrator decide to use. And each users then can use different Collation setting to get the type of characters one desires.
Because of this, I was no longer able to use phpMyAdmin that host company provided to edit Japanese. And when I ran following SQL query to check my server setting:
SHOW VARIABLES LIKE 'char%'
And result
character_set_client utf8
character_set_connection utf8
character_set_database utf8
character_set_results utf8
character_set_server latin1
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets/
So I submitted a ticket asking to change my.conf on the server to use utf8 as server character set (as seen as character_set_server in the above result) instead of latin1. And here is the response I got from the hosting…
I tried to get the character set switched to utf8 on the server but was unable to get it working, I will try more tomorrow to see if there is anything I can do to get it functioning with the different setting.
I didn’t hear any thing more, but I’ve decided I was entering my blog entry from WordPress interface and editing some info and news on japaneseballplayers.com from very simple custom-made php script with content=”text/html; charset=utf-8″ setting in html header, and didn’t think twice about it.
Till Now.
Now I am switching hosting, and want to migrate all my data into new hosting, and found that only way to do that is through phpMyAdmin’s ‘Export” option. Or at least to get the data from database OUT from the old server is that way. For new server, I can use other apps like CocoaMySQL. Old site does not allow other host to connect to the database. Crap. I have some work cut out for me… So anyone with any info on this subject I’d appreciate your input… (Not that many people read this blog… especially for the database related issues.)
My Old server:
MySQL 4.1.13-standard + phpMyAdmin 2.6.4-pl2
My New server:
MySQL 5.0.18-standard-log + phpMyAdmin 2.6.4-pl3
New server’s sql setting:
character_set_client utf8
character_set_connection utf8
character_set_database latin1
character_set_results utf8
character_set_server latin1
character_set_system utf8
character_sets_dir /data/mysql/[name]/share/mysql/charsets/
UPDATE: a friend pointed out that I can use mysqldump command from commandline. I had to look fort syntax, but this website was overall very useful for MySQL stuff.
mysqldump --add-drop-table -u [username] -p [database] > [backup_file]
Related posts:
