#1016 - Can't open file: 'tablename.MYI' (errno: 145)

[bdunning]

I'm getting the above error whenever I try to access this table, either through my normal app or through phpMyAdmin. It just started today, and nothing was changed. It has been working fine for a long time. All my other tables are fine. I have no idea what to do - can anyone suggest anything????? Thanks!

[c5davey]

I'm getting the above error whenever I try to access this table, either through my normal app or through phpMyAdmin. It just started today, and nothing was changed. It has been working fine for a long time. All my other tables are fine. I have no idea what to do - can anyone suggest anything????? Thanks!

I have the exact same problem that just started earlier today as well. No changes to the code whatsoever.

[riskynil]

There's several threads that have been started about this problem. I don't know what the problem was, but it had been mucking up my website. And since a permanent solution seems to be out of our hands, I devised this temporary solution for my site you might find useful:

function Query($query, $dbc) {
$result = mysql_query($string, $dbc);

if ($result == FALSE) {
$error = mysql_error($dbc)
if (preg_match("/Can't open file: '([a-zA-Z]+)\.MYI' \(errno: 145\)/", $error, $matches) > 0) {
$repairQuery = 'REPAIR TABLE '.$matches[1];
mysql_query($repairQuery, $dbc);
$result = mysql_query($query, $dbc);
}
}

return $result;
}

Basically, it's a wrapper function for the mysql_query() function. It checks if the query failed and, if so, if it failed due to that specific error. If that was the problem, it runs "REPAIR TABLE" on the problem table and tries the query again. I put it up on my site about an hour and a half ago and my site hasn't had any problems since, but it's really just a temporary solution. The tables shouldn't be corrupted on a regular basis in the first place!

-- Ryan

[Alli]

I got this problem as well. Luckily repairing fixed it. Glad to know it wasn't anything I was doing! Now I can't connect with the entire database...

[Alli]

Ok Ryan, now that this error has been occurring so frequently with no word from Powweb on when they might have it fixed, I've decided to try your code. I don't want to mess anything up so can you tell me where I should put the code and if there's anything I need to change?

[musox]

this is happening to me constantly now for the past two days on mysql03 AND mysql06.

- MusOX

[Croc Hunter]

Try this guys:

 Go to http://phpmyadmin.powweb.com and login with the mysql user, password and servername for your database (db). once logged in, click the db name (in bold at the top) on the left pane, then on the right pane, click "select all" and then on the "with selected:" dropdown, select "Repair tables" 

[Crowsworld]

Try this guys:

 Go to http://phpmyadmin.powweb.com and login with the mysql user, password and servername for your database (db). once logged in, click the db name (in bold at the top) on the left pane, then on the right pane, click "select all" and then on the "with selected:" dropdown, select "Repair tables" 

yeah that works for about 5 mins. :mad:

[rnorth6920]

I've also had to repair my sessions table about three times. The same thing happened again late last night and was fixed by powweb(i assume) around 7am est this morning. These corrupt databases as well as the connection errors all in the same week should make quite a few people leave powweb.....

[blw911]

rnorth6920,

Yes you are correct, simply ridiculous! And "leave Powweb" is a brilliant idea.

[bdunning]

Same here - the fix worked, but then it was down again as soon as I tried to use it. I deleted all my keys and indexes, recreated them, and it went right back down again. So now I dropped the whole table (fortunately it wasn't critical data), recreated the whole thing, and it seems OK so far.

[rnorth6920]

Mine went corrupt AGAIN within 30 minutes of repairing it. This is getting old.

[Ifrit]

same problem here

powweb .. you better do something ^^

[RBB]

Croc,

Can't even connect through phpMyAdmin to get to the repair function. When I try to login to mysql01.powweb.com, I just get the #1016 error.

[cyberCrank]

ditto on these MySQL errors, lost posts, etc., phpMyAdmin repair works but is temporary at best, daily backups of little help on keeping current, pesty bugger... :(

[DrewGreen]

That code helped thanks, however there are a few bugs.
It might be a forum-specific problem rather than anything else, my Mambo site has been working fine all this time.
Here's my modified code that is running on phpbb 2.0.6:

Add this to the mysql4.php script found in the db/ directory: (altered the match string to include underscores, added a few ; delimiters & changed a few variables)

function Query($query, $dbc) {
$result = mysql_query($query, $dbc);

if ($result == FALSE) {
$error = mysql_error($dbc);

$matchstring = '/Can\'t open file: \'([a-zA-Z_ -]+)\.MYI\' \(errno: 145\)/';

if (preg_match($matchstring, $error, $matches) > 0) {
$repairQuery = 'REPAIR TABLE ' . $matches[1];
mysql_query($repairQuery, $dbc);
$result = mysql_query($query, $dbc);
}

}

return $result;
}

And then find the "sql_query" function and change all occurrences of "mysql_query" to "$this->Query" (no quotes)

this line may also work:
$matchstring = '/can\'t open file: \'([a-zA-Z_ -]+)\.MYI\' \(errno: 145\)/i';
but the one in the code works ok.

You could also change the line $result = mysql_query($query, $dbc);
to
$result = $this->Query($query, $dbc);
for some recursive fun in case the first repair didn't work (this happened to me when I was repairing from phpmyadmin), but I don't think it's necessary.

Cheers,
Drew

NB - you may not even be seeing the error code in the forum scripts, all I was seeing is this:

Could not insert new word

DEBUG MODE

INSERT IGNORE INTO phpbb_search_wordlist (word_text, word_common) VALUES
then a whole load of these:
('drinks', 0), ('stuff', 0), ('getting', 0), ('mean', 0)

Line : 232
File : /www/?/?????/htdocs/includes/functions_search.php

[msxsecurity.com]

Anyone mind posting that script for ipb 2.0.x ? (invision)

[DrewGreen]

I'm not familiar with ipb's structure but it should be pretty much the same - find the script that deals with mysql4, add the Query function as above, and then modify the existing function that calls "mysql_query" with ipb's equivalent of $this->functionname (you might not need the $this-> part - look around the script in other functions and see how they call their own functions)

you can always replace the if(preg_match... { ... } block with this:


if (preg_match($matchstring, $error, $matches, PREG_OFFSET_CAPTURE) > 0) {
$repairQuery = 'REPAIR TABLE ' . $matches[1];
mysql_query($repairQuery, $dbc);
$result = mysql_query($query, $dbc);
}
else
{
message_die(CRITICAL_ERROR, "Oops! Error=" . $error . " - Matches = " . $matches[1], "", __LINE__, __FILE__, $dbc);
}

which will show you what's being stored in the $error and $matches variables.

Remove it again once you've got it working though, otherwise other things like deleting threads won't work!

[jeremyp]

LOL how about vbulletin 3.x also?

[msxsecurity.com]

function query($the_query, $bypass=0) {

//--------------------------------------
// Change the table prefix if needed
//--------------------------------------

if ($bypass != 1)
{
if ( $this->obj['sql_tbl_prefix'] != "ibf_" and ! $this->prefix_changed )
{
$the_query = preg_replace("/\sibf_(\S+?)([\s\.,]|$)/", " ".$this->obj['sql_tbl_prefix']."\\1\\2", $the_query);
}
}

if ($this->obj['debug'])
{
global $Debug, $ibforums;

$Debug->startTimer();
}

$this->query_id = mysql_query($the_query, $this->connection_id);

//--------------------------------------
// Reset array...
//--------------------------------------

$this->force_data_type = array();

if (! $this->query_id )
{
$this->fatal_error("mySQL query error: $the_query");
}

//--------------------------------------
// Debug?
//--------------------------------------

if ($this->obj['debug'])
{
$endtime = $Debug->endTimer();

$shutdown = $this->is_shutdown ? 'SHUTDOWN QUERY: ' : '';

if ( preg_match( "/^select/i", $the_query ) )
{
$eid = mysql_query("EXPLAIN $the_query", $this->connection_id);

$ibforums->debug_html .= "<table width='95%' border='1' cellpadding='6' cellspacing='0' bgcolor='#FFE8F3' align='center'>


What to put where?

[DrewGreen]

Ok, first, rename the Query function I/riskynil posted to something else, like My_Query, and paste it above function query($the_query, $bypass=0) {

Here's the modified code you posted:


function query($the_query, $bypass=0) {

//--------------------------------------
// Change the table prefix if needed
//--------------------------------------

if ($bypass != 1)
{
if ( $this->obj['sql_tbl_prefix'] != "ibf_" and ! $this->prefix_changed )
{
$the_query = preg_replace("/\sibf_(\S+?)([\s\.,]|$)/", " ".$this->obj['sql_tbl_prefix']."\\1\\2", $the_query);
}
}

if ($this->obj['debug'])
{
global $Debug, $ibforums;

$Debug->startTimer();
}

$this->query_id = $this->My_Query($the_query, $this->connection_id);

//--------------------------------------
// Reset array...
//--------------------------------------

$this->force_data_type = array();

if (! $this->query_id )
{
$this->fatal_error("mySQL query error: $the_query");
}

//--------------------------------------
// Debug?
//--------------------------------------

if ($this->obj['debug'])
{
$endtime = $Debug->endTimer();

$shutdown = $this->is_shutdown ? 'SHUTDOWN QUERY: ' : '';

if ( preg_match( "/^select/i", $the_query ) )
{
$eid = $this->My_Query("EXPLAIN $the_query", $this->connection_id);

$ibforums->debug_html .= "<table width='95%' border='1' cellpadding='6' cellspacing='0' bgcolor='#FFE8F3' align='center'>



and find any other occurrences of "mysql_query" and replace with "$this->My_Query"

Hope that helps

[riskynil]

That code helped thanks, however there are a few bugs.

Cool! Someone actually used my code! =) That doesn't happen very often....

The code I posted was just a temporary crutch to keep my site working. It's not fully tested--just an off the cuff function I created to get my site back up and running. I'd expect people to modify it for their own sites as well. (The actual code I used on my site is a bit different--I just posted the pertinent portions of the code involved.) It could certainly use a bit of extra work if I wanted to keep it permanently, but I intend to comment out the code when the databases start working reliably again. (I'd delete it completely, but what if I ended up needing it again?!)

My site has been running all day with the code I had posted with no ill effects. The part that reads the table name assumes you only use letters of the alphabet--it would need adjusting if you use underscores, numbers, or other odd characters in your table names. My table names are strictly letters so I took a shortcut and didn't include all the other characters that could possibly work. *shrug*

I considered the recursive loop to do repairs until the query worked, but decided not to since I was afraid of the effects of a table that failed to repair correctly. Endless loops aren't good. At worst, a page refresh runs the repair again, though I have not yet needed a refresh to get a page to work.

I don't want to mess anything up so can you tell me where I should put the code and if there's anything I need to change?

Just make sure the function is available on any page that tries to perform a query, then call that function instead of mysql_query().

Happy trails!

-- Ryan

[msxsecurity.com]

I am having a great amount of difficulty getting this to work, Maybe you can try your hand at it?
I put my mysql.php file at www.msxsecurity.com/mysql.txt See if you can get it working.
Thanks

[bdunning]

Oh well. Recreating the table did not help. Went corrupt again.

Powweb - hire an experienced sysad! It's called REDUNDANCY. A lot of businesses depend on your services, and you depend on us as customers. You can't afford this kind of slacking.

[SilentK]

Anyone have any clue why the tables are corrupting so frequently? I know it's probably related to whatever they did during their maintence since that's as soon as I had it start happening but anyone have a guess as to what might be causing this? By the way my database is on mysql01.powweb.com it looks like all the mysql servers are having this problem.

[DrewGreen]

msxsecurity, the answer is in my post above. Modify your existing query function as shown and add the fix but change the line
function Query(..bla bla bla...)
to
function My_Query(..bla bla bla...)

@riskynil - cheers, if you hadn't posted it I wouldn't have known where to begin :D I just tried to make it more beginner-friendly (but it seems I failed!) Karma coming your way.

edit:
one more idea if people still can't get this to work

try replacing the line
$matchstring = '/Can\'t open file: \'([a-zA-Z_ -]+)\.MYI\' \(errno: 145\)/';

with
$matchstring = '/SQL Error : 1016 Can\'t open file: \'([0-9a-zA-Z_ -]+)\.MYI\' \(errno: 145\)/';

or
$matchstring = '/^Can\'t open file: \'([0-9a-zA-Z_ -]+)\.MYI\' \(errno: 145\)/';

Basically it seems some people have extra info in their error messages that might be preventing the fix from picking up the problem correctly.

[mesmoviescom]

i get this:
Parse error: parse error, unexpected '=' in /www/m/mesmoviescom/htdocs/db/mysql4.php on line 337

after adding it

[msxsecurity.com]

Maybe you could physically edit the file at www.msxsecurity.com/mysql.txt and host the new one for us?


BTW: Check out www.mirc-scripts.de/dev/funny.htm for a good laugh

[DrewGreen]

Sorry, just noticed I'd left a line in that shouldn't be there (hey, it's late). I've corrected it in the posts above - double check to make sure you've got the right ones.

Alternatively, if you've got this line in the Query function, delete it:
Matches = " . $matches[1], "", __LINE__, __FILE__, $dbc);

[riskynil]

try replacing the line
$matchstring = '/Can\'t open file: \'([a-zA-Z_ -]+)\.MYI\' \(errno: 145\)/';

with
$matchstring = '/SQL Error : 1016 Can\'t open file: \'([0-9a-zA-Z_ -]+)\.MYI\' \(errno: 145\)/';

or
$matchstring = '/^Can\'t open file: \'([0-9a-zA-Z_ -]+)\.MYI\' \(errno: 145\)/';

I prefer the original one. I wasn't sure if the "SQL Error: 1016" would always show up, so I focused on the message instead since without the message, you wouldn't be able to parse out the offending MYI file to repair in the first place.

The second suggestion would work, but I'd avoid using the caret (^) at the beginning of the string. Like you said, other people might be getting extra information in their error messages (such as 'SQL Error: 1016') and the pattern won't match unless "Can't open file" is the beginning of the string. So I'd allow the search to match any part of the string rather than require it to match at the beginning of it.

-- Ryan

[msxsecurity.com]

OK, I got a working one done for invision 2.0.x !
get it @ www.msxsecurity.com/pw.txt

[DrewGreen]

well that was really clutching at straws as the first line works a ok for me. Then I realised I'd made a mistake in the code, caused when I was cleaning up unnecessary lines I'd put in and various curse word messages ;) D'oh!

Glad to hear you got it working eventually msxsecurity

[SilentK]

I think powweb has fixed the problem now. I know I am not having anymore issues and I am on mysql01

[msxsecurity.com]

My site is INCREDIBLY laggy right now but not corrupted tables ( I assume because of the script drew provided )

[DrewGreen]

When I first put this up on my site (http://www.musicdonkeyproject.org) I didn't notice much difference - I suspect it's more to do with slow sql databases (ok, so that's because of the code - it's repairing the table each time there's an error, and if the table keeps getting corrupted on a 30-second basis like my site was, that adds up to a lot of repair time).

You may as well leave the code in now that it's working - if the tables aren't corrupted it won't take much longer to execute than it did before. I'm only guessing here though & could be wrong... it's happened before :)

[msxsecurity.com]

If there is no tables to repair, does it add any extra queries?

[DrewGreen]

Here's a quick explanation:

function Query($query, $dbc) {
$result = mysql_query($query, $dbc);

if everything's ok, don't do this next part: (in bold) and skip to the next bit in italics

if ($result == FALSE) {
$error = mysql_error($dbc);

$matchstring = '/Can\'t open file: \'([a-zA-Z_ -]+)\.MYI\' \(errno: 145\)/';

if (preg_match($matchstring, $error, $matches) > 0) {
$repairQuery = 'REPAIR TABLE ' . $matches[1];
mysql_query($repairQuery, $dbc);
$result = mysql_query($query, $dbc);
}

}

just return to the original query function with the right info:

return $result;
}

if it goes into the bold part then there's an error of some sort, though not necessarily a corrupted table
If it goes into the bold & italic part then the database is definitely corrupted

In short, if everything's ok, you've only got a couple of extra lines (the ones that aren't bold or italic) to execute, plus a bit of extra time taken due to the original query function having to jump to the fix.
That shouldn't take much longer - a few milliseconds maybe? I don't know how fast it runs normally, but take a quick look at the size of the other scripts that make up your site, they're massive in comparison and only take a second or so

Hope that made sense :) Right, at 4:20am I'm off to bed (12 hours later and 4:20 would be a different story, ho ho ho)