MySQL Bugs: #23526: show table status reports incorrect values for MyISAM tables

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

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:
.

see top of file for compiling instructions.

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

note the jump from 14541 rows to 14420 to 14566 to 14420

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

4.1 doesn't appear to have this problem.

.

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

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.

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.

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.

bug#24575 was marked as a duplicate of this bug.

Pushed into trees currently marked as 5.0.36 and 5.1.15.

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.