How MySQL Handles a Full Disk, Temp Files, Socket Files and Time Zones - MySQL

A.4.3 How MySQL Handles a Full Disk

When a disk-full condition occurs, MySQL does the following:

• It checks once every minute to see whether there is enough space to write the current row. If there is enough space, it continues as if nothing had happened.

• Every six minutes it writes an entry to the log file, warning about the disk-full condition.

To alleviate the problem, you can take the following actions:

• To continue, you only have to free enough disk space to insert all records.

• To abort the thread, you must use mysqladmin kill. The thread will be aborted the next time it checks the disk (in one minute).

• Other threads might be waiting for the table that caused the disk-full condition. If you have several "locked" threads, killing the one thread that is waiting on the disk-full condition will allow the other threads to continue.

Exceptions to the preceding behavior are when you use REPAIR TABLE or OPTIMIZE TABLE or when the indexes are created in a batch after LOAD DATA INFILE or after an ALTER TABLE statement. All of these statements may create large temporary files that, if left to themselves, would cause big problems for the rest of the system. If the disk becomes full while MySQL is doing any of these operations, it will remove the big temporary files and mark the table as crashed. The exception is that for ALTER TABLE, the old table will be left unchanged.

A.4.4 Where MySQL Stores Temporary Files

MySQL uses the value of the TMPDIR environment variable as the pathname of the directory in which to store temporary files. If you don't have TMPDIR set, MySQL uses the system default, which is normally /tmp, /var/tmp, or /usr/tmp. If the filesystem containing your temporary file directory is too small, you can use the --tmpdir option to mysqld to specify a directory in a filesystem where you have enough space.

Starting from MySQL 4.1, the --tmpdir option can be set to a list of several paths that are used in round-robin fashion. Paths should be separated by colon characters (':') on Unix and semicolon characters (';') on Windows, NetWare, and OS/2. Note: To spread the load effectively, these paths should be located on different physical disks, not different partitions of the same disk.

If the MySQL server is acting as a replication slave, you should not set --tmpdir to point to a directory on a memory-based filesystem or to a directory that is cleared when the server host restarts. A replication slave needs some of its temporary files to survive a machine restart so that it can replicate temporary tables or LOAD DATA INFILE operations. If files in the temporary file directory are lost when the server restarts, replication will fail.

MySQL creates all temporary files as hidden files. This ensures that the temporary files will be removed if mysqld is terminated. The disadvantage of using hidden files is that you will not see a big temporary file that fills up the filesystem in which the temporary file directory is located.

When sorting (ORDER BY or GROUP BY), MySQL normally uses one or two temporary files. The maximum disk space required is determined by the following expression:

(length of what is sorted + sizeof(row pointer))
* number of matched rows
* 2

The row pointer size is usually four bytes, but may grow in the future for really big tables.

For some SELECT queries, MySQL also creates temporary SQL tables. These are not hidden and have names of the form SQL_*.

ALTER TABLE creates a temporary table in the same directory as the original table.

A.4.5 How to Protect or Change the MySQL Socket File /tmp/mysql.sock

The default location for the Unix socket file that the server uses for communication with local clients is /tmp/mysql.sock. This might cause problems, because on some versions of Unix, anyone can delete files in the /tmp directory.

On most versions of Unix, you can protect your /tmp directory so that files can be deleted only by their owners or the superuser (root). To do this, set the sticky bit on the /tmp directory by logging in as root and using the following command:

shell> chmod +t /tmp

You can check whether the sticky bit is set by executing ls -ld /tmp. If the last permission character is t, the bit is set.

Another approach is to change the place where the server creates the Unix socket file. If you do this, you should also let client programs know the new location of the file. You can specify the file location in several ways:

• Specify the path in a global or local option file. For example, put the following lines in /etc/my.cnf:

  [mysqld]
  socket=/path/to/socket
  [client]
  socket=/path/to/socket

• See Section 3.3.2, "Using Option Files."

• Specify a --socket option on the command line to mysqld_safe and when you run client programs.

• Set the MYSQL_UNIX_PORT environment variable to the path of the Unix socket file.

• Recompile MySQL from source to use a different default Unix socket file location. Define the path to the file with the --with-unix-socket-path option when you run configure. See Section 2.3.2, "Typical configure Options."

You can test whether the new socket location works by attempting to connect to the server with this command:

shell> mysqladmin --socket=/path/to/socket version

A.4.6 Time Zone Problems

If you have a problem with SELECT NOW() returning values in GMT and not your local time, you have to tell the server your current time zone. The same applies if UNIX_TIMESTAMP() returns the wrong value. This should be done for the environment in which the server runs; for example, in mysqld_safe or mysql.server. See Appendix B, "Environment Variables."

You can set the time zone for the server with the --timezone=timezone_name option to mysqld_safe. You can also set it by setting the TZ environment variable before you start mysqld.

The allowable values for --timezone or TZ are system-dependent. Consult your operating system documentation to see what values are acceptable.