SQL Database Character Set Conversion Script

Discussion in 'Application Software' started by admin, Jun 22, 2007.

  1. admin

    admin Administrator
    Staff Member

    Apr 24, 2007
    229
    98
    10
    The script generate SQL manipulation commands that need to convert a WordPress database to UTF-8 charset and collation.

    Instruction guide at the blog.
     
  2. EJ-K

    EJ-K MDL Novice

    Jul 26, 2007
    2
    0
    0
    I got sent over to this download from the blog.

    When I clicked the file to download, it prompted me to register for the forum.

    I registered, logged in, and still can not download the file. I cleared my browser cache, refreshed the page, and still nothing. I did get a setup email and a confirmation email from admin@mydigitallife.net.

    Thanks for the help! I'd like to try this out.

    Here's the page I get when I try to download the file:

    You are not logged in or you do not have permission to access this page. This could be due to one of several reasons:
    You are not logged in. Fill in the form at the bottom of this page and try again.
    You may not have sufficient privileges to access this page. Are you trying to edit someone else's post, access administrative features or some other privileged system?
    If you are trying to post, the administrator may have disabled your account, or it may be awaiting activation.
     
  3. admin

    admin Administrator
    Staff Member

    Apr 24, 2007
    229
    98
    10
    Hi EJ-K, there is no problem with your user account. Can you try to download again?
     
  4. EJ-K

    EJ-K MDL Novice

    Jul 26, 2007
    2
    0
    0
    Hi, thanks for the help. I just tried again, same issue in Safari.

    Tried it on firefox, and it works! Woohoo.

    Now to test the script on my server. Thanks again.
     
  5. Palamedes

    Palamedes MDL Novice

    Feb 25, 2008
    1
    0
    0
    Problems using PHPmyAdmin

    Hi there,

    I'm using PHPmyAdmin and try to convert my database to utf-8, but...PHPmA interrupts the process with the first error message (concering key length as mentioned in ur guide).

    Is there a way to make PHPmA ignore errors / to proceed the other tables?

    Thx in advance!
     
  6. something2

    something2 Guest

    extended script

    Hello,
    The script didn't work for me because my tables and columns were already utf8, but I didn't set the DB_CHARSET and DB_COLLATE variables when I installed Wordpress. That's why the script tried to convert the text from utf8 to utf8, in effect, doing nothing.

    So I am posting a script that first converts all the columns and tables to latin1, then to blob and finally to utf8.

    Hope someone finds it useful.
     
  7. mat8iou

    mat8iou MDL Novice

    Feb 9, 2008
    1
    0
    0
    Many many thanks for this. I've been struggling with this issue on & off for ages - obviously, like you at some point in trying to convert things or during a database restore, the defaults for the tables had already been switched.

    Everything now seems to be working fine after many false attempts previously, which never seemed to make any difference.
     
  8. balaji

    balaji MDL Novice

    Sep 16, 2008
    1
    0
    0
    #8 balaji, Sep 16, 2008
    Last edited by a moderator: Apr 20, 2017
    I was trying the extended script posted by something2 because I'm faced with the same problem of tables that are marked as utf8 but contain latin1 data.

    Unfortuantely, the script doesn't seem to be working for me. Specifically when I try to mark the columns are latin1, I get an error:

    Code:
    mysql> ALTER TABLE wp_comments MODIFY comment_author CHARACTER SET latin1 COLLATE latin1_swedish_ci;
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that    corresponds to your MySQL server version for the right syntax to use near 'SET latin1 COLLATE latin1_swedish_ci' at line 1
    Could someone advise on how to get the script working?
     
  9. ejm

    ejm MDL Novice

    Sep 18, 2009
    2
    0
    0
    how to ensure that fields do not belong to indexes?

    First of all, many thanks for creating this script. I have been staring in dismay at the wp codex page on how to convert the wordpress Database Character Set to UTF8, completely unable to understand it (the only thing I know about mySQL is how to get myself in trouble).

    In the Guide to Convert WordPress Database Character Set to UTF8, it says:

    What exactly do I look for to find out if fields belong to indexes or FULLTEXT indexes?

    Thank you.

    (I am reluctant to simply dive in and hope for the best, having already restored wordpress from a backup yesterday after a disastrous result from running the wordpress plugin to Convert WordPress Database Character Set.)
     
  10. ejm

    ejm MDL Novice

    Sep 18, 2009
    2
    0
    0
    #10 ejm, Sep 25, 2009
    Last edited by a moderator: Apr 20, 2017
    Even though this thread is over a year old, I have to assume that others besides me will be getting a similar error. I had the same problem and learned from a kind soul at codingforum that with the sql version at 4.1, one has to include the "type" beside the name - something like VARCHAR(100) or tinytext or...

    I looked in my database backup files to find out what each of the types were for all the various tables. (Yes, it was rather labour intensive.) It turns out that there IS a better way. Use the following when in the sql section of phpMyAdmin:

    Code:
    SHOW COLUMNS FROM wp_comments;
    and the type(s) will be displayed to learn that the "comment_author" type is "tinytext". Then put this into the sql window.

    Code:
    ALTER TABLE wp_comments MODIFY comment_author tinytext CHARACTER SET latin1 COLLATE latin1_swedish_ci;
    Change the database name in the "SHOW COLUMNS FROM" command for every time you get a similar error to the one outlined by balaji.

    Then follow the rest of the instructions, ignoring the error messages related to key length that will likely come up (the likely files are listed in #12 of how to convert character set and collation of wordpress database)


    Incidentally, I never did find out how to tell if a wp table belongs to any indexes or FULLTEXT indexes. I just went ahead and hoped for the best. Happily, it appears that I did not run into this difficulty and at last, my wordpress tables are all utf-8.
     
  11. trecords

    trecords MDL Novice

    Mar 13, 2010
    1
    0
    0
    Hi, i use wordpress MU with multi db so i have 16 databases and all need to convert into UTF8. I can add manually each database so so i need to remove require_once and wpdb codes and replace them with my database name? or can yu please help me with this?
    Thanks.