Finding used themes in wordpress mu

2012-10-15 Bertas Wordpress

I’m responsible for one blog farm, which are running on wordpress mu, and we needed to find what themes are used in order to remove not used.

In our setup there is one database and a lot of tables. Theme name, used for blog is stored in options table. It’s simple to find theme used in one blog:

SELECT option_value FROM wp_20_options WHERE option_name='current_theme';

If we need to find themes used in couple blogs it is quite easy too:

(SELECT option_value FROM wp_20_options WHERE option_name='current_theme') UNION 
(SELECT option_value FROM wp_21_options WHERE option_name='current_theme') UNION 
(SELECT option_value FROM wp_26_options WHERE option_name='current_theme');

But we have 1500 options tables….


We can find all tables names from information_schema:

SELECT TABLE_NAME FROM TABLES WHERE TABLE_NAME LIKE '%options%' 
AND table_schema='blogs';

But to write union by hand… no, we need some smarter solution. Lets allow sql to create union query for us:

SELECT concat('select * from (select option_value from ',
group_concat(tb SEPARATOR ' where option_name="current_theme" union select option_value from '),' 
where option_name="current_theme") as res') 
INTO @foo_query FROM (
SELECT CONCAT(table_schema,'.',TABLE_NAME) tb 
FROM information_schema.tables WHERE TABLE_NAME LIKE '%options%' 
AND table_schema='blogs'
) A;

If we take a look to @foo_query variable we se query:

SELECT @foo_query\G
*************************** 1. ROW ***************************
@foo_query: SELECT * FROM 
(SELECT option_value FROM blogs.wp_1001_options WHERE option_name="current_theme" UNION 
SELECT option_value FROM blogs.wp_1002_options WHERE option_name="current_theme" UNION 
SELECT option_value FROM blogs.wp_1003_options WHERE option_name="current_theme" UNION 
SELECT option_value FROM blogs.wp_1005_options WHERE option_name="current_theme" UNION 
SELECT option_value FROM blogs.wp_1006_options WHERE option_name="current_theme" UNION 
SELECT option_value FROM blogs.wp_1008_options WHERE option_name="current_theme" UNION 
SELECT option_value FROM blogs.wp_1012_options WHERE option_name="current_theme" UNION
SELECT option_value FROM blogs.wp_1016_options WHERE option_name="current_theme" UNION 
SELECT option_value FROM blogs.wp_1019_options WHERE option_name="current_theme" UNION 
SELECT option_value FROM blogs.wp_1021_options WHERE option_name="current_theme" UNION 
SELECT option_value FROM blogs.wp_1022_options WHERE option_name="current_theme" UNION 
SELECT option_value FROM blogs.wp_1024_options WHERE option_name="current_theme" UNION 
selec WHERE option_name="current_theme") AS res

It’s to small… and there is error in last line…
Lets execute first sentence once more and look to warnings:

Warning  1260  1 line(s) were cut BY GROUP_CONCAT()

There is limitation of GROUP_CONCAT() result. By default it is 1024 chars and it’s defined by group_concat_max_len.

SHOW VARIABLES LIKE 'group_concat_max_len';
group_concat_max_len = 1024

Lets increase it:

SET group_concat_max_len=102400;

And execute sql, which creates union for us again. Now if we execute select @foo_query\G we will see a lot longer sql sentence. Lets execute it:

PREPARE stmt FROM @foo_query;

Oh… I have got error:

ERROR 1016 (HY000): Can't open file: './blogs/wp_3263_options.frm' (errno: 24)

This error is because mysql reached open file limit. In order to avoid this error we need to set open-files-limit variable in my.cnf and restart mysql.

Then execute PREPARE stmt FROM @foo_query; again. And after that the last sentence which will give us results:

EXECUTE stmt;


Leave a Reply

Keep Things Simple

Powered by WordPress. Designed by elogi.