Expired transients ? My dummy Cleanup script

Recently I noticed that in my wordpress multisite installation database, were in all wp_options table some “strange” records beginning with “_transient” and also the wp_sitemeta table had more than 1000 records with ‘_site_transient’. Each of these had also a  “_transient_timeout” record.

After a bit of googling and reading, I learn that transients are the wordpress way to temporaly cache records into db in order to avoid doing the same query or get request (in case of rss feed) again.  Wordpress has even a Transients API 

Allthough in the codex is written that

“Our transient will die naturally of old age once $expiration seconds have passed since we last ran set_transient()" 

But I noticed that in our database the expired transients records were still there. In an article  http://wpengine.com/2013/02/wordpress-transient-api/ I found the following:

When a transient is accessed, WordPress pulls the expiration date first. If it’s expired, WordPress deletes both options from the table, thereby “cleaning up” the data, and pretends the data was never there. If it’s not expired, it grabs the content from the options table.

This means that if the transient is newer requested again it simple stays in the database and doesn’t get deleted? I thought  that wp_cron does this job. Maybe it does, and something in my installation is wrong.

Anyway, I wanted the clear those records for now in order to save some space in my db. So I wrote a simple PHP script to do the “cleanup’ manually. The best way of course would be to write a plugin or try to figure how to make it works with wp_cron, but no time for it.

Here is the ‘dummy’ way for one use only, manually trea

1. Create a php file into your root directory. e.x /cleanup.php

2. Paste the following code:

<?php

require_once(‘wp-load.php’);

/** Goes through each blogs wp_options table, find the expired transients and deletes them

*/
function delete_expired_db_transients() {
global $wpdb;
$deletescount = 0;
echo ‘<h1>Expired transients deletion begin</h1>’;
$site_blog_ids = $wpdb->get_col($wpdb->prepare(“SELECT blog_id FROM wp_blogs where blog_id > 1”)); // get all subsite blog ids
echo ‘Found ‘ . count($site_blog_ids) . ‘ blogs in site <ul>’;
foreach ($site_blog_ids AS $blog_id) {
switch_to_blog($blog_id);
$time = isset($_SERVER[‘REQUEST_TIME’]) ? (int) $_SERVER[‘REQUEST_TIME’] : time();
$expired = $wpdb->get_col(“SELECT option_name FROM {$wpdb->options} WHERE option_name LIKE ‘_transient_timeout%’ AND option_value < {$time};”);
echo ‘<li>Blog id: ‘ . $blog_id . ‘ Begin delete expired transients from ‘ . $wpdb->options;

if (count($expired) > 0) {
foreach ($expired as $transient) {
echo ‘<ol>’;
$key = str_replace(‘_transient_timeout_’, ”, $transient);
if (delete_transient($key)) {

echo ‘<li>Deleted:’ . $key . ‘</li>’;
$deletescount = $deletescount + 1;
}
echo ‘</ol>’;
}

} else {
echo ‘<br/>No expired transients found.’;
}
echo ‘</li>’;
}
echo ‘</ul>’;
echo ‘<h2>Expired transients deletion complete</h2>
Total <b>’ . $deletescount . ‘</b> expired transients records where removed.’;
}

/**find the expired site_transients from wp_sitemeta table and deletes them

*/

function delete_expired_db_site_transients() {
global $wpdb;
$deletescount = 0;
$time = isset($_SERVER[‘REQUEST_TIME’]) ? (int) $_SERVER[‘REQUEST_TIME’] : time();
$expired = $wpdb->get_col(“SELECT meta_key FROM {$wpdb->sitemeta} WHERE meta_key LIKE ‘_site_transient_timeout_%’ AND meta_value < {$time};”);
echo ‘<H1>Expired site_transients deletion begins </h1>’;

foreach ($expired as $transient) {

$key = str_replace(‘_site_transient_timeout_’, ”, $transient);
if (delete_site_transient($key)) {
echo ‘&nbsp;&nbsp;Deleted:’ . $key . ‘<br>’;
$deletescount = $deletescount + 1;
}
}
echo ‘Expired Site transients deletion complete.<br> Total ‘ . $deletescount . ‘ expired site transients where removed from wp_sitemeta’;
}

delete_expired_db_site_transients();

delete_expired_db_transients();

?>

3. Visit your http://www.yoursite.com/cleanup.php

4. Wait the process to complete. The time is related to the number of blogs you have.

5. After the “cleaning’ is done, delete or rename the file (just in case).

It would be great if someone could suggest a more robust way to do it, such as included into wp_cron jobs or via a plugin.