SQL Database Character Set Conversion Script

[admin]

The script generate SQL manipulation commands that need to convert a WordPress database to UTF-8 charset and collation.

Instruction guide at the blog.

[EJ-K]

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.info.

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.

[admin]

Hi EJ-K, there is no problem with your user account. Can you try to download again?

[EJ-K]

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.

[Palamedes]

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!

[something2]

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.

[mat8iou]

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.

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.

[balaji]

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:

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?

[ejm]

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:

Ensure that your post_content and post title fields on wp_posts table DOES NOT belongs to any indexes or FULLTEXT indexes. Else the type of the fields may not be converted to BLOB with one of the errors list below. Some plugins, such as related posts tend to add indexes to these fields. In this case, temporarily drop the indexes.

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.)

[ejm]

I get an error:

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?

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:

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.

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.