The Usual Tech Ramblings

Argh... table full [1114]

So for the last few days I’ve been battling with our internal monitoring server. It’s running Zabbix on a Gentoo server and does quite well. As far as I could tell, it was checking fine. In fact I could see it updating results, however when I went to check graphs and what not, they all returned no data. This had me baffled.

I checked the server for space issues, certainly not a problem in this case:

Filesystem            Size  Used Avail Use% Mounted on
/dev/hda3             965M   56M  910M   6% /
udev                  490M  128K  490M   1% /dev
/dev/hda8              45G   89M   45G   1% /home
/dev/hda7              19G  1.6G   18G   9% /usr
/dev/hda6             9.4G  112M  9.3G   2% /var
/dev/hda5             495M   33M  462M   7% /tmp
/dev/md/0             150G  232M  149G   1% /data
shm                   490M     0  490M   0% /dev/shm

I had setup MySQL to store the data on /data/mysql which was 3 80GB drives setup in a RAID5 configuration. Looking at the “Latest Data” tabs in Zabbix, it showed it was polling data just fine, but looking at the history graphs, nothing. I checked the MySQL logs, nothing in there to suggest a problem, and it seemed to be updating other tables just fine. So I decide to check the Zabbix server log files. To my surprise this error was there:

Query failed:The table 'history' is full [1114]

Table full? What? There is plenty of space, in fact nearly the whole drive, how could it be full? Good old google pointed me in a rough direction. There is a couple of bugs with temporary tables, and a couple when updating two tables at once, but this wasn’t the case for me, so I started searching about. I decided to look at the mysql configuration file /etc/mysql/my.cnf. I noticed an odd line:

innodb_data_file_path = ibdata1:10M:autoextend:max:128M

Hang on…

pasiphae mysql # ls -lh ibdata1 
-rw-rw----  1 mysql mysql 128M Nov  9 12:13 ibdata1

Hey, the InnoDB has hit the maximum size. With a little more googling, I found this. Then all became clear. It appears the default installation from Gentoo puts the InnoDB tables at 128MB with an initial size of 10M which autoexpands until it hits the max. After some tinkering, I came up with the following to fix it:

innodb_data_file_path = ibdata1:128M;ibdata2:50M:autoextend

Restarted mysql and zabbix, and watched my lovely graphs return. I’ve not set a cap on the expansion, though I should consider it, maybe 10G, so it doesn’t get too happy about space stealing.

My lesson for the day. Table Full is not the same as Disk Full :)