Databases work as the storage for many Web applications. Maintaining these applications involve frequent export or import of data. Unfortunately, SQL server can report errors during this import/export process.
One such error is “error 1114 (hy000): the table is full.“ The exact reason for the error can be disk space shortage or wrong database server settings.
At Bobcares, we often get requests from customers to fix database errors as part of our Outsourced Technical Support Services.
Today, we’ll see the causes for “error 1114 (hy000): the table is full” and how our Support Engineers fix them.
Where do we see table full error?
Firstly, let’s take a look at the typical scenarios where we see the error “1114 (hy000): the table is full”.
This error primarily happens in the process of exporting and importing sql files into databases. It can be either via utilities like phpMyAdmin or even via command line.
Recently, a customer reported this error when he was trying to import a database via phpMyAdmin. The error said:
ERROR 1114 (HY000) at line 12345: The table 'abc' is full.
Surprisingly, the table it was complaining about was empty and contained no rows. Therefore, the natural question comes:
Why then table full error?
[Do you know that proactive server management can reduce MySQL errors drastically? Just signup with us and we’ll take care of your servers 24×7]
What causes “error 1114 (hy000): the table is full”?
Usually, the description for the error is often misleading as it says database table being full. But, the actual reason for the error may vary.
Let’s now see the typical causes for the error “1114 (hy000): the table is full.”
1. Disk Full
From our experience in managing databases, our Dedicated Engineers often see the table full error due to disk full issues. If a server partition or disk has used up all the space and MySQL still attempts to insert data into the table, it will fail with error 1114.
Similarly, this error can also happen during backup of large databases too. Here, the backup process create large files and can cause space constraints in the disk. Backup file along with original database will result in doubling the size required for the table.
2. innodb_data_file_path limits
When the disk space of the server is all okay, and still you get error 1114 (hy000): the table is full, it means the problem will be with the Database server configuration settings.
For instance, on a database server with storage engine set as InnoDB , the parameter innodb_data_file_path often cause this error.
When the innodb_data_file_path in the my.cnf file is set as per the entry below, the ibdata1 file can grow only up to a maximum size of 512M.
innodb_data_file_path = ibdata1:10M:autoextend:max:512M
And, when the file size grows over this limit, it ends up in the error 1114 (hy000): the table is full.
[Are you getting error 1114 (hy000): the table is full? Leave it for us, we are here to help you.]
How to fix “error 1114 (hy000): the table is full”?
So far, we saw the possible reasons for the error 1114. Now, let’s take a look on how our Dedicated Engineers resolve this and make database server working.
1. Fix disk space
First and foremost, we check the disk usage of the server using the command:
df -h
This would show up the disk that contains the least free space. Lack of free space on the disks can even stop the MySQL server. That’s why, our Support Engineers quickly try to clear out some disk space by removing unwanted backup files, log files and so on.
Additionally, to avoid problems with database restore, we always ensure enough free space in the partition that holds MySQL data directory. This applies to the /tmp partition too where MySQL store the temporary files.
2. Fix SQL server settings
Further, we fix the Database server settings. This involves setting the right value for the MySQL variables in the configuration file at /etc/my.cnf.
For instance, our Dedicated Engineers often do not put a maximum limit cap for ibdata1 file by adding the following entry in MySQL configuration.
innodb_data_file_path = ibdata1:10M:autoextend
Similarly, we do an analysis of the MySQL database usage and set the tmp_table_size, max_heap_table_size in the my.cnf file.
3. Recreating indexes
Indexes in databases helps SQL server to find the exact row or rows associated with the key values quickly and efficiently. Again, from our experience, when importing databases via phpmyAdmin, recreating the indexes at a different point can solve the table full error.
Conclusion
In short, error 1114 (hy000): the table is full happens mainly due to server running out of disk space or wrong MySQL configuration limits. Today, we saw the top causes for the error and how our Support Engineers solve them in live servers.
0 Comments