Drupal - Clean database cache tables automatically

Drupal - Clean database cache tables automatically

By Daniel
Sun, 06/04/2017 - Updated 5 months ago
0 comments

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).

<?php
/* =============================================================
/   * Remote Drupal Cache Cleaner
	* Version 1.0 (06/05/2017)
	* 
	* Developed by Daniel Brooke Peig - daniel@danbp.org
	* http://www.danbp.org
	* Copyright 2017 - Daniel Brooke Peig
	*
	* This software is distributed under the MIT License.
	* Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
	*
	* The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
	*
	* THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
	*
	*
/* =============================================================*/
 
$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
for($i=0;$i<10;$i++){
    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>";
    }
}
 
$conn->close();
?> 

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
Dim URL
 
Set objRequest = CreateObject("Microsoft.XMLHTTP")
URL = "http://www.mywebpage/myCleanCacheScript.php"
 
objRequest.open "GET", URL , false
objRequest.Send
Set objRequest = Nothing