4

When running an ALTER TABLE ... ADD COLUMN ... I get:

Error Code : 1878
Message    : Temporary file write failure.

And on log_error (/DATA/mysql/.err)

2016-06-29 17:27:01 7f11f08a7700 InnoDB: Error: Write to file (merge) failed at offset 3145728.
InnoDB: 1048576 bytes should have been written, only 327680 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.6/en/operating-system-error-codes.html

Context

  • I'm running MySQL on a VM. Guest is SuSE, host is Windows 7.
  • Got the error after having dropped the schema and recreating it from a dump.
  • I had the same error one day ago (except the statement was a create index), and it just spontaneously stopped happening. But this time it's happening for a while.
  • If I keep retrying, the count of bytes written in the logs gets smaller every once in a while.
1
  • 1
    Could it be that you don't have enough disk space to perform this operation? Jun 29 '16 at 17:45
4

It's likely the disk space is the root cause. Watch the space free in the tmpdir location whilst the ALTER is running.

2
  • You were right. I was rejecting that possibility before because the /DATA filesystem (where mysql is) had plenty of space. But /tmp is in the root, and it was running out of it.
    – Vituel
    Jun 30 '16 at 7:39
  • 1
    Change the MySQL variable tmpdir to point to somewhere in the main fs.
    – Rick James
    Jul 7 '16 at 5:01
1

if you are using docker and docker-compose you can add a line to your volumes to a spot where you have more disk space. E.g.,

volumes:
  - /data/mysql_tmp:/tmp

Again, this will allow you to get more than the default tmp space you need and is helpful if that table you are ALTER-ing is big (over 1.9 GB) as our docker had like 3.8GB /tmp

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.