Bug #84 Bug in state info during an auto-repair launched by myisam-recover option
Submitted: 19 Feb 2003 17:10 Modified: 10 Aug 2003 8:03
Reporter: jocelyn fournier (Silver Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S3 (Non-critical)
Version:4.x OS:Any (all)
Assigned to: CPU Architecture:Any

[19 Feb 2003 17:10] jocelyn fournier 
Description:
When using myisam-recover, after a MySQL crash when checking/reparing tables process are launched, the status displayed by SHOW PROCESSLIST are sometimes completely wrong.

e.g. :

|   10 | mysql | localhost | Hardwarefr | Query   |  649 | Repair by
sorting | SELECT COUNT(*) FROM threadhardwarefr1  |
| 2052 | DELAYED | localhost | Hardwarefr | Delayed_insert |   44 | Checking table           |  |

How to repeat:
Launched myisam-recover process (using myisam-recover=BACKUP,FORCE,QUICK options) on a database with several crashed tables, with normal queries running on crashed and non crashed tables in parallel.

Suggested fix:
Don't know.
I noticed the only difference between myisam-recover and mysqlcheck is
that myisam-recover doesn't wait that an operation on a table stops before
launching another process.
(e.g. : 

030204 13:19:04  Warning: Checking table:   './Hardwarefr/config'
030204 13:19:06  Warning: Recovering table: './Hardwarefr/config'
030204 13:19:08  Warning: Checking table:
'./forum_hardwarefr/searchmainhardwarefr13'
030204 13:19:08  1 clients is using or hasn't closed the table properly
030204 13:19:08  Warning: Retrying repair of: './Hardwarefr/config' with
keycache

"Checking table: './forum_hardwarefr/searchmainhardwarefr13'" is launched although "Recovering table: './Hardwarefr/config'" is not yet finished).)
[8 Apr 2003 7:40] MySQL Verification Team 
Sorry I wasn't be able to repeat the behavior reported.
Probably that was already fixed.
[18 Apr 2003 10:44] jocelyn fournier 
Hi,

Sorry I've just tested with the latest MySQL-4.0 tree and the bug is still here :

show processlist;

<cut>
|  50 | mysql   | localhost | forum_presencepc        | Query          | 24   | Checking table     | DELETE FROM searchmainpresencepc4 WHERE numreponse='233844'                                          |
| 150 | DELAYED | :3632     | forum_powerattitude     | Delayed_insert | 19   | Waiting for INSERT |                                                                                                      |
| 151 | DELAYED |           | forum_powerattitude     | Delayed_insert | 19   | Waiting for INSERT |                                                                                                      |
| 288 | mysql   | localhost | presence                | Sleep          | 8    |                    | NULL                                                                                                 |
| 298 | mysql   | localhost | presence                | Sleep          | 6    |                    | NULL                                                                                                 |
| 299 | mysql   | localhost | presence                | Sleep          | 6    |                    | NULL                                                                                                 |
| 310 | DELAYED |           | forum_sosordidiscussion | Delayed_insert | 5    | Waiting for INSERT |                                                                                                      |
| 311 | DELAYED |           | forum_sosordidiscussion | Delayed_insert | 5    | Waiting for INSERT |                                                                                                      |
| 313 | mysql   | localhost | presence                | Sleep          | 5    |                    | NULL                                                                                                 |
| 319 | mysql   | localhost | presence                | Sleep          | 5    |                    | NULL                                                                                                 |
| 320 | mysql   | localhost | forum_overclex          | Query          | 5    | Checking table     | DELETE FROM searchmainoverclex1 WHERE numreponse='49705'                                             |
| 341 | mysql   | localhost | presence                | Sleep          | 3    |                    | NULL                                                                                                 |
| 346 | mysql   | localhost | presence                | Sleep          | 2    |                    | NULL                                                                                                 |
| 349 | mysql   | localhost | presence                | Sleep          | 1    |                    | NULL                                                                                                 |
| 353 | mysql   | localhost | presence                | Sleep          | 1    |                    | NULL                                                                                                 |
| 364 | mysql   | localhost | prix                    | Query          | 0    | Checking table     | SELECT date, magasin, IP FROM stats WHERE date='2003-04-18' AND magasin='5' AND IP='194.158.104.34'  |
<cut>

Perhaps the delayed threads are also involved in this bug ?

Regards,
  Jocelyn
[18 Apr 2003 10:50] MySQL Verification Team 
Sorry can't see a bug.

Which of those threads is repairing a table ??
[18 Apr 2003 10:53] jocelyn fournier 
Take a look to the threads which have the "Checking table" state ;)
[18 Apr 2003 10:56] jocelyn fournier 
Hmmm strange, my comment dropped the Status field of this bug ?!
[18 Apr 2003 10:58] MySQL Verification Team 
And a time column shows what ???
And how long do threads stay in that state ??

And does changin recover to FORCE helps anything ??
[18 Apr 2003 11:06] jocelyn fournier 
My my.cnf has the following option :

myisam-recover=BACKUP,FORCE,QUICK

The threads stay in this state all time of the check of the table (the tables are really checked according to the .err file, but this is not properly written in the processlist.) 
To force the myisam-recover process, I just used killall -9 mysqld.
[18 Apr 2003 11:09] MySQL Verification Team 
What if you put just FORCE ??
[18 Apr 2003 11:13] jocelyn fournier 
same result :

<cut>
|   5 | mysql   | localhost | forum_presencepc        | Query          | 10   | Checking table     | SELECT contenu,id FROM threadpresencepc4 WHERE numreponse='233816'      
| 162 | mysql   | localhost | forum_overclex          | Query          | 1    | Checking table     | DELETE FROM searchmainoverclex2 WHERE numreponse='18601'                                             |
| 165 | mysql   | localhost | forum_crazycity         | Query          | 1    | Checking table     | SELECT 1 FROM threadcrazycity8 WHERE numeropost='431' AND id='5908' LIMIT 1  
<cut>
[18 Apr 2003 11:23] MySQL Verification Team 
Please clarify.

Do threads stay for ever in that state, or is state changed once table(s) are repaired ??
[18 Apr 2003 11:24] MySQL Verification Team 
Please clarify.

Do threads stay for ever in that state, or is state changed once table(s) are repaired ??
[18 Apr 2003 11:27] jocelyn fournier 
Once the table is checked/repared the thread disappears from the show processlist list, but during the check/repair of the table, the thread stays in the same state.
[18 Apr 2003 11:41] MySQL Verification Team 
Yes, that is how it should work.
[18 Apr 2003 11:43] jocelyn fournier 
Yes, excepted the thread Info is wrong ;)
[18 Apr 2003 12:17] MySQL Verification Team 
It should say: Repairing table ???

Ok, we can fix that.
[18 Apr 2003 12:22] jocelyn fournier 
No absolutely not !!

"Checking table" status is right !!
but take a look at the associated Info :

i.e. : "DELETE FROM searchmainpresencepc4 WHERE
numreponse='233844'"

I don't see any link between "DELETE FROM searchmainpresencepc4 WHERE
numreponse='233844'" and checking/repairing table status.

"CHECK TABLE searchmainpresencepc4" should be writing in the status field !
[18 Apr 2003 12:39] MySQL Verification Team 
Sorry, it can't.

User did not run CHECK / REPAIR TABLE ...
[18 Apr 2003 12:43] jocelyn fournier 
Well, I don't understand : if you run mysqlcheck, it prints explicitely in show processlist the repair/check command.
This is not possible with myisam-recover ?
[15 May 2003 8:23] Michael Widenius 
Some additonal notes.

The myisam-recover option tells MySQL to automaticly repair an crashed MyISAM table if it notes that it was not closed properly. In this case
"SHOW PROCESS LIST" will show "Checking table" in the status column and in the Query column show the command that triggered the REPAIR.

It should not show CHECK or REPAIR table in the query column as this is not the command the user evoked. I belive that showing CHECK/REPAIR in the Query column could confuse the user to belive that someone had actually executed these commands.