Table IN USE?

[ticoroman]

What does it mean when phpMyAdmin reports that at table is IN USE. The table is unaccesible and the only way to make it back is to do a repair. This has happened several times in the last hour. What's going on?

[ticoroman]

Hey, my tables are breaking one by one... Wtf is going on?

MySQL server: mysql01

I've never head such problems before. The tables are locked and the lock are never released...

[riskynil]

What does it mean when phpMyAdmin reports that at table is IN USE. The table is unaccesible and the only way to make it back is to do a repair. This has happened several times in the last hour. What's going on?

Holy jumping junipers! I just got a table with that error! I ran "REPAIR TABLE" on it which seemed to fix it for all of about five minutes before the table stopped working again. I'd like to know what's going on--I haven't changed any of my code relating to that table since I first created it nearly a year ago.

When I run "CHECK TABLE", I get the following message about it:

Table is marked as crashed
Found key at page 2731008 that points to record ou...
Corrupt

Fortunately, it's a non-critical table in my database. I just store each page that a user views in it, so I removed the line of code that adds a new record to that table with each page view and everything seems to be working okay. Well, except that when I repair the table, it lasts for a few minutes before it stops working again!

-- Ryan

[ticoroman]

I'm "glad" that there are more people with this problem. It means that the error might be fixed...

Yes, my tables too brakes after a few minutes. It seems to effect tables where the ID key is "big" (higher than 1 000 000).


PS. It crashed again... just 2 minutes after last repair. Please fix it!

[nmctwx]

I use MySql08 and for the past week, I've been having problems with my tables. I haven't added anything recently that would cause it to break.

I have seen 'in use' errors and a few others that I have fixed using the repair command - (I didn't save a list of the errors). Now I am getting errors in many of my tables saying "PRIMARY and INDEX keys should not both be set for column `id`". Even for some tables that aren't used!

[riskynil]

Okay, I've been playing around with my own errors. When I run REPAIR TABLE, it says everything is fixed and all is well. Then crash, almost immediately it goes down again. So I tried REPAIR TABLE EXTENDED and it says, "Sorry, beyond repair, bub.". (Well, not in so many words, but that's the jist.)

So I said to hell with it, and I ran REPAIR TABLE again and everything is supposedly okey-dokey. I don't really think it is, but at least I can do some things this way. I back up the table, copy it to my development machine, and load it up. Everything seems to work okay. *shrug*

So I go back to the live site and delete all the data in the table. (It had about 200,000 rows, though the id numbers as you mentioned before are up in the 1,200,000 range.) Like I said, it was non-essential data and there was no harm in deleting it. It's just a record of all the pages viewed.

Did a REPAIR TABLE EXTENDED and it says everything is okay. I should hope so given the fact there's no data in it to be corrupted. Turn back on the page logging on the live site. It's now been about a half hour with no new crashes with over 50 page views and counting. I'm cautiously optimistic the problem is gone. *fingers crossed* Not sure what caused the problem in the first place, though.

If you have pertinent data in your tables that you can't just delete like I did, you might try backing it up, dropping the table, then readding it with your backup and see if that fixes the table once and for all.

Good luck! =)

-- Ryan

[ticoroman]

It could be a way to do it, but I would rather like that Powweb fixes the problem. This is something that didn't occur earlier even though I had tables in the same size and ID key value range.

Therefore this is a server error on the mysql servers (unfortunately I'm not surprised). I just wonder where all the PW-s are hiding, instead of fixing the issue.. roll back the last change or whatever, just make it work like it always did....

...I just hate when changes are done to the servers without any notice.

[riskynil]

I was hopeful, but alas, that same table crashed again. This time, I just dropped it and readded it. I also updated the code on my site so if the function that inserts a new row fails, it'll ignore the error and continue. So at least it won't take down my site anymore--even if the table crashes yet again.

But this is really starting to tick me off! What the heck is going on?

-- Ryan

[unproductive]

Yes, I am also having issues with mysql01. It will not connect anymore and I'm getting all sorts of errors. I'm not even going to go in and bother with repairing the tables as that doesn't seem to work for very long. It would be nice to hear something from powweb.

[riskynil]

Okay, I've hacked in a little code that I *hope* keeps my site running without needing constant babysitting. For those of you who are interested, I tried creating my own wrapper function for a query that detects if a query failed and if it failed because the table was "in use". Then it'll run a REPAIR TABLE on that table and try the query again. So far, my site has been working error free for about an hour now! Wooo-who! =)

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

Good luck to anyone out there that wants to give it a whirl! =)

-- Ryan

[ticoroman]

I just got a response from the support, and they said there was an issue with mysql that has been fixed. Hopefully everything is OK now.

Oh no, it just happened again, so it's not fixed!

[mathieumg]

I have the same damn problem.

Flushed the table, tried to reload last night's backup, still nothing...