MySQL Error Message : ERROR 1114 (HY000) at line 4032: The table ‘table’ is full

This is an article where the focus of the main discussion is about how to solve the error message of MySQL Database Server generated upon restoring a dump file into a single database. The error specifically shown in the title of the article which is ‘ERROR 1114 (HY000) at line 4032: The table named ‘table’ is actually full. So, the error happened at the time of restoring a single database is in progress. It is shown as follows :

root@hostname:/etc/mysql/conf.d# mysql -uroot -p mydb < /root/mydb_20170919_140100.sql
Enter password: 
ERROR 1114 (HY000) at line 4780: The table 'xx_first_table' is full
root@hostname:/etc/mysql/conf.d#

As shown in the restoring progress of the database named ‘mydb’ using the dump file named ‘mydb_20170919_140100.sql’ as located in the ‘/root/’, the process eventually stop and generated an error shown in the following highlight :

ERROR 1114 (HY000) at line 4780: The table 'xx_first_table' is full.

The progress for restoring database stop in the MySQL dump file at line 4780 at the operation on restoring the table named ‘xx_first_table’. At first, the troubleshooting step taken is just trying to look at the MySQL Database Server’s error message log file to take a deeper look on what is actually gone wrong so that an error shown. Below is the error log file :

InnoDB: End of page dump
2017-09-19T09:00:30.374051Z 4 [Note] InnoDB: Uncompressed page, stored checksum in field1 492130239, calculated checksums for field1: crc32 1232268277/3592550205, innodb 2962603457, none 3735928559, stored checksum in field2 492130239, calculated checksums for field2: crc32 1232268277/3592550205, innodb 1053660304, none 3735928559,  page LSN 0 2898285209, low 4 bytes of LSN at page end 2898285209, page number (if stored to page already) 32087, space id (if created with >= MySQL-4.1.1 and stored already) 0
InnoDB: Page may be an index page where index id is 2
2017-09-19T09:00:30.374068Z 4 [Note] InnoDB: Index 2 is `CLUST_IND` in table `SYS_COLUMNS`
2017-09-19T09:00:30.374073Z 4 [Note] InnoDB: It is also possible that your operating system has corrupted its own file cache and rebooting your computer removes the error. If the corrupt page is an index page. You can also try to fix the corruption by dumping, dropping, and reimporting the corrupt table. You can use CHECK TABLE to scan your table for corruption. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
2017-09-19T09:00:30.374078Z 4 [ERROR] [FATAL] InnoDB: Aborting because of a corrupt database page in the system tablespace. Or,  there was a failure in tagging the tablespace  as corrupt.
2017-09-19 16:00:30 0x7fe254038700  InnoDB: Assertion failure in thread 140610048853760 in file ut0ut.cc line 916
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
09:00:30 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

The error definitely start in the following line :

InnoDB: End of page dump

and it is finally started to show the clear reason in the following line :

2017-09-19T09:00:30.374078Z 4 [ERROR] [FATAL] InnoDB: Aborting because of a corrupt database page in the system tablespace. Or, there was a failure in tagging the tablespace as corrupt.

So, to solve the above error, using the available error generated, it is concluded that the InnoDB file allocated doesn’t have enough storage to contain the restored database. So, the following step is taken to solve the problem :

1. Enlarge the size of the InnoDB file used to store the data. It is specificed in MySQL Database Server’s configuration file. Usually located in /etc/mysql/my.cnf. Add the following line :

innodb_data_file_path = ibdata1:12M:autoextend

The file in the context of this article is actually located in ‘/etc/mysql/mysql.conf.d’. The most important content is shown in the following snippet code, especially in the ‘[mysqld]’ section. Just add the line above to increase automatically the size of InnoDB file which is represented by a file named ‘ibdata1’. The file ‘ibdata1’ itself normally located in ‘/var/lib/mysql’.

[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
innodb_data_file_path = ibdata1:12M:autoextend

The size specified above as the initial size can be vary and in the above context, it is started at 12 M.

2. But apparently, the above solution doesn’t fixed the problem. It is because in the end, the culprit of the problem is because the space storage of the server is exhausted. Since there is no space left, the file named ‘ibdata1’ cannot be resized into a larger unit because of the database restore process. So, the solution is to reclaim some space area so that ‘ibdata1’ file can be extended automatically because of the database restore process. Don’t forget to restart MySQL Database Service after claiming some spaces.

3. In case the step in the 2nd one also ends in vain. Don’t forget to move the InnoDB file first to another place when MySQL Database Server is inactive. Move it back again to the original location and then restart or start MySQL Database Server again. The solution is similar with the one given in the article titled ‘MySQL Error Message : ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)’ in this link. The solution given in the article is by moving InnoDB file in the inactive MySQL Database Server’s service state which the main problem also relates with InnoDB file.