How to change my my.cnf file

I'm getting a "Temporary Write Failure" error message and I'm confident that I need to change the size of my temporary directory.

Here is my my.cnf file:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

I'm going by the insight published on this page: http://www.lophost.com/tutorials/how-to-change-or-move-mysql-tmp-directory/ and I want to be sure I do this right the first time.

First off, I don't see the tmp directory listed, so I want to make sure I'm in the right place.

Secondly, I don't see the current status of the current temporary directory. I would like to test those waters and see what kind of space I'm looking at before I make any adjustments just so I can make intelligent comparisons.

I know I've got 3TB worth of space on my server, so I've got room. But how do I dictate  the size of my tmp directory? How can I determine where it is currently?

Thoughts?
brucegustPHP DeveloperAsked:
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.

Beverley PortlockCommented:
The tmp folder should be the system tmp folder in /tmp

ls -l /tmp

Open in new window


But the article you linked to is talking about creating a NEW temp folder elsewhere. In its example it is /home/mysqltmp


/tmp is owned (under Debian/Ubuntu) by root with permissions of 777 so everyone and everything can write to it. Because root has no quota restrictions there should be no issues with writing to it unless your /tmp is owned by some other ID

To find where your tmpdir is, go into phpmyadmin and click on LOCALHOST at the top, then click on VARIABLES (3rd or 4th tab across) and scan for tmpdir and the value will be over to the right.  You may need to scroll sideways.
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
StampelCommented:
I need more informations, be carefull, newer systems use systemd and wont allow direct writing to /tmp for security reasons !!

- Do you have a root shell access ?
- What distribution & version do you use ?
0
brucegustPHP DeveloperAuthor Commented:
Beverly, I'm using a SQLyog interface and when I'm not certain about the phpMyAdmin interface in that when I tried to login using the credentials that I have, I wasn't able to access it. I've got complete access to the server. Is there another file that would have the info you're referring to that I could access in order to determine the location and size of the temp folder?
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

brucegustPHP DeveloperAuthor Commented:
Found it!

And I've been able to learn that there's a difference between the tmp directory that PHP is using versus what MySQL is using. Tell me if this isn't correct: PHP's temp directory is more for uploads etc. The location of that directory can be discerned by using sts_get_temp_dir().

The MySQL tmp directory is a default setting according to the documentation that I found here: http://dev.mysql.com/doc/refman/5.0/en/temporary-files.html. Unless you make a change of some kind, that's where everything is going when temporary files are being made.

Still looking for some specifics though, on how to alter the size of that directory. Even figuring out what the current size is would be helpful. When I click on "File Attributes," all I get are write permissions.
0
Beverley PortlockCommented:
@brucegust

use the SQL "SHOW VARIABLES" perhaps like this?

$rs = mysql_query("SHOW VARIABLES");

if ( $rs ) {

     while ( $rw = mysql_fetch_array( $rs, MYSQL_NUM ) )

          if ( $rw[0] == 'tmpdir' ) {
               echo "tmpdir is set to {$rw[1]}";
               exit;
          }

}

Open in new window


Which gave this when I ran it

tmpdir is set to /tmp
0
Beverley PortlockCommented:
"Still looking for some specifics though, on how to alter the size of that directory. Even figuring out what the current size is would be helpful. When I click on "File Attributes," all I get are write permissions."

Who owns the folder? MySQL? root? Do an ls -l on the folder in question and see who the owner and group are then look these up and see if a system quota is set. If that is not an issue then look at the setting in MySQL for tmp_table_size which defaults to 16MB. A variation of the script above would let you see the value


$rs = mysql_query("SHOW VARIABLES");

if ( $rs ) {

     while ( $rw = mysql_fetch_array( $rs, MYSQL_NUM ) )

               echo "{$rw[0]} = {$rw[1]}<br/>";

}

Open in new window

0
brucegustPHP DeveloperAuthor Commented:
Beverly, are you running this in the SQL window of your phyMyAdmin interface, or are you running it as a PHP page.

In my interface, I get this:

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '}



}' at line 1
(109 ms taken)

As a PHP page, I get:

Warning: mysql_query(): Access denied for user ''@'localhost' (using password: NO) in /opt/lampp/htdocs/twit/tmpdir.php on line 2

Warning: mysql_query(): A link to the server could not be established in /opt/lampp/htdocs/twit/tmpdir.php on line 2
0
Beverley PortlockCommented:
The code fragment above? I ran it as a small script in a PHP file. I connected to the database and ran it, the only bit not shown was a standard "connect.php" I use for attaching to my EE testing database.

index.php
<?php

include_once 'connect.php';



$rs = mysql_query("SHOW VARIABLES");

if ( $rs ) {

     while ( $rw = mysql_fetch_array( $rs, MYSQL_NUM ) )

               echo "{$rw[0]} = {$rw[1]}<br/>";

}

Open in new window

0
brucegustPHP DeveloperAuthor Commented:
Beverly, I got it done! I didn't have to fool with the my.cnf file after all. I was able to adjust the size of the tmp directory and got my indexes installed.

Not quite out to the woods yet, though. My indexes are running and the resulting rows being considered are considerably less than the total database but the query is taking 30 minutes and more. Feel free to weigh in at:
http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28537414.html

Thanks!
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.