Bug #36179 ALTER TABLE creates a temporary table in the same directory as the original tabl

• Submitted: 17 Apr 2008 14:14
• Modified: 18 Apr 2008 5:13
• Reporter: Glen Solsberry
• Status: Not a Bug
• Category: MySQL Server: General
• Severity: S2 (Serious)
• Version: 5.1.23
• OS: Linux
• CPU Architecture: Any
• Tags: alter, table, temporary

[17 Apr 2008 14:14] Glen Solsberry

Description:
ALTER TABLE creates a temporary table in the same directory as the original table.  This doesn't use the tmpdir setting, and for large tables, causes the same disk to be read to and written to, causing a massive slow down.

How to repeat:
N/A

Suggested fix:
Make ALTER TABLE write temporary tables to tmpdir, or add a parameter saying where they should be written to.

[18 Apr 2008 5:13] Valeriy Kravchuk

This is not a bug. Manual clearly says (http://dev.mysql.com/doc/refman/5.1/en/alter-table.html):

"The temporary table is created in the database directory of the new table. This can be different from the database directory of the original table if ALTER TABLE is renaming the table to a different database."

There is a good reason for this. Renaming temporary table is possible as fast operation only in frames of the same filesystem.