AWS RDS MySQL ERROR Code 1114 - The table '/rdsdbdata/tmp/#sql1ae5_a6_2' is full

Question

I am experiencing an issue with our MySQL (v8.0) database running on AWS RDS. When executing a specific query, after 4 minutes the query will fail with:

Error Code 1114: The table '/rdsdbdata/tmp/#sql1ae5_a6_2' is full

I've tried setting tmp_table_size to 32 Mib (up from the default of 16 MiB).

tmp_table_size = 16777216 # Default value (16 MiB)
tmp_table_size = 33554432 # New value (32 MiB)

I've also tried increasing the AWS instance type from:

db.t2.micro -> db.t2.small

Other relevant settings:

temptable_max_ram 1073741824 # Default value
temptable_max_mmap 1073741824 # Default value
temptable_use_mmap 1 # Default value (and cannot be changed for AWS instances)

How do I fix this error? What setting do I have to adjust?

This is my query:

SELECT cur.actualstart AS actual_start,
cur._regardingobjectid_value AS related_entity_id,
opt5.label AS status_reason,
cur.createdon AS created_on,
cur.activityid AS id,
cur.prefix_activestagename AS journey_stage_name,
cur.actualend AS actual_end,
opt6.label AS status,
cur._ownerid_value AS owner_user_id,
IFNULL(_prefix_predecessor_value,
        pre_helper.predecessor) AS predecessor FROM
prefix_stageduration_current cur
    LEFT JOIN
optionset_current opt1 ON (opt1.entity = 'prefix_stageduration'
    AND opt1.logicalname = 'instancetypecode'
    AND opt1.valueid = cur.instancetypecode)
    LEFT JOIN
optionset_current opt2 ON (opt2.entity = 'prefix_stageduration'
    AND opt2.logicalname = 'prioritycode'
    AND opt2.valueid = cur.prioritycode)
    LEFT JOIN
optionset_current opt3 ON (opt3.entity = 'prefix_stageduration'
    AND opt3.logicalname = 'deliveryprioritycode'
    AND opt3.valueid = cur.deliveryprioritycode)
    LEFT JOIN
optionset_current opt4 ON (opt4.entity = 'prefix_stageduration'
    AND opt4.logicalname = 'community'
    AND opt4.valueid = cur.community)
    LEFT JOIN
optionset_current opt5 ON (opt5.entity = 'prefix_stageduration'
    AND opt5.logicalname = 'statecode'
    AND opt5.valueid = cur.statecode)
    LEFT JOIN
optionset_current opt6 ON (opt6.entity = 'prefix_stageduration'
    AND opt6.logicalname = 'statuscode'
    AND opt6.valueid = cur.statuscode)
    LEFT JOIN
(SELECT DISTINCT
    a.activityid, MAX(b.activityid) AS predecessor
FROM
    (SELECT 
    c1.activityid,
        c1._regardingobjectid_value,
        MAX(c2.actualend) AS actualend
FROM
    prefix_stageduration_current c1
LEFT JOIN prefix_stageduration_current c2 ON (c1._regardingobjectid_value = c2._regardingobjectid_value
    AND c1.actualstart <= c2.actualend)
GROUP BY c1.activityid , c1._regardingobjectid_value) a
LEFT JOIN prefix_stageduration_current b ON (a._regardingobjectid_value = b._regardingobjectid_value
    AND a.actualend = b.actualend)
GROUP BY a.activityid) pre_helper ON pre_helper.activityid = cur.activityid;