Converting MySQL Database Contents to UTF-8

Your post covered exactly what I set out to do. Thank you!

FYI, you only got the max_allowed_packet error with the UTF-8 version because UTF-8 takes more space (more bytes) than ISO-8859-15.

i don’t have shell access but would love to know how to do this using phpmyadmin. any tips?

i’ve checked phpmyadmin and iconv docs — and the answer may be there — but alas i’m quite the novice and unsure how to word the query within phpmyadmin.

i am migrating a home-built coldfusion blog to wordpress and it’s full of special characters.

It is a very useful post, but as the previous commenter said: those people who have only phpmyadmin access to their database (i.e. the majority of the average WP users!) will just stare at it. Is there any chance to “convert” this instruction for them?
Thanks!

Linux 下将 MySQL 编�转�至 UTF-8 的实例

Ping Back�自：blog.csdn.net

I like the part about AddDefaultCharset OFF - didn’t think about that myself. For those without shell access and without access to iconv, I suggest to look at the method I used and has described in the wp support forum, in a thread namedchange from ISO-8859-15 to UTF-8

a few notes to those without shell access (and even on a windows machine on top of that )

phpMyAdmin can dyump mysql data just fine (although it calls it “exporting”). Specify a file name and check the “save to file” option, and make sure you select all your tables.

As for backup, it’s unnecessary to import it back. just make a copy of the dump if you really want to. This description does contain a lot of redundant saves -beyond your normal safety needs-.

When you’re finished with conversion –see Uffe’s post above– just use phpMyAdmin’s “import file” feature to import it back.

Very good! Very helpful!
Work finelly fixing the problem to a Portuguese (Brazilian) site.
Thank you!

FYI for windows users - open the doc in wordpad and save with utf-8 encoding - should cure what ails you

Replace utf8 with utf-8 on some systems…

Eu le même problème et ayant suivi vos pas ça a marché niquel: zéro problèmes! Je vous remercie énormement!

Thanks, this how-to was working great for me.

I’ve find this article partially usefull for me but I’ve imported from a custom CMS and I’ve found better doing “iconv -f windows-1252 -t utf8 file.sql > file_utf8.sql” ’cause sometimes ppl do copy/paste from Word into the textarea.

Wow this is great! I was just pulling my hair wondering on how to convert the database

Thanx for sharing the knowledge

Thanks for that solution - worked like a charm, when I tried to import a greymatter blog written in danish.

:o) A

It worked perfect for me. Nice tip!

Hi,

I have same problem, can you help me convert ?
thank !

I think there's an easier solution than restarting mysql (although I haven't tested this, because I've never had a database big enough). Simply tell mysqldump to limit the size of the packets it creates by adding the following commandline argument:

–set-variable=max_allowed_packet=1M

That should have been two dashes, not an em dash. Let me try again:

--set-variable=max_allowed_packet=1M

For those using libiconv-1.9.2-1 ,new iconv file, “utf8″ does not work use “utf-8″ instead as in “iconv -f iso-8859-9 -t utf-8 wordp…..” perfectly converted iso türkish database to utf-8. Also be sure to convert your site language files (in joomla/mambo case /languages/turkish.php for example) to utf-8 by opening in textpad/notepad and saving as utf-8.

add to above. using phpmyadmin is fine, but do not compress, zip version of phpmyadmin and winrar/winzip does not match, dumpt to just sql, also no need to restart mysql server (at least in localhost), just refresh your page.

Just a quick note - it is wise to specify the character set when exporting/dumping from mysql with mysqldump, using the –default-character-set=latin1 command. If not, and your system is defaulting to utf8 after an upgrade, you get iso-8859-15 data inside a utf8 file, which took me quite some time to find out (sigh).

OR you can

Executing SQL Statements from a Text File

The mysql client typically is used interactively, like this:

shell> mysql db_name

However, it is also possible to put your SQL statements in a file and then tell mysql to read its input from that file. To do so, create a text file text_file that contains the statements you wish to execute. Then invoke mysql as shown here:

shell> mysql db_name < text_file

If you place a USE db_name statement as the first statement in the file, it is unnecessary to specify the database name on the command line:

shell> mysql < text_file

If you are already running mysql, you can execute an SQL script file using the source or . command:

mysql> source file_name
mysql> . file_name

Sometimes you may want your script to display progress information to the user. For this you can insert statements like this:

SELECT ” AS ‘ ‘;

The statement shown outputs .

This info just saved my life. I thank you. bows

thanks you soo much for you documentation on how to convert the charset, it saved me a lot of time .. grüsse

Thank you for this post, especially the iconv bit! It worked perfectly!

I had problem converting a joomla ISO DB to UTF8 using the iconv -f iso-8859-15 -t utf8…
It didn’t convert german special caracters.

Then I used iconv -f CP1252 -t utf8 and it worked perfecly….

I had to convert a database from iso latin1 to UTF-8, but I’m not an expert in encodings.
What I found out is that MySQL obviously uses the character set you specify when creating a table to store the data and makes conversions depending of the client.
If the DB is stored using UTF-8 and the client specify latin1 (using the SET NAMES ‘latin1′ statement) after connecting, MySQL will convert all data to latin1 before sending the results to the client.

My need was to convert a database stored in latin1 to UTF-8, so I simply used the following commands (a quick hack) and MySQL would do the rest :

mysqldump –opt -u db_user -p db_name | sed “s/CHARSET=latin1/CHARSET=utf8/” > db.dump.sql

mysql -u db_user -p -D db_name < db.dump.sql

As suggested by Egil, specifying the charset to these commands would be wise.

The default charset of my server is latin1 so db.dump.sql is encoded in latin1, sed replaces all default charset for each table from latin1 to UTF-8 and the second mysql will parse the file using the latin1 charset, send the query to the server in latin1 and the server will convert and store all the data in UTF-8.

Here is a technique I found that allows you to do the conversion to unicode through phpMyAdmin. I have tried this personally with a WP site/database and it worked for me on a site that has both English and Japanese text. This approach becomes really tedious if you have a lot of database tables though.

Making your Wordpress database portable:
http://jonkenpon.com/2007/02/20/making-your-wor...>

Here is a technique I found that allows you to do the conversion to unicode through phpMyAdmin. I have tried this personally with a WP site/database and it worked for me on a site that has both English and Japanese text. This approach becomes really tedious if you have a lot of database tables though.

Making your Wordpress database portable:
http://jonkenpon.com/2007/02/20/making-your-wordpress-database-portable-because-it-probably-isnt-right-now/

AFAIK, this could be done much simpler:

ALTER TABLE table_name CHARSET 'utf8';

This is what official MySQL documentation says: “If you want to change all CHAR/VARCHAR/TEXT columns to a new character set you can do: ALTER TABLE table_name CHARACTER SET character_set_name;”

I'm afraid that does not convert the actual content. It won't work in this case. Trust me, I'd have done it, otherwise

Sorry, I have mistaken with the statement: it should be

ALTER TABLE table_name CONVERT TO CHARACTER SET 'utf8';

I have recently faced the same problem and that statement did really help!

You can read about it here: http://dev.mysql.com/doc/refman/5.0/en/alter-ta...>

Believe me, it works

Damn… I don't get anything.
Seems, it DOES work.

Where to write this? mysqldump –opt -u root -p wordpress > wordpress.sql

No idea where to start. Please help me guys to fix this old Mongolian DB!

Where to write this?

Frank, you will need to log into the server with your SSH client (provided you have SSH access enabled). And in the command line you type those commands.

Alternatively, you can probably do this with the help of PHP Shell.

Please feel free to contact me if you need help: sjinks (at) sjinks (dot) org (dot) ua

Regards,
Vladimir

Hello there,

unfortunately, it is not enough to add the –max_allowed_packet option to your mysql import statement. While this will increase the allowed packed size the client can send, it will not change the allowed packet size the mysql server. See here:
http://lists.mysql.com/mysql/198070

So, if your dump is very big, you will have to edit my.cnf to increase the packet size there–at least temporarily. Don't forget to restart after editing my.cnf

DrTebi

Great! I was having the same host header error on the validator. I had actually forgotten where the host header came from but your post cleared it up for me.

-Thanks