Solved

How to change my my.cnf file

Posted on 2014-10-10
9
184 Views
Last Modified: 2014-10-14
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?
0
Comment
Question by:brucegust
  • 4
  • 4
9 Comments
 
LVL 34

Accepted Solution

by:
Beverley Portlock earned 500 total points
ID: 40373700
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
 
LVL 7

Expert Comment

by:Stampel
ID: 40373870
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
 

Author Comment

by:brucegust
ID: 40376920
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
 

Author Comment

by:brucegust
ID: 40377226
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 40377237
@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
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 40377247
"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
 

Author Comment

by:brucegust
ID: 40377272
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
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 40377614
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
 

Author Comment

by:brucegust
ID: 40380675
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 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

17 Experts available now in Live!

Get 1:1 Help Now