Latin1 wordpress veritabanınızı utf8′e çevirmek

Eski bir wordpress kurulu bloğuma yeni bir eklenti kurmaya kalktığımda mysql latin1 karakter seti ile problem yaşadım. Karakter setini utf8′e çevirmeye kalkınca yazılar okunmaz hale geldi. Sorunu araştırdığımda WordPress’in eski sürümlerinde latin1 mysql tabloları oluşturup verileri bunlara utf8 olarak yazdığını ve bu yüzden problem olduğunu gördüm. Bu durumda utf8 e çevirirken utf8 olan karakterler tekrar utf8 e çevrildiğinden yani çifte kavrulmuş utf8 olduğundan yazılar bozuluyor. Çözüm için utf8 e çevirme işlemi uygulanacak tablolardaki text alanları blob yapıp utf8 e çevirip sonra tekrar text yapmanın mümkün olduğunu okuyunca bunu uygulamaya koydum.

Yapılacak olan işlemler geri dönüşü olmayan ve wordpress’inizi çalışmaz hale getirebilecek işlemler olduğundan mutlaka yedek almalısınız

WordPress’te bulunan ana tabloları çevirmek için aşağıdaki sql sorgusunu kullandım:

ALTER TABLE wp_commentmeta MODIFY meta_key varbinary(255);
ALTER TABLE wp_comments MODIFY comment_author_email varbinary(100);
ALTER TABLE wp_comments MODIFY comment_author_url varbinary(200);
ALTER TABLE wp_comments MODIFY comment_author_IP varbinary(100);
ALTER TABLE wp_comments MODIFY comment_approved varbinary(20);
ALTER TABLE wp_comments MODIFY comment_agent varbinary(255);
ALTER TABLE wp_comments MODIFY comment_type varbinary(20);
ALTER TABLE wp_links MODIFY link_url varbinary(255);
ALTER TABLE wp_links MODIFY link_name varbinary(255);
ALTER TABLE wp_links MODIFY link_image varbinary(255);
ALTER TABLE wp_links MODIFY link_target varbinary(25);
ALTER TABLE wp_links MODIFY link_description varbinary(255);
ALTER TABLE wp_links MODIFY link_visible varbinary(20);
ALTER TABLE wp_links MODIFY link_rel varbinary(255);
ALTER TABLE wp_links MODIFY link_rss varbinary(255);
ALTER TABLE wp_options MODIFY option_name varbinary(64);
ALTER TABLE wp_options MODIFY autoload varbinary(20);
ALTER TABLE wp_postmeta MODIFY meta_key varbinary(255);
ALTER TABLE wp_posts MODIFY post_status varbinary(20);
ALTER TABLE wp_posts MODIFY comment_status varbinary(20);
ALTER TABLE wp_posts MODIFY ping_status varbinary(20);
ALTER TABLE wp_posts MODIFY post_password varbinary(20);
ALTER TABLE wp_posts MODIFY post_name varbinary(200);
ALTER TABLE wp_posts MODIFY guid varbinary(255);
ALTER TABLE wp_posts MODIFY post_type varbinary(20);
ALTER TABLE wp_posts MODIFY post_mime_type varbinary(100);
ALTER TABLE wp_statpress MODIFY date binary(8);
ALTER TABLE wp_statpress MODIFY time binary(8);
ALTER TABLE wp_statpress MODIFY ip binary(15);
ALTER TABLE wp_statpress MODIFY urlrequested varbinary(250);
ALTER TABLE wp_statpress MODIFY agent varbinary(250);
ALTER TABLE wp_statpress MODIFY referrer varbinary(250);
ALTER TABLE wp_statpress MODIFY search varbinary(250);
ALTER TABLE wp_statpress MODIFY nation varbinary(2);
ALTER TABLE wp_statpress MODIFY os varbinary(30);
ALTER TABLE wp_statpress MODIFY browser varbinary(32);
ALTER TABLE wp_statpress MODIFY searchengine varbinary(16);
ALTER TABLE wp_statpress MODIFY spider varbinary(32);
ALTER TABLE wp_statpress MODIFY feed varbinary(8);
ALTER TABLE wp_statpress MODIFY user varbinary(16);
ALTER TABLE wp_statpress MODIFY timestamp varbinary(10);
ALTER TABLE wp_term_taxonomy MODIFY taxonomy varbinary(32);
ALTER TABLE wp_terms MODIFY name varbinary(200);
ALTER TABLE wp_terms MODIFY slug varbinary(200);
ALTER TABLE wp_usermeta MODIFY meta_key varbinary(255);
ALTER TABLE wp_users MODIFY user_login varbinary(60);
ALTER TABLE wp_users MODIFY user_pass varbinary(64);
ALTER TABLE wp_users MODIFY user_nicename varbinary(50);
ALTER TABLE wp_users MODIFY user_email varbinary(100);
ALTER TABLE wp_users MODIFY user_url varbinary(100);
ALTER TABLE wp_users MODIFY user_activation_key varbinary(60);
ALTER TABLE wp_users MODIFY display_name varbinary(250);

ALTER TABLE wp_commentmeta MODIFY meta_value longblob;
ALTER TABLE wp_comments MODIFY comment_author tinyblob;
ALTER TABLE wp_comments MODIFY comment_content blob;
ALTER TABLE wp_links MODIFY link_notes mediumblob;
ALTER TABLE wp_options MODIFY option_value longblob;
ALTER TABLE wp_postmeta MODIFY meta_value longblob;
ALTER TABLE wp_posts MODIFY post_content longblob;
ALTER TABLE wp_posts MODIFY post_title blob;
ALTER TABLE wp_posts MODIFY post_excerpt blob;
ALTER TABLE wp_posts MODIFY to_ping blob;
ALTER TABLE wp_posts MODIFY pinged blob;
ALTER TABLE wp_posts MODIFY post_content_filtered blob;
ALTER TABLE wp_term_taxonomy MODIFY description longblob;
ALTER TABLE wp_usermeta MODIFY meta_value longblob;

ALTER TABLE wp_commentmeta MODIFY meta_key varchar(255) CHARACTER SET utf8;
ALTER TABLE wp_comments MODIFY comment_author_email varchar(100) CHARACTER SET utf8;
ALTER TABLE wp_comments MODIFY comment_author_url varchar(200) CHARACTER SET utf8;
ALTER TABLE wp_comments MODIFY comment_author_IP varchar(100) CHARACTER SET utf8;
ALTER TABLE wp_comments MODIFY comment_approved varchar(20) CHARACTER SET utf8;
ALTER TABLE wp_comments MODIFY comment_agent varchar(255) CHARACTER SET utf8;
ALTER TABLE wp_comments MODIFY comment_type varchar(20) CHARACTER SET utf8;
ALTER TABLE wp_links MODIFY link_url varchar(255) CHARACTER SET utf8;
ALTER TABLE wp_links MODIFY link_name varchar(255) CHARACTER SET utf8;
ALTER TABLE wp_links MODIFY link_image varchar(255) CHARACTER SET utf8;
ALTER TABLE wp_links MODIFY link_target varchar(25) CHARACTER SET utf8;
ALTER TABLE wp_links MODIFY link_description varchar(255) CHARACTER SET utf8;
ALTER TABLE wp_links MODIFY link_visible varchar(20) CHARACTER SET utf8;
ALTER TABLE wp_links MODIFY link_rel varchar(255) CHARACTER SET utf8;
ALTER TABLE wp_links MODIFY link_rss varchar(255) CHARACTER SET utf8;
ALTER TABLE wp_options MODIFY option_name varchar(64) CHARACTER SET utf8;
ALTER TABLE wp_options MODIFY autoload varchar(20) CHARACTER SET utf8;
ALTER TABLE wp_postmeta MODIFY meta_key varchar(255) CHARACTER SET utf8;
ALTER TABLE wp_posts MODIFY post_status varchar(20) CHARACTER SET utf8;
ALTER TABLE wp_posts MODIFY comment_status varchar(20) CHARACTER SET utf8;
ALTER TABLE wp_posts MODIFY ping_status varchar(20) CHARACTER SET utf8;
ALTER TABLE wp_posts MODIFY post_password varchar(20) CHARACTER SET utf8;
ALTER TABLE wp_posts MODIFY post_name varchar(200) CHARACTER SET utf8;
ALTER TABLE wp_posts MODIFY guid varchar(255) CHARACTER SET utf8;
ALTER TABLE wp_posts MODIFY post_type varchar(20) CHARACTER SET utf8;
ALTER TABLE wp_posts MODIFY post_mime_type varchar(100) CHARACTER SET utf8;
ALTER TABLE wp_statpress MODIFY date char(8) CHARACTER SET utf8;
ALTER TABLE wp_statpress MODIFY time char(8) CHARACTER SET utf8;
ALTER TABLE wp_statpress MODIFY ip char(15) CHARACTER SET utf8;
ALTER TABLE wp_statpress MODIFY urlrequested varchar(250) CHARACTER SET utf8;
ALTER TABLE wp_statpress MODIFY agent varchar(250) CHARACTER SET utf8;
ALTER TABLE wp_statpress MODIFY referrer varchar(250) CHARACTER SET utf8;
ALTER TABLE wp_statpress MODIFY search varchar(250) CHARACTER SET utf8;
ALTER TABLE wp_statpress MODIFY nation varchar(2) CHARACTER SET utf8;
ALTER TABLE wp_statpress MODIFY os varchar(30) CHARACTER SET utf8;
ALTER TABLE wp_statpress MODIFY browser varchar(32) CHARACTER SET utf8;
ALTER TABLE wp_statpress MODIFY searchengine varchar(16) CHARACTER SET utf8;
ALTER TABLE wp_statpress MODIFY spider varchar(32) CHARACTER SET utf8;
ALTER TABLE wp_statpress MODIFY feed varchar(8) CHARACTER SET utf8;
ALTER TABLE wp_statpress MODIFY user varchar(16) CHARACTER SET utf8;
ALTER TABLE wp_statpress MODIFY timestamp varchar(10) CHARACTER SET utf8;
ALTER TABLE wp_term_taxonomy MODIFY taxonomy varchar(32) CHARACTER SET utf8;
ALTER TABLE wp_terms MODIFY name varchar(200) CHARACTER SET utf8;
ALTER TABLE wp_terms MODIFY slug varchar(200) CHARACTER SET utf8;
ALTER TABLE wp_usermeta MODIFY meta_key varchar(255) CHARACTER SET utf8;
ALTER TABLE wp_users MODIFY user_login varchar(60) CHARACTER SET utf8;
ALTER TABLE wp_users MODIFY user_pass varchar(64) CHARACTER SET utf8;
ALTER TABLE wp_users MODIFY user_nicename varchar(50) CHARACTER SET utf8;
ALTER TABLE wp_users MODIFY user_email varchar(100) CHARACTER SET utf8;
ALTER TABLE wp_users MODIFY user_url varchar(100) CHARACTER SET utf8;
ALTER TABLE wp_users MODIFY user_activation_key varchar(60) CHARACTER SET utf8;
ALTER TABLE wp_users MODIFY display_name varchar(250) CHARACTER SET utf8;

ALTER TABLE wp_commentmeta MODIFY meta_value longtext CHARACTER SET utf8;
ALTER TABLE wp_comments MODIFY comment_author tinytext CHARACTER SET utf8;
ALTER TABLE wp_comments MODIFY comment_content text CHARACTER SET utf8;
ALTER TABLE wp_links MODIFY link_notes mediumtext CHARACTER SET utf8;
ALTER TABLE wp_options MODIFY option_value longtext CHARACTER SET utf8;
ALTER TABLE wp_postmeta MODIFY meta_value longtext CHARACTER SET utf8;
ALTER TABLE wp_posts MODIFY post_content longtext CHARACTER SET utf8;
ALTER TABLE wp_posts MODIFY post_title text CHARACTER SET utf8;
ALTER TABLE wp_posts MODIFY post_excerpt text CHARACTER SET utf8;
ALTER TABLE wp_posts MODIFY to_ping text CHARACTER SET utf8;
ALTER TABLE wp_posts MODIFY pinged text CHARACTER SET utf8;
ALTER TABLE wp_posts MODIFY post_content_filtered text CHARACTER SET utf8;
ALTER TABLE wp_term_taxonomy MODIFY description longtext CHARACTER SET utf8;
ALTER TABLE wp_usermeta MODIFY meta_value longtext CHARACTER SET utf8;

ALTER TABLE wp_comments DEFAULT CHARACTER SET utf8;
ALTER TABLE wp_commentmeta DEFAULT CHARACTER SET utf8;
ALTER TABLE wp_links DEFAULT CHARACTER SET utf8;
ALTER TABLE wp_options DEFAULT CHARACTER SET utf8;
ALTER TABLE wp_postmeta DEFAULT CHARACTER SET utf8;
ALTER TABLE wp_posts DEFAULT CHARACTER SET utf8;
ALTER TABLE wp_term_taxonomy DEFAULT CHARACTER SET utf8;
ALTER TABLE wp_users DEFAULT CHARACTER SET utf8;
ALTER TABLE wp_usermeta DEFAULT CHARACTER SET utf8;
ALTER TABLE wp_terms DEFAULT CHARACTER SET utf8;
ALTER TABLE wp_term_relationships DEFAULT CHARACTER SET utf8;

İşlemlerin ardından wordpress veritabanını da utf8 olarak değiştiriyoruz, sonra binary yaptığımız text alanları geri çevirme işlemi kalıyor bunun için de aşağıdaki sql komut dizisini kullandım

ALTER TABLE wp_commentmeta MODIFY meta_key varchar(255);
ALTER TABLE wp_comments MODIFY comment_author_email varchar(100);
ALTER TABLE wp_comments MODIFY comment_author_url varchar(200);
ALTER TABLE wp_comments MODIFY comment_author_IP varchar(100);
ALTER TABLE wp_comments MODIFY comment_approved varchar(20);
ALTER TABLE wp_comments MODIFY comment_agent varchar(255);
ALTER TABLE wp_comments MODIFY comment_type varchar(20);
ALTER TABLE wp_links MODIFY link_url varchar(255);
ALTER TABLE wp_links MODIFY link_name varchar(255);
ALTER TABLE wp_links MODIFY link_image varchar(255);
ALTER TABLE wp_links MODIFY link_target varchar(25);
ALTER TABLE wp_links MODIFY link_description varchar(255);
ALTER TABLE wp_links MODIFY link_visible varchar(20);
ALTER TABLE wp_links MODIFY link_rel varchar(255);
ALTER TABLE wp_links MODIFY link_rss varchar(255);
ALTER TABLE wp_options MODIFY option_name varchar(64);
ALTER TABLE wp_options MODIFY autoload varchar(20);
ALTER TABLE wp_postmeta MODIFY meta_key varchar(255);
ALTER TABLE wp_posts MODIFY post_status varchar(20);
ALTER TABLE wp_posts MODIFY comment_status varchar(20);
ALTER TABLE wp_posts MODIFY ping_status varchar(20);
ALTER TABLE wp_posts MODIFY post_password varchar(20);
ALTER TABLE wp_posts MODIFY post_name varchar(200);
ALTER TABLE wp_posts MODIFY guid varchar(255);
ALTER TABLE wp_posts MODIFY post_type varchar(20);
ALTER TABLE wp_posts MODIFY post_mime_type varchar(100);
ALTER TABLE wp_statpress MODIFY date char(8);
ALTER TABLE wp_statpress MODIFY time char(8);
ALTER TABLE wp_statpress MODIFY ip char(15);
ALTER TABLE wp_statpress MODIFY urlrequested varchar(250);
ALTER TABLE wp_statpress MODIFY agent varchar(250);
ALTER TABLE wp_statpress MODIFY referrer varchar(250);
ALTER TABLE wp_statpress MODIFY search varchar(250);
ALTER TABLE wp_statpress MODIFY nation varchar(2);
ALTER TABLE wp_statpress MODIFY os varchar(30);
ALTER TABLE wp_statpress MODIFY browser varchar(32);
ALTER TABLE wp_statpress MODIFY searchengine varchar(16);
ALTER TABLE wp_statpress MODIFY spider varchar(32);
ALTER TABLE wp_statpress MODIFY feed varchar(8);
ALTER TABLE wp_statpress MODIFY user varchar(16);
ALTER TABLE wp_statpress MODIFY timestamp varchar(10);
ALTER TABLE wp_term_taxonomy MODIFY taxonomy varchar(32);
ALTER TABLE wp_terms MODIFY name varchar(200);
ALTER TABLE wp_terms MODIFY slug varchar(200);
ALTER TABLE wp_usermeta MODIFY meta_key varchar(255);
ALTER TABLE wp_users MODIFY user_login varchar(60);
ALTER TABLE wp_users MODIFY user_pass varchar(64);
ALTER TABLE wp_users MODIFY user_nicename varchar(50);
ALTER TABLE wp_users MODIFY user_email varchar(100);
ALTER TABLE wp_users MODIFY user_url varchar(100);
ALTER TABLE wp_users MODIFY user_activation_key varchar(60);
ALTER TABLE wp_users MODIFY display_name varchar(250);

ALTER TABLE wp_commentmeta MODIFY meta_value longtext;
ALTER TABLE wp_comments MODIFY comment_author tinytext;
ALTER TABLE wp_comments MODIFY comment_content text;
ALTER TABLE wp_links MODIFY link_notes mediumtext;
ALTER TABLE wp_options MODIFY option_value longtext;
ALTER TABLE wp_postmeta MODIFY meta_value longtext;
ALTER TABLE wp_posts MODIFY post_content longtext;
ALTER TABLE wp_posts MODIFY post_title text;
ALTER TABLE wp_posts MODIFY post_excerpt text;
ALTER TABLE wp_posts MODIFY to_ping text;
ALTER TABLE wp_posts MODIFY pinged text;
ALTER TABLE wp_posts MODIFY post_content_filtered text;
ALTER TABLE wp_term_taxonomy MODIFY description longtext;
ALTER TABLE wp_usermeta MODIFY meta_value longtext;

wordpress veritabanınız artık utf8