How to fix ERROR 1114 (HY000) in Aurora MySQL - TempTable and disk usage explained

Nana Zedginidze

ā€” 2 min read
Share

šŸŽÆ Summary

If you are running analytical queries on Aurora MySQL and encounter:

ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sql...' is full

This is not a table issue ā€” it is a TempTable and disk usage limitation.

In this case, what started as a reporting query on a Reader endpoint escalated into a production risk after the workload unexpectedly shifted to the Writer node.

šŸ§  What ERROR 1114 really means

This error is often misunderstood.

šŸ‘‰ It does NOT mean your table is full.

šŸ‘‰ It means:

An internal temporary table used during query execution has exceeded available disk space.

In Aurora, these temporary tables are stored under:

/rdsdbdata/tmp/

āš ļø Why this happens in Aurora MySQL

In this case, the primary trigger was a poorly optimized query.

The query:

  • Did not effectively use indexes
  • Performed full table scans
  • Operated on a continuously growing dataset

How it leads to failure

  • Temporary tables are created in memory
  • Once memory limits are exceeded ā†’ spill to disk
  • Disk space is limited
  • When disk is exhausted ā†’ query fails

šŸ“Š Monitoring signal ā€” FreeLocalStorage

One of the earliest indicators during this incident was a drop in FreeLocalStorage:

FreeLocalStorage ā€” local storage running low

šŸ§  What FreeLocalStorage means

FreeLocalStorage represents the remaining available disk space on the database instance.

This includes:

  • Temporary tables (/rdsdbdata/tmp/)
  • TempTable spill-to-disk operations
  • Internal database processes

āš ļø Why this matters

Under heavy analytical workloads:

  • Large aggregations generate TempTables
  • Memory is exceeded ā†’ data spills to disk
  • Disk usage increases rapidly
  • FreeLocalStorage drops

šŸ” Relationship with ERROR 1114

TempTable spill to disk
ā†’ temporary storage grows
ā†’ FreeLocalStorage drops
ā†’ disk exhausted
ā†’ ERROR 1114

āš ļø Can this cause failover?

Not directly ā€” but it can lead to failover conditions.

When local storage becomes critically low:

  • Queries start failing
  • Internal operations may be affected
  • Instance health degrades
  • Aurora may trigger failover or restart to protect the cluster

In practice:

Disk pressure
ā†’ query failures
ā†’ degraded instance health
ā†’ failover or restart

šŸ§  Key insight

FreeLocalStorage is not just a storage metric ā€” it is a stability signal for the entire instance.

āš ļø TempTable behavior on Aurora Readers

In this case, the query was intended to run on a Reader endpoint.

During investigation, it was confirmed:

  • TempTables use both in-memory and disk-backed storage
  • Disk usage grows rapidly under heavy queries

šŸ” Reader ā†’ Writer shift (critical behavior)

Despite using a Reader endpoint, the workload did not remain isolated.

What happened

  • Query executed on Reader
  • TempTable exceeded limits ā†’ disk filled
  • Query failed with ERROR 1114
  • Application reconnected
  • Connection was re-established via cluster endpoint
  • Query started executing on the Writer node

šŸ‘‰ Important Aurora behavior:

  • Cluster endpoint ā†’ always routes to Writer
  • Reader endpoint ā†’ routes to read replicas

šŸšØ Why this is dangerous

Heavy analytical query
ā†’ fails on Reader
ā†’ reconnect happens
ā†’ routed via cluster endpoint
ā†’ runs on Writer
ā†’ impacts production workload

šŸ‘‰ A reporting query became a production stability risk

šŸ› ļø Recommendations

āœ… Optimize queries

  • Ensure proper index usage
  • Avoid full table scans
  • Limit dataset size

āœ… Isolate analytical workloads

  • Use Reader endpoint explicitly
  • Prevent fallback to cluster endpoint
  • Separate BI workloads from OLTP systems