How to Fix MySQL ERROR 1114 (HY000): The table 'session_cache' is full

Understanding the 'Table is Full' Error

Seeing ERROR 1114 (HY000): The table 'session_cache' is full is a classic "out of gas" moment for your database. Unlike a syntax error or a permission issue, this is a hard resource limit. MySQL is telling you it has hit a ceiling—either on your physical hardware or within a specific configuration file.

This usually strikes during heavy INSERT operations or when complex queries generate massive internal temporary tables. The fix depends entirely on which engine is powering your table: InnoDB or MEMORY.

Identifying the Table Engine

Before changing any settings, confirm which storage engine you are dealing with. Run this in your MySQL shell:

SHOW TABLE STATUS WHERE Name = 'session_cache';

Check the Engine column. If it is InnoDB, you have likely run out of disk space or hit a tablespace cap. If it is MEMORY, you have exhausted your allocated RAM.

Scenario 1: Fixing InnoDB Tables

When InnoDB hits this wall, it almost always means the database cannot write another byte to the storage drive.

Step 1: Inspect Physical Storage

A full hard drive is the most frequent culprit. Check your disk usage immediately from the Linux terminal:

df -h

If your data partition (often /var/lib/mysql) is at 98% or 100%, you need to purge files. Look for massive slow query logs or abandoned backups that are eating up space. For example, a 50GB slow_query.log can easily crash a small server:

sudo du -sh /var/log/*
sudo find /var/lib/mysql -name "*.log" -size +100M

Step 2: Expand InnoDB Tablespace Limits

What if you have 200GB of free space but still see the error? Your my.cnf (or my.ini) might have a hard cap on the data file size. Look for the innodb_data_file_path directive.

You might find a line that limits growth to a specific size, like 2GB:

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

Once ibdata1 hits 2000MB, MySQL stops writing. To fix this, increase the limit to something larger (like 10G) or remove the maximum limit entirely to let it grow with your disk:

# Remove the :max:2000M suffix to allow unlimited growth
innodb_data_file_path = ibdata1:10M:autoextend

Note: You must restart the MySQL service to apply these changes.

Step 3: Clear the Temporary Directory

MySQL often uses /tmp for large sort operations. If /tmp is mounted on a tiny 512MB partition, complex queries will fail with Error 1114. You can redirect these temporary files to a larger partition by editing your config:

[mysqld]
tmpdir = /home/mysql_tmp

Scenario 2: Fixing MEMORY Tables

MEMORY tables live entirely in RAM. They don't care how much SSD space you have; they only care about their pre-allocated memory budget.

Step 1: Check RAM Limits

Query your current heap and temporary table limits:

SHOW VARIABLES LIKE 'max_heap_table_size';
SHOW VARIABLES LIKE 'tmp_table_size';

Step 2: Increase the Memory Budget

If your session_cache table has grown too large for its 16MB default, you can bump it up to 512MB or 1GB dynamically:

SET GLOBAL tmp_table_size = 536870912; -- 512MB
SET GLOBAL max_heap_table_size = 536870912; -- 512MB

To make this persist after a reboot, add these lines to the [mysqld] section of my.cnf:

[mysqld]
tmp_table_size = 512M
max_heap_table_size = 512M

Crucial: Existing MEMORY tables don't automatically resize when you change these variables. You must "refresh" the table for the new limit to take effect:

ALTER TABLE session_cache ENGINE=MEMORY;

Verifying the Solution

After applying your changes, verify the fix with these three checks:

Retry the write: Attempt the INSERT or UPDATE that failed earlier.
Watch growth: Use SHOW TABLE STATUS LIKE 'session_cache'; to ensure Data_length is now increasing beyond the old limit.
Monitor system health: Run htop to ensure you aren't starving the OS of RAM after increasing the MySQL heap limits.

Prevention Strategy

Use File-Per-Table: Enable innodb_file_per_table. This prevents ibdata1 from becoming a giant, unmanageable blob.
Automate Alerts: Set up a cron job or monitoring tool (like Zabbix) to ping you when disk usage hits 85%.
Purge Sessions: For tables like session_cache, run a cleanup script daily: DELETE FROM session_cache WHERE expires_at < NOW();.
Swap to InnoDB: If a MEMORY table frequently exceeds your RAM, convert it to InnoDB. With modern NVMe SSDs, the performance hit is negligible compared to the stability of disk-backed storage.