InnoDB “Table xx Is Full” during database alterations

Question

I am running Percona Mysql Server 5.6.14-56 on CentOS 6.3 64bit.

Server has plenty ram (96gb) and storage free. File system type: ext4

dev/sda6     ext4   1093623528  56576512 981494148   6% /mysqlstorage

I tried to make some alterations to a table with 42M records (about 12.5gb in size (about 50% of that is indexes)), and today it showed me the Table Is Full error.

I checked the loaded variables, and innodb_data_file_path = ibdata1:12M:autoextend it shows in my mysql manager.

This DB is live, and I prefer to fix this issue without (or with minimal) downtime.

file-per-table is active ibdata1 is about 3.9GB

From MYSQL LOGS:

2015-02-19 01:43:04 7fe921f4b700 InnoDB: Error: Write to file (merge) failed at offset 356515840.
InnoDB: 1048576 bytes should have been written, only 1036288 were written.
InnoDB: Operating system error number 28.
InnoDB: Check that your OS and file system support files of this size.
InnoDB: Check also that the disk is not full or a disk quota exceeded.
InnoDB: Error number 28 means 'No space left on device'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html

How would I go about doing this?

// EDIT

Since asking this question, I've learned that during this "alternation", my /tmp quickly fills up to 100%. Once it hits 100%, mysql pushes out that error msg.

Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/sda2             50395844   1745156  46090688   4% /
tmpfs                 37105808         0  37105808   0% /dev/shm
/dev/sda1               495844     37092    433152   8% /boot
/dev/sda5              4080064     73740   3799068   2% /tmp
/dev/sda6            1093623528  56576524 981494136   6% /storage

How can I safely increase the size of /tmp without any downtime?

score 2 · Accepted Answer · answered Feb 19 '15 at 08:31

2

Table alteration involves /tmp partition as intermediate storage for explicitly and implicitly created temporary tables. You have to use the tmpdir on a partition that big enough. Add the next line to the my.cnf

tmpdir = /path/to/the/mysqltmp

answered Feb 19 '15 at 08:31

Kondybas

6,964
2
20
24

Could I add this to my.cnf and 'reload' mysql instead of 'restart'? – Mr.Boon Feb 19 '15 at 08:35
Reload wasn't enough. Fixed it with a Restart. Thanks! – Mr.Boon Feb 19 '15 at 08:54
Sorry, posted an answer before you have edited the question. `tmpdir` variable is not changeable on-the-fly. – Kondybas Feb 19 '15 at 09:14

InnoDB “Table xx Is Full” during database alterations

1 Answers1