MySQL The table is full on query

Question

I can't find the source of the issue on our Master/Slave replication .

Today i was updating the Master and suddenly got the following error from the slave

Error 'The table 'caching_api' is full' on query.

Query: '
ALTER TABLE `caching_api`
ADD UNIQUE INDEX `id` (`id`) USING BTREE ,
ADD INDEX `search` (`component`, `method`) USING BTREE

It's not a disk issue , the Slave is an exact replicate of the Master

And my.cnf config :

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

key_buffer              = 16M
max_allowed_packet      = 128M
thread_stack            = 192K
thread_cache_size       = 64

table_open_cache        = 3000
join_buffer_size        = 128k

# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
max_connections        = 4000
wait_timeout           = 150
interactive_timeout    = 30
innodb_buffer_pool_size = 25G
innodb_log_file_size    = 1G

innodb_buffer_pool_instances   = 10
tmp_table_size                 = 256M
max_heap_table_size            = 256M
innodb_flush_log_at_trx_commit = 2
query_cache_limit              = 64M
query_cache_size               = 256M
relay_log_space_limit          = 10G
server-id                      = 2
relay-log                      = /var/log/mysql/mysqld-relay-bin
expire_logs_days               = 1
max_binlog_size                = 100M
slave-skip-errors              = 1062,1054

[mysqldump]
quick
quote-names


[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer              = 16M

Update to questions from comments :

When i try to run the query directly on the SLAVE :

caching_api table status

Slave Disk Info

ibdata1 about 36GB

ulimit -a

@e4c5 Updated the question , we have about 10 database on that server and i only posted the one that caused the issue — Tarek, Aug 10 '16 at 09:10
your screenshot isn't much use because the table names don't show up — e4c5, Aug 10 '16 at 09:16
@e4c5 can't show the names on the public for different reasons ... do you see any problems with the data ? you can mention the row number ? — Tarek, Aug 10 '16 at 09:21
I do see two huge myisam tables. Very likely that they have reached there limites. Please see the link in my answer — e4c5, Aug 10 '16 at 09:27
They are not that big , in fact they contain (21k and 1.8k rows) , the big number you see is the max data length while the read length is about 5mb , i'll add a screenshot also — Tarek, Aug 10 '16 at 09:32
Oops sorry misread the column headers. BTW, for your information your original screenshot can be seen in your edit history — e4c5, Aug 10 '16 at 09:34
MySQL error code 1114 (ER_RECORD_FILE_FULL): The table '%-.192s' is full; May check the MYISAM tables wether it reaches the maximum size; also strange why the "Data Free" is 0bytes? the same as other MYISAM tables? — Wang Wen'an, Aug 10 '16 at 09:48
@GreenBlade i dont think the issue is from myisam , the error was on the Innodb table — Tarek, Aug 10 '16 at 10:03
@Tarek sorry for misread...... so only caching_api has this error or other innodb tables has the same error? the mysql version? the value of "innodb_file_per_table" is ON or OFF? — Wang Wen'an, Aug 11 '16 at 02:10
@GreenBlade its set to OFF and i think we had the same issue before on different tables. — Tarek, Aug 11 '16 at 04:41
You show a shot of the disk free for the the /data volume, but is that on the master or the slave? You said the error occurs on the slave, so double-check that it has enough free disk space. — Bill Karwin, Aug 11 '16 at 04:56
Also double-check that the table is InnoDB on the slave, and that you have an innodb_data_file_path with autoextend (and no `max`). Please run `show table status like `caching_api'` *on the slave* and share that information. — Bill Karwin, Aug 11 '16 at 04:58
Thanks @BillKarwin , it is t as autoextend , i've updated the question with the info you requested . — Tarek, Aug 11 '16 at 05:16
I even deleted the table and recreated it and still same issue . — Tarek, Aug 11 '16 at 05:40
@Tarek check the system limit to single file size, it may reach the size limit; "i think we had the same issue before on different tables"-- if all the other innodb meet the same error, try to set innodb_file_per_table = ON and create a table < caching_api_new> with all rows in , then try the SQL again; — Wang Wen'an, Aug 11 '16 at 06:03
i set the innodb_file_per_table = on and had the same error with caching_api_new — Tarek, Aug 11 '16 at 07:24

score 2 · Answer 1 · edited Jun 20 '20 at 09:12

2

The manual doesn't rule out the possibility of a full disk.

If a table-full error occurs, it may be that the disk is full or that the table has reached its maximum size. The effective maximum table size for MySQL databases is usually determined by operating system constraints on file sizes, not by MySQL internal limits.

But since have checked that already before posting this question. So the only other possiblility is that maximum table size has been reached.

edited Jun 20 '20 at 09:12

Community

1
1

answered Aug 10 '16 at 09:01

e4c5

52,766
11
101
134

I don't think the table size has been reached , the table data was small and so nothing close 2-4GB mentioned in the article . – Tarek Aug 10 '16 at 16:10

MySQL The table is full on query

1 Answers1