Error 1878 hy000 temporary file write failure - Исправление ошибок и поиск оптимальных решений проблем

When i am running Alter command in mysql to add a new column to my existing table. I am facing this error.

ERROR 1878 (HY000): Temporary file write failure.

Any idea regarding this? Please guide.

Many Thanks.

Jens

66.3k15 gold badges97 silver badges113 bronze badges

asked Jan 23, 2015 at 12:25

This error is most likely resulting from the temporary directory MySQL is using being too small (as others mention in the comments). In many cases altering a table in MySQL will result in the table being duplicated and you will need at least as much free space in the temporary directory as is used by the table being altered.

The destination for the temporary directory is specified in the my.cnf file under the variable tmpdir. The my.cnf is usually stored in /etc/mysql/my.cnf on Ubuntu for example (not sure what OS you are using). You can either change the location the tmpdir variable points toward (to another larger partition), or alter the size available to the directory specified.

On Ubuntu, for example, you can use df -h to see the sizes of the various filesystems available.

answered May 28, 2016 at 1:27

wingrwingr

2,27020 silver badges12 bronze badges

Find more detailed logs under /DATA/mysql/<log-file-name>.err (find the right path with show variables like 'log%';)

answered Jun 28, 2016 at 12:35

Victor BassoVictor Basso

5,3865 gold badges40 silver badges59 bronze badges

Источник

I reported an error when adding fields to the message table.
SQL used

ALTER TABLE message ADD COLUMN syncid int(10) NOT NULL DEFAULT 0;

MYSQL reported the following error:

ERROR 1878 (HY000): Temporary file write failure.

The following is information from the message table:
Table Type: InnoDB
Number of rows: 15786772
Index length: 1006.89 MB
Data length: 11.25 GB

The problem of insufficient disk space has been eliminated.
More than 50% of server 64G memory remains.
CentOS 64-bit server operating system.
MySQL Ver 14.14 Distrib 5.6.15, for Linux (x86_64) using EditLine wrapper

Attach a part of strace log to the time of error reporting.

2014-08-26 17:18:29 39108 [ERROR] InnoDB: Could not find a valid tablespace file for 'cfm/#sql-ib1445'. See http://dev.mysql.com/doc/refman/5.6/en/  innodb-troubleshooting-datadict.html for how to resolve the issue.
 2014-08-26 17:18:29 39108 [ERROR] InnoDB: Tablespace open failed for '"cfm"."#sql-ib1445"', ignored.
 2014-08-26 17:18:29 39108 [Note] InnoDB: 128 rollback segment(s) are active.
 2014-08-26 17:18:30 39108 [Note] InnoDB: Waiting for purge to start
 2014-08-26 17:18:30 39108 [Note] InnoDB: 5.6.15 started;  log sequence number 919641462089
 2014-08-26 17:18:30 39108 [Note] Server hostname (bind-address): '0.0.0.0';  port: 3306
 2014-08-26 17:18:30 39108 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
 2014-08-26 17:18:30 39108 [Note] Server socket created on IP: '0.0.0.0'.
 2014-08-26 17:18:30 39108 [Note] Event Scheduler: Loaded 0 events
 2014-08-26 17:18:30 39108 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
 Version: '5.6.15'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
 2014-08-26 17:22:36 7f99b0686700 InnoDB: Error: Write to file (merge) failed at offset 7486832640.
 InnoDB: 1048576 bytes should have been written, only 663552 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

The problem has been resolved because the remaining space in mysql’s tmpdir partition is not as large as message.
Mysql needs more temporary space than the alert table.

Источник

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.

asked Jun 29, 2016 at 15:45

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

answered Jun 29, 2016 at 18:21

eroomydnaeroomydna

1,0315 silver badges5 bronze badges

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

answered Jul 18, 2017 at 14:26

Источник

Читайте также: