MariaDB/InnoDB: Temp file write failure

Question

When attempting to alter a table to enable compression by modifying the KEY_BLOCK_SIZE I received the error:

ERROR 1296 (HY000): Got error 64 'Temp file write failure' from InnoDB

The command run was:

MariaDB [events_prod]> alter table positions KEY_BLOCK_SIZE=8;

The alter table ran for about an hour before generating this error. The table in question is 280 gigabytes and the filesystem on which it resides has 3.4 terabytes free. I am already using per-table files and barracuda for storage.

I can't seem to find any documentation that explains what the cause of this error might be or where to even start trying to resolve it. Any suggestions are appreciated!

Answer 1

Look carefully at the error message

ERROR 1296 (HY000): Got error 64 'Temp file write failure' from InnoDB

What is error 64 ? perror says the following:

$ perror 64
OS error code  64:  Machine is not on the network

The ALTER TABLE command is create a temp file with the new compression. However, it is trying to create a temp file but it cannot write to the directory tmpdir is mapped to.

• If tmpdir is mapped to a SAN that disconnected from the network, that error would make sense.
• If tmpdir ran out of space, the error message makes no sense but the end result would still stop ALTER TABLE from completing its operation.

If this problem is repeatable, you may want to try doing this change manually.

create table positions_comp like positions;
alter table positions_comp KEY_BLOCK_SIZE=8;
insert into positions_comp select * from positions;
alter table positions rename positions_old;
alter table positions_comp rename positions;

Answer 2

I've experienced this recently, as well, in two different situations with different root causes and solutions:

1. Filesystem wasn't full, but did run out of inodes. MySQL (mariadb) data directory was default /var/lib/mysql/, but the /var volume had maxed out inodes due to a runaway process writing too many files to /var/tmp/ (unrelated to mysql) without properly cleaning them up, filling up the inode table. Solution was to write a cleanup script to clear out old files from /var/tmp/ and fix application that was creating them.
2. The mysql service was running with incorrect ulimits. Someone had made a typo in the startup script where instead of "ulimit -n 100000", they used ulimit -f, which put a limit on size of files being written to (100MB in this case). Fixing the typo and restarting mysqld resolved this. This would probably happen if a quota was set on the mysql user, as well.