Restore MySQL dump to a container fails with ERROR 1114 "The table is full"

Question

I am trying to restore a MySQL dump of size around 18GB to another MySQL server, which is running inside a container using this command:

mysql -h example.com -u user -p matomo < dump.sql

But it fails with an error:

ERROR 1114 (HY000) at line 7238: The table 'piwik_log_link_visit_action' is full

Many other small tables are copied successfully, but while coping this table it fails with above error. The size of this table is more than 2GB.

Based on different suggestions available on Stack Overflow, I tried each one but nothing worked.

I tried adding 'autoextend' to the my.cnf file:

innodb_data_file_path=ibdata1:10M:autoextend

I also tried to increase the tmp_table_size and heap_table_size by adding following parameter to the my.cnf file:

tmp_table_size=2G
max_heap_table_size=2G

Also, I made sure that the server (from where I am running the dump restore command) has enough space (more than 20GB of storage available). But nothing worked.

I tried debugging this more and found that, the docker container where MySQL is running has overlay filesystem of size 5GB which starts getting filled and as soon as it fills 100%, I get above error.

Volume mounted on the container is of more than 30GB size. I am not sure from where this overlay file system is coming in docker. Overlay is something coming from docker I guess, but not sure where I can increase its size.

I can't even go inside the overlay directory and keep deleting or freeing the space. Can anyone please help me here.

my.cnf file

[mysqladmin]
user=user1
[mysqld]
skip_name_resolve
explicit_defaults_for_timestamp
basedir=/opt/bitnami/mariadb
port=3306
tmpdir=/opt/bitnami/mariadb/tmp
socket=/opt/bitnami/mariadb/tmp/mysql.sock
pid_file=/opt/bitnami/mariadb/tmp/mysqld.pid
max_allowed_packet=256MB
bind_address=0.0.0.0
log_error=/opt/bitnami/mariadb/logs/mysqld.log
character_set_server=utf8
collation_server=utf8_general_ci
plugin_dir=/opt/bitnami/mariadb/plugin
innodb_data_file_path=ibdata1:10M:autoextend:max:10G
max_heap_table_size=2G
tmp_table_size=2G
[client]
port=3306
socket=/opt/bitnami/mariadb/tmp/mysql.sock
default_character_set=UTF8
plugin_dir=/opt/bitnami/mariadb/plugin
[manager]
port=3306
socket=/opt/bitnami/mariadb/tmp/mysql.sock
pid_file=/opt/bitnami/mariadb/tmp/mysqld.pid
!include /opt/bitnami/mariadb/conf/bitnami/my_custom.cnf

Answer 1

"Table is full" implies a disk space problem. But there are other issues:

How much RAM in your container?

tmp_table_size=2G
max_heap_table_size=2G

not more than 1% of RAM available. Anyway, those are used during complex SELECTs; they are not used during INSERTs.

dump of size around 18GB

Depending on the datatypes, etc, etc, the amount of disk needed could be a lot less or a lot more than 18GB. I would be skeptical about 30GB being enough. The dump is text; the data may be something else. For example, a 4-byte INT could take 2 bytes ("0,") in the dump or 12 bytes ("-1234567900,") in the dump. After loading into the database it is likely to take 8-12 bytes, depending on overhead. That might be a 6x expansion (2->12) or a shrinkage (12->8). (That's just one example.)

If you have integers arbitrarily in BIGINT, that 4 bytes becomes 8. Shrinking the datatypes (where appropriate) will shrink the disk space needed. Please provide SHOW CREATE TABLE; there may be some other obvious suggestions.

And what is "overlay"? It really looks like it hit a limit of 5GB.

Answer 2

The problem here has absolutely nothing to do with diskspace usage.

Back in May 2013, I answered this post : `ERROR 1114 (HY000) the table ... is full` with innodb_file_per_table set to autoextend

In my answer, I basically stated how InnoDB interprets a table full condition. It boils down to the undo log getting filled to the point it cannot support MVCC (for rollback purposes).

My solution in that old post is to create the dump with mysqldump --skip-extended-insert. Why ?

Since you are loading data into a docker container, OS resources are at a very critical premium. You cannot afford to waste it on chunking hundreds or even thousands of rows of MySQL Data into buffers and logs. Creating a mysqldump with --skip-extended-insert causes the mysqldump to generate INSERT commands one at a time. This is great for loading data with very small environments. You just have to make sure the container has a proper sized disk and you have enough patience (since the dump may take 2-10 times longer to load).

I have answered questions related to this in the DBA StackExchange over the years