MySQL Bugs: #1320: Creating indexes in /tmp

Bug #1320	Creating indexes in /tmp
Submitted:	17 Sep 2003 4:36	Modified:	18 Sep 2003 11:05
Reporter:	[ name withheld ]	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	4.0.15	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
I have a database on a big disk array mounted to /var/lib/mysql.
I create a table with keys, disable them, load huge amount of data and run "ALTER TABLE xxx ENABLE KEYS". MySQL opens a temporary file in /tmp, deletes it and uses it probably as a temporary space. The problem is that /tmp is on a small partition with only few GB od free space and it quickly gets out of space. 

How to repeat:
See above.

Suggested fix:
- temporary file should live in the directory of the database, e.g. 
  /var/lib/mysql/xxx where it is expected to be enough space.
- if it runs out of space, it should close the connection to the 
  client, not just silently die.
- and should write something to the log.

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

I believe it's somehow (but how?) possible to alter the place where the temporary files are created, but I still don't think it's a good practice to make them in /tmp by default.

More importantly - could you, please, give me a hint about the exact part of the documentation where it's written that mysqld will silently die without noticing the client and without writing a note into the log when it runs out of space when creating the keys? 

Thanks!

Michal,

Please note that Bugs Database is not intended to provide Support of any kind. It is dedicated bugs with repeatable test cases.

You are asking about two issues which are well documented in manual. You can simply go and search through it.

A brief look at table of contents at http://www.mysql.com/doc/en/index.html will help you easily see chapters like:

A.4.4 Where MySQL Stores Temporary Files
and
A.4.3 How MySQL Handles a Full Disk

Please spend some time reading manual before entering reports and comments into Bugs database

Thanks for references and sorry if I have been too rough - I just spent too much time figuring out why MySQL silently dies.

But still (A.4.4):

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

This wasn't true. It used /tmp, not /var/lib/mysql/test when I issued "ALTER TABLE xxx ENABLE KEYS".

Never mind, thank you anyways.

no, the manual is correct.

What is created in tmp (or whatever your tmpdir variable points) is not the table as such, but a temporary file that is used to create a sorted list of all key values. The table itself is created in the same directory as the original table (otherwise MySQL would not be able to rename this table over the old one, after ALTER TABLE is done)