How can I troubleshoot the error "MySQL HA_ERR_RECORD_FILE_FULL" when using Amazon RDS for MySQL?

Short description

This error most often occurs on read replicas that cause the replication to fail, or when the table that the data is written to is full. This error can also have one of the following causes:

1. The table that is sending the error message is a MEMORY engine table, and the table reached its maximum size.
2. The table is a MyISAM engine table, and the table reached the maximum pointer size.
3. The table uses the InnoDB engine, and the InnoDB tablespace file is full.
4. The Amazon RDS DB instance has run out of disk space.
5. The table has reached the file size limit.

For more information about the storage engines that Amazon RDS supports, see supported storage engines for MySQL on Amazon RDS.

Resolution

MEMORY engine table reached its maximum size

Confirm that the MEMORY engine table has reached its maximum size by running a command similar to the following and checking the engine value:

mysql> show table status from database_name like 'table_name'\G

If the value of the data_length variable in the output is greater than the value of the max_data_length variable, then the table has reached its maximum size. This is the cause of the error message.

You can resolve this error by increasing the size of the max_heap_table_size parameter in the custom parameter group associated with your DB instance. For more information, see creating a DB parameter group. Be sure not to exceed the DB instance class memory limit that is based on the DB instance class that you use. For more information about the memory available for your DB instance, see Amazon RDS instance types.

MyISAM engine table reached the maximum pointer size

Confirm that the MyISAM engine table has reached the maximum pointer size by running a command similar to the following and by checking the engine value:

mysql> show table status from database_name like 'table_name'\G

You can resolve this error by using the ALTER TABLE command to alter the existing table's maximum size:

mysql> ALTER TABLE tbl_name MAX_ROWS=1000000000 AVG_ROW_LENGTH=nnn;

Optionally, you can change the default size limit for all MyISAM tables. Then, set the myisam_data_pointer_size parameter in your custom DB parameter group to a higher value.

InnoDB tablespace file is full

The maximum tablespace size for an InnoDB table is four billion pages (16 TB).

You can partition tables into multiple tablespace files for tables larger than 1 TB. You can use different types of partitioning, such as RANGE, LIST, and HASH, to partition your table based on your use case. For example, you can truncate the old data based on the year you created it, or you can create separate partitions for each year. For more information, see MySQL file size limits and the MySQL documentation for partitioning.

Important: Thoroughly test the impact that partitioning might have on your application before implementing the changes in a production environment.

You can convert an existing table to a partitioned table by using an alter table statement similar to the following:

ALTER TABLE table_name 
PARTITION BY HASH(id)
PARTITIONS 8;

Note: Converting an existing table to a partitioned table doesn't recover space allocated for the InnoDB tablespace. To recover the InnoDB space, see how do I resolve problems with my Amazon RDS MySQL DB instance that is using more storage than expected?

The Amazon RDS DB instance is out of disk space

If the Amazon RDS DB instance is in the STORAGE_FULL state, you receive the HA_ERR_RECORD_FILE_FULL error. To resolve this error, add more storage space to your DB instance. For more information, see Amazon RDS DB instance running out of storage.

Optionally, you can monitor the available storage space for the DB instance using the Amazon CloudWatch FreeStorageSpace metric. For more information, see the overview of monitoring Amazon RDS. You can also subscribe to the low storage Amazon RDS event notification so that you're notified when your DB instance consumes more than 90% of its allocated storage. For more information, see Amazon RDS event categories and event messages.

The table has reached the file size limit

Note: Some existing DB instances have a lower limit. For example, MySQL DB instances created before April 2014 have a file and table size limit of 2 TB. This 2 TB file size limit also applies to DB instances or read replicas created from DB snapshots taken before April 2014, regardless of when the DB instance was created.

If you have a DB instance that has a lower size limit, you can take a MySQL dump of your data by using mysqldump. Then, import the data into a new DB instance that has a higher limit.

Related information

How do I resolve problems that occur when Amazon RDS DB instances run out of storage?