Bug #23526 show table status reports incorrect values for MyISAM tables
Submitted: 21 Oct 2006 16:45 Modified: 25 Jan 2007 15:21
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S1 (Critical)
Version:5.0.26,5.0BK,5.1BK OS:Linux (suse, windows x86)
Assigned to: Sergey Vojtovich CPU Architecture:Any
Tags: bfsm_2006_11_02, Incorrect Results, myisam, row count, show table status

[21 Oct 2006 16:45] Shane Bester
Description:
show table status like 'myisam_table' reports wrong values sometimes.

In particular, the rowcount decreases and increases on a table that is receiving only inserts.  There's no way a decrease in row count should happen in this case.

sometimes the row count doesn't increase, even though inserts are happening continuously.  select count(*) returns correct result.  the show table status returns correct result after FLUSH TABLES is run.

see attached myisam_show_table_status.txt file to see a session output.

How to repeat:
compile the attached testcase.c program.  while it runs, log into mysql command line client and watch the output from 

show table status like 'sort';  do this a few times and notice the values are occoasionally incorrect.

Suggested fix:
.
[21 Oct 2006 16:46] Shane Bester
see top of file for compiling instructions.

Attachment: testcase.c (text/x-csrc), 3.78 KiB.

[21 Oct 2006 16:47] Shane Bester
note the jump from 14541 rows  to 14420 to 14566 to 14420

Attachment: myisam_show_table_status.txt (plain/text, text), 13.48 KiB.

[21 Oct 2006 16:51] Shane Bester
4.1 doesn't appear to have this problem.
[21 Oct 2006 17:40] Shane Bester
.

Attachment: another_session_output.txt (plain/text, text), 13.68 KiB.

[9 Nov 2006 11:54] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15083

ChangeSet@1.2294, 2006-11-09 15:55:27+04:00, svoj@mysql.com +3 -0
  BUG#23526 - show table status reports incorrect values for MyISAM tables
  
  This problem could happen when show table status get outdated copy
  of TABLE object from table cache.
  
  MyISAM updates state info when external_lock() method is called. Though
  I_S does not lock a table to avoid deadlocks. If I_S opens a table which
  is in a table cache it will likely get outdated state info copy.
  
  In this case shared state copy is more recent than local copy. This problem
  is fixed by reading state info directly from share not locking it.
  
  As a side effect show table status will report outdated state info when
  it is run inside LOCK TABLES.
  
  Affects MyISAM only.
[15 Nov 2006 12:46] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15353

ChangeSet@1.2294, 2006-11-15 16:47:34+04:00, svoj@mysql.com +1 -0
  BUG#23526 - show table status reports incorrect values for MyISAM tables
  
  This problem could happen when show table status get outdated copy
  of TABLE object from table cache.
  
  MyISAM updates state info when external_lock() method is called. Though
  I_S does not lock a table to avoid deadlocks. If I_S opens a table which
  is in a table cache it will likely get outdated state info copy.
  
  In this case shared state copy is more recent than local copy. This problem
  is fixed by reading state info directly from share not locking it.
  
  Affects MyISAM only. No good deterministic test case for this fix.
[5 Dec 2006 14:44] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/16460

ChangeSet@1.2303, 2006-12-05 18:44:14+04:00, svoj@mysql.com +5 -0
  BUG#23526 - show table status reports incorrect values for MyISAM tables
  
  This problem could happen when show table status get outdated copy
  of TABLE object from table cache.
  
  MyISAM updates state info when external_lock() method is called. Though
  I_S does not lock a table to avoid deadlocks. If I_S opens a table which
  is in a table cache it will likely get outdated state info copy.
  
  In this case shared state copy is more recent than local copy. This problem
  is fixed by correctly restoring myisam state info pointer back to original
  value, that is to shared state.
  
  Affects MyISAM only. No good deterministic test case for this fix.
[6 Dec 2006 12:02] Sergey Vojtovich
bug#24575 was marked as a duplicate of this bug.
[25 Jan 2007 8:46] Sergey Vojtovich
Pushed into trees currently marked as 5.0.36 and 5.1.15.
[25 Jan 2007 15:21] Paul Dubois
Noted in 5.0.36, 5.1.15 changelogs.

The row count for MyISAM tables was not updated properly, causing
SHOW TABLE STATUS to report incorrect values.