Bug #71470 Is ANALYZE TABLE supposed to reset I_S.INNODB_SYS_TABLESTATS.NUM_ROWS to 0?
Submitted: 24 Jan 2014 14:51 Modified: 24 Jan 2014 19:07
Reporter: Laurynas Biveinis (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.6.17 OS:Any
Assigned to:
Tags: information_schema, innodb_sys_tablestats, num_rows
Triage: Needs Triage: D3 (Medium)

[24 Jan 2014 14:51] Laurynas Biveinis 
Description:
ANALYZE TABLE resets INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS.NUM_ROWS to zero.  Why?

This is somewhat similar to bug 71469, but I don't think it's a doc bug in this case (as "estimated number of rows in a table since last ANALYZE" would not make much sense)

Or maybe I'm missing something and this is not a bug?

How to repeat:
CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;

INSERT INTO t1 VALUES ();

SELECT NUM_ROWS FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE NAME LIKE 'test/t1';
NUM_ROWS
1

ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK

SELECT NUM_ROWS FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE NAME LIKE 'test/t1';
NUM_ROWS
0

DROP TABLE t1;

Suggested fix:
Do not reset NUM_ROWS on persistent stats update?
[24 Jan 2014 19:07] Sveta Smirnova 
Thank you for the report.

Verified as described. SHOW TABLE STATUS shows correct number of row.