MySQL tmpdir setting being ignored ?

Posted on 2014-08-21
Medium Priority
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

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

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

Expert Comment

ID: 40279074
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 51

Assisted Solution

by:Steve Bink
Steve Bink earned 1000 total points
ID: 40279205
Also, post the results of this query:
show variables like 'tmpdir';

Open in new window

LVL 28

Accepted Solution

FishMonger earned 1000 total points
ID: 40279218
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.

Author Closing Comment

ID: 40288295
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month16 days, 12 hours left to enroll

864 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