MySQL tmpdir setting being ignored ?

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?

AR
LVL 5
ducky801Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

For eg
tmpdir=[other drive]:/tmp
0
FishMongerCommented:
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?
0
Steve BinkCommented:
Also, post the results of this query:
show variables like 'tmpdir';

Open in new window

0
FishMongerCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ducky801Author Commented:
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!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.