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;
  • Alter table should not write tmp file to tmpdir, it should be created in the database directory.
    – jkavalik
    Sep 3 '15 at 19:29
  • Hmmm.. And both /var/tmp and the database directory has terabytes free..? Sep 3 '15 at 22:26
  • Oh, and it's not on a SAN, it's a local RAID. Sep 3 '15 at 22:26
  • Consider using pt-online-schema-change to avoid the downtime.
    – Rick James
    Sep 3 '15 at 22:57
  • Is tmpdir set to /var/tmp or /tmp?
    – Rick James
    Sep 3 '15 at 22:58

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.