how to Convert Database Character Sets to utf-8

  • Resolved ejm

    (@llizard)


    14 years, 7 months ago

    Please excuse as some of this is a direct repeat of my reply in the thread “Upgrading from Legacy 2.0.11 to 2.8.x”. Please note that I have searched this forum as best I can to find the answer to my question.

    I am running two wordpresses. One was upgraded sometime ago from Legacy 2.0.11 to 2.7 (and recently upgraded to 2.8.4). The second wordpress, that I have just recently upgraded from 2.0.11 (which was stable and officially supported until very recently) to 2.8.4 was upgraded in one fell swoop going directly from 2.0.11 to 2.8.4 by following the steps in the codex. There appeared to be only a few glitches that were quickly ironed out by reading these forums and I thought I was back on track. Until I noticed several stray Âs in some of the older posts. And ûs instead of üs, és instead of és, âs instead of âs, etc. etc.

    I looked in SQL and for the 1st wordpress, all the database tables have latin1_swedish_ci collation labels. In the most recently upgraded wordpress, I have two different character sets for the tables in the wordpress database: latin1_swedish_ci collation and utf-8

    As a result of this giant glitch, I have stared at all of the codex “Converting Database Character Sets” at http://codex.wordpress.org/Converting_Database_Character_Sets

    The very beginning gives me pause!

    Warning: character set conversion is not a simple process.

    And they’re not just whistling Dixie. Alas, I’m stuck on the very first step:

    Place notice that blog is out of service

    How do I put the blog out of service? Is there some place in the wordpress dashboard that I can check to say “maintenance mode”? Or must I upload a special index page saying that it is unavailable?

    In the thread entitled “Apostrophes and other special characters showing strange code after upgrade” noyz319 wrote the following:

    In the codex it says:

    Warning for those performing upgrades: If DB_CHARSET and DB_COLLATE do not exist in your wp-config.php file, DO NOT add either definition to your wp-config.php file unless you read and understand Converting Database Character Sets. Adding DB_CHARSET and DB_COLLATE to the wp-config.php file, for an existing blog, can cause problems — as Ryan Boren says, “your queries will go boom!”

    – http://codex.wordpress.org/Editing_wp-config.php

    I wish this info was included alongside the wordpress upgrade instructions. But is there a solution for people who have accidently done this? It seems to have done some sort of change/damage where just removing the lines of code are not providing a cure.

    I see that there was a plugin created for this. Sadly, the utf-8-database-converter plugin is only compatible up to version 2.2

    Does anyone have a babystep-by-babystep walkthrough for converting the database character sets? Thank you for any spoon-feeding you can supply.

    EMorris etherwork.net/blog (2.8.4, WindowsXP, firefox3.0.14)

Viewing 12 replies - 1 through 12 (of 12 total)
  • Samuel B

    (@samboll)

    14 years, 7 months ago

    Place notice that blog is out of service

    http://wordpress.org/extend/plugins/maintenance-mode/

    not sure about the rest, though
    http://wordpress.org/extend/plugins/utf-8-database-converter/
    at this point would it hurt to try this plugin?

    Thread Starter ejm

    (@llizard)

    14 years, 7 months ago

    Thank you for your reply, samboll. At this point, I’m not completely comfortable with using plugins that are not compatible with 2.8.4. Both wordpresses are at this point, for the most part, readable and I worry about blindly using the plugins in case something really goes bad.

    I have not yet had to restore a wordpress from a backup and REALLY don’t want to have to do that.

    Thread Starter ejm

    (@llizard)

    14 years, 7 months ago

    I have to confess that I’m terrified of making a mistake. (I KNEW there was a reason I didn’t want to upgrade from 2.0.11!!) The only thing I know about PHPmyadmin is how to open it up and collect the backups.

    The following is an excerpt from http://codex.wordpress.org/Converting_Database_Character_Sets

    1. Place notice that blog is out of service
    2. Backup database
    3. ALTER TABLE wp_users MODIFY display_name BLOB;
    4. …ALTER TABLE commands for all other tables/columns…
    5. ALTER DATABASE wordpress charset=utf8;
    6. ALTER TABLE wp_users charset=utf8;
    7. …ALTER TABLE command for all other tables…
    8. ALTER TABLE wp_users MODIFY display_name TEXT CHARACTER SET utf8;
    9. …ALTER TABLE for all other tables/columns…
    10. Add DB_CHARSET and DB_COLLATE definitions to wp-config.php
    11. Place blog back on-line

    Please excuse me for having to have this absolutely spelled out. With regards to #4, does this mean that every table in the wordpress database should follow the same format? Along the following lines:

    ALTER TABLE wp_comments MODIFY display_name BLOB;
    ALTER TABLE wp_links MODIFY display_name BLOB;

    Do I do these commands one at a time? Or can I do them all at once in the sql command window?

    and after ALL the wp-tables have been modified to “display_name BLOB” do I then follow step #5?

    Thread Starter ejm

    (@llizard)

    14 years, 7 months ago

    Because I couldn’t seem to wrap my mind around how to follow the steps at wp codex Converting_Database_Character_Sets and encouraged by the thread “Convert database to UTF-8“, I threw caution to the wind and decide to try running http://wordpress.org/extend/plugins/utf-8-database-converter/

    First, I installed ‘Search & Replace’ Plugin and made an attempt to find all the instances of extended characters and replace them with their character entities.

    I should have paid even closer attention to the following in the thread entitled “[Plugin: UTF-8 Database Converter] All entries chopped“

    it appears that [UTF-8 Database Converter plugin] truncates the posts at the first non-latin character.

    Alas, I didn’t get all the extended characters before running UTF-8 Database Converter. Quel disaster!!

    Luckily, I HAD made a backup. And thank goodness for this tutorial on how to restore using phpMyAdmin:

    http://tamba2.org.uk/wordpress/restore/

    (The wp-codex restore tutorial is just as incomprehensible as the one on how to convert database character sets.)

    I have now restored my wordpress to pretty much what it was before ( except now I have some double letters on the sidebar… û instead of ü and é instead of é). And alas, I still have a mix of latin1-swedish-ci and utf-8 in the tables. Sigh.

    Please excuse my shouting but IF YOU HAVE ANY ACCENTED LETTERS OR EXTENDED CHARACTERS ON YOUR WORDPRESS, DO NOT USE utf-8-database-converter PLUGIN!!!

    jennsoer

    (@jennsoer)

    14 years, 7 months ago

    I am having the same problem. Just updated wordpress to the most recent. Check here:

    http://little-hypotenuse.com/rortymacintyr/

    Man. Nothing is ever simple, huh? I remember having had this problem in the past and having had solved it by doing something with the language thing in phpAdmin. But as someone else had told me what to do and I blindly followed, I have no real memory of it! Any ideas would be grand.

    Thanks
    Jenn

    Thread Starter ejm

    (@llizard)

    14 years, 7 months ago

    You might give this a try:

    http://www.mydigitallife.info/2007/06/23/how-to-convert-character-set-and-collation-of-wordpress-database/

    I haven’t yet tried using those instructions yet though; the only glitch I’ve run into is not knowing how to find out if fields belong to indexes or FULLTEXT indexes. (My question about that is here.)

    If switching the collation to utf-8 doesn’t work to get rid of all the strange letters, you might try using the Search and Replace Plugin. (At this point, on your wordpress, you would use “ to replace “ and ” and ‘ to replace ’.)

    Thread Starter ejm

    (@llizard)

    14 years, 7 months ago

    Okay. I finally got my wordpress tables to be altered from latin1 to utf-8. I followed the instructions here:

    http://www.mydigitallife.info/2007/06/23/how-to-convert-character-set-and-collation-of-wordpress-database/

    However, I did run into difficulties because my sql version is at 4.1 and I was getting error messages

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax

    Having no idea where the manual was OR how to read it if I found it, I asked the folks at codingforums.com and one kind soul answered my pathetic pleas for help. It turns out that I had to include the “type” beside the name – something like VARCHAR(100) or tinytext or…

    I looked in my backup files to find out what each of the types were for all the various tables. (Yes, it was rather labour intensive. There MUST be a better way.)

    Thank goodness it’s done (I hope). No explosions yet….

    Thread Starter ejm

    (@llizard)

    14 years, 7 months ago

    Apparently, there IS an easier way to learn what the types are. If the following coding is put into the sql window

    SHOW COLUMNS FROM databasename;

    the type(s) will be displayed.

    Cool.

    teddyen

    (@teddyen)

    14 years, 6 months ago

    I have the same problem. I updated from 1.x.x to 2.8.4 and was left with post containing lots of Ã¥s and øs… Nobody told me to leave out the DB_CHARSET and DB_COLLATE in wp-config.php.

    I don’t have time or will to do this manually. I have done some php and a lot of html in the past, but trying to get into SQL and getting through a 50-step guide to get this working is not something I even want to start on reading on.

    This seems to be a major problem for many people, I can’t believe that there isn’t a script that can do this easily for us. Have anyone found any “easy” way to do this?

    sebastianbay

    (@sebastianbay)

    14 years ago

    I have run the UTF-8 Database Converter Plugin on WordPress 2.9.2. I don’t know my previous version any more but it was very old. Before the first datebase update. I had written all my posts in Swedish so when I inserted DB_CHARSET and DB_COLLATE into the config file all the å, ä and ö:s got quite messed up. Tired and unwilling to alter everything manually I ran the UTF-8 Database Converter Plugin on WordPress 2.9.2 and thank god it worked and my website now looks beautiful again :). This is by all means no guarantee that it will work of any of you guys out there though…

    Gazzer48

    (@gazzer48)

    13 years, 11 months ago

    I am having the same problem wit this Theme: http://www.visitosthammar.se/wordpress

    Should I run the plugin?? 🙂

    Gazzer48

    (@gazzer48)

    13 years, 11 months ago

    As you can see the weird thing is that I get Swedish characters on the Start page but NOT on the “Titles” in the top of the folowing pages!! WTF!?! 🙂

Viewing 12 replies - 1 through 12 (of 12 total)
  • The topic ‘how to Convert Database Character Sets to utf-8’ is closed to new replies.