Error Number: 126 "Incorrect key file for table '/mysql-temp/#sql_2e14_0.MYI'; try to repair it

[eluser]ray023[/eluser]
I have a sporadic issue (see attachment) that I have not seen in my development environment

The error will disappear on the production server after I refresh the browser.

I opened a support ticket with my hosting provider and this is the response I received:

The issue seems to be with the MySQL queries used to connect the login sessions with database. You need to contact the application provider to get the details about the error.

I was given no further information as to why support felt this way.

The database is mySql and, in addition to the query listed in the error, there are 14 other SELECT queries that run when the page loads. IMHO, these are not load intensive queries and they are run against a small database (< 1MB).

There is one query that runs to get session information. It looks like this:

Code:

SELECT *
FROM (`ci_sessions`)
WHERE `session_id` =  '9701bfb0318ac47911954f90f3b10950'
AND `user_agent` =  'Mozilla/5.0 (Windows NT 6.2; WOW64; rv:17.0) Gecko/20100101 Firefox/17.0';

Googling the error brought me to this Stack Overflow question and also to this one on the CI Forum.

I have reported the issue to my ISP before and pointed them to the SO question. They responded by saying there was plenty of space on the tmp folder.

Any chance that CI code could be causing this problem?

If not, is there anything I send to my ISP support line that will point them in the right direction to troubleshoot?

NOTE: I just ran "REPAIR TABLE table_name" on every table in my db.

[eluser]Rowan Wilson[/eluser]
As the other forum postings suggest, this is usually down to disk space. The fact that you report this is sporadic and doesn't occur on your dev server also suggest this is the case.

So it would be worth doing some investigation on your server hosting. Do they offer plesk or cpanel etc?

Otherwise you can do your own digging via CI:

Code:

class Diskspace extends CI_Controller
{
public function __construct()
{
  parent::__construct();
}

public function index()
{
  //set this to the drive or mount point
  $drive = '/tmp';
  $df = disk_free_space($drive);

  //bear in mind this is just a hack, outputting html from the controller is baaad practise!
  echo '<h1>Disk Space Check:</h1>';
  echo 'diskspace free on: ' . $drive . ' is ' . $this->decodeSize($df);

}

protected function decodeSize($bytes)
{  
     $types = array( 'B', 'KB', 'MB', 'GB', 'TB' );

     for( $i = 0; $bytes >= 1024 && $i < ( count( $types ) -1 ); $bytes /= 1024, $i++ );
     return( round( $bytes, 2 ) . " " . $types[$i] );
}


}

Index function and decodeSize function adapted from here: http://php.net/manual/en/function.disk-free-space.php

Bear in mind, depending what 'security' your host is running, like open_basedir etc this may or may not work. You'll need to amend the $drive variable to suit your needs.

Then point your browser at /diskspace and see what it reports.



HTH

[eluser]ray023[/eluser]
Wow..thank-you!

Just ran it:

diskspace free on: /tmp is 3.09 GB

The queries run are simple and the data retrieved is small so I don't think it is the size.

At least I can definitively rule that out.

[eluser]ray023[/eluser]
of course, now that I think about it, the drive I checked may not even be the drive where the mySql temp table is stored...so I suppose it's still a possibility.

My Hosting provides assures me it is not a size issue.

It does strike me as odd that they would link the problem to "login sessions" though. The only thing CI does is run a query SELECT * FROM CI_SESSIONS. It seems like a straight-forward query.

I have a control panel, the two options that stand out are "My SQL Database" and "Server Information"

The only thing mySQL option does does is link me to phpAdmin and I only get the version of mySQL on the "server information" option.

I want to rule out CI's code so that I can send it back to their support line. I looked in CI's database code for running mysql queries and nothing looks like it would cause this error IMO.

[eluser]Rowan Wilson[/eluser]
Yep looks that way. Just do one last check...

According to here:

http://dev.mysql.com/doc/refman/5.0/en/t...files.html

MySQL stores it's temporary files wherever the TMPDIR environment variable is set.

So create a php file with phpinfo(); inside. Have a look under Environment for TMPDIR. That will confirm if that is truly the correct location.

Might also be worth check memory limits etc.

[eluser]ray023[/eluser]
Great idea, but no environment variable with that name existed.

Hosting provider still insisting problem is not on their end.

I found a link where someone asked about pconnect.

This might be it so I have set my DB pconnect to FALSE to see if that will make a difference.

Thanks so much for your help.

[eluser]ray023[/eluser]
Update:
setting pconnect to FALSE did not help; still got the error.

I turned on profiler and grab the 15 queries that were run and put them in a php file outside CI:

Code:

echo time().'<br>';
$link = mysql_connect('ray023.myhostingprovider.com', 'myLogin', 'myPassword');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
$db_selected = mysql_select_db('myDatabase', $link);
if (!$db_selected) {
    die ('Can\'t use myDatabase : ' . mysql_error());
}
echo 'connected<br>';

$sql_array = get_sql_array();

$counter = 0;
foreach ($sql_array as $query)
{
$result = mysql_query($query);
if (!$result)
  echo 'false<br>';
else
  echo 'query no:  '.++$counter.'  result count:'.mysql_num_rows($result).'<br>';
}

echo $counter.' queries run';


function get_sql_array()
{
$sql_01 = "SELECT *
    FROM (`ci_sessions`)
    WHERE `session_id` =  '6c0833dbd929ccf4e0b5816af2d48649'
    AND `user_agent` =  'Mozilla/5.0 (Windows NT 6.2; WOW64; rv:17.0) Gecko/20100101 Firefox/17.0'
    ";
$sql_02 = "SELECT
                        #MY_FIELDS
                    FROM
                        #MyTables
   ";
///...populate variables with my sql tables obtained from profiler
$sql_15 = "SELECT
                        #MY_FIELDS
                    FROM
                        #MyTables
   ";
  
        return array($sql_01,$sql_02,$sql_03,$sql_04,$sql_05,$sql_06,$sql_07,$sql_08,$sql_09,$sql_10,$sql_11,$sql_12,$sql_13,$sql_14,$sql_15);

}

I refreshed the screen 200 times and did not get the error...I went back to my "problem" controller, refreshed it 68 times and did NOT get the error

I currently have no idea what's going on. I'll just to have watch how often this happens and the conditions in which happens. I'll update if I get anywhere.