A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sql161_17a011_a' is fullShort answer
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114. Raise temptable_max_ram and temptable_max_mmap in the parameter group, keep the combined size below local storage, and size the instance for the workload.
Fix it fast
1Find out which cause you have
Confirm which instance ran the query and read the current limits. Writers fall back to on-disk InnoDB temporary tables; readers cannot, which is why the error appears on readers first. The last two values are in bytes.
SELECT @@innodb_read_only, @@aurora_version, @@temptable_max_ram, @@temptable_max_mmap;2Match what you see, then jump to its fix
- 1
@@innodb_read_only is 1 and a large GROUP BY, sort, CTE or UNION failsThe shared TempTable pool is full on a reader instanceFix - 2
aurora_tmptable_enable_per_table_limit is ON and a single query fails on a readerThe per-table limit is switched on and tmp_table_size is smallFix - 3
The error stays after you raised the limits to the sensible maximum, and the FreeLocalStorage metric drops to near zero during the queryThe instance class has too little local storage for the temporary dataFix
- 1
What does the error look like?
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sql161_17a011_a' is fullERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
Cause 1 of 3
The shared TempTable pool is full on a reader instance
1Check
TempTable keeps data in RAM up to temptable_max_ram, then overflows into memory-mapped files on local storage up to temptable_max_mmap, which defaults to 1 GiB. When that global limit is reached, a writer converts the table to InnoDB on disk and a reader ends the query with 1114.
SELECT event_name, current_alloc, high_alloc
FROM sys.memory_global_by_current_bytes WHERE event_name LIKE 'memory/temptable/%';2Fix
Set both parameters in the DB parameter group so they fit your biggest queries. Example from the docs: 2 GiB in memory and 20 GiB overflow means temptable_max_ram 2147483648 and temptable_max_mmap 21474836480. A common start for the overflow value is 80 percent of the instance's local storage. Do not set temptable_max_ram too high, or the instance can run out of memory.
# DB instance parameter group values (bytes)
temptable_max_ram = 2147483648
temptable_max_mmap = 214748364803Verify
Run the failing query again on the reader and watch the same sys view.
SELECT @@temptable_max_ram, @@temptable_max_mmap;Confirmed by docs.aws.amazon.com
Cause 2 of 3
The per-table limit is switched on and tmp_table_size is small
1Check
From Aurora MySQL 3.04, when aurora_tmptable_enable_per_table_limit is ON, tmp_table_size caps each TempTable table. A reader that hits it ends the query with 1114 even though the global pool still has room. The docs also say raising the global values does not help in this mode.
SELECT @@aurora_tmptable_enable_per_table_limit, @@tmp_table_size;2Fix
Raise tmp_table_size in the parameter group, or set aurora_tmptable_enable_per_table_limit back to OFF (its default) so only the global limits apply.
# DB instance parameter group
aurora_tmptable_enable_per_table_limit = 03Verify
Repeat the query on the reader.
SELECT @@aurora_tmptable_enable_per_table_limit;Confirmed by docs.aws.amazon.com
Cause 3 of 3
The instance class has too little local storage for the temporary data
1Check
Overflow files live on the instance's local storage, which depends on the instance class: the docs list 32 GiB for a large and 160 GiB for a 2xlarge in the r6g family, and say the real amount is a little lower. Watch the FreeLocalStorage CloudWatch metric for the instance while the query runs.
# RDS console > the instance > Monitoring > FreeLocalStorage, while the query runs2Fix
Keep temptable_max_mmap below the local storage size, and move the reporting workload to an instance class with more local storage. Rewriting the query to sort or group less data helps as well.
aws rds modify-db-instance --db-instance-identifier my-reader \
--db-instance-class db.r6g.2xlarge --apply-immediately3Verify
Run the heaviest query again and check that FreeLocalStorage stays above zero.
SELECT @@temptable_max_mmap;Confirmed by docs.aws.amazon.com
How do I stop it coming back?
- Send long reporting queries to a dedicated reader, and size that reader for its local storage.
- Alert on FreeLocalStorage so you see the pressure before a query fails.
- Add an index or a narrower SELECT list to queries that sort or group big result sets.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fixDatabases
DBeaver cannot verify the MySQL server certificate
unable to find valid certification path to requested target
DBeaver's MySQL driver is checking the server certificate and Java does not trust the CA that signed it.
2 causes, each with a fixDatabases
Django cannot load MySQLdb and asks whether mysqlclient is installed
Error loading MySQLdb module: Did you install mysqlclient or MySQL-python?
Django's MySQL backend imports MySQLdb, which is provided by the mysqlclient package.
3 causes, each with a fix