MySQL RDS 1114 (HY000): The table '/rdsdbdata/tmp/#sql4c54_f_a' is full

Question

I have a MySQL RDS instance that started becoming unavailable intermittently.

At some point I managed to get the following error:

1114 (HY000): The table '/rdsdbdata/tmp/#sql4c54_f_a' is full

My instance has heaps of storage (300GB) that I can see is barely used.

Not sure how to resolve this issue.

Please help.

Answer 1

Suppose tmp storage is too small. Issue below queries (before yours) to extend it:

SET session tmp_table_size = 8*16777216; 
SET session max_heap_table_size = 8*16777216;

16777216 is a default value. Change 8 if you need. You could try with [global] instead [session] to make it available for other sessions.

Answer 2

Try this :

set global internal_tmp_mem_storage_engine=Memory 

https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html