MySQL query INSERT INTO ... failed - The Table "x" is full

Question

I apologize if my question is incomplete or noobish, I have extremely limited knowledge of SQL but the boss wants me to figure this one out.

Environment: Webmin/Virtualmin | MySQL version 5.1.73 on CentOS 6.5

Basically, Im getting an error that the table "Email_Messages" is full. I compare the "Email_Messages" table to other tables in the database and it has a cell count of around 232K but others have cell counts higher: one has 334K.

I still have 8GB free space on disk and googling has told me that there really isnt much of a limit on how big a table can be beyond the OS limitations. It looks like the database in question is only 203mb.

Any ideas on how I can troubleshoot this further/ increase the max table size?

Thanks so much - let me know what other information I can provide.

• watery: Have a look at table size limits; I have no experience on that, but I bet it's an InnoDB tablespace issue.
• Rick James: You have not hit a max size; something else is wrong. Limits.
• Rick James: Please provide SHOW CREATE TABLE; maybe it will give a clue.

Answer 1

I don't think the issue is InnoDB table size limit at all.

I have posts about InnoDB Tables having a Table is Full Condition in the InnoDB Plumbing

• May 31, 2013 : How to solve "The table ... is full" with "innodb_file_per_table"?
• Mar 31, 2014 : mysql directory grow to 246G after one query, which failed due to table is full

My guess is that your InnoDB Log Files (ib_logfile0, ib_logfile1) are probably too small. (See my second post under CAVEAT #2)

Since you are using MySQL 5.1.73 you will have to increase innodb_log_file_size and innodb_log_buffer_size doing some manual labor. Here is how:

STEP 01 : Add these lines to my.cnf under the [mysqld] group header

[mysqld]
innodb_log_file_size=1G
innodb_log_buffer_size=64M

STEP 02: Login as root@localhost and run this command

SET GLOBAL innodb_fast_shutdown = 0;

STEP 03: Shutdown mysql

service mysql stop

STEP 04: Rename the log files

cd /var/lib/mysql
mv ib_logfile0 ib_logfile0.old
mv ib_logfile1 ib_logfile1.old

STEP 05: Start mysql

service mysql start

This will regenerate new log files at 1G each.

Please, give it a try and see if it helps !!!

• Rick James: Is the innodb_log_file_size really relevant?
• Rick James: Is it safe to blow away the iblogs with innodb_fast_shutdown = 0?
• RolandoMySQLDBA: @RickJames Yes to both. 1) InnoDB internal chokes on TEXT/BLOB with small logs. Wrote about this before (dba.stackexchange.com/a/2300/877) because I learned about it from someone's ServerFault post (serverfault.com/a/3979/69271). 2) When your disable fast shutdown, everything MVCC and its grandmother are fully purged. The logs can actually be deleted afterwards.
• RolandoMySQLDBA: @RickJames To be honest with you, I could be totally wrong on this if InnoDB is not being used. It could be a big MyISAM tmp table in /tmp.
• rainfly: @RolandoMySQLDBA the ib_logfiles are only 5mb each. Is that really the default cap?

Answer 2

My MySQL database ran out of space some months ago although it looked like there was plenty of disc space available. This was caused by MySQL (under most Linuxes) installing the database in /root which ran out of space whilst there was plenty in /home. Moving the database from /root to /home cured the problem.