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

4 minute read
0

When I write to an Amazon Relational Database Service (Amazon RDS) for MySQL DB instance or read replicas, I receive an error. On the Amazon RDS for MySQL DB instance, I get the following: "ERROR 1114 (HY000): The table 'Table Name' is full." On read replicas, in the Last_Error field of show slave status (Amazon RDS for MySQL 5.7) or show replica status (Amazon RDS for MySQL 8.0), I get the following: “Error_code: 1114; handler error HA_ERR_RECORD_FILE_FULL”

Short description

These errors occur on read replicas that cause the replication to fail, or when the table that the data is written to is full. The following issues also cause this error:

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

For more information, see Supported storage engines for MySQL on Amazon RDS.

Resolution

The MEMORY engine table reached its maximum size

To confirm that the MEMORY engine table reached its maximum size, run a command similar to the following example:

mysql> show table status from database\_name like <YourTableName>\\G

If the value of data_length in the output is larger than the value of max_data_length, then the table reached its maximum size.

To resolve this error, increase 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. Don't exceed the DB instance class memory limit that's based on the DB instance class that you use.

The MyISAM engine table reached the maximum pointer size

To confirm that the MyISAM engine table reached the maximum pointer size, run a command similar to the following:

mysql> show table status from database\_name like <YourTableName>\\G

To resolve this error, use 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.

The InnoDB tablespace file is full

The maximum tablespace size for an InnoDB table is four billion pages (16 TB). You can partition tables in to multiple tablespace files for tables larger than 1 TB. Partition your table by RANGE, LIST, and HASH, based on your use case. For example, truncate the old data based on the year you created it, or create separate partitions for each year. For more information, see MySQL file size limits in Amazon RDS and the MySQL documentation for partitioning.

Important: Before you implement changes in a production environment, thoroughly test the effects that partitioning might have on your application.

To convert an existing table to a partitioned table, use an ALTER TABLE statement similar to the following example:

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

Note: When you convert an existing table to a partitioned table, you don't recover the space that's allocated for the InnoDB tablespace. To recover the InnoDB space, see Why is my Amazon RDS for MySQL DB instance 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, then you receive the "HA_ERR_RECORD_FILE_FULL" error. To resolve this error, add more storage space to your DB instance.

Optionally, you can use the Amazon CloudWatch FreeStorageSpace metric to monitor the available storage space. You can also subscribe to the low storage Amazon RDS event notification. This notifies you when your DB instance consumes more than 90% of its allocated storage.

The table reached the file size limit

Note: Some existing DB instances have a lower limit. For example, MySQL DB instances that are 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. The limit applies regardless of when you created the DB instance.

If you have a DB instance with a lower size limit, use mysqldump to make a MySQL dump of your data. Then, import the data into a new DB instance with a higher limit.

Related information

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