Orthogonal Thought | Random musings from the creator of Cooking For Engineers and Lead Architect of Fanpop

AUTHORS

CATEGORIES

ARCHIVE

Mysql database migration and special characters

Posted 5 May, 2007 at 10:12pm by Michael Chu
(Filed under: Web 2.0)

So, I keep having all sorts of problems when moving my mysql database from one machine to another. The issue is that in text or varchar columns containing data that have special characters like smart quotes (those crazy curly quotes), accented letters, etc. result in scrambled data. For example the following phrase

it’s

results in

itâs

in the database and then renders incorrectly on the website as

it’s

This is supposed to be a popular problem when moving from mysql 4.0 to 4.1 or later, but I seem to have this problem moving data from one database to another on the same mysql installation. Moving from one maching with 4.1 to another with 4.1 also results in this crazy character problem.

Most of the solutions I found online end up with complicated find and replace functions to remove these problem characters. Unfortunately, that just not a solution that I'm okay with. After fiddling with it for most of the day (and giving up several times in the past), here's what I did that worked for me.

As I understand it, the problem arises from the fact that mysqldump uses utf8 for character encoding while, more often than not, mysql tables default to latin1 character encoding. (If you were smart enough to manually set the character encoding to utf8, then you'll have no problems - everyone running mysql 4.0 or early will be using latin1 since it didn't support any other encodings.) So lets say we have a database named example_db with tables that have varchar and text columns. If you have special characters that are really UTF-8 encoded characters stored in the db, it works just fine until you try to move the db to another server.

What happens when you execute the command:

mysqldump -u mysql_username -p example_db > example_db.mysql

is that mysqldump takes the latin1 encoded tables (containing utf8 characters) and translates them into utf8 encoding (so the utf8 characters are no longer what they are supposed to be) before writing it to disk. In theory, importing the data into mysql should work since it's in utf8 and mysql 4.1 and higher understands utf8, but unfortunately, the double encoding on these characters messes it up and you get funny text (as shown above). As far as I can tell when you execute:

mysql -u mysql_username -p new_db < example_db.mysql

mysql reads the data in (which is in utf8) and converts it to your character encoding. The special character that was misinterpreted by mysqldump and encoded is now decoded to the misinterpreted character and you have a funky series of symbols.

There's two ways of dealing with this that works (for me). The first is dealing with it on an individual table column level and the second works on the dump (database) level.

1. Fixing a single column
With this solution, you just perform a mysqldump as you normally would (see above) and then import as you normally would (see above). The special characters are messed up. They aren't really messed up - they just looked messed up. In actuality, your data is probably perfectly formatted in utf8 but is in a column that is latin1. So, we simply need to switch that column from latin1 to utf8 without altering the data. Unfortunately, you can't just run the ALTER TABLE command that changes the character encoding because then mysql will convert the data from latin1 to utf8 (including the special characters) and you'll end up with a different set of gibberish characters. We just need to change the type WITHOUT running a conversion. To do this change the varchar to binary and the text to blob. This change does not result in any conversion or re-encoding. Then switch it back to varchar or text with the correct encoding.
For a varchar(255) column named "column_name" in a table named "example_table":

ALTER TABLE example_table MODIFY column_name BINARY(255);
ALTER TABLE example_table MODIFY column_name VARCHAR(255) CHARACTER SET utf8;

For a text column named "text_column_name" in a table named "example_table":

ALTER TABLE example_table MODIFY text_column_name BLOB;
ALTER TABLE example_table MODIFY text_column_name TEXT CHARACTER SET utf8;

The data in that table should now be properly interpreted. If not, you've got a different problem than the one I experienced.

2. Fixing the problem on import
If you've got a lot of columns and would prefer to fix it while importing, a solution that works most of the time (repeat: most of the time) is to perform a mysqldump forcing the dump to write out data in latin1. Then on the import we "fool" mysql into thinking it's utf8 data.
For a database called example_db (one following is one line):

mysqldump -u mysql_username -p --default-character-set=latin1 example_db > example_db.mysql

Now open the dump file (example_db.mysql in this example) and edit it. There will be a line that reads like:

/*!40101 SET NAMES latin1 */;

Change latin1 to utf8:

/*!40101 SET NAMES utf8 */;

Now, import the file into mysql:

mysql -u mysql_username -p new_db < example_db.mysql

In most cases everything will import properly. In a few corner cases, the decoded data will resolve to the same text as another piece of decoded data and this could result in a conflict if this occurs on fields that must have unique entries. In this case, you'll need to re-encode manually using the first solution.

There is a popular solution online that claims you just perform a mysqldump with --default-character-set=latin1 and import with mysql --default-character-set=utf8 but it didn't work for me. It is possible that it might work if you deleted the SET NAMES latin1 line in the dump, but if you're in there you might as well just change it to utf8.

I hope this helps some people out.

Update 2007-05-21: Even after testing the conversion so many times, sometimes it just doesn't work as expected. The last time I did this, the conversion dropped all the text after a special character. So, please test this before using.

96 comments to Mysql database migration and special characters

markus.denhoff.com - Datenbank Migration, May 6th, 2007 at 11:09 pm:

  • […] Wer schon öfter MySQL-Datenbanken migriert hat, wird das ein oder andere Mal auf verrückte Kodierungsprobleme gestoßen sein, wo Sonderzeichen in verschiedenste Zeichen kodiert wurde. Auf orthogonalthought.com findet sich eine Beschreibung wie man Encodingprobleme bei der Migration von Datenbanken vermeiden kann. […]

Jamshid Afshar, May 6th, 2007 at 11:35 pm:

  • I don't know the MySQL particulars, but fyi that "curly quote" character is not part of the "ISO-8859-1" character encoding standard (aka, Latin-1).

    It is in the very common Microsoft "Windows-1252" character set, at position 146. The Windows-1252 (aka ANSI) character set is pretty much ISO-8859-1 plus a few characters (eg, Euro, curly apostrophe) at the "unused" ISO-8859-1 character values. http://en.wikipedia.org/wiki/Windows-1252

    Also, here's a really good article on character encodings / characters sets.

    http://www.joelonsoftware.com/articles/Unicode.html
    The Absolute Minimum Every Software Developer Absolutely, Positively Must Know About Unicode and Character Sets (No Excuses!)

» MySQL-Dumps ohne Kopfschmerzen | Kein Coder, May 6th, 2007 at 11:49 pm:

  • […] orthogonaltought.com zeigt dazu zwei Methoden , wie dieses Problem mit wenigen SQL-Befehlen repariert werden kann. […]

Jordan, May 7th, 2007 at 1:14 am:

  • Typo FYI ("Now option the dump file" should probably be "Now open the dump file"). Good article, and very useful.

Stephanie Booth, May 7th, 2007 at 1:18 am:

  • Thanks, I'll try this out next time I bump into one of my horrid MySQL encoding breakages!

Michael Chu, May 7th, 2007 at 1:19 am:

  • Thanks Jamshid,

    That's a really interesting article. Joel does a good job explaining the subject of character encoding and what to do. It's too bad there isn't a "list of things you need to know before you start fiddling with your own web application". Man, all the stuff I learned while implementing the CMS for Cooking For Engineers came in handy when we started developing Fanpop, but not a day goes by where I don't discover a better way of doing something or wish I had known about some piece of information or method six months ago. Sometimes you're ignorant and don't even know it, then three years later you try to migrate your database and then learn you need to fix your tables because mysql defaults to latin1 while php does it's own thing.

Michael Chu, May 7th, 2007 at 1:21 am:

  • Typo fixed. Thanks Jordan!

Luke, May 7th, 2007 at 1:41 am:

  • I had this task once - not fun, upgrading mysql 3.2 -> 4.1 with utf-8, quite big, many databases. I tried everything that was suggested on the mysql docs….nothing worked properly.

    Eventually I created an empty shell replica of each of the databases, setting all the tables up - empty - but instead of latin1 - I changed it to utf-8 (I think I also calculated the length of each table was going to grow with the char set change and made the tables long enough to cope).

    We were running coldfusion at the time, but this should work with any J2EE server, connected up mysql 3.2 to one connection, and mysql 4.1 to the other, and my boss wrote a bit of code to dump one DB into the other. Pretty soon we copied all the tables, and in turn each of the databases relatively quickly,

MySQL und die Zeichensätze :: Exanto, May 7th, 2007 at 3:39 am:

  • […] diesem Thema hat Orthogonal Thought eine kleine Hilfe veröffentlicht, die hoffentlich dem ein oder anderen Zeichensatz-geplagten Admin einige […]

Jolyon Ralph, May 7th, 2007 at 4:01 am:

  • I have hit this problem many times, and have used variants of the solutions listed at times. But I have found by far the easiest way to migrate a mysql database is simply to stop the database server on the source, archive up the entire database folder from the mysql data folder, copy that to the new server, unarchive, restart mysql and do a CHECK TABLES (and REPAIR TABLES if necessary). Sometimes indexes need to be rebuilt - but in general if things were shut down cleanly to begin with it won't result in any problems.

    I've only tried this with MyISAM tables - it's up to you to make sure this is safe with InnoDB, and obviously migrating to an older version of MySQL is probably not going to work for you using this route.

    Jolyon

vanto, May 7th, 2007 at 4:18 am:

  • I solved it like this if i remember it right
    mysqldump –default-character-set=latin1 –compat=mysql40 db > dump.sql
    mysql –default-character-set=cp1250 newdb

vanto, May 7th, 2007 at 4:25 am:

  • Looks like this wordpress does not handle &lt very well, it cut off my previous post, there was &lt dump.sql at the end of 2nd line.

    That latin1 can be binary, it's there so mysql does not try to do any conversion of the data in db. cp1250 is the real charset of data you had in the db so mysql does not use the new default which is utf8 in my case.

Jürgen Hauser, May 7th, 2007 at 5:04 am:

  • Alternatively, you can use Navicat, which will handle all those issues for you ;)

Clayton Smith, May 7th, 2007 at 6:04 am:

  • One problem I experienced recently was that the kernel wasn't compiled with UTF8 support… so, if your MySQL tables/columns are UTF8 and you see question marks or your data is truncated by certain characters then check your kernel config.

MySQL Database Migration and Special Characters | position: absolute, May 7th, 2007 at 6:46 am:

  • […] heads in acknowledgment right now. Well then, it's your lucky day: Michael Chu has written an article explaining why that happens, what you can do to fix it, and how you can prevent it in the future. […]

AJS, May 7th, 2007 at 6:55 am:

  • Or, you could just modify your application to reject anything that isn't plain old 7-bit ASCII …..

    In my experience, you can copy the .MYD and .MYI files to another server even if it's running a different MySQL version. You probably can copy the .frm file too, but it didn't work when I tried it — I most probably got the directory structure or the permissions wrong. So we shall get MySQL to generate the directory and .frm file for us. First, do a mysqldump with -d to get just the table structure, no data. Then run that on the new host to create the database and table with no data in. Stop the mysql daemon (/etc/init.d/mysql stop) on the old machine (not required if you know for sure that the database won't be altered in the meantime) and copy the .MYD and .MYI files somewhere safe. Restart the mysql daemon (/etc/init.d/mysql start). Stop the mysql daemon on the new machine before copying over the .MYD and .MYI files. Then restart it, and let the automatic integrity check deal with everything.

    BTW, if you can't get a shell on a machine, do this. Create a file in your cgi-bin directory with the first few lines as follows:

    #!/bin/sh
    echo -e "Content-type: text/plain\n"

    shell commands
    more shell commands

    Make it executable and call it up in your browser. The first line says "The correct program to interpret this script is the unix shell". The second line sets the MIME type to be text/plain and throws a blank line to mark the end of the headers. Put any shell commands you like after that. env may be useful. Note that unless your hosting company has enabled suexec, chances are your scripts will be running as some unprivileged user e.g. www-data or apache and not you. Remove the script immediately it has served its purpose (or disable it by making it non-executable).

jDavid.net “@” gmail.com, May 7th, 2007 at 8:43 am:

  • […] complex operations that effect our throughput speed on processing iCals, and this was one of them. The special character bug which might have to do with MySQL acting as Google Calendar's backend, this article might offer a clue as to that problem and others that we might be able to […]

Alexandre Racine, May 7th, 2007 at 9:21 am:

Davide Salerno, May 7th, 2007 at 1:45 pm:

  • Migrazione di un database MySQL e caratteri speciali

    Chi si è cimentato qualche volta nella migrazione di un database MySQL da un macchina ad un'altra si sarà spesso imbattuto in qualche problema con i caratteri speciali. Il problema si manifesta quasi sistematicamente all'interno del testo…

Backups de mySQL y acentos | Jompeich d'er Bisente, May 7th, 2007 at 9:40 pm:

  • […] Slashdot hay una historia sobre migraciones de mySQL y cómo mantener los caracteres especiales como acentos, eñes, etc. A todos nos ha pasado alguna vez tener que restaurar una BD desde un […]

Saumendra Swain, May 7th, 2007 at 9:52 pm:

  • As Vento Said, I too had solved it with the following :

    mysqldump –default-character-set=latin1 –compat=mysql40 db > dump.sql
    mysql –default-character-set=cp1250 newdb

    Anyway Thans for such a good article.

Exportar una base de datos Mysql sin simbolitos raros | La brujula verde, May 8th, 2007 at 4:29 am:

  • […] Todos los detalles en Mysql database migration and special characters […]

John, May 8th, 2007 at 7:17 am:

  • Sheesh, set your database to UTF-8.

Michael Chu, May 8th, 2007 at 9:53 am:

  • Right, but you'll have to set your database to UTF-8 BEFORE you put data into it. If you started on a version of mysql which doesn't support UTF-8 or didn't know any better three years ago, then setting it to UTF-8 now won't work properly. You'll need to use technique one to avoid latin1 to UTF-8 conversion of data you already have in UTF-8.

Skylog » Blog Archive » links for 2007-05-09, May 8th, 2007 at 11:20 pm:

  • […] Mysql database migration and special characters (tags: mysql) […]

links for 2007-05-11 at Wired Gecko, May 10th, 2007 at 10:26 pm:

  • […] Mysql database migration and special characters | Orthogonal Thought The funny special characters drive me a little nutty (tags: mysql database encoding migration howto tutorial hack export webdev) […]

Pythian Group Blog » Log Buffer #44: a Carnival of the Vanities for DBAs, May 11th, 2007 at 9:54 am:

  • […] is the much-read (thanks in part to good old Slashdot) piece on Orthogonal Thought dealing with MySQL database migration and special characters. "…(The) problem arises from the fact that mysqldump uses utf8 for character encoding […]

BaroqueW, May 13th, 2007 at 11:26 am:

  • Nice article, I wish I had this article in hand when migrating my SQL databases… nevermind, i will keep it in hand for the next occasion!
    Thanks, keep up the good work!

Chris' Blog, May 17th, 2007 at 4:54 am:

  • Converting MySQL data to UTF-8

    I decided to convert my blog to use UTF-8 rather than Latin-1 as its character set, this turned out to be somewhat of a PITA. After changing all the table and database character sets manually I had to convert the data. Eventually settling for this litt…

MySQL encoding « François Schiettecatte’s Blog, May 18th, 2007 at 10:56 am:

  • […] I have run into the MySQL encoding issue detailed in this post, basically a transcoding issue when going from latin-1 to […]

Vikram, June 20th, 2007 at 2:48 am:

  • Thanks a ton! This saved me many hours!

Adam, June 22nd, 2007 at 9:18 am:

  • Thank you!

Delicious links at Daydreamer-Stargazer, July 6th, 2007 at 8:22 pm:

  • […] Mysql database migration and special characters | Orthogonal Thought - utf8 […]

Kathi, July 13th, 2007 at 11:13 am:

  • I am converting an exising database (MySql 5.0.1) from latin1 to utf8. Have you ever encountered a problem when dumping the data back where the index was too long? The error I get is:
    ERROR 1071 (42000) at line 77: Specified key was too long; max key length is 999 bytes
    If so, any suggestions? I even tried setting that particular column to latin1, but the syntax (which I followed from the MySql site) fails. I understand it is an issue with MySql utf8 indexes needing to be

Michael Chu, July 15th, 2007 at 11:54 pm:

Gary, August 5th, 2007 at 11:40 am:

  • Thank you for the solution - worked great!

Technik, Gothic und Anderes » Blog Archive » Unicode-Hölle: PHP, Apache, Mysql und Symfony in UTF-8, August 7th, 2007 at 2:00 pm:

  • […] ähnliches, hässliches Problem bei der Datenbankmigration wird bei Orthogonal Thought beschrieben. Teile und geniesse: Diese Icons verzweigen auf soziale Netzwerke bei denen Nutzer […]

James, August 13th, 2007 at 2:55 pm:

  • Thank you! The ALTER TABLE solution worked perfectly for my problem with Wordpress. Saved a lot of time. I love you.

jdfh, August 14th, 2007 at 3:09 pm:

  • Big thanx to webmaster!e

Adrian, August 31st, 2007 at 4:09 am:

  • I tried the ALTER solution, it works fine until I am converting back from VARBINARY to VARCHAR… after changing the column back to VARCHAR, I am getting question marks instead of the special chars…

Michael Chu, August 31st, 2007 at 10:46 am:

  • Adrian,

    That's a problem that can arise - which is why I recommended testing the methods out first. Looks like you'll have to use one of the other techniques to convert the data.

links for 2007-10-18 « Caiwangqin’s delicious bog, October 18th, 2007 at 4:22 pm:

  • […] Mysql database migration and special characters | Orthogonal Thought mysqldump -u mysql_username -p –default-character-set=latin1 example_db > example_db.mysql (tags: mysql migration charset) […]

Nadeem, October 19th, 2007 at 12:43 am:

  • Thats great
    It solved my problem…( I used the first method )
    Earlier i have been manually finding the special characters and removing them.

Adrian, October 19th, 2007 at 6:24 am:

  • Another interesting way to fix this is to do this:

    1. open the MYSQL MIGRATION TOOLKIT;
    2. choose mySQL db type, source and destination databases;
    3. when it shows the screen to choose "Object mapping" leave migration method to mySQL Default, paramter LATIN 1 for the source and Data consistency for the destination;
    4. on the "Object creation options" uncheck "Creates objects online" and check "Create script file for create statements" - then choose a location on your disk to save it;
    5. on the "Standard options - Bulk transfer settings" do the same;
    6. then open with NOTEPAD both script file and run it using a mySQL GUI using copy + paste - I use SQLyog; note - don't open with notepad in VIEW MODE (F3 in total commander) cause it will not work; open for edit with NOTEPAD.
    7. it will work just fine, at least for me works fine.

    I don't know what will happen when my database will grow too much… I am not sure how the copy paste function will work then :)

Jack, October 25th, 2007 at 12:20 pm:

  • Your post saved us a lot of work. I probably would have discovered the same thing after…after about 3 days of hair pulling. :)

    Just to add my $.02 cents to your $3000 solution, where you say:
    "Now open the dump file (example_db.mysql in this example) and edit it. There will be a line that reads like:
    /*!40101 SET NAMES latin1 */;
    Change latin1 to utf8:
    /*!40101 SET NAMES utf8 */;"

    If you have a simple backup from file from phpMyAdmin, simply enter:
    SET NAMES utf8;
    prior to the first table definition. SET NAMES is a standard SQL directive.

    Thanks for sharing your nightmare. I'm sure it has translated in many more nights of sleep for more people than you will ever know.

Waldo Jaquith, November 16th, 2007 at 8:46 pm:

  • This was enormously helpful. Thank you for providing this guide.

the rasx() context » Blog Archive » Today’s Development of the Server Side, November 21st, 2007 at 11:24 am:

  • […] The article tries to tackle a subject that I still have yet to master. There are so many variables of configuration the factorial combinations distract me… […]

El blog de Neurotic » Blog Archive » Configuración de Apache para utilizar Drupal en multisite., November 23rd, 2007 at 2:37 am:

  • […] Ojo: Si tenéis problemas con la codificación de caracteres al exportar-importar (maldito latin1), echadle un vistazo a esta página. […]

Idetrorce, December 15th, 2007 at 4:15 am:

  • very interesting, but I don't agree with you
    Idetrorce

Michael Chu, December 17th, 2007 at 6:11 pm:

  • Idetrorce,

    Not sure what you are disagreeing with… at first I thought you were a spammer, but you didn't put a link in or anything…

    Michael

Tim, December 18th, 2007 at 3:40 pm:

  • Thanks bro! Real good work!t

alex, December 28th, 2007 at 12:03 pm:

  • wow nice!l

El blog de Neurotic » Blog Archive » Codificación de caracteres en MySQL, January 10th, 2008 at 2:23 am:

  • […] en la propia instalación, en una base de datos… hasta en una columna de una tabla. En esta entrada del blog Orthogonal Thought hay un estupendo análisis de la codificación de caracteres orientada […]

JT, January 14th, 2008 at 9:03 am:

  • Thanks for taking the time to write this. I was pulling my hair out with the same problem and your solution (#2) worked perfectly.

ricko, January 15th, 2008 at 3:56 am:

  • This+is+a+great+tutorial+thanks%21r

BaroqueW and his sidekick nikkitaa » Hébergeurs et migrations - 1&1, Hosteur et MavenHosting, February 20th, 2008 at 8:46 am:

  • […] apparaissent (accents étranges, caractères fantômes etc.). Pour éviter ça, lisez cet article (en anglais). Tags: galerie, hébergeur, SPIP, […]

Cazare Brasov, March 25th, 2008 at 1:43 pm:

  • I have a similar problem with a my MySQL server. Whenever I create a field withouth specifing the tipe it automaticly sets to latin1_swedish_ci

    Is there any way I can set the default?

Amy, April 19th, 2008 at 5:24 pm:

  • Thank you so much, your solution (#1) helped me immensely.

Alfonso, April 21st, 2008 at 2:16 pm:

  • ditto here… changed to utf8 column solved the data problem

Denis, May 20th, 2008 at 12:56 pm:

  • I found this to be fairly reliable (on linux):
    mysqldump –default-character-set=utf8 (tables) > table.sql
    iconv -f ISO-8859-1 -t UTF-8 table.sql -o iso_dump.sql
    mv -f iso_dump.sql table.sql

    then you simply mysql < table.sql

Scott A., August 26th, 2008 at 2:24 pm:

  • Huge thanks for this post a year later. And to think, I was just getting ready to break out the regexes!

Naveed Ahmad, November 7th, 2008 at 5:40 am:

  • thank mic, you helped me solve my problem

    -Navi

Risolvere i problemi di codifica del database di WordPress » Archivi Blog » WordPress Italy, November 27th, 2008 at 6:11 am:

  • […] agire diversamente. Le soluzioni sono diverse: qualcuno consiglia di modificare direttamente il file esportato dal database e di reimportarlo tramite shell, qualcun altro come il sottoscritto ha risolto scegliendo in […]

Ultimate solution to weird UTF character encoding problem | This Is The Maverick Of Blogs, December 17th, 2008 at 2:35 pm:

  • […] There are several ready solutions to this problem like modifying the character set before and after the database restore as described in this article. […]

eins78, December 20th, 2008 at 8:39 am:

  • this solution did not work for me, but pointed me in the rigth direction:
    the import function in mysql allows you to choose which encoding should be used to interpret the import dump. i just choosed "latin1" and everything worked fine.

    thanks

Dave’s Whiteboard » Blog Archive » mySQL: what a character, December 30th, 2008 at 4:42 am:

  • […] that's easy for Michael Chu to […]

Leonardo Fontenelle, February 8th, 2009 at 5:43 am:

  • Thanks, you just saved me!

gfdgdsf, February 8th, 2009 at 1:41 pm:

  • GEEKS NEED TO LERN TO SPEEK NORMAL

Fixing mixed-encoding MySQL dumpfiles with WordPress, May 26th, 2009 at 8:07 pm:

  • […] Mysql database migration and special characters — Orthogonal Thought […]

SecondoMe, July 5th, 2009 at 8:40 am:

  • Thank you!
    Your procedure was very useful and efective!

J vincent, August 19th, 2009 at 2:51 am:

  • I use data loader for migrating almost any data, it helps me to convert MSSQL to MYSQL, MS access to MSSQL, mysql, csv loader, foxpro and MSSQL to MS access, MYSQl, CSV, foxpro etc. In my view this is a best Data Migration Tool

Jeans3167, August 28th, 2009 at 2:34 am:

  • Here is a tool to convert mssql to MYSQL database. That i found on google search it says it can convert almost any database try and tel me is that worth or not.

    Download Free : http://www.convert-db.com/mssql-to-mysql.htm

Miguel, September 6th, 2009 at 1:28 pm:

  • Brilliant!!!

How to prevent weird characters after MySQL export and import | labs.leftcolumn.net, September 25th, 2009 at 7:52 pm:

rouademunte, October 18th, 2009 at 10:56 am:

mysql, soluzione per la migrazione | Nerd - Matteo Cisilino, November 6th, 2009 at 6:28 am:

  • […] qua e la ho trovato in questo sito un'ottima spiegazione sul come migrare applicazioni a MySQL preservando i caratteri speciali, […]

Da latin1_swedish_ci in utf8, November 13th, 2009 at 12:41 pm:

  • […] Mysql database migration and special characters […]

Shumaker212, December 11th, 2009 at 6:05 am:

  • Nice Tool to Migrate database called dbload its a simple tool and still powerful to migrate any database with ease.

    Find Here : http://www.dbload.com

wanghongli, January 11th, 2010 at 6:23 pm:

  • Nice Tool to Migrate database called dbload its a simple tool and still powerful to migrate any database with ease.

Prashant, January 14th, 2010 at 11:39 pm:

  • How would this work on a new blog where there is no import of data? Is it possible to change the character encoding from within wordpress and not have to edit mysql now that we are working on the latest 2.9.1

Ricardo España, March 19th, 2010 at 6:22 am:

  • Great article man, very complete.

Chris, April 8th, 2010 at 7:58 am:

  • Following the very clear steps for converting at the column level proved successful. Thanks so much for the concise article.

    Cheers.

Wordpress Host Migration Weird Character problem [Solved], July 13th, 2010 at 3:46 pm:

  • […] So I started looking for problem and realize that the issue is with database character encoding. You can find more information about this character encoding here. […]

MySQL Migration from Windows to Linux Character issue.. | jieme, August 7th, 2010 at 4:01 pm:

Marian, October 31st, 2010 at 3:30 pm:

  • we have the same problems, tried all possible methods but still it drops the text after some special characters…

Pinyo, November 2nd, 2010 at 7:34 am:

  • Awesome information. You just saved us a lot of work. Thank you.

Olivier, June 28th, 2011 at 6:14 am:

  • Thanks a lot, it helped !

Rico, July 5th, 2011 at 6:55 am:

  • Thank you SO MUCH for this!

    It saves me lots of time, over and over again.

How to configure your web application to correctly deal with character set/encoding issues- Jeremy Tunnell, July 17th, 2011 at 7:47 pm:

  • […] Fixing MYSQL charset issues on existing data. Share/Bookmark […]

Dan Eskildsen, August 29th, 2011 at 1:35 am:

  • Thank you for this post. It helped! I just set my database to utf instead of Latin 1, imported the data and that solved the problem!

Jesse, September 19th, 2011 at 11:35 pm:

  • Another thankful reader, this saved my bacon, thanks!

Anonymous, September 30th, 2011 at 5:14 pm:

  • 1000 thank you

Peter Jukes, November 7th, 2011 at 5:57 pm:

  • Wow. You've just saved my bacon. I went through 24 hours of agony thinking I'd lost large chunks of my website, because the special characters were truncating the import.

    But I selected latin1 instead of utf for import, and all my work is still there.

    Now, I still have my work, albeit with some strange characters. But that's better than nothing. Cheers.

Tim, February 17th, 2012 at 9:13 pm:

  • Beautiful, thanks for this post. It's the only one that's worked for me flawlessly.

Hanan Ali, June 20th, 2012 at 1:57 pm:

  • Thanks for sharing such amazing guidance :)

    If you need to import sql dump add this line at the top of the file

    SET NAMES utf8;

    it will not disturb the special characters.

Niles ife, April 14th, 2013 at 10:09 am:

  • |

All content published by CFE Enterprises, Inc. under license with copyright holders.

NAVIGATION

MY WEB

SEARCH