Solved

How to change my my.cnf file

Posted on 2014-10-10
9
190 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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
 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql_mode 1 33
remote mysql 8 36
Where on a calculated field 1 30
MySQL programmer starter 25 20
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 …
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…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

761 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