Bug #23222 False (misleading) error message when tmp directory not available
Submitted: 12 Oct 2006 17:30 Modified: 5 Dec 2007 18:55
Reporter: Gisbert Selke (Basic Quality Contributor) Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Errors Severity:S3 (Non-critical)
Version:4.1.8,4.1.21 OS:Windows (windows)
Assigned to: CPU Architecture:Any
Tags: Contribution, qc

[12 Oct 2006 17:30] Gisbert Selke
Description:
Situation: freshly installed MySQL server 4.1.8, default database engine InnoDB.
When the system variable temp points to an invalid location (like c.\temp -- note: period instead of colon!), trying to create a temporary table will result in an error message "database xxx does not exist".
(Apparently, MySQL creates separate dataspaces for temporary tables in a completely different location from ordinary ones. Allocation of space in non-existent directories then goes wrong.)

To be sure, the cause of the error is a misconfiguration of the environment variable. However, the error message is technically wrong, but more importantly, it is of no help in pinpointing the real mistake.

How to repeat:
Set system environment variable temp to "c.\temp" (period, not colon).
Install MySQL server, default engine InnoDB.
Start server.
Create a database mytest; use it.
Create a table: CREATE TABLE t1 (a char(1))
This should work ok.
Create a temporary table: CREATE TEMPORARY TABLE t2 (a char(1))
This should yield the error message quoted above.

Suggested fix:
Change error message to something like
"Cannot create temporary table in database ...". (An additional hint in the trouble-shooting section that malconfigured environment variables may be the cause might be helpful.)
Better still: make MySQL more fault-tolerant through implementation of a fallback strategy, like other software seemingly does. E.g.,
if environment var temp does not work, try variable tmp instead. If still unsuccessful, try c:\temp (hard-coding the usual Windows place). If still unsuccessful, try c:\.
(Of course, nothing is absolutely fail-proof. However, it should make failure very improbable.)
[13 Oct 2006 9:52] Sveta Smirnova
Thank you for the report.

Please, try using current 4.1.21 version
[14 Oct 2006 9:23] MySQL Verification Team
G:\mysql-4.1.21-win32\bin>mysqld-nt --console --tmpdir=c.\tmp 
mysqld-nt: Can't read dir of 'c.\tmp\' (Errcode: 2)
061014 11:14:59  InnoDB: Started; log sequence number 0 43634
mysqld-nt: ready for connections.
Version: '4.1.21-community-nt'  socket: ''  port: 3306  MySQL Community Edition (GPL)

G:\mysql-5.0.26-win32\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.21-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table g1(id int);
Query OK, 0 rows affected (0.11 sec)

mysql> create temporary table g2(id int);
ERROR 1049 (42000): Unknown database 'test'
mysql>
mysql> show global variables like 'tmpdir';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| tmpdir        | c.\tmp |
+---------------+--------+
1 row in set (0.00 sec)

So, maybe if the tmpdir is known to be invalid/inaccessible at startup time, mysql should maybe fallback to the system's tmpdir ?  Other queries could also fail, that require tmpdir access.
[19 Oct 2006 15:32] Gisbert Selke
Shane, thanks fro the test of a later release!
Re your suggestion "use the system's tmpdir": That's exactly the problem: where to find it? There is no really fixed loacation. Either of the environment variables tmp, temp, tmpdir or tempdir (literally any) could point to it, but what do you do if all fail? I'm suggesting a fallback mechanism of trying all of these, and finally to use some hard-coded (pugh!) location as a last resort.
[19 Oct 2006 15:34] Gisbert Selke
I just checked: the bug seems to be fixed in the 5.x branch (current beta release).

So, the code for the fix could probably be ported backwards to the 4.x branch. If, for some reason, this is not feasible, I could have a go at supplying a patch. Sveta, if you're interested, let me know!
[10 Nov 2006 16:46] Gisbert Selke
If it is of any help: I repeat my offer to attempt to supply a fix.
(I have none ready, otherwise I would just post it. Before starting on it, I would rather know whether anyone is interested.)
[14 Nov 2006 1:37] Timothy Smith
Gisbert,

If you are able to patch the 4.1 server, I would do my best to get your fix applied to the server code, with thanks!  I confess that this bug is not at the top of our priority list at the moment, but we value any contribution.

Regards,

Timothy
[20 Dec 2006 16:05] Gisbert Selke
Patched source for init_tmpdir

Attachment: mf_tempdir.c (application/octet-stream, text), 5.54 KiB.

[20 Dec 2006 16:06] Gisbert Selke
Diff between patched and original file version

Attachment: mf_tempdir.diff (application/octet-stream, text), 5.02 KiB.

[20 Dec 2006 16:18] Gisbert Selke
I just added my attempts at fixing the problem with corrupt TMPDIR environment variables. These patches do not address the symptom (incorrect error message from the server), but rather the cause (unusable information in TMPDIR).
The code will, in turn, check the environment variables TMPDIR, TEMPDIR, TMP, and TEMP until it finds one that contains at least one writable directory. As a last resort, it will default to "c:\".
There is practically no performance penalty involved, because the function is (as far as I can tell) called only once at startup time, and the result is cached for later use.
As coded, the patch applies only to DOSish operating systems (via #ifdef). This could probably be extended, but I cannot currently verify proper operation on other systems.