MariaDB/InnoDB: Temp file write failure

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!

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;

StackExchange Database Administrators Q#113102, answer score: 3