Are your WordPress MySQL Tables of mixed charsets? The ideal or standard character set is utf-8 as it supports multiple languages and special characters – so people from all over the world can post comments, register, and interact with your blog without having characters from their name, languages, etc converted into weird or strange symbols.
I read the WordPress article on Converrting your Tables but wasn’t so sure which direction to go. The article states:
> In most cases if a collation is not defined MySQL will assume the default collation for the CHARSET which is specified. For UTF8 the default is utf8_general_ci, which is usually the right choice.
After a little research I got it working.
The following code is from the supplemental code page from that article and I found that the code below (second code block on that page) worked well for WordPress 3.4.1
I would not recommend using the bash script at the bottom of that page because while it converts the tables it doesn’t convert the fields. When connecting from the command line you may need to specify `–protocol=TCP` if you get an error like
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)
But again, I would not really recommend that script, because while it will run, it will basically only run the following commands:
ALTER TABLE wp_commentmeta CHARACTER SET utf8;
ALTER TABLE wp_comments CHARACTER SET utf8;
ALTER TABLE wp_links CHARACTER SET utf8;
ALTER TABLE wp_options CHARACTER SET utf8;
ALTER TABLE wp_postmeta CHARACTER SET utf8;
ALTER TABLE wp_posts CHARACTER SET utf8;
ALTER TABLE wp_term_relationships CHARACTER SET utf8;
ALTER TABLE wp_term_taxonomy CHARACTER SET utf8;
ALTER TABLE wp_terms CHARACTER SET utf8;
ALTER TABLE wp_usermeta CHARACTER SET utf8;
ALTER TABLE wp_users CHARACTER SET utf8;
And the thing that is missing is converting the individual fields as well.
BACK UP YOUR DATABASE before running these SQL statements
## SQL To convert a site from latin1 to UTF8
In this case all columns were previously set to latin1 and the data inside them was latin1
## SQL To convert a site from latin1 to UTF8
In this case all columns were previously set to latin1 and the data inside them was latin1
### post\_content
alter table wp_posts change post_content post_content LONGBLOB;
alter table wp_posts change post_content post_content LONGTEXT CHARACTER SET utf8 not null;
### post\_title
alter table wp_posts change post_title post_title BLOB;
alter table wp_posts change post_title post_title TEXT CHARACTER SET utf8 not null;
### post\_excerpt
alter table wp_posts change post_excerpt post_excerpt BLOB;
alter table wp_posts change post_excerpt post_excerpt TEXT CHARACTER SET utf8 not null;
### post\_status
alter table wp_posts change post_status post_status VARBINARY(20);
alter table wp_posts change post_status post_status VARCHAR(20) character set utf8 not null default ‘publish’;
### wp\_posts comment\_status
alter table wp_posts change comment_status comment_status VARBINARY(20);
alter table wp_posts change comment_status comment_status VARCHAR(20) character set utf8 not null default ‘open’;
### ping\_status
alter table wp_posts change ping_status ping_status VARBINARY(20);
alter table wp_posts change ping_status ping_status VARCHAR(20) character set utf8 not null default ‘open’;
### post\_password
alter table wp_posts change post_password post_password VARBINARY(20);
alter table wp_posts change post_password post_password VARCHAR(20) character set utf8 not null default ”;
### post\_name
alter table wp_posts change post_name post_name VARBINARY(200);
alter table wp_posts change post_name post_name VARCHAR(200) character set utf8 not null default ”;
### wp\_posts to\_ping
alter table wp_posts change to_ping to_ping BLOB;
alter table wp_posts change to_ping to_ping TEXT CHARACTER SET utf8 not null;
### wp\_posts pinged
alter table wp_posts change pinged pinged BLOB;
alter table wp_posts change pinged pinged TEXT CHARACTER SET utf8 not null;
### post\_content\_filtered
alter table wp_posts change post_content_filtered post_content_filtered BLOB not null;
alter table wp_posts change post_content_filtered post_content_filtered TEXT CHARACTER SET utf8 not null;
### guid
alter table wp\_posts change guid guid VARBINARY(255);
alter table wp_posts change guid guid VARCHAR(255) character set utf8 not null default ”;
### post\_type
alter table wp_posts change post_type post_type VARBINARY(20);
alter table wp_posts change post_type post_type VARCHAR(20) character set utf8 not null default ‘post’;
### post\_mime\_type
alter table wp_posts change post_mime_type post_mime_type VARBINARY(100);
alter table wp_posts change post_mime_type post_mime_type VARCHAR(100) character set utf8 not null default ”;
ALTER TABLE wp_posts CHARACTER SET utf8;
### comment\_author
alter table wp_comments change comment_author comment_author TINYBLOB;
alter table wp_comments change comment_author comment_author TINYTEXT character set utf8 not null;
### comment\_author\_email
alter table wp_comments change comment_author_email comment_author_email VARBINARY(100);
alter table wp_comments change comment_author_email comment_author_email VARCHAR(100) character set utf8 not null default ”;
### comment\_author\_url
alter table wp_comments change comment_author_url comment_author_url VARBINARY(200);
alter table wp_comments change comment_author_url comment_author_url VARCHAR(200) character set utf8 not null default ”;
### comment\_author\_ip
alter table wp_comments change comment_author_IP comment_author_IP VARBINARY(100);
alter table wp_comments change comment_author_IP comment_author_IP VARCHAR(100) character set utf8 not null default ”;
### comment\_content
alter table wp_comments change comment_content comment_content BLOB;
alter table wp_comments change comment_content comment_content text character set utf8 not null;
### comment\_approved
alter table wp_comments change comment_approved comment_approved VARBINARY(20);
alter table wp_comments change comment_approved comment_approved VARCHAR(20) character set utf8 not null default ‘1’;
### comment\_agent
alter table wp_comments change comment_agent comment_agent VARBINARY(255);
alter table wp_comments change comment_agent comment_agent VARCHAR(255) character set utf8 not null default ”;
### comment\_type
alter table wp_comments change comment_type comment_type VARBINARY(20);
alter table wp_comments change comment_type comment_type VARCHAR(20) character set utf8 not null default ”;
ALTER TABLE wp_comments CHARACTER SET utf8;
### commentmeta meta\_key
alter table wp_commentmeta change meta_key meta_key VARBINARY(255);
alter table wp_commentmeta change meta_key meta_key VARCHAR(255) character set utf8;
### commentmeta meta\_value
alter table wp_commentmeta change meta_value meta_value LONGBLOB;
alter table wp_commentmeta change meta_value meta_value LONGTEXT character set utf8;
ALTER TABLE wp_commentmeta CHARACTER SET utf8;
### link\_url
alter table wp_links change link_url link_url VARBINARY(255);
alter table wp_links change link_url link_url VARCHAR(255) character set utf8 not null default ”;
### link\_name
alter table wp_links change link_name link_name VARBINARY(255);
alter table wp_links change link_name link_name VARCHAR(255) character set utf8 not null default ”;
### link\_image
alter table wp_links change link_image link_image VARBINARY(255);
alter table wp_links change link_image link_image VARCHAR(255) character set utf8 not null default ”;
### link\_target
alter table wp_links change link_target link_target VARBINARY(25);
alter table wp_links change link_target link_target VARCHAR(25) character set utf8 not null default ”;
### link\_description
alter table wp_links change link_description link_description VARBINARY(255);
alter table wp_links change link_description link_description VARCHAR(255) character set utf8 not null default ”;
### link\_visible
alter table wp_links change link_visible link_visible VARBINARY(20);
alter table wp_links change link_visible link_visible VARCHAR(20) character set utf8 not null default ‘Y’;
### link\_rel
alter table wp_links change link_rel link_rel VARBINARY(255);
alter table wp_links change link_rel link_rel VARCHAR(255) character set utf8 not null default ”;
### link\_notes
alter table wp_links change link_notes link_notes MEDIUMBLOB;
alter table wp_links change link_notes link_notes MEDIUMTEXT character set utf8 not null;
### link\_rss
alter table wp_links change link_rss link_rss VARBINARY(255);
alter table wp_links change link_rss link_rss VARCHAR(255) character set utf8 not null default ”;
ALTER TABLE wp_links CHARACTER SET utf8;
### option\_name
alter table wp_options change option_name option_name VARBINARY(64);
alter table wp_options change option_name option_name VARCHAR(64) character set utf8 not null default ”;
### option\_value
alter table wp_options change option_value option_value LONGBLOB;
alter table wp_options change option_value option_value LONGTEXT character set utf8 not null;
### wp\_options autoload
alter table wp_options change autoload autoload VARBINARY(20);
alter table wp_options change autoload autoload VARCHAR(20) character set utf8 not null default ‘yes’;
ALTER TABLE wp_options CHARACTER SET utf8;
### wp\_postmeta meta\_key
alter table wp_postmeta change meta_key meta_key VARBINARY(255);
alter table wp_postmeta change meta_key meta_key VARCHAR(255) character set utf8;
### wp\_postmeta meta\_value
alter table wp_postmeta change meta_value meta_value LONGBLOB;
alter table wp_postmeta change meta_value meta_value LONGTEXT character set utf8;
ALTER TABLE wp_postmeta CHARACTER SET utf8;
### wp\_terms name
alter table wp_terms change name name VARBINARY(200);
alter table wp_terms change name name VARCHAR(200) character set utf8 not null default ”;
### wp\_terms slug
alter table wp_terms change slug slug VARBINARY(200);
alter table wp_terms change slug slug VARCHAR(200) character set utf8 not null default ”;
ALTER TABLE wp_terms CHARACTER SET utf8;
### wp\_term\_taxonomy taxonomy
alter table wp_term_taxonomy change taxonomy taxonomy VARBINARY(32);
alter table wp_term_taxonomy change taxonomy taxonomy VARCHAR(32) character set utf8 not null default ”;
### wp\_term\_taxonomy description
alter table wp_term_taxonomy change description description LONGBLOB;
alter table wp_term_taxonomy change description description LONGTEXT character set utf8 not null;
ALTER TABLE wp_term_taxonomy CHARACTER SET utf8;
### term\_relationships has no encoded content, but switch carset anyway
ALTER TABLE wp_term_relationships CHARACTER SET utf8;
### wp\_usermeta meta\_key
alter table wp_usermeta change meta_key meta_key VARBINARY(255);
alter table wp_usermeta change meta_key meta_key VARCHAR(255) character set utf8;
### wp\_usermeta meta\_value
alter table wp_usermeta change meta_value meta_value LONGBLOB;
alter table wp_usermeta change meta_value meta_value LONGTEXT character set utf8;
ALTER TABLE wp_usermeta CHARACTER SET utf8;
### user\_login
alter table wp_users change user_login user_login VARBINARY(60);
alter table wp_users change user_login user_login VARCHAR(60) character set utf8 not null default ”;
### user\_pass
alter table wp_users change user_pass user_pass VARBINARY(64);
alter table wp_users change user_pass user_pass VARCHAR(64) character set utf8 not null default ”;
### user\_nicename
alter table wp_users change user_nicename user_nicename VARBINARY(50);
alter table wp_users change user_nicename user_nicename VARCHAR(50) character set utf8 not null default ”;
### user\_email
alter table wp_users change user_email user_email VARBINARY(100);
alter table wp_users change user_email user_email VARCHAR(100) character set utf8 not null default ”;
### user\_url
alter table wp_users change user_url user_url VARBINARY(100);
alter table wp_users change user_url user_url VARCHAR(100) character set utf8 not null default ”;
### user\_activation\_key
alter table wp_users change user_activation_key user_activation_key VARBINARY(60);
alter table wp_users change user_activation_key user_activation_key VARCHAR(60) character set utf8 not null default ”;
### wp\_users display\_name
alter table wp_users change display_name display_name VARBINARY(250);
alter table wp_users change display_name display_name VARCHAR(250) character set utf8 not null default ”;
ALTER TABLE wp_users CHARACTER SET utf8;
