MySQL Bugs: #1320: Creating indexes in /tmp

Bug #1320	Creating indexes in /tmp
Submitted:	17 Sep 2003 6:36	Modified:	18 Sep 2003 13:05
Reporter:	[ name withheld ]
Status:	Not a Bug
Category:	Server: MyISAM	Severity:	S2 (Serious)
Version:	4.0.15	OS:	Linux (Linux)
Assigned to:		Target Version:

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)