Drupal 9 502 for editors only: a session table outage

The Slack ping came at 14:07 on a Thursday. A Dutch agency we work with had a Drupal 9 site that had been running quietly for four years. Suddenly the editorial team, all twelve of them, were seeing 502 Bad Gateway every time they tried to save a node. Anonymous traffic was fine. The homepage rendered. The contact form posted. Search worked. The moment an editor logged in and tried to do anything, nginx threw 502 in their face. The cause turned out to be a session table that had silently outgrown its tablespace, but it took the better part of an hour to find.

Their lead developer had spent forty minutes restarting PHP-FPM, bumping worker counts, and reading nginx error logs. The error logs said upstream prematurely closed connection while reading response header from upstream. That is the most uninformative log line in our industry. It tells you PHP-FPM died, not why. He had also doubled memory_limit in php.ini and reloaded the pool; no change. He had restarted MySQL itself, which scared the agency lead and did nothing.

We joined the shared screen at 14:48. The site was back up an hour later. The cause was a session table that had quietly outgrown the InnoDB system tablespace on a server nobody had touched since 2022. This is the walkthrough.

The asymmetry that names the problem

The first thing we did was not look at logs. We loaded the site in an incognito window. Homepage: fine. Article page: fine. Any cached path: fine. Then we logged in as a test editor and clicked Edit on a node. 502.

That asymmetry is the most useful signal in the entire incident. Anonymous users were being served from Drupal's internal page cache or, in this case, a Varnish layer in front of nginx. Their requests never touched a writeable database session. Logged-in users always bypass the page cache by design. Every request from an authenticated user writes to the sessions table to refresh sid, timestamp, and sometimes the session blob itself.

So the rule we wrote on the whiteboard was: anything that only breaks for authenticated users is almost always a write path. Database, filesystem, or a cache that depends on a write. We had three suspects. We could rule out the filesystem because anonymous form posts (the contact page) worked, so PHP could still write to the temp dir. We could rule out user-specific caches because the failure was instant and total, not stale-data weird. That left the database write path.

What the logs actually said

Once we knew where to look, the PHP-FPM error log gave us the real signal. The site ran FPM under a dedicated pool whose error log lived at /var/log/php8.1-fpm.log, not the distro default. The lead developer had been tailing php-fpm.log from the package install path, which had been empty for two years. We tailed the right file during a failed save:

PHP Fatal error:  Uncaught PDOException: SQLSTATE[HY000]: General error:
1114 The table 'sessions' is full in /var/www/drupal/web/core/lib/Drupal/
Core/Database/Statement.php:59

Error 1114 from MySQL is not "the table has too many rows." It is "the underlying storage cannot accept another write." On InnoDB that almost always means the tablespace file has hit its configured maximum and cannot autoextend. The MySQL reference manual is unusually direct about this: the system tablespace does not shrink, and if you cap it, you can hit the cap.

The site was on MySQL 5.7 with innodb_file_per_table set to OFF, which used to be the default on some older Debian builds. That means every InnoDB table, including sessions, was living inside one shared file: /var/lib/mysql/ibdata1. We checked it:

ls -lh /var/lib/mysql/ibdata1
# -rw-r----- 1 mysql mysql 12G Jun 11 14:09 ibdata1

Then we looked at the my.cnf:

innodb_data_file_path = ibdata1:10M:autoextend:max:12288M

There it was. The tablespace was capped at 12 GB. It had reached the cap. No more writes. The sessions table happened to be the most write-heavy table on this site, so it was the first one to surface the failure, but every other table had also stopped accepting inserts. Cron was silently failing. Watchdog had stopped six days ago. Nobody had noticed because the public site read fine from the InnoDB buffer pool, which only needs read access.

Why the session table grew

The session table itself was 4.3 GB. For a site with twelve editors. That is absurd, and worth explaining.

SELECT
  table_name,
  ROUND(data_length / 1024 / 1024, 1) AS data_mb,
  ROUND(index_length / 1024 / 1024, 1) AS index_mb,
  table_rows
FROM information_schema.tables
WHERE table_schema = 'drupal'
ORDER BY data_length DESC
LIMIT 5;

Output:

+--------------------+---------+----------+------------+
| table_name         | data_mb | index_mb | table_rows |
+--------------------+---------+----------+------------+
| sessions           |  4312.0 |    180.4 |   18402231 |
| cache_render       |   904.7 |     12.1 |      44211 |
| watchdog           |   612.0 |     33.0 |    2104883 |
| cachetags          |   188.2 |      9.4 |     412009 |
| cache_dynamic_page |    91.4 |      4.0 |       9802 |
+--------------------+---------+----------+------------+

Eighteen million session rows. Drupal's default session garbage collection runs on cron through system_cron(), but on this server cron had been silently broken for months. The cause was almost embarrassingly mundane: an OS upgrade a year earlier had moved drush from /usr/local/bin/drush to a Composer-installed path under /var/www/drupal/vendor/bin/drush, but the root crontab still pointed at the old binary. Cron fired on schedule, the shell could not find the executable, the error went to /dev/null, and the entry above it was a logrotate line that always exited zero. The cron mail alias was disabled. Nothing surfaced.

So GC had not actually run in a long time. Every anonymous bot that hit a non-cached path (admin URLs, search with query strings, certain JSON:API endpoints) wrote a session row. Those rows were never cleared. They piled up by hundreds of thousands per day.

Worse, the row format was DYNAMIC and the session BLOB column was being padded with extra state by a contrib module that stored anonymous form-build IDs in $_SESSION. The average row was 230 bytes, not the 80 bytes the schema would suggest. The site was being poisoned from underneath, and the only symptom anyone could see was an upper-bound failure when the hard cap was hit. The Drupal session-handling docs assume cron is healthy. When it is not, you are running an uncapped queue with no consumer.

The fix, in two stages

We did the immediate thing first. The site needed to be up.

Stage one: drain and reclaim

TRUNCATE TABLE sessions;

TRUNCATE on InnoDB drops and recreates the table. That cleared eighteen million rows in under a second. Every logged-in user, including the editors yelling in Slack, was kicked back to the login screen. That was acceptable. The site came back instantly.

But the tablespace was still 12 GB. TRUNCATE does not return space to ibdata1 when file-per-table is off. You cannot shrink the shared tablespace in place. You have to dump, drop, and reimport, or migrate to innodb_file_per_table. We did the latter, but later, on a weekend: a full mysqldump --single-transaction --routines --triggers, a fresh data directory with innodb_file_per_table = ON, a restore, and a swap. About four hours of database downtime, which the agency took at 06:00 on a Sunday. For the Thursday afternoon, we just needed headroom.

We bought headroom by lifting the cap:

innodb_data_file_path = ibdata1:10M:autoextend

Restart MySQL. Now the file could grow past 12 GB. Not a long-term answer, but it bought us six months of runway while we planned the file-per-table migration.

Stage two: stop the bleeding

We added a cron entry that worked, pointing at the binary that actually exists:

*/15 * * * * www-data /var/www/drupal/vendor/bin/drush -r /var/www/drupal/web cron > /dev/null 2>&1

We dropped the session garbage-collection threshold in settings.php, partly to lean on PHP's own GC and partly to shorten the lifetime so the table could not grow as fast:

$settings['session_write_interval'] = 180;

ini_set('session.gc_maxlifetime', 86400);
ini_set('session.gc_probability', 1);
ini_set('session.gc_divisor', 100);

That puts session GC on PHP's own probabilistic schedule (1 in 100 requests triggers a sweep) on top of Drupal's cron. Belt and braces.

We also added a Telegraf check on information_schema.tables so the next time sessions crossed 500 MB we would get paged before it crossed 4 GB. The check is one line of SQL feeding a Grafana panel. It is not glamorous and it would have prevented the entire incident.

The held-breath problem

The hardest part of the incident was not the diagnosis. It was the moment, at 14:55, where we ran TRUNCATE TABLE sessions on a production database and held our breath. We had no backup of that table from earlier in the day. The agency's nightly mysqldump was twenty hours old. If TRUNCATE had locked something it should not have, or if a row in sessions had been load-bearing for a third-party module we did not know about (it happens; some contrib modules use the session table as a scratchpad), we would have rolled back twenty hours of CMS work.

This is the part of legacy site work that rarely gets written about. The diagnosis is bounded. You can read the MySQL manual. You can grep the codebase. The destructive write, however, is unbounded. You are operating on a system you did not build, with implicit dependencies you cannot see. Senior engineers learn to be very calm and very paranoid at the same time. The agency lead, who had been doing Drupal since version 6, was visibly relieved when the editors started reporting working saves at 14:58. We were too.

When we built Pier we ran into this exact thing on enough customer sites that we made it a primary feature: every database write goes into a version history you can roll back with one click, and the MySQL editor keeps a per-table snapshot of anything you mutated in the last seven days. The afternoon would have been the same diagnosis, but the TRUNCATE would have been a button with an Undo next to it, not a held breath.

The smallest thing you can do today

Open a shell to your largest legacy Drupal box. Run:

SELECT table_name, ROUND(data_length/1024/1024, 1) AS mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY data_length DESC LIMIT 10;

If sessions, watchdog, cache_render, or any cache_* table is in the hundreds of megabytes, you have homework. If ibdata1 is over half its cap, you have a 14:07 Slack ping in your future. Find out before it finds you.