global $conf;
$result = db_query("SHOW TABLES LIKE 'cache%'");
while ($table_name = db_result($result)) {
  if ($table_name == 'cache_form' || (!empty($conf['memcache_bins'][$table_name]) && $conf['memcache_bins'][$table_name] == 'database')) {
    echo t('%name NOT truncated.', array('%name' => $table_name)) . "<br>\n";
    continue;
  }
  db_query("TRUNCATE TABLE %s", $table_name);
  echo t('%name truncated.', array('%name' => $table_name)) . "<br>\n";
}

Want to reduce the size of our database backups. Data is still in the db tables even after turning on memcache.

CommentFileSizeAuthor
#3 memcache-truncate-cache-1507640-3.patch789 bytesmarkpavlitski

Comments

doublejosh’s picture

doublejosh commented
Title: Code to clear the cache tables in DB » Add utility to clear the cache tables in DB
Category: feature » task

Would be very nice to have a utility button to "Clear Leftover Database Cache" within admin settings.

Personally, was having problems with database backups on local dev environments, and it took a while to realize why. Assume there are plenty of other weird consequences like this when moving between environments. #1896142: PHP Fatal error: Unsupported operand types

Mikeytown2's point about reducing database backup size is also certainly a valid reason.

BTW: Here's how to do this from the command line...
mysql -uUSER -pPASS --execute="SELECT concat('TRUNCATE TABLE ', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'DB_NAME' AND TABLE_NAME LIKE 'cache%'" | sed 1d | mysql -uUSER -pPASS DB_NAME

Change: USER, PASS, and DB_NAME

doublejosh’s picture

doublejosh commented
Version: 6.x-1.x-dev » 7.x-1.x-dev
Category: task » feature
markpavlitski’s picture

markpavlitski commented
Title: Add utility to clear the cache tables in DB » Code to clear the cache tables in DB
Category: task » feature
Status: Active » Needs review
StatusFileSize
new memcache-truncate-cache-1507640-3.patch789 bytes

I can't see this feature making it into memcache, however this patch adds a note to README.txt telling users how to clear these tables manually.

doublejosh’s picture

doublejosh commented

At least it's documented :)

Might mention the technique for clearing all cache tables at once with one command. Each person who enables Memcache on an existing site will have to go through this step, and I assume most sites don't start out with in-memory caching.

doublejosh’s picture

doublejosh commented
Issue summary: View changes

Updated issue summary.

jeremy’s picture

jeremy commented
Issue summary: View changes
Status: Needs review » Needs work

I wouldn't be opposed to seeing this go in as a drush command, together with functionality like #1993170: Add Drush command to retrieve Memcached values. It'll land after 7.x-1.1 is released.

jeremy’s picture

jeremy commented
Status: Needs work » Closed (won't fix)

Not really a memcache feature, closing.