MySQL Bugs: #71469: INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS.MODIFIED

Bug #71469	INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS.MODIFIED_COUNTER resets not documented
Submitted:	24 Jan 2014 10:49	Modified:	11 Feb 2014 16:02
Reporter:	Laurynas Biveinis (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.6	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	doc, information_schema, innodb_sys_tablestats, modified_counter

Description:
The description of MODIFIED_COUNTER at https://dev.mysql.com/doc/refman/5.6/en/innodb-sys-tablestats-table.html reads

"The number of rows modified by DML operations, such as INSERT, UPDATE, DELETE, and also cascade operations from foreign keys."

Based on this, one would expect this counter to count from the server start (if persistent stats are disabled) or table create time (if persistent stats are enabled). However, this is not the case, as this counter is reset at least every time the table stats are recalculated.

How to repeat:
Run

CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;

let $i=1000;

--disable_query_log
--disable_result_log
while ($i)
{
        INSERT INTO t1 VALUES ();
        dec $i;
}
--enable_result_log
--enable_query_log

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

SELECT COUNT(*) FROM t1;

This shows MODIFIED_COUNTER value of 96 after a 1000 inserts, presumably due to stat updates.

Furthermore,

ANALYZE TABLE t1;

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

will show that ANALYZE TABLE resets the counter to 0.

Suggested fix:
Describe when MODIFIED_COUNTER is reset.

See also bug 71470.

Thank you for the report.

Verified as described.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.