MySQL Bugs: #5822: env variable TMPDIR overrides my.cnf tmpdir

Bug #5822	env variable TMPDIR overrides my.cnf tmpdir
Submitted:	30 Sep 2004 11:40	Modified:	15 Feb 2005 15:35
Reporter:	Mark Maunder	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.5-7	OS:	Linux (Linux 2.6.3-4mdk)
Assigned to:	Marko Mäkelä	CPU Architecture:	Any

Description:
$TMPDIR appears to override what tmpdir in my.cnf is set to. 

I've noticed this issue confusing a few other users on the web: 
http://www.linuxquestions.org/questions/showthread.php?s=&forumid=17&threadid=234077

How to repeat:
Log in as root
export TMPDIR=/root/tmp [it is probably set to that by default]
Install the binary RPM distro. [The install can't start mysql]
Create a /etc/my.cnf with tmpdir=/tmp
Try to start mysql as root: /etc/rc.d/init.d/mysql start
It fails because the TMPDIR environment var overrides my.cnf's tmpdir

Suggested fix:
Temp workaround is:
export TMPDIR=/tmp
or unset TMPDIR

Permanent fix: make sure my.cnf settings take precedence over anything in environment variables.

Hi,

Thank you for your bug report!

I'm having some trouble repeating this.  $TMPDIR is definitely used but I've found that specifying 
it in the cnf file or using the cmd line argument overrides it.  Also, when running mysqld_safe as 
root and having it the server started as the mysql user, root's env value is not used at all.

Am I missing anything on your end?

Best Regards

I was having the same problem and this is what I found:

It appears that (in my case anyways) it had everything to do with using csh as my shell.  Obviously I couldn't 'export' TMPDIR as Mark suggested so intead I used 'set TMPDIR=/tmp' (a simple 'echo $TMPDIR' confirms that it is set).  However this would fail as above - even with tmpdir set correctly in my.cnf and using the --user=mysql option.

Now, if I simply run bash and export TMPDIR, MySQL runs fine.  It seems that no matter what I do in csh, mysqld_safe will not run.  This is all happening on an old Mandrake 8 machine and I've noticed that in most of the discussion boards, people with the same problems were using Mandrake 10.0 so I'm not sure if maybe that has anything to do with it.

Hope this helps.  Cheers.

I was able to repeat this on Mandrake 10.0 (commercial):
Linux ariadne 2.6.3-13mdk #1 Tue May 18 18:49:17 EDT 2004 i686 unknown unknown GNU/Linux

I was logged in as root, my env had TMPDIR set to /root/tmp while my.cnf had tmpdir=/tmp
I started mysql and it was creating tmp files in /root/tmp.

I verified this again, this time on linux (Linux booty 2.4.21 #12 SMP Thu Aug 14 00:49:40 EDT 
2003 i686 i686 i386 GNU/Linux) using 4.1.7-ga.

I couldn't repeat this originally as I use mysqld_multi-mysqld which doesn't pass on any env
vars.  If you use mysqld_safe or start up mysqld directly you will see the problem.  Here's how
to repeat:

sudo bash
export TMPDIR=/root/tmp
cd /usr/local/mysql/
./bin/mysqld_safe &
tail -f 50 /usr/local/mysql/hostname.err

Here's the error that prevents mysql from starting up:
/usr/local/mysql/bin/mysqld: Can't create/write to file '/root/tmp/ibH5WqUR' (Errcode: 13)

http://dev.mysql.com/doc/mysql/en/Environment_variables.html

says 

"Note that any options on the command line take precedence over values specified in option files and environment variables, and values in option files take precedence over values in environment variables."

and TMPDIR should overridable by --tmpdir.

I am unassigning this from myself because it's not a doc bug.
The documentation is correct.  my.cnf should override
environment variables.  If there is a case where this is not happening,
it's a program bug.

I've tried to reproduce this, but can't. The problem that Jason Tennier is having can be chalked up to using 'set' instead of 'setenv' with csh (or tcsh).

In coming up with a test case to reproduce this, you can try running mysqld directly with the --help --verbose options, and the tmpdir will be printed in the output. I tried this with various variations of tmpdir specified via TMPDIR, ~/.my.cnf (in the [mysqld] section), /etc/my.cnf, and on the command-line with --tmpdir, and could not reproduce any problem. I also tried running through mysqld_safe (also using the --help --verbose option), and also could not reproduce the problem.

A basic inspection of the code doesn't indicate any cases where TMPDIR would be favored over tmpdir specified on the command line or in a defaults file that is being read. mysqld_safe doesn't touch the TMPDIR environmental variable, or pass the --tmpdir flag to mysqld.

To reproduce this, I need at least: The output of 'env' from the shell where the server is being started. The command-line being used to start the server. (It is preferable if it can be reproduced by running mysqld directly, and not mysqld_safe or other such wrappers.) The contents of /etc/my.cnf and the contents of ~/.my.cnf for the user running the line above. The output of "SHOW VARIABLES LIKE 'tmpdir'" from the server after it is started.

Thanks.

I just tested this with 4.1.9 and it is still a problem.  Your my.cnf needs to at least have this:
[mysqld]
tmpdir=/tmp

and *cannot* have skip-innodb

./bin/mysqld --help --verbose | grep tmpdir
  --slave-load-tmpdir=name 
  -t, --tmpdir=name   Path for temporary files. Several paths may be specified,
slave-load-tmpdir                 /tmp/
tmpdir                            /tmp/

After taking these steps:
sudo bash
mkdir /root/tmp
export TMPDIR=/root/tmp
cd /usr/local/mysql/
./bin/mysqld_safe &

I have this in my error log:
050211 19:47:34  mysqld started
/usr/local/mysql/bin/mysqld: Can't create/write to file '/root/tmp/ibXdhcGI' (Errcode: 13)
050211 19:47:34  InnoDB: Error: unable to create temporary file; errno: 13
050211 19:47:34 [ERROR] Can't init databases
050211 19:47:34 [ERROR] Aborting

I just wanted to note that from my testing the --tmpdir=/tmp command line option to mysqld_safe seems to be overriding the env variable but tmpdir=/tmp in the [mysqld] section of my.cnf is not.

This is a bug in sql/ha_innobase.c. When it calls create_temp_file(), it does not specify a directory. If it is supposed to use the temporary directory (or directories) in the tmpdir server variable, it needs to pass mysql_tmpdir as the directory to use. From all appearances, the tmpdir variable is always getting set correctly.

Jim,
you're right: passing mysql_tmpdir to create_temp_file() fixes the problem. I'll fix this in ha_innodb.cc.
However, I found some other places where create_temp_file() is called with a null directory argument. Is anyone going to fix those?

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

I fixed the InnoDB bug in the 4.0 and 4.1 trees. The fix will also be merged to 5.0 later.