Wednesday, June 20, 2012

Character Set Conversion for Wordpress MySql

0 comments
In my wordpress database, there were some tables which were running with character set latin1 while others were running with utf8.
As our application use SET NAMES UTF8 before every query, we were not facing any trouble from client side. But it was not standard that without any requirement we had the database with multiple character set. So I decided to modify all tables character set to utf8.

To modify the character set of tables, we had to look for any char, varchar or text data type column in those tables. If any type of these columns exists, we had to modify it to BLOB and then convert the character set and then again modify to its original data type. In this way, we can ensure no loss of any character.

As primary and unique indexes do not allow to modify the data type of a column, we had to drop indexes from the columns to achive the above task.

Our wordpress database has been running with Hyperdb configuration. So we have 'db' as the common database and then 'dbN' for each 1000 blogs.

I followed the below steps:-

Step 1: Stop connetions


Change database user password to stop connection


Step 2: Create Drop indexes Script

vi 2_drop_indexes.sql
alter table db.table1 drop  index index1;
select 1;
alter table db.table2 drop  index index1;
select 2;
...
...
alter table db.table3 drop  primary key;
select 16;



Step 3: Create Script tp Modify Columns to BLOB


select
concat('alter table `', table_schema, '`.`', table_name,'` modify ',column_name,' BLOB;') as cmd_before
from information_schema.columns as c
where table_schema = 'db'
and data_type in ('char', 'varchar','text')
and exists
(
select 1
from information_schema.tables as t
where table_schema = 'db'
and t.table_type = 'BASE TABLE'
and t.table_collation <> 'utf8_general_ci'
and t.table_name = c.table_name
and t.table_schema = c.table_schema
)
INTO OUTFILE '3_col_text_to_blob.sql' ;


Step 4: Create Script tp Change table character set


#ALTER TABLE table_name CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];
select concat('ALTER TABLE `',table_schema, '`.`', table_Name,'` CONVERT TO CHARACTER SET utf8;') as cmd
from information_schema.tables
where table_schema = 'db'
and table_type = 'BASE TABLE'
and table_collation <> 'utf8_general_ci'
INTO OUTFILE '4_table_latin_to_utf8.sql' ;


Step 5: Create Script to Modify wp_N_tems_relationships



select concat('ALTER TABLE `',table_schema, '`.`', table_Name,'` CONVERT TO CHARACTER SET utf8;') as cmd
from information_schema.tables
where table_name like 'wp_%_term_relationships'
and table_type = 'BASE TABLE'
and table_collation <> 'utf8_general_ci'
INTO OUTFILE '5_table_tems_relationships_to_utf8.sql' ;


Step 6: Create Script to Revert column character set


select concat('alter table `',table_schema, '`.`', table_name,'` modify ',column_name,' ', column_type
,';') as cmd_after
from information_schema.columns as c
where table_schema = 'db'
and data_type in ('char', 'varchar','text')
and exists
(
select 1
from information_schema.tables as t
where table_schema = 'db'
and t.table_type = 'BASE TABLE'
and t.table_collation <> 'utf8_general_ci'
and t.table_name = c.table_name
and t.table_schema = c.table_schema
)
INTO OUTFILE '6_col_blob_to_text.sql' ;


Step 7: Create Script to Create index


vi 7_crate_index.sql
alter table table1 add unique key index1(col1, col2);
select 1;
alter table db.table2 add unique index index1 (index1);
select 2;
...
...
alter table db.table3 add primary key (col1);
select 16;


Step 8: Create Script to Analyze Table


vi 8_analyze_tables.sql
analyze table table1;
select 1;
analyze table db.table2;
select 2;
...
...
analyze table db.table3 ;
select 16;


Step 9: Execute Scripts


mysql db -s -N < 2_drop_indexes.sql
mysql db -s -N < 3_col_text_to_blob.sql
mysql db -s -N < 4_table_latin_to_utf8.sql
mysql db -s -N < 5_table_tems_relationships_to_utf8.sql
mysql db -s -N < 6_col_blob_to_text.sql
mysql db -s -N < 7_crate_index.sql
mysql db -s -N < 8_analyze_tables.sql


Step 10: Modify database parameters


vi my.cnf
...
[mysqld]
...
default_character_set=utf8
...
[mysql]
...
default_character_set=utf8

mysql restart

Leave a Reply

Labels