The table is full ошибка

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.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

Ubuntu server 15.10, php5.6, nginx 1.9.3, mysql 5.7 в виртуальной машине под Hyper-V в Windows 10

Настраиваю seo-url в opencart 2, который стоит в подкаталоге сайта на joomla 3.5 После включения seo в настройках opencart

В логе mysql появляются ошибки

[ERROR] InnoDB: Tablespace innodb_temporary ran out of space. Please add another file or use ‘autoextend’ for the last file in setting innodb_temp_data_file_path

[ERROR] /usr/sbin/mysqld: The table ‘/tmp/mysql/#sql_c76e_2’ is full [ERROR] /usr/sbin/mysqld: The table ‘/tmp/mysql/#sql_c76e_3’ is full

Сделал так

mkdir /tmp/mysql
chown mysql:mysql /tmp/mysql
service mysql restart

Права есть, но в /tmp/mysql/ ни одного файла нет.

В mysqld.cnf

Секция InnoDB

innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 5M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_buffer_pool_size = 1G
innodb_log_buffer_size = 8M
innodb_max_undo_log_size = 50M
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:512M

Если убрать ограничение до 512 М, то этот файл заполняет все свободное место за несколько минут.

innodb_data_file_path = ibdata1:10M:autoextend:max:512M

innodb_thread_concurrency = 8
innodb_buffer_pool_instances = 8
innodb_buffer_pool_dump_pct = 75
innodb_flush_log_at_trx_commit = 0
sync_binlog = 0
innodb_io_capacity = 600
innodb_io_capacity_max = 1000
innodb_read_io_threads = 32
innodb_write_io_threads = 16

innodb_fast_shutdown = 0
innodb_max_dirty_pages_pct = 0
df -h

Файл.система Размер Использовано Дост Использовано% Cмонтировано в
udev 5,0G 0 5,0G 0% /dev
tmpfs 1,0G 110M 915M 11% /run
/dev/sda2 35G 22G 12G 65% /
tmpfs 5,0G 0 5,0G 0% /dev/shm
tmpfs 5,0M 0 5,0M 0% /run/lock
tmpfs 5,0G 0 5,0G 0% /sys/fs/cgroup
/dev/sda1 511M 3,4M 508M 1% /boot/efi
tmpfs 1,0G 0 1,0G 0% /run/user/1000

FACTS

You said you are using ext4. File size limit is 16TB. Thus, Sample.ibd should not be full.

You said your innodb_data_file_path is ibdata1:10M:autoextend. Thus, the ibdata1 file itself has no cap to its size except from the OS.

Why is this message coming up at all? Notice the message is «The table … is full», not «The disk … is full». This table full condition is from a logical standpoint. Think about InnoDB. What interactions are going on ?

My guess is InnoDB is attempting to load 93GB of data as a single transaction. Where would the Table is Full message emanate from? I would look at the ibdata1, not in terms its physical size (which you already ruled out), but in terms of what transaction limits are being reached.

What is inside ibdata1 when innodb_file_per_table is enabled and you load new data into MySQL?

• Data Dictionary
• Double Write Buffer
  • Safety Net to Prevent Data Corruption
  • Helps Bypass OS for Caching
• Insert Buffer (Streamlines Changes to Secondary Indexes)
• Rollback Segments
• Undo Logs
• Click Here to see a Pictorial Representation of ibdata1

My suspicions tell me that the Undo Logs and/or Redo Logs are to blame.

What are these logs? According to the Book

Chapter 10 : «Storage Engines» Page 203 Paragraphs 3,4 say the following:

The InnoDB engine keeps two types of logs: an undo log and a redo log. The purpose of an undo log is to roll back transactions, as well as to display the older versions of the data for queries running in the transaction isolation level that requires it. The code that handles the undo log can be found in storage/innobase/buf/log/log0log.c.

The purpose of the redo log is to store the information to be used in crash recovery. It permits the recovery process to re-execute the transactions that may or may not have completed before the crash. After re-executing those transactions, the database is brought to a consistent state. The code dealing with the redo log can be found in storage/innobase/log/log0recv.c.

ANALYSIS

There are 1023 Undo Logs inside ibdata1 (See Rollback Segments and Undo Space). Since the undo logs keep copies of data as they appeared before the reload, all 1023 Undo Logs have reached its limit. From another perspective, all 1023 Undo Logs may be dedicated to the one transaction that loads the Sample table.

BUT WAIT…

You are probably saying «I am loading an empty Sample table». How are Undo Logs involved? Before the Sample table was loaded with 93GB of data, it was empty. Representing every row that did not exist must take up some housecleaning space in the Undo Logs. Filling up 1023 Undo Logs seems trivial given the amount of data pouring into ibdata1. I am not the first person to suspect this:

From the MySQL 4.1 Documentation, note Posted by Chris Calender on September 4 2009 4:25pm:

Note that in 5.0 (pre-5.0.85) and in 5.1 (pre-5.1.38), you could receive the «table is full» error for an InnoDB table if InnoDB runs out of undo slots (bug #18828).

Here is the bug report for MySQL 5.0 : http://bugs.mysql.com/bug.php?id=18828

SUGGESTIONS

When you create the mysqldump of the Sample table, please use —no-autocommit

mysqldump --no-autocommit ... mydb Sample > Sample.sql

This will put an explicit COMMIT; after every INSERT. Then, reload the table.

If this does not work (you are not going to like this), do this

mysqldump --no-autocommit --skip-extended-insert ... mydb Sample > Sample.sql

This will make each INSERT have just one row. The mysqldump will be much larger (10+ times bigger) and could take 10 to 100 times longer to reload.

In either case, this will spare the Undo Logs from being inundated.

Give it a Try !!!

UPDATE 2013-06-03 13:05 EDT

ADDITIONAL SUGGESTION

If the InnoDB system table (a.k.a ibdata1) strikes a filesize limit and Undo Logs cannot be used, you could just add another system tablespace (ibdata2).

I just encountered this situation just two days ago. I updated my old post with what I did: See Database Design — Creating Multiple databases to avoid the headache of limit on table size

In essence, you have to change innodb_data_file_path to accommodate a new system tablespace file. Let me explain how:

SCENARIO

On disk (ext3), my client’s server had the following:

[root@l*****]# ls -l ibd*
-rw-rw---- 1 s-em7-mysql s-em7-mysql     362807296 Jun  2 00:15 ibdata1
-rw-rw---- 1 s-em7-mysql s-em7-mysql 2196875759616 Jun  2 00:15 ibdata2

The setting was

innodb_data_file_path=ibdata1:346M;ibdata2:500M:autoextend:max:10240000M

Note that ibdata2 grew to 2196875759616 which is 2145386484M.

I had to embed the filesize of ibdata2 into innodb_data_file_path and add ibdata3

innodb_data_file_path=ibdata1:346M;ibdata2:2196875759616;ibdata3:10M:autoextend

When I restarted mysqld, it worked:

[root@l*****]# ls -l ibd*
-rw-rw---- 1 s-em7-mysql s-em7-mysql     362807296 Jun  3 17:02 ibdata1
-rw-rw---- 1 s-em7-mysql s-em7-mysql 2196875759616 Jun  3 17:02 ibdata2
-rw-rw---- 1 s-em7-mysql s-em7-mysql   32315015168 Jun  3 17:02 ibdata3

In 40 hours, ibdata3 grew to 31G. MySQL was once again working.