Drupal INSERT Command Denied — Fix Large Cache Tables — Tutorials | Lhoucine Cherif

drupalmysqldatabasecachingtroubleshooting

When Drupal throws INSERT command denied to user errors, it's often because the database has exceeded its allocated size due to bloated cache and log tables.

Diagnose: Find Large Tables

sql

      SELECT table_name AS "Table",
  ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
FROM information_schema.TABLES
WHERE table_schema = 'your_database'
ORDER BY (data_length + index_length) DESC
LIMIT 20;

Common culprits:

cache_* tables (cache_default, cache_render, cache_page, etc.)
watchdog (Drupal log table)
flood
sessions

Fix: Clear Cache Tables

sql

      TRUNCATE TABLE cache_default;
TRUNCATE TABLE cache_render;
TRUNCATE TABLE cache_page;
TRUNCATE TABLE cache_discovery;
TRUNCATE TABLE cache_bootstrap;
TRUNCATE TABLE cache_config;
TRUNCATE TABLE cache_entity;
TRUNCATE TABLE cache_menu;
TRUNCATE TABLE cache_data;
TRUNCATE TABLE cache_dynamic_page_cache;

Prune the Watchdog Log

sql

      -- Delete logs older than 30 days
DELETE FROM watchdog WHERE timestamp < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY));

-- Or truncate entirely
TRUNCATE TABLE watchdog;

Automated Cron Script

Create a PHP script to run periodically and keep the database lean:

php

      <?php
$host = 'localhost';
$db   = 'your_database';
$user = 'your_user';
$pass = 'your_password';

$pdo = new PDO("mysql:host=$host;dbname=$db", $user, $pass);

// Clear all cache tables
$tables = $pdo->query("SHOW TABLES LIKE 'cache_%'")->fetchAll(PDO::FETCH_COLUMN);
foreach ($tables as $table) {
    $pdo->exec("TRUNCATE TABLE `$table`");
    echo "Cleared $table\n";
}

// Prune watchdog
$pdo->exec("DELETE FROM watchdog WHERE timestamp < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 7 DAY))");
echo "Pruned watchdog\n";

Schedule it with cron:

bash

      0 3 * * * /usr/bin/php /path/to/clear-cache.php >> /var/log/drupal-cache-cleanup.log 2>&1

Prevention

Set Database Logging limits in Drupal admin: Configuration > Development > Logging
Use Drush to clear caches regularly: drush cr
Consider switching to Syslog module instead of Database Logging for production