Drupal - Clean database cache tables automatically
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.1 (25/03/2019)
*
* Developed by Daniel BP - [email protected]
* http://www.danbp.org
* Copyright 2019 - Daniel BP
*
* 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_SQL_db_username";
$password = "your_SQL_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";
$sqltbl[10] = "cache_page";
//Run the command
for($i=0;$i<count($sqltbl);$i++){
if ($conn->query("TRUNCATE ".$sqltbl[$i]) === TRUE) {
echo "Limpeza concluída na tabela <i>". $sqltbl[$i] ."</i> .<BR>";
} else {
echo "Erro limpando a tabela de cache <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