Solved

How to change my my.cnf file

Posted on 2014-10-10
9
189 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL: Updating SubQuery Match Faster 9 53
How to count in a table in php 22 44
mysql between clause 2 32
Using cfstoredproc to return query data 2 30
Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

856 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