WordPress.org

Ready to get started?Download WordPress

Forums

Convert database to UTF-8 (39 posts)

  1. mjsteinbaugh
    Member
    Posted 7 years ago #

    Hi,

    I noticed that a guide for converting the WordPress character set is now available on the Codex. However, its instructions are pretty vague and I'm not comfortable following them.

    http://codex.wordpress.org/Converting_Database_Character_Sets

    Has anyone tried this successfully? If so, what were the commands you used to convert the database? I would love it if someone would post a SQL script to achieve this. Such a script should be bundled with the next WordPress install.

  2. Jack
    Member
    Posted 7 years ago #

    I've seen it but didn't try it. Same reason, it all sounds rather vague and I don't wanna make a mistake :).
    I'm also anxious to convert my site from latin1 to utf8!

  3. Jack
    Member
    Posted 7 years ago #

    I don't get it: In my database there are tables reserved for the gallery program Plogger (utf-8), the rest is WordPress, all collated and charset latin1.
    Now I changed the collate/charset in the config.php file to utf-8, imported a backup of this database in a fresh install, changed the 'read option' under options from iso-8859-1 to utf-8 and all my posts seem to be allright.

    Is that possible? I don't get it. These small steps didn't change my database or dit it???

  4. mjsteinbaugh
    Member
    Posted 7 years ago #

    I'm not sure if reimporting your posts does the trick, even on a fresh install. I'm pretty sure you want to convert the text entries to BLOB like the guide says, then convert the charset to UTF-8. However, like I mentioned previously, the guide is very cryptic and likely to be problematic.

  5. andersapt
    Member
    Posted 7 years ago #

    I followed the guide recently, made myself a little PHP script to generate the various SQL commands for me.

    The source is here if anyone wants to use it:
    generate sql for utf8 conversion

    I actually made this as a template that I ran (made an empty page that used the template), worked as a charm. The only thing one need to change is the name of the database in the first foreach loop.

  6. MichaelH
    Member
    Posted 7 years ago #

    Thanks for the script anderapt. I've placed a reference to that script in Converting_Database_Character_Sets and Trac ticket 3517.

  7. g30rg3x
    Member
    Posted 7 years ago #

    Well 6 days ago i release a plugin called "UTF-8 Database Converter" that easy converts the wordpress database, today i release the second version (2.0) which adds certain new logic an a better solution to the problem.
    http://g30rg3x.com/utf8-database-converter/

  8. MrGunn
    Member
    Posted 7 years ago #

    g30rgg3x's plug-in worked for me. I added the appropriate entries to wp-config first.

  9. sfong15
    Member
    Posted 6 years ago #

    Anderapt, thanks for the script. I have a look at yours and the guide in codex, I'm a little confused. Is there any need to convert data type to BLOB first?

    I tried a little script from here (not for WP but easy to convert) which worked fine at my localhost. Before I risk my data/database at hosting can someone clarify the need to convert data type to BLOB and back? This little script from sveit.com doesn't do that step.

  10. g30rg3x
    Member
    Posted 6 years ago #

    Hi sfong15,

    The codex don't mention "why you do this or that" because this topic is a little complex and advance so the users that want to do this normally have a partially knowledge about the problem.

    The need to convert to the data type BLOB is simply, what the codex is trying to say is that you need to convert the data type (string type) to his binary representations, we need to that in order to avoid problems of characters getting lost or garbage-characters, that it will ruin all our data in a simply. This is a safe-step because normally you wouldnt bother, the only chars affected are foreign chars like the "ñ" in spanish languages, this char if is not converted before in its binary representation equivalence it will be lost and sometimes screw all your database, so the risk that you have accepted using the script you provide is that, if you have foreign characters they will be converted to trash during the conversion process...

    Adding just a little more explanation for why you really need to convert all text(string fields) to blob its because -as i previously says- this is a safe step for converting, because setting to binary it will not be affected the data during conversion and after convertion it will be easy to rollback from binary to string types.
    A little list about string-data-type to his binary representations data type is listed next
    char -> binary
    varchar -> varbinary
    tinytext -> tinyblob
    text -> blob
    mediumtext -> mediumblob
    longtext -> longblob
    enum -> set charset to binary
    set -> set charset to binary

    All this fields need to be converted to his correctly representations before convertion, as you can see in the codex thay give the example of converting a text type to blob, but if you need to convert a lets say longtext you will need to change longtext to longblob.

  11. sfong15
    Member
    Posted 6 years ago #

    Thanks g30rg3x,

    Great explanation now I would only use your plugin for my data in the hosting.

  12. sfong15
    Member
    Posted 6 years ago #

    g30rg3x,

    Just tried your great plugin on localhost, it works great. One little question forgive my little knowledge my mysql, I have a quick glance on your code it alters db (line 126) and fields of each table (line 128) to utf8. I couldn't see anything is done to collation of fields of each table?

    When I did my little test on localhost collation was changed to utf8_general_ci? What if someone needs a different collation?

  13. moshu
    Member
    Posted 6 years ago #

    What if someone needs a different collation?
    Why would they?
    The whole mess around upgrades was caused in part because of the earlier mismatch in encoding and collation (+mysql's own bugs).
    That collation should work well for everybody, I guess.

  14. g30rg3x
    Member
    Posted 6 years ago #

    yes as moshu says,
    The "general" (default for utf8) collation should work well for everybody, so i don't wanna to mess around with this because normally if someone really needs a special collation i think they have the knowledge to do the job himself rather than using automated conversion tools, but in the next version of my plugin i will consider that feature (even i know its really pointless considering that i'm just converting WordPress powered sites, not any type of database)

  15. raphzone
    Member
    Posted 6 years ago #

    Justed tried the conversion, and I noticed that some of my plugings stop working properly. I figured out that the problem was in the configuration strings (in the options table). As an example:

    a:4:{s:5:"count";b:0;s:12:"hierarchical";b:1;s:8:"dropdown";b:0;s:5:"title";s:10:"Catégories";}

    You'll notice that the term "Catégories" has an e-acute in it. If you convert to UTF-8, then this will break because the byte count will be wrong (s:10).

    You need to use PhpMyAdmin and change manually the byte count so it reflects the change correctly.
    s:10 would become s:11

    But, I still have a problem though.... my widgets editor isn't working properly. I cannot modify the properties of an active widget. The popup doesn't appear at all.

    Anyone ?

    Thanks.

  16. raphzone
    Member
    Posted 6 years ago #

    Alright... I just deactivated all plugins, and reactivated them one by one, and now everything is okay.

  17. pdelisle
    Member
    Posted 6 years ago #

    andersapt's script URL (http://kunde.apt.no/aso/wordpress/convert_to_utf8_sql_generator.txt) throws a 404 error.

    Can someone post the correct URL or email me the script (philip at de-lisle.co.uk) as I have to move 20+ blogs to a new web host for clients and I don't have access to their WP control panel to be able to use the wp_backup plugin (which works fine when phpmysqladmin doesn't - weird)

    TIA

  18. sbstextvof
    Member
    Posted 6 years ago #

    Has anyone tried to start g30rg3x's plugin (utf8-database-converter) to work with WP2.3?
    I'm a bit carefull because in his readme he states "This plugin has been designed to be only compatible with WordPress versions 2.2.x and 2.1.x so running the plugin on other minor or major versions may have unexpected behavior."

  19. g30rg3x
    Member
    Posted 6 years ago #

    @sbstextvof:
    As stated in the "readme" file and in the warning before the final step, i haven't fully tested under 2.3.x so i don't know what behavior to be expected, but my guesses and of course as the main developer of the plu in i have to say: "Why it shouldn't?", my guesses is that it will work fine.

    But i sure recommend to you (and all) to wait i little time because i started to develop the next version that will have new features, more compatibility across different mysql and php versions and of course a fully support of 2.3.x so i encourage you to wait a little extra time but of course if you need ASAP solution i will suggest to give a try to the current/stable version.

    Greetings From Mexico

  20. DavidRoberts
    Member
    Posted 6 years ago #

    I tried g20rg3x's plugin and received the following error:

    Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/myuname/public_html/wp/wp-content/plugins/UTF8_DB_Converter.php on line 115
    
    Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/myuname/public_html/wp/wp-content/plugins/UTF8_DB_Converter.php on line 115
    
    Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/myuname/public_html/wp/wp-content/plugins/UTF8_DB_Converter.php on line 115

    Odd, three times in a row like that. I'm on WP 2.2x and the first time I tried, this popped up quite quickly. I look around a bit, and tried it again just to see and it went serveral minutes, used mysql memory quite a bit, and then spit out the same error 3 times. I don't think there is anything special about my install, but I've desperately been wanting to convert over to utf-8 so this is just wonderful if it will work for me.

    I'm open for suggestions on debug.

    Thank you!

  21. g30rg3x
    Member
    Posted 6 years ago #

    Hi DavidRoberts...

    That error means that the SQL Query that should be executed on line 114 is not... for consequence the mysql_fecth_assoc() function cant have a valid mysql resource.

    This is related to your mysql user privileges, if your user doesn't have grant access to Show_view_priv the EXPLAIN SQL Statement will does not work properly.
    This is unfortunately a know-bug that is already being work-around in the version 3.0...

    If you need further help about contact me over my email, (is in the read btw)

    Greetings from Mexico

  22. timweber
    Member
    Posted 6 years ago #

    I tried g30rg3x's plugin on a v.2.3.1 site and it seemed to work fine. It complained about the WP version (which I expected) and warned about possible data loss (ditto). When I crossed my fingers and said "Run it anyway!" it quickly threw me back to the version-warning page, so that I didn't it had done anything. So I ran it again! Same thing, so I checked my database and saw that the conversions were indeed made; then checked my blog, and the Cyrillic and accented characters and so on were rendering correctly. Given that it seemed to work, I was surprised at how fast the script ran (I guess I have a small blog).

    I wonder what would have happened if I had hacked the version check to include 2.3, but at this point I'm grateful to be running utf8. Gracias!

  23. alexz
    Member
    Posted 6 years ago #

    Dear, g30rg3x.

    When I made step one for my fresh 2.2 upgrade, wrote define('DB_CHARSET', 'utf8'); in wp-config.

    I get a nasty error:

    "
    Warning: Invalid argument supplied for foreach() in /home/***/public_html/wp-includes/capabilities.php on line 31

    Warning: Cannot modify header information - headers already sent by (output started at /home/***/public_html/wp-includes/capabilities.php:31) in /home/iskall/public_html/wp-includes/functions.php on line 1348
    WordPress

    You do not have sufficient permissions to access this page."

    I actually got the same errormessage when I tried to convert directly with your plugin under 2.1.

    Thank you.

  24. g30rg3x
    Member
    Posted 6 years ago #

    Hi alexz,

    I think that problem is directly related to your recently update/fresh WordPress 2.2 and your server.
    You should see
    http://wordpress.org/support/topic/122367
    It maybe help you....

    Also read this entry...
    http://codex.wordpress.org/Upgrading_WordPress

    Greetings from Mexico

  25. alexz
    Member
    Posted 6 years ago #

    Thanks for your fast answer g30rg3x.

    If I set magic_quotes_runtime in my .htaccess file I only get Internal Server Error when I try to get into the wp-admin. No error log is created.

    My older install of 2.1 got the same error when I only converted my database with your plugin. I had to ask my host to upload the backup again (because phpmyadmin failed to upload my own sql backup).

    So I tried to upgrade to 2.2 and followed your readme file, as I said. I only have to write define('DB_CHARSET', 'utf8'); to create this error.

  26. g30rg3x
    Member
    Posted 6 years ago #

    Hi Alexz,

    Unfortunately this is still a server side problem, so m sorry to tell you that there is really nothing i can do (as the plugin main developer).
    You should check that you upload all your files correctly and that you have and a supported php and mysql version for WordPress also i totally suggest you to drop version 2.2 and start using 2.3...

    Greetings from Mexico

  27. alexz
    Member
    Posted 6 years ago #

    Well, I then drop 2.2 and update to 2.3 and wait for your plugin to be 2.3 compatible.

    Thanks for your help!

  28. LucasES
    Member
    Posted 6 years ago #

    I just tried g30rg3X's plugin in WP 2.3.2 and it worked fine for me.
    Just to let you know!

  29. zern
    Member
    Posted 6 years ago #

    Nope - it did not work for me on 2.3.2

    The script reported Done. But it truncated every post after the first 60(?) words or so.

    Lucky I had a backup!

  30. benzilla
    Member
    Posted 6 years ago #

    I got the same deal as zern got trying to use this plugin to convert a 2.3.3 WP: all the old posts truncated at 60 words or so. Oh well...

Topic Closed

This topic has been closed to new replies.

About this Topic