MySQL tmpdir setting being ignored ?

Posted on 2014-08-21
Last Modified: 2014-08-27
Hi.  I've got 2 HDD partitions:  C: is really small and only has about 3gb of space left.  E: has plenty of space and is where my MySQL instance is installed and where the tables are being kept.  

I'm running some queries which are failing and I think it's because i'm running out of room on C: while the query uses the temp path C:\Windows\Temp, which as i understand is being read from an environment variable but should be overridden if the tmpdir option is set in the config file.  As the query runs i see a growing #sql* file in there.

Anyway.  I'd like to re-direct the temp directory to a folder on E:.  

To attempt this I:
1) Stopped Mysql (Net Stop Mysql)
2) Modified the settings file via mysql workbench.  I changed the tmpdir value and used the browse button to navigate to the desired path so there isn't a chance that i've misspelled it
3) Restarted MySQL (Net Start Mysql)

Now, running a query again, and my tmpdir setting is being ignored.  How can I fix this?

Question by:ducky801
    LVL 15

    Expert Comment

    Please paste the conf file if possible.
    Where actually you have changed the setting

    For eg
    tmpdir=[other drive]:/tmp
    LVL 28

    Expert Comment

    It's unlikely that the disc space on the C drive is the cause of the query failure.  3gb should be plenty of room for the query temp files unless we're talking about huge tables and inefficient queries.

    How big are the temp files and how many are being created?

    What error message(s) are you receiving when the queries fail?

    Can you post the query as well as a mysql explain of the query?
    LVL 50

    Assisted Solution

    by:Steve Bink
    Also, post the results of this query:
    show variables like 'tmpdir';

    Open in new window

    LVL 28

    Accepted Solution

    When you modified the config file via workbench, did you get a popup message telling you that it couldn't find the file and a new one would be created?

    I just ran a test on my system and that was what I received and after setting the tmpdir value and restarting mysql, that setting was not applied.

    After a little more troubleshooting, I discovered 2 different locations for the mysql files.
    c:\Program Files\MySQL\MySQL Server 5.6
    c:\ProgramData\MySQL\MySQL Server 5.6

    Workbench was looking in c:\Program Files\MySQL\MySQL Server 5.6

    When I manually edited the file in c:\ProgramData\MySQL\MySQL Server 5.6 and restarted mysql, the tmpdir setting was applied.
    LVL 5

    Author Closing Comment

    Hi Experts.  Sorry I lost track of this thread.  I've had a busy couple of days  :)  I was able to figure this out on my own mostly through a combination of what some of you are suggesting.

    It actually was the capacity on C: that was the issue here.  I watched it fill up completely as the query ran, right before I got an error message and the query failed.  After i changed the tempdir, the query worked.  Here's how I fixed:

    From the command line, I ran 'mysqld --help --verbose', which told me a few things:  The specific paths and file names that a config file would be sought in, and the variations of the config file name.  Also, that the value of tmpdir was not what I wanted it to be.  Note that the mySQL documentation indicates that if tmpdir is not specified, MySQL uses the value of the temp dir defined in windows environment variables.

    It turns out that the config file I had modified via workbench was none of the above, which is weird.  Don't know if this is a quirk of workbench or a side-effect of me missing some step last time I upgraded mySQL.  Turns out the path of the 'wrong' file was the same that FishMonger mentions

    So, I ended up modifying the right file in the right path with notepad.  I used 'net stop' and 'net start' to restart mySQL to get the setting to apply but that DIDN'T WORK EITHER.  Confirmed by showing variables like '%tempdir%', as Steve Bink suggests

    The last step I took, which did fix the problem was to stop and start mySQL via the workbench, which I believe actually issues some variation of 'mysqladmin shutdown' (as opposed to stopping with 'net stop').

    So.  Lessons learned:
    If you're modifying the config file from the workbench, make sure it's the same config file that MySQL is actually using
    mysqld --help --verbose will tell you where mysql is going to look for config files, and also the value of variable settings
    try using 'mysqladmin shutdown' instead of 'net stop', or just shutdown from the workbench.

    Thanks everybody for the suggestions!

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi ( had suggested a “sed” way, I actually shell …
    Both Easy and Powerful How easy is PHP? (  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now