2

I'm having some problems with my mysql 5.6 server (community edition) on debian.

I have one table around 4,5 GB. When i'm trying to alter this table to add one column (TINYINT with default 0 value) ERROR 1114 occurs, as follows:

ERROR 1114 (HY000): The table 'some table' is full

I've read this (and other) questions but I couldn't find an answer that suits my case ERROR 1114 (HY000): The table is full

I have innodb_file_per_table parameter enabled. I also have around 50GB free on hdd that mysql data and tmp dir is located.

Inserting new data to this table works just fine but altering it just do not work.

My MySQL dirs are located on the same disk as the whole system. MySQL's tmpdir is located in /dev/shm. Can this dir be limited somehow for mysql processes?

Community
  • 1
  • 1
Tom
  • 23
  • 4
  • not entirely duplicat because in that question I have innodb_file_per_table param enabled from the beginning and I have free space on HDD – Tom Jul 01 '14 at 12:09
  • possible [solution](http://stackoverflow.com/questions/21850287/error-1114-hy000-the-table-xxx-is-full)? – Jens Jul 01 '14 at 12:18
  • I'm not a linux master but all my mysql dirs are located on the same disk as the whole system and mysql tmpdir which is located in /dev/shm. Can this dir be limited somehow for mysql processes? – Tom Jul 01 '14 at 12:23

1 Answers1

3

It's extremely unlikely that MySQL can function correctly with a temp directory in shared memory. In any case it's a very wasteful use of shared memory, an expensive resource.

MySQL uses the temp directory to make a copy of a table when you try to ALTER the table. You have to do that to add a column. But a /dev/shm tempdir can't possibly be bigger than RAM on your system. So your table doesn't fit in the tempdir. So, MySQL protests that your table is 'full' -- which means it's too big.

Follow the directions in this post, and put your database daemon's temp directory on a hard drive with lots of space.

ERROR 1114 (HY000): The table 'XXX' is full

Community
  • 1
  • 1
O. Jones
  • 103,626
  • 17
  • 118
  • 172