WordPress MySQL Database Encoding Fun

I’ve finished moving thesamet.com to a new web host. The most tedious thing to do was to move the WordPress SQL database to the new server.

It turns out that the database was created with latin1 character set while WordPress pushed utf8 encoded data to it. For those who got stuck in the same problem, here is what I did to transfer the data to a new server.

First, get a sql dump of your database. Use the encoding of the database when doing it (i.e. latin1). Here is how to do it from the command line:

$ mysqldump –default-character-set=latin1 -u db_user -p db_name > mydb.sql

After inspecting the dump, it turned out that this file could be considered as UTF8 encoded. this was to be expected as we had UTF8 data in the database considered as latin1 and we dumped it as latin1.

Python is known to have great support for handling Unicode, and is very comfortable to inspect strings with it. The next thing I did was to load the file with Python, and tried to decode the data as utf8.

>>> s = open(‘mydb.sql’).read()
>>> s.decode(‘utf8’)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib/python2.5/encodings/utf_8.py", line 16, in decode
    return codecs.utf_8_decode(input, errors, True)
UnicodeDecodeError: utf8 codec can’t decode byte \x92 in position 561129: unexpected code byte</stdin>

Great, so the first trouble comes at index 561129. Let’s see what’s in there:

>>> s[561110:561136]
‘ points of view, it\x92s good’

That \x92 looks suspicious. It turns out that this one is still encoded in latin1, and \x92 in unicode is some kind of ‘ (slanted single-quote or something). To translate it to UTF8, I did:

>>> \x92′.decode(‘latin1’).encode(‘utf8’)
\xc2\x92′

Now changing all occurrences of \x92 to \xc2\x92, can change it in places we would not want. So before we do that, we check what the other occurrences of \x92 looks like:

>>> [s[i-5:i+5] for i,ch in enumerate(s) if ch==\x92′]
[\xd7\x9c\xd7\x95\xd7\x92… ‘, ‘w, it\x92s go’]

We see that the first occurrence of \x92 should not be changed, it is within a UTF8 string. So we are safe to do

>>> s=s.replace(‘it\x92s’, ‘it\xc2\x92s’)
>>> u8=s.decode(‘utf8’)

And this time the decoding succeeded. This means that our data contains valid UTF8. My case was easy, but you might have to repeat the above process several times until you get rid of all latin1 characters in your data. It might be possible that you’ll have latin1 data that looks like utf8, and you’ll need to do even more manual search/replace.

Now write the utf8-encoded string (s, not u8) to a new file:

>>> open(‘/tmp/out.sql’, ‘w’).write(s)

After that, I’ve changed all the CREATE statements in the SQL file from CHARSET=latin1 to CHARSET=latin1.

It is important to add SET NAMES=utf8; at the top of the file.

On the destination server I created the database with UTF8 default encoding:

CREATE DATABASE db_name DEFAULT CHARSET utf8;

and imported the new SQL file into the database:

mysql –default-character-set=utf8 -u db_user -p db_name < /tmp/out.sql

and voila, it works.

This entry was posted in daily. Bookmark the permalink.

32 Responses to WordPress MySQL Database Encoding Fun

  1. Rashad says:

    Expect that the psychic will make the decisions for you.
    It is a gift and ought to be used properly and only for the good, so usually use
    your present in a positive way. Keep that in mind if your feelings
    are harm.

    my website :: online psychic (Rashad)

  2. If you aren’t home much, have children under the age of ten,
    don’t enjoy chaos, or have a busy lifestyle which would
    prevent a ferret from roaming freely, then these pet are not for you.

    Not only that, but what happens is what is supposed to happen for that ferret.
    Spa Presents – Take care of her to a day at a beauty spa exactly where she can loosen up and rejuvenate.

  3. Pingback: Python:Compile Syntax Error: non ASCII letters in a string – IT Sprite

  4. benidorm says:

    Wonderful beat ! I would like to apprentice while you amend your website, how
    can i subscribe for a weblog website? The account aided me a appropriate deal.

    I have been a little bit familiar of this your broadcast offered shiny
    transparent idea

  5. discos ssd says:

    he estado navegando en línea más de 2 horas hoy, pero nunca encontré ningún artículo interesante como el tuyo.
    Es Vale bastante lo suficiente para mí. En mi opinión, si todos propietarios
    de sitios y bloggers hacen un buen contenido como tú,
    Hey ¿Te importaría decirme qué hosting company estás trabajando con ?
    He cargado tu blog en 3 completamente diferentes navegadores y debo decir que este blog carga mucho más rápido
    que la mayoría. ¿Puede sugerir un buen proveedor de
    hospedaje de Internet a un precio razonable ?

    Felicitaciones , ¡lo aprecio!
    Me encanta cuando personas se reúnan y compartan pensamientos .
    Estupendo sitio, continúen ! mantener el fantástico trabajo !

  6. Wang Zirui led Xinjiang with 22.As much as 1,700 millimeters of rain was recorded at one weather station in the week to Monday. He also said that lithium batteries were environmentally friendly. The most effective plan for achieving it’s to get boxes of the same size if possible. TIANJIN,saucony shadow uomo offerte, Sept.Authorities were releasing water from seven reservoirs in line with a plan to limit flooding,scarpe saucony offerta, the disaster committee said.By Jon Day, Zhu Chao, Liu

  7. The best CBD oil is all-pure and organic.

  8. webcasting says:

    Please let me know if you’re looking for a article writer for your site.

    You have some really great posts and I believe I would be a good asset.
    If you ever want to take some of the load off, I’d absolutely love to write some articles for your blog
    in exchange for a link back to mine. Please shoot me an email if interested.
    Many thanks!

  9. An impressive share! I have just forwarded this onto a colleague who was doing a little homework on this.
    And he in fact ordered me breakfast because I found
    it for him… lol. So let me reword this…. Thanks for the meal!!
    But yeah, thanks for spending time to talk about this topic here on your
    site.

  10. Thanks for a marvelous posting! I certainly enjoyed reading it, you’re a great author.I will ensure that I bookmark your blog and definitely will come back later on. I want to encourage you to ultimately continue your great writing, have a nice afternoon!

  11. Do you mind if I quote a few of your posts as long
    as I provide credit and sources back to your blog? My blog is in the very same niche as yours and my users would genuinely benefit
    from some of the information you present here.
    Please let me know if this ok with you. Cheers!

  12. anal says:

    Oh my goodness! Amazing article dude! Thanks, However I am going through problems with your RSS.
    I don’t understand the reason why I can’t subscribe to
    it. Is there anybody having the same RSS problems?

    Anyone that knows the answer can you kindly respond?

    Thanks!!

  13. Excellent items from you, man. I’ve take into accout your stuff previous to and you’re simply too excellent.
    I actually like what you have received right here, certainly like what you are
    stating and the best way during which you assert it. You make it entertaining and you continue to take care of to stay it smart.
    I can’t wait to read far more from you. This is really a great web site.

  14. I as well as my pals have been reviewing the nice
    suggestions found on your web blog and so all of a sudden I got a horrible suspicion I never thanked the web site owner for those tips.

    My ladies became consequently glad to see all of them and have
    now really been taking advantage of these
    things. Many thanks for indeed being simply kind and then for
    picking certain excellent ideas millions of individuals are really
    needing to be aware of. Our sincere regret for not expressing gratitude to you sooner.

  15. Ich bin gerade zufaellig auf Ihrer Webpage gelandet (war eigentlich auf der Suche nach einer anderen Seite).
    Ich moechte diese websiete nicht verlassen, ohne Ihnen ein Lob zu dieser gut strukturierten und schick designten Page zu hinterlassen!

  16. Marcelo says:

    Super wpis, generalnie to ma sens, jednak w kilku aspektach bym się kłóciła.
    Z pewnością ten blog zasługuje na uznanie.
    Z pewnością tu jeszcze wpadnę.

  17. It’s amazing to pay a quick visit this site and reading the views of all mates on the
    topic of this post, while I am also keen of getting experience.

  18. Pculiar article, exactly what I was looking for.

  19. I blog frequently and I truly appreciate your information.
    The articcle has truly peaked my interest.
    I will take a note oof your website and keep checking
    for new information abouit once per week. I opted
    in for your RSS feed too.

  20. 主播 says:

    Just want to say your article is as astounding. The clarity in your post is just great
    and i can assume you are an expert on this subject. Well with
    your permission let me to grab your RSS feed to keep up to date with forthcoming post.
    Thanks a million and please continue the gratifying
    work.

  21. milano new says:

    At this time I am goiing to do my breakfast, when having my breakfast coming over again to read additional news.

  22. Wһy users ѕtill make uѕe of to read nws paperѕ when inn this technological globe alll іs acceѕsible
    on net?

  23. 홀덤추천 says:

    Thank you a bunch for sharing this with all people you really realize what you’re speaking
    approximately! Bookmarked. Please additionally talk over
    with my web site =). We can have a link trade arrangement between us

  24. What’s up to every one, for the reason that I am truly eager of reading this web
    site’s post to be updated regularly. It carries
    good stuff.

  25. blog says:

    An outstanding share! I’ve just forwarded this onto a friend who was
    conducting a little research on this. And he in fact ordered me lunch because I discovered it for him…

    lol. So allow me to reword this…. Thank YOU for the meal!!
    But yeah, thanks for spending time to discuss
    this topic here on your web site.

  26. Phillip says:

    Pretty section of content.I just stumbled upon your
    web site and in accession capital to assert
    thst I get actually enjoyed account your blog posts.
    Anyway I will be subscribing to your feeds annd even I achievement
    you access consistentlpy rapidly.

  27. Linnie says:

    You ought to take part in a contest for one of the greatest sites online.

    I will highly recommend this site!

  28. That is a great tip particularly to those fresh to the blogosphere.
    Simple but very accurate information? Appreciate your sharing this one.
    A must read article!

  29. What’s up, everything is going well here and ofcourse every one is sharing facts, that’s really excellent, keep up writing.

  30. Very efficiently written information. It will be supportive to
    anybody who utilizes it, as well as me. Keep doing what you
    are doing – can’r wait to read more posts.

  31. I was looking at some of your articles on this internet site and I
    believe this site is rattling instructive! Retain putting up.

  32. Yeah bookmaking this wasn’t a high risk determination great post!

Leave a Reply

Your email address will not be published. Required fields are marked *