Aurora MySQL Error number 28 means 'No space left on device'

Question

Hello,

We have a error when run alter table to add column into MySQL Aurora table. Here is a list of errors:

1. Mysql client:

```
SQLSTATE[HY000]: General error: 1878 Temporary file write failure
```

2. And here **mysql-error-running.log:**

```
2022-02-18T14:19:06.136874Z 16 [Warning] InnoDB: 1048576 bytes should have been written. Only 237568 bytes written. Retrying for the remaining bytes.
2022-02-18T14:19:06.136904Z 16 [Warning] InnoDB: Retry attempts for writing partial data failed.
2022-02-18T14:19:06.136912Z 16 [ERROR] InnoDB: Write to file (merge)failed at offset 5473566720, 1048576 bytes should have been written, only 237568 were written. Operating system error number 28. Check that your OS and file system support files of this size. Check also that the disk is not full or a disk quota exceeded.
2022-02-18T14:19:06.136923Z 16 [ERROR] InnoDB: Error number 28 means 'No space left on device'
2022-02-18T14:19:06.136929Z 16 [Note] InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html
2022-02-18T14:19:07.835261Z 16 [Note] Aborted connection 16 to db: 'XXXX' user: 'XXXX' host: 'XX.XX.XX.XX' (Temporary file write failure.)
```

**Cluster config:**
* Table: The size of this table is ~ 100GB with the indexes.
* **RDS version:** 5.7.mysql_aurora.2.09.0
* **Instance class:** db.r5.large

If we running this command on a larger instance (db5.xlarge) succeeds but it is our understanding that storage in **MySQL Aurora** is dynamic and we don't understand what is happening.

Can we modify some value in the **PARAMETER GROUP** to solve this ?

Any other solution other than changing the instance type ?

Answer

The space which is being exhausted is the temp space which is used before data is written to the dynamic persistent storage. See here for temp space and instance type info. There is also some tips there what parameters you could tweak to use less temp storage....in the end it is possible some queries can exhaust memory (and therefore use temp space) and may need tuning into smaller queries.

If the queries can't be tuned and you do not want to use a larger instance type you could consider standard mysql rds (not aurora) where you can provision the ebs used for temp space

Aurora MySQL Error number 28 means 'No space left on device'

Relevant content