Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

UPDATE: tl;dr: The problem was MySQL uses the TMPDIR when creating indexes. And my TMPDIR was the one running out of disk space.

Original Q:

I'm trying to add an index to an InnoDB table, and getting a table is full error. I have enough disk space and the MySQL configuration has a file-per-table=1. The table data is 85GB and I assume the index will be around 20GB - 30GB and I have much more disk space than that. I'm also using ext3 so I don't feel there's any problem with the file size limit from the OS point of view.

The logged error looks like this:

140616 13:04:33  InnoDB: Error: Write to file (merge) failed at offset 3 1940914176.
InnoDB: 1048576 bytes should have been written, only 970752 were written.
InnoDB: Operating system error number 0.
InnoDB: Check that your OS and file system support files of this size.
InnoDB: Check also that the disk is not full or a disk quota exceeded.
InnoDB: Error number 0 means 'Success'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/operating-system-error-codes.html
140616 13:04:33 [ERROR] /usr/libexec/mysqld: The table 'my_table' is full

What is causing this and how can I resolve?

The create table:

`CREATE TABLE `my_table` (
 `uid_from` bigint(11) NOT NULL,
 `uid_to` bigint(11) NOT NULL,
 `counter` int(11) NOT NULL,
 `updated` date NOT NULL,
 PRIMARY KEY (`uid_to`,`uid_from`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8`

As is the Data is 87.4GB and I estimate there are around 1.5B rows.

SHOW GLOBAL VARIABLES LIKE 'tmpdir';
Variable_name   Value
tmpdir  /tmp

[root@web ~]# df -h /tmp
Filesystem      Size  Used Avail Use% Mounted on
/dev/xvda1       40G   24G   14G  64% /
share|improve this question
1  
"InnoDB: Check that your OS and file system support files of this size. InnoDB: Check also that the disk is not full or a disk quota exceeded." - have you checked that? Are you using ext2? ext3? xfs? Checked the quotas for the user? –  Phil Jun 16 at 13:18
    
@Phil Checked both. I'm using ext3 and am almost positive there isn't a problem with disk space. Hunch is it's got something to do with a log reaching his limit or something, but no idea really how to verify and fix. –  Noam Jun 16 at 13:33
    
I wonder what file "(merge)" is? –  akuzminsky Jun 16 at 14:43
    
@akuzminsky hmm I have no idea. I only ran a new index creation from PHPMyAdmin. –  Noam Jun 16 at 14:56

1 Answer 1

up vote 3 down vote accepted

Please don't fooled by the error message The table 'my_table' is full. This rare scenario has absolutely nothing to do with diskspace. This table full condition has to do with the internal plumbing of InnoDB.

First, take a look at this diagram of the InnoDB Architecture

InnoDB Architecture

Please note that the system tablespace (the file ibdata) only has 128 Rollback Segments and 1023 Rollback Slots Per Rollback Segment. This places limits on the size of a transaction's rollback capacity. In other words, if a single rollback segment needs more than 1023 slots to support a transaction, the transaction will hit that table is full condition.

Think of the restaurant Red Lobster in New Jersey. It may have a capacity of 200 people. If the restaurant is full, a line of people may go outside to wait. If the people on the line get impatient, they may leave because the restaurant is full. Obviously, the solution would not be to make New Jersey bigger (or getting more diskspace). The solution would be to make the Red Lobster restaurant bigger. That way you can increase the seating capacity to, let's say, 240. Even with that, a line may form outside if more than 240 people decide to come to Red Lobster.

Just to give you an example, I had a client with 2TB of system tablespace and innodb_file_per_table was disabled. (346G for ibdata1, the reset for ibdata2). I ran this query

SELECT SUM(data_length+index+length) InnoDBDataIndexSpace
FROM information_schema.tables WHERE engine='InnoDB';

Next, I substracted InnoDBDataIndexSpace from the sum of the filesizes for ibdata1 and ibdata2. I got two things that shocked me

  • There was 106GB remaining inside the system tablespace.
  • I got this same Table is Full condition

This means that the 106GB was using for InnoDB's internal plumbing. The client was using ext3 at the time.

The solution for me was to add ibdata3. I discussed this in my old post How to solve "The table ... is full" with "innodb_file_per_table"?

I have discussed this in other posts as well

Keep in mind that this condition can happen even if innodb_file_per_table was enabled. How? Rollbacks and Undo Logs are the source of uncontrolled spikes is growth for ibdata1.

YOUR ACTUAL QUESTION

Since your are adding an index to a table and getting Table is Full, the table must be huge and cannot fit inside a single rollback segment. You must do the following:

STEP 01

Get the data into a dump file

mysqldump --no-create-info mydb mytable > table_data.sql

STEP 02

Login to MySQL and run this

USE mydb
CREATE TABLE mytable_new LIKE mytable;
ALTER TABLE mytable_new ADD INDEX ... ;
ALTER TABLE mytable RENAME mytable_old;
ALTER TABLE mytable_new RENAME mytable;

STEP 03

Load the table with the additional index with the data

mysql -Dmydb < table_data.sql

That's all.

See, the problem is that the ALTER TABLE will try to inject all the rows in your huge table as a single transaction. Using the mysqldump will insert the data into table (now with a new index) thousands of rows at a time, not all the rows in a single transaction.

Don't worry about what if this doesn't work? The original table will be named mytable_old in case of anything. it can serve as a backup. You can drop the backup when you know the new table works for you.

Give it a Try !!!

UPDATE 2014-06-16 11:13 EDT

If you are worried about the dump of the data bigger huge, just gzip it.

You can do the same steps, but as follows

STEP 01

Get the data into a dump file

mysqldump --no-create-info mydb mytable | gzip > table_data.sql.gz

STEP 02

Login to MySQL and run this

USE mydb
CREATE TABLE mytable_new LIKE mytable;
ALTER TABLE mytable_new ADD INDEX ... ;
ALTER TABLE mytable RENAME mytable_old;
ALTER TABLE mytable_new RENAME mytable;

STEP 03

Load the table with the additional index with the data

gzip -d < table_data.sql.gz | mysql -Dmydb

or

gunzip < table_data.sql.gz | mysql -Dmydb

UPDATE 2014-06-16 12:55 EDT

I just thought of another aspect with regard to this issue.

Since you are doing DDL and not DML, it is possible that this is not InnoDB internal plumbing. Since DDL cannot rollback for InnoDB, the issue has to be external plumbing. Where is this external plumbing getting clogged? I suspect the temp folder for the OS. Why?

140616 13:04:33  InnoDB: Error: Write to file (merge) failed at offset 3 1940914176.
InnoDB: 1048576 bytes should have been written, only 970752 were written.
InnoDB: Operating system error number 0.
InnoDB: Check that your OS and file system support files of this size.
InnoDB: Check also that the disk is not full or a disk quota exceeded.
InnoDB: Error number 0 means 'Success'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/operating-system-error-codes.html
140616 13:04:33 [ERROR] /usr/libexec/mysqld: The table 'my_table' is full

See the disk quota exceeded ? Where is this disk quota being imposed ?

Run this query

SHOW GLOBAL VARIABLES LIKE 'tmpdir';

You said it's /var/lib/mysqltmp

Now, run this in the OS

df -h /var/lib/mysqltmp

Something tells me that the space for /var/lib/mysqltmp was running out After all, you only have 14G free. In the eyes of the DDL (to create the index), a rollback happened, not in the ibdata1 file, but in the tmpdir location. If /var/lib/mysqltmp is not mounted anywhere, then the temp data is being written in the root partition. If /var/lib/mysqltmp is mounted somewhere, then that mount is being filled with row data. In either case, there is not enough room to complete the DDL.

You have two options here

OPTION #1

You could also create a large disk (perhaps with 100+GB) and mount /var/lib/mysqltmp on that large disk.

OPTION #2

My 3-step suggestions should still work, even with the limited diskspace you have

COMMENTARY

It's a shame the error message you posted says

InnoDB: Operating system error number 0.
InnoDB: Error number 0 means 'Success'.

This means the OS is just fine. It also there doesn't exist any associated error number for this situation.

There is another thing you should know. The MySQL Documentation says that Fast Index Creation for InnoDB still goes to disk:

During index creation, files are written to the temporary directory ($TMPDIR on Unix, %TEMP% on Windows, or the value of --tmpdir configuration variable). Each temporary file is large enough to hold one column that makes up the new index, and each one is removed as soon as it is merged into the final index.

Due to a limitation of MySQL, the table is copied, rather than using “Fast Index Creation” when you create an index on a TEMPORARY TABLE. This has been reported as MySQL Bug #39833.

UPDATE 2014-06-16 13:58 EDT

[mysqld]
datadir                         = /mnt/cbsvolume1/var/lib/mysql
tmpdir                          = /mnt/cbsvolume1/var/lib/mysql

Please make sure /mnt/cbsvolume1/var/lib/mysql has 100G or more free

share|improve this answer
    
It took me weeks to build the table in the first place from the dump (85GB data). Is there any other way other than doing this all over again? Would you recommend maybe to partition the table? –  Noam Jun 16 at 15:09
    
Partitioning won't help because the ALTER TABLE to add the index would still attempt to load everything in a single transaction. Remember, you are fighting the InnoDB Architecture, not the diskspace. My answer should help you in this case since the mysqldump injects a few thousand rows per INSERT, not an all-or-nothing insert into a temp table with the possibility of a rollback. –  RolandoMySQLDBA Jun 16 at 15:16
    
But creating this table will (again) take forever. Any way to run a create index while disabling the rollback option? –  Noam Jun 16 at 15:23
    
Creating an index is DDL not DML. You cannot change DDL behavior. That's why my post uses DML techniques. –  RolandoMySQLDBA Jun 16 at 15:38
    
BTW how many rows does the table have ??? –  RolandoMySQLDBA Jun 16 at 15:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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