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.

Advertisements

3 thoughts on “Expired transients ? My dummy Cleanup script

  1. Hi, thanks for this, but your editor changed a bunch of single- and double-quote characters to “smart quotes”, which PHP doesn’t recognize, and even changed two consecutive single-quotes (the empty string in your str_replace calls) into a double-quote. Also, your script assumes a multisite WordPress installatoin. Standalone WordPress installations don’t have a sitemeta table, and store both site transients and local transients in the wp_options table.

    I made a revision of your script that makes it work on multisite or standalone. It’s still not the cleanest code in the world, but it works. I’m happy to share it, but don’t want to host it. I’ll paste it here wrapped in <pre> tags and maybe it won’t get corrupted with smart-quotes again.

    get_col("SELECT option_name FROM {$wpdb->options} WHERE option_name LIKE '_transient_timeout%' AND option_value  0) {
        foreach ($expired as $transient) {
          echo '';
          $key = str_replace('_transient_timeout_', '', $transient);
          if (delete_transient($key)) {
            echo 'Deleted:' . $key . '';
            $deletescount = $deletescount + 1;
          }
          echo '';
        }
      } else {
        echo 'No expired transients found.';
      }
    
      return $deletescount;
    }
    
    /** find the expired site (global) transients from wp_sitemeta (multisite) or wp_options (standalone) table and deletes them */
    
    function delete_expired_db_site_transients() {
      global $wpdb;
      $deletescount = 0;
      $time = isset($_SERVER['REQUEST_TIME']) ? (int) $_SERVER['REQUEST_TIME'] : time();
    
      if ( is_multisite() ) {
        $site_transients_table = $wpdb->sitemeta;
        $keycol = 'meta_key';
        $valcol = 'meta_value';
      } else {
        $site_transients_table = $wpdb->options;
        $keycol = 'option_name';
        $valcol = 'option_value';
      }
    
      $expired = $wpdb->get_col("SELECT $keycol FROM $site_transients_table WHERE $keycol LIKE '_site_transient_timeout_%' AND $valcol  0) {
        foreach ($expired as $transient) {
          echo '';
          $key = str_replace('_site_transient_timeout_', '', $transient);
          if (delete_site_transient($key)) {
            echo 'Deleted:' . $key . '';
            $deletescount = $deletescount + 1;
          }
          echo '';
        }
      } else {
        echo 'No expired transients found.';
      }
    
      return $deletescount;
    }
    
    
    /** Delete transients using functions defined above */
    
    /* site (global) transients */
    echo 'Expired site transients deletion begins ';
    $deletescount = delete_expired_db_site_transients();
    echo 'Expired site transients deletion complete. Total ' . $deletescount . ' expired site transients were removed.';
    
    /* blog (local) transients */
    echo 'Expired transients deletion begins';
    $deletescount = 0;
    
    if ( is_multisite() ) {
      $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 ';
    
      foreach ($site_blog_ids AS $blog_id) {
        echo 'Blog id: ' . $blog_id . ' Begin delete expired transients from ' . $wpdb->options;
        switch_to_blog($blog_id);
        $deletescount += delete_expired_db_transients();
        echo '';
      }
      echo '';
    } else {
      $deletescount = delete_expired_db_transients();
    }
    
    echo 'Expired transients deletion complete.Total ' . $deletescount . ' expired transients records were removed.';
    
    ?>
    
  2. Well, it used preformatted code, but it looks like it’s missing the first few lines that I pasted. I’ll try to re-paste just those first few lines, ending with the one that seems to start with “get_col” in my previous post. I hope you’ll be able to put them together into the original post and delete this one, or again, you (webmaster of this blog) can drop me a line and I’ll send you the whole file.

    Here are the missing first few lines:

    get_col("SELECT option_name FROM {$wpdb->options} WHERE option_name LIKE '_transient_timeout%' AND option_value < {$time};");
    
  3. This editor is not as smart as it thinks it is. 😉 One more try, this time after HTML-encoding the script so it has no browser interpretable characters. If this doesn’t work, I’ll wait to hear from you.


    <?php

    require_once('wp-load.php');

    /** find the expired transients from the wp_options table of a single blog and deletes them */
    function delete_expired_db_transients() {
    global $wpdb;
    $deletescount = 0;
    $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};");

    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 'No expired transients found.';
    }

    return $deletescount;
    }

    /** find the expired site (global) transients from wp_sitemeta (multisite) or wp_options (standalone) table and deletes them */

    function delete_expired_db_site_transients() {
    global $wpdb;
    $deletescount = 0;
    $time = isset($_SERVER['REQUEST_TIME']) ? (int) $_SERVER['REQUEST_TIME'] : time();

    if ( is_multisite() ) {
    $site_transients_table = $wpdb->sitemeta;
    $keycol = 'meta_key';
    $valcol = 'meta_value';
    } else {
    $site_transients_table = $wpdb->options;
    $keycol = 'option_name';
    $valcol = 'option_value';
    }

    $expired = $wpdb->get_col("SELECT $keycol FROM $site_transients_table WHERE $keycol LIKE '_site_transient_timeout_%' AND $valcol < {$time};");

    if (count($expired) > 0) {
    foreach ($expired as $transient) {
    echo '<ol>';
    $key = str_replace('_site_transient_timeout_', '', $transient);
    if (delete_site_transient($key)) {
    echo '<li>Deleted:' . $key . '</li>';
    $deletescount = $deletescount + 1;
    }
    echo '</ol>';
    }
    } else {
    echo 'No expired transients found.';
    }

    return $deletescount;
    }

    /** Delete transients using functions defined above */

    /* site (global) transients */
    echo '<h1>Expired site transients deletion begins </h1>';
    $deletescount = delete_expired_db_site_transients();
    echo '<p>Expired site transients deletion complete.<br/> Total ' . $deletescount . ' expired site transients were removed.</p>';

    /* blog (local) transients */
    echo '<h1>Expired transients deletion begins</h1>';
    $deletescount = 0;

    if ( is_multisite() ) {
    $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) {
    echo '<li>Blog id: ' . $blog_id . ' Begin delete expired transients from ' . $wpdb->options;
    switch_to_blog($blog_id);
    $deletescount += delete_expired_db_transients();
    echo '</li>';
    }
    echo '</ul>';
    } else {
    $deletescount = delete_expired_db_transients();
    }

    echo '<p>Expired transients deletion complete.<br/>Total <b>' . $deletescount . '</b> expired transients records were removed.</p>';

    ?>

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s