0

I have a MySQL database table with 31,107,600 rows in it. It's using InnoDB. This is on a CentOS 7 system.

The table has these columns:

  • id
  • timestamp
  • mac
  • data
  • direction
  • site_id

I want to create an index, so I run this command:

ALTER TABLE MY_TABLE ADD INDEX idx_my_index (mac, site_id, timestamp);

It takes about 30 seconds, and then gives me this:

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

My MySQL is using the default /var/lib/mysql to store its data. Currently, the partition that folder is on has 12GB free, and my database is only 2.7 GB in size, so as far as I can tell, it should have more than enough space.

Why am I seeing this error? Online searches show that perhaps it is using a temporary table, and hitting restrictions on the size of the temporary table? Could it be that? Or something else? How can I confirm this?

1 Answer 1

0

While the ALTER TABLE statement is running, observe your space usage on all file systems using df. See if space on another file system, particularly a small one, is rapidly decreasing, e.g. if you have /tmp on tmpfs.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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