Alain Kelder is a Giant Dork…

WordPress helpfully creates revisions of blog posts. If you’re concerned about a number of rows in the wp_posts table, you could delete old revisions. Let’s say we want to delete revisions older than 7 days:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

SELECT COUNT(*) FROM wp_posts WHERE post_type = 'revision';
+----------+
| COUNT(*) |
+----------+
|      110 | 
+----------+
SELECT COUNT(*) FROM wp_posts WHERE post_type = 'revision' AND post_date < DATE_ADD(NOW(), INTERVAL -7 DAY);
+----------+
| COUNT(*) |
+----------+
|       84 | 
+----------+
DELETE FROM wp_posts WHERE post_type = 'revision' AND post_date < DATE_ADD(NOW(), INTERVAL -7 DAY);
SELECT COUNT(*) FROM wp_posts WHERE post_type = 'revision';
+----------+
| COUNT(*) |
+----------+
|       26 | 
+----------+

If you’re running WordPress MU, the process is the same, just need to specify the right table as each WPMU blog has its own. See this post for how to identify WPMU blog numbers and corresponding URLs.

Let’s say a WordPress blog previously accessible via http://old/ is now at http://new/. You’ve updated the Apache config, yet when accessing http://new/, you get redirected to http://old/, which no longer exists.

This is because WordPress still considers itself at the old URL. You could use the following SQL find/replace old URL instances with new, directly in the database:

1

UPDATE wp_options SET option_value = REPLACE(option_value, 'http://old/', 'http://new/');

But wait, you’re not done, there are more places to update..

Read the rest of this entry…

With WordPress MU, all blogs share the same database. A number is added to standard WordPress table names for each blog. Main blog is usually wp_1_xxx, second blog that was created is wp_2_xxx, etc.

To modify a WPMU blog directly in the DB, one would usually want to identify which tables are used for which blog, to ensure the correct blog is being modified. Here’s a little shell one liner to do just that:

1
2
3
4

$ CMD="mysql giantdorks --skip-column-names -Be"; TBS=$($CMD "show tables like 'wp%options'"); for TB in $TBS; do URL=$($CMD "select option_value from $TB where option_name='siteurl'") && echo $TB is for $URL; done
wp_1_options is for http://giantdorks.org/
wp_2_options is for http://giantdorks.org/alain/
wp_3_options is for http://giantdorks.org/jason/