Description:
When executing queries against a large database (15G) MySQL is writing temporary files
into the directory defined by datadir rather then to the directory identitfied by
tmpdir.
This is causing complex queries that have previously completed, to fail with 'OS error
code 28: No space left on device.'
Temporary tables specifically created using CREATE TEMPORARY TABLE... do write files to
tmpdir
This has only been happening since upgrading from 5.0.41 to 5.0.45 (Current installation
reports as:
'Server version: 5.0.45-Debian_1~bpo.1-log Debian etch distribution'
How to repeat:
Edit my.cnf so that datadir and tmpdir point to locations on different file systems. (can
confirm this in mysql with: show variables like '%dir%';)
Execute a complex query, with multiple tables and a GROUP BY clause against a large
database (15 Gig in our case).
Execute SHOW FULL PROCESSLIST repeatedly until mysql reports that it is Copying to tmp
table on disk.
Use Linux commands such as df or iostat -xk to monitor disk usage.
ls -altr in the datadir and tmpdir show that the MYI and MYD files are created in the
datadir rather than the tmpdir.
Suggested fix:
Create MySQL internal temporary files in the same directory as user created temporary
files.
