Mysql – Restore Mysql dump to a thesql container fails with ERROR 1114 (HY000) at line 7238: The table is full

dockermy.cnfMySQLmysqldump

I am trying to restore a mysql dump of size around 18GB to other 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 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 stackoverflow, I tried each one but nothing worked.

I tried adding 'autoextend' to 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 param to 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 running has overlay filesystem of size 5GB which starts getting filled and as soon as it fills 100%, I get above error.

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

Please let me know if you need any more information.

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

Best Answer

"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.

Related Solutions

Thesqld_safe version different than thesqld

As we all know, mysqld_safe and mysqld are very different

mysqld : The database server instance daemon

mysqld_safe : Control program that examines and sets the environment for mysqld to execute. The mysqld executable is actually launched in a loop. When mysqld terminates, the mysqld_safe program will examine the return results and decide whether

mysqld terminated normally (intentional shutdown), leaves mysqld_safe
mysqld terminated abnormally (crash or kill -9 of mysqld)
- Loop back, mysqld fails on retry, leaves mysqld_safe
- Loop back, mysqld starts up, stays in the mysqld_safe loop

Why is it important to have mysqld and mysqld_safe using the same MySQL version?

Let me illustrate it this way: Percona Server sometimes has additional features in mysqld_safe for manipulating the OS. For example, I have seen numactl --interleave=all in a Percona Server mysqld_safe. If that line was not there, the mysqld for Percona Server may run into issues with memory and swapping.

The same scenario could possibly be the case for Oracle's (ugh, still hate saying that) mysqld and mysqld_safe. There could be improvements from one major release to another that would be removed if the mysqld_safe was older.

Rather than exploring the possibilities of using a old mysqld_safe and a new mysqld (or vica versa), please make your life simple and reinstall MySQL 5.5.30 from scratch.

Before doing so, please run

updatedb
locate mysqld_safe

in Linux and see if there are two lingering. If there are, get the paths straightened out. Otherwise, you may have to reinstall MySQL 5.5.30.

MySQL database drop insanely slow

I hate the checking permissions issue.

You may have to disable key checks before the DROP DATABASE

SET unique_checks = 0;
SET foreign_key_checks = 0;
SET GLOBAL innodb_stats_on_metadata = 0;
DROP DATABASE db_madeintouch;
SET GLOBAL innodb_stats_on_metadata = 1;
SET foreign_key_checks = 1;
SET unique_checks = 1;

UPDATE 2013-04-15 18:04 EDT

I just noticed you have innodb_file_per_table OFF. What gives ?

You currently have all the InnoDB data and the corresponding index sitting in a single file.
Any CREATE TABLE statement must make data dictionary updates and look for space (small but annoying in this instance)
Internal Fragmentation of ibdata1
Dropping a table means scanning the table and its indexes for availability to lock. With data and index pages possibly fragmented, this takes spindles, seek time, and latency.
See Pictorial Representation of ibdata1 to see everything that goes into ibdata1

Recommendation : Remove all Data and Index Pages from ibdata1

This will give ibdata1 a breather to handle just data dictionary and MVCC management. In addition, ibdata1 will stay rather lean and mean and can be read more quickly.

You will need to perform the InnoDB Infrastructure Cleanup. I wrote out all the steps back on October 29, 2010 in StackOverflow.

UPDATE 2013-04-22 08:10 EDT

Three suggestions

SUGGESTION 1 : I just noticed something else. You are using an ancient version of MySQL (5.0.45). You should think about upgrading to MySQL 5.6.11 as it performs significantly faster that MySQL 5.5 and way faster than MySQL 5.0.

SUGGESTION 2 : You should also go ahead and implement the InnoDB Infrastructure Cleanup.

SUGGESTION 3 : You should also check the disk itself. If the data is sitting on a RAID10 set, one of the disks may have an issues. Check the disk controller's battery as well because it can slow down disk caching and affect read performance.