Issue 

When running large-scale queries or batches on Aurora DB, a tmp full error occurs.

An error message in the format of:
error code [xxxx]; The table '/rdsdbdata/tmp/#sqlxxxx_yyyy_z' is full
may appear. Is there a way to prevent this error?

Explanation 

This error commonly occurs in MySQL 8.x versions.
https://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles_use.html

To explain the cause:

• MySQL 8.0 introduced a new storage engine called TempTable. Previously, all internal temp tables were created using the MEMORY engine. With the introduction of the TempTable engine, a new variable named internal_tmp_mem_storage_engine was also added, and its default value is TempTable. 

• The TempTable engine uses a variable named temptable_max_ram to define the maximum RAM it can use. Once this limit is reached, it starts creating memory-mapped temporary files, which consume disk space. These files are created in the /tmp directory, and if this partition becomes full, the query fails and a “table is full” error is displayed. 

Below is the related MySQL bug documentation:
[+]https://bugs.mysql.com/bug.php?id=99100

Resolution 

1. To resolve this error, it is recommended to set the internal_tmp_mem_storage_engine variable from TempTable to MEMORY.
   (Before applying this change in production, please test it in a staging environment to minimize potential issues.) 

2. You may also consider increasing the value of the temptable_max_ram variable. This dynamic variable defines the maximum amount of RAM the TempTable storage engine can occupy before switching to on-disk internal temporary tables using memory-mapped files or the InnoDB format. The default value is 1 GiB. 

When using the MEMORY storage engine, the maximum size of in-memory temp tables is defined by the tmp_table_size or max_heap_table_size values. If a memory temp table exceeds these limits, MySQL automatically converts it to an on-disk temp table. Please refer to the documentation on the following variables:

• internal_tmp_mem_storage_engine [1]

• tmp_table_size [2]

• max_heap_table_size [3]
  Additional explanation on MySQL temporary tables is available here [4].

Before switching to the MEMORY engine, ensure that your instance has sufficient available memory. This will help avoid other issues caused by memory shortages.

Temporary tables are deleted when the session ends, so you cannot view previous temp tables. However, if the error occurs again, you can run the following query to check the space used by temporary tables.

Run the following query to find internal temporary tables:

mysql> SELECT * FROM information_schema.innodb_tables WHERE name LIKE '%#%';

Run the following query to find the InnoDB system tablespace:

mysql> SELECT file_name, tablespace_name, table_name, engine, index_length, total_extents, extent_size from information_schema.files WHERE file_name LIKE '%ibdata%';

Run the following query to find the InnoDB temporary tablespace:

mysql> SELECT file_name, tablespace_name, table_name, engine, index_length, total_extents, extent_size from information_schema.files WHERE file_name LIKE '%ibtmp%';

References
[1] https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_internal_tmp_mem_storage_engine
[2] https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmp_table_size
[3] https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_heap_table_size
[4] https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html