As I said earlier, I have troubles with 3 tables from over a
hundred. In my quest to find out what happens, I converted those
3 from isam to myisam. Hence I am able to check from my program
if the tables are corrupt and then try to correct them.
The most important table is the session table. This table must
definitely run, else many things don't work correct.
I have lots of other tables which get written to and deleted
from, but never had a problem with them. This one is under heavy
load, though.
I have sessions for a long time. In the past, I didn't have problems.
There are times when it will run fine for days (e.g. this week
Mon-Wed). Tonight, I was testing some regular stuff. It used
sessions. Next I realized the site didn't respond.
I checked top & usage, which was both fine. So I looked for
php or mysql. processlist showed some few records the like
| 21527 | pferdezeitung | localhost | pferdezeitung | Query | 647
| Waiting for table | SELECT * FROM sessData WHERE sid = 'fa9b'
and some more
| 21528 | pferdezeitung | localhost | pferdezeitung | Query | 900
| Waiting for table | CHECK TABLE sessData
I stopped mysql and restarted, everything was fine. The table
sessData was corrupt, I repaired manually. Now I could
investigate.
I send myself an email when something goes wrong. Here I had some
3500 mails waiting - no problem now that I have a DSL flatrate
connection. But how come?
Well, nearly all of them were generated by a while loop which
could not stop because of a never fulfilled condition. This in
turn happened because
Table 'savers' was not locked with LOCK TABLES
What? What does that mean? I made a simple query
SELECT * FROM savers where id = '-1';
I didn't LOCK that table for sure - but the message said it was
NOT locked????
Well, some days ago I asked about LOCK TABLE, because I wanted to
implement a function which would repair the table automatically.
And I wasn't sure if I had to use LOCK TABLE for that table to be
repaired.
Something went wrong and chances are I didn't understand the
process. I hope one of you can give me a hint.
In part the plan worked out fine. Here is the scenario: whenever I
touch this table liking to go corrupt, I invoke my function
checkTable first which should check & repair the table. Then I am
sure the table is ok and I do my thing.
function checkTable($table){
$db = newDB($db);//PHPLIB-style
$q = "CHECK TABLE $table";
$db->query($q);
if ($db->nf()){
//just to make sure we have some result
while ($db->next_record()){
if ($db->f('Msg_text') != "The handler for the table doesn't support
check/repair"){
//make sure it can be repaired - just in case
if ($db->f('Msg_text') != 'OK'){
//something is wrong
$ar[] = $db->f('Msg_text');
//collect all the messages in an array
}
mailSafe('[EMAIL PROTECTED]',
$q,
$ar);
//send me the result and the query
//this function is modified mail(), which can handle arrays
//properly, among other things
}
}
if ($ar) {
//we have some problem
la($ar, "\\checkTable.php3 $table");//-*
//list-array, show it on screen in case I am testing
$q = "LOCK TABLE $table WRITE";
//here I don't know - is that necessary or correct?
//I thought so.
$db->query($q);
$q = "REPAIR TABLE $table";
$db->query($q);
//do it - hopefully
if ($db->nf()){
while ($db->next_record()){
$arR[] = $db->f('Msg_text');
//see what it says
}
if (lastElement($arR) != 'OK'){
//that's bad - last Element should be OK
la($arR, "\\checkTable.php3 $table");//-*
}
mailSafe('[EMAIL PROTECTED]',
$q,
$arR);
}
$q = "UNLOCK TABLE $table";
$db->query($q);
}
}
}
The havoc starts with regular Error mails:
Duplicate entry '06e7' for key 1,
Invalid SQL: INSERT INTO sessData
(sid, varsVal, datum)
VALUES ('06e7',
'a:12:{s:11:\"zM\";s:11:\"Ls\";s:9:\"bv\";s:9:\"333333333\";s:5:\"bvB\";s:8:\"22222222\";s:8:\"bvN\";s:7:\"1111111\";s:9:\"sessionID\";s:4:\"06e7\";s:3:\"kid\";s:2:\"78\";s:7:\"oldHost\";s:16:\"pferdezeitung.de\";s:2:\"nr\";N;s:3:\"bid\";N;s:9:\"kostenlos\";N;s:11:\"uriRedirect\";N;s:15:\"titleB\";N;}',
'2001-07-12 02:42:49');
In this minute, we have 11 seconds left.
This is a typical fault condition: I was working with that key
for some time, so it was a unique key present in the table. I
know, as a rule, that I own this key, so I proceed as follows:
$q = "SELECT * FROM sessData
WHERE sid = '$GLOBALS[sessionID]'";
$db->query($q);
if ($db->nf()){
$q = "UPDATE sessData
SET varsVal='$serData', datum='$datum'
WHERE sid = '$GLOBALS[sessionID]'";
$db->query($q);
}
else{
$q = "INSERT INTO sessData
(sid, varsVal, datum)
VALUES ('$GLOBALS[sessionID]', '$serData', '$datum')";
$db->query($q);
}
So the table must have been corrupt in the first place, as the
key was not found, next the insert failed because of the very
same key - how come?
Funny thing, I get a second message with the same content at the
same time (well, mail time, I don't record microtime yet).
Next message is
Got error 127 from table handler,
SELECT * FROM sessData
WHERE sid = '06e7';
so the table knows now it is corrupt.
This I get a coupe of times mixed with insert errors, 12 messages
total, all at 02:42 our time. Then the LOCKED errors start
coming, first all kinds of tables, then after a while, it is
savers only which is in the loop - everything else is dead by
now, I guess.
At 02:42, I had 59 normal error messages, later about 300/min.
The mail from checkTable sessData, also from 02:42, said:
Record-count is not ok; is 3184 Should be: 3183
Size of datafile is: 1302860 Should be: 579332
Found 10399 parts Should be: 3183 parts
Found 4113 deleted blocks Should be: 0
Corrupt
0 clients is using or hasn't closed the table properly
Then the function checkTable LOCKED that table and something went
wrong. I didn't get any message nor was the table repaired.
Instead, MySQL tells me that another table was NOT locked.
I think that's all I can tell about it.
At first measure, I disabled that LOCK command in my function.
Things are stable now.
Any ideas? Well, it is 04:48 a.m. here now, time to get a little
sleep, so I won't respond the next hours. Thank you all.
--
Herzlich
Werner Stuerenburg
_________________________________________________
ISIS Verlag, Teut 3, D-32683 Barntrup-Alverdissen
Tel 0(049) 5224-997 407 · Fax 0(049) 5224-997 409
http://pferdezeitung.de
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
