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.
