Drupal - Clean database cache tables automatically

Drupal - Clean database cache tables automatically

By Daniel
Sun, 06/04/2017 - Updated 2 weeks ago

Script and instructions to manually or automatically clean the Drupal 8.x cache tables (cache_config, cache_container, cache_data, cache_default, cache_discovery, cache_dynamic_page_cache, cache_entity, cache_menu, cache_render and cache_toolbar).

Drupal 8 has a known issue that allows database cache tables grow to the infinite (hundreds of MB or even GB) this can slow or stop automated backups and in the worst scenario, take all available storage and put the site offline.

The proposed solution is a temporary solution until Drupal team fixes this problem. I does not require the installation of any module or add-on, just a small PHP script that can be run manually, from a CRON task or from a Windows Scheduled event from the same server the page is hosted or from any other computer.

Why not a Drupal module? Because this can clean the cache tables even if the Drupal website went offline. It also can be executed remotely from your favorite task scheduler.

The PHP script to clear the cache tables

Edit your database access (db name, location, user and password) configuration in this code and then save with a PHP extension in any accessible folder in your server (Example: http://www.mywebpage/myCleanCacheScript.php). The script can run manually from the URL or be executed as an automated task (recommended).

$servername = "localhost";
$username = "your_drupal_db_username";
$password = "your_drupal_db_password";
$dbname = "your_drupal_db_name";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error ."<BR>");
//Tables to truncate
$sqltbl[0] = "cache_config";
$sqltbl[1] = "cache_container";
$sqltbl[2] = "cache_data";
$sqltbl[3] = "cache_default";
$sqltbl[4] = "cache_discovery";
$sqltbl[5] = "cache_dynamic_page_cache";
$sqltbl[6] = "cache_entity";
$sqltbl[7] = "cache_menu";
$sqltbl[8] = "cache_render";
$sqltbl[9] = "cache_toolbar";
//Run the command
    if ($conn->query("TRUNCATE ".$sqltbl[$i]) === TRUE) {
        echo "Cache table <i>". $sqltbl[$i] ."</i> cleaned!<BR>";
    } else {
        echo "Error cleaning cache table <i>".$sqltbl[$i].": ". $conn->error ."</i><BR>";

The CRON command

This command allows scheduled events to run in Linux/Unix servers. It can be run from the same server where the Drupal site is installed or from any other computer with internet access. Replace mywebpage with the path to the cache clear PHP script and then configure CRON to run this command every day, week or whenever you want to clear the cache tables.

wget -O - -q -t 1 http://www.mywebpage/myCleanCacheScript.php > /dev/null

VB Script for the Windows Task Scheduler

Replace the URL in the code below with the access path of your PHP script and save it as a Visual Basic Script (Example: runDrupalClear.vbs). Then configure Windows Task Scheduler to run this script every day, week or whenever you want to clean the cache tables.

On Error Resume Next
Dim objRequest
Set objRequest = CreateObject("Microsoft.XMLHTTP")
URL = "http://www.mywebpage/myCleanCacheScript.php"
objRequest.open "GET", URL , false
Set objRequest = Nothing