1

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.

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

2 Answers 2

Reset to default
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

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 !!!

Improve this answer
6
  • Is the innodb_log_file_size really relevant?
    – Rick James
    Commented Apr 7, 2015 at 22:14
  • Is it safe to blow away the iblogs with innodb_fast_shutdown = 0?
    – Rick James
    Commented Apr 7, 2015 at 22:14
  • @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
    Commented Apr 7, 2015 at 22:18
  • @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.
    – RolandoMySQLDBA
    Commented Apr 7, 2015 at 22:21
  • @RolandoMySQLDBA the ib_logfiles are only 5mb each. Is that really the default cap?
    – rainfly
    Commented Apr 8, 2015 at 12:29
0

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.