Bug #77301 I_S.innodb_sys_tablestats.modified_counter doesn't change on UPDATE
Submitted: 10 Jun 2015 17:28 Modified: 29 Oct 2015 16:50
Reporter: Elena Stepanova Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.6/5.7/8.0 OS:Any
Assigned to: CPU Architecture:Any

[10 Jun 2015 17:28] Elena Stepanova 
Description:
Initially reported by our user as https://mariadb.atlassian.net/browse/MDEV-8297

According to https://dev.mysql.com/doc/refman/5.6/en/innodb-sys-tablestats-table.html, the MODIFIED_COUNTER value in information_schema.INNODB_SYS_TABLESTATS is supposed to change on all DML operations. However, the value doesn't change when I issue an UPDATE on an InnoDB table.

Output from the test case provided in 'How to repeat' section:

MySQL [test]> create table t1 (i int);
Query OK, 0 rows affected (0.55 sec)

MySQL [test]> select * from information_schema.innodb_sys_tablestats where name = 'test/t1';
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
| TABLE_ID | NAME    | STATS_INITIALIZED | NUM_ROWS | CLUST_INDEX_SIZE | OTHER_INDEX_SIZE | MODIFIED_COUNTER | AUTOINC | REF_COUNT |
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
|       50 | test/t1 | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         0 |
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
1 row in set (0.01 sec)

MySQL [test]> insert into t1 values (1);
Query OK, 1 row affected (0.29 sec)

MySQL [test]> insert into t1 values (2);
Query OK, 1 row affected (0.14 sec)

MySQL [test]> select * from information_schema.innodb_sys_tablestats where name = 'test/t1';
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
| TABLE_ID | NAME    | STATS_INITIALIZED | NUM_ROWS | CLUST_INDEX_SIZE | OTHER_INDEX_SIZE | MODIFIED_COUNTER | AUTOINC | REF_COUNT |
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
|       50 | test/t1 | Initialized       |        2 |                1 |                0 |                2 |       0 |         1 |
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
1 row in set (0.01 sec)

MySQL [test]> delete from t1 where i = 1;
Query OK, 1 row affected (0.05 sec)

MySQL [test]> select * from information_schema.innodb_sys_tablestats where name = 'test/t1';
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
| TABLE_ID | NAME    | STATS_INITIALIZED | NUM_ROWS | CLUST_INDEX_SIZE | OTHER_INDEX_SIZE | MODIFIED_COUNTER | AUTOINC | REF_COUNT |
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
|       50 | test/t1 | Initialized       |        1 |                1 |                0 |                3 |       0 |         1 |
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
1 row in set (0.00 sec)

MySQL [test]> update t1 set i = 4 where i = 2;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MySQL [test]> select * from information_schema.innodb_sys_tablestats where name = 'test/t1';
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
| TABLE_ID | NAME    | STATS_INITIALIZED | NUM_ROWS | CLUST_INDEX_SIZE | OTHER_INDEX_SIZE | MODIFIED_COUNTER | AUTOINC | REF_COUNT |
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
|       50 | test/t1 | Initialized       |        1 |                1 |                0 |                3 |       0 |         1 |
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
1 row in set (0.00 sec)

MySQL [test]> drop table t1;
Query OK, 0 rows affected (0.20 sec)

MySQL [test]> select @@version;
+----------------+
| @@version      |
+----------------+
| 5.7.7-rc-debug |
+----------------+
1 row in set (0.00 sec)

How to repeat:
drop table if exists t1;
set global innodb_stats_auto_recalc=off;
create table t1 (i int);
select * from information_schema.innodb_sys_tablestats where name = 'test/t1';
insert into t1 values (1);
insert into t1 values (2);
select * from information_schema.innodb_sys_tablestats where name = 'test/t1';
delete from t1 where i = 1;
select * from information_schema.innodb_sys_tablestats where name = 'test/t1';
update t1 set i = 4 where i = 2;
select * from information_schema.innodb_sys_tablestats where name = 'test/t1';
drop table t1;
select @@version;
[10 Jun 2015 18:43] MySQL Verification Team 
Thank you for the bug report.

C:\dbs>c:\dbs\5.8\bin\mysql -uroot -p --port=3580 --prompt="mysql 5.8 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.8.0-m17 Source distribution PULL 2015/06/05

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.8 > use d1
Database changed
mysql 5.8 > create table t1 (i int);
Query OK, 0 rows affected (0.25 sec)

mysql 5.8 > select * from information_schema.innodb_sys_tablestats where name = 'd1/t1';
+----------+-------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
| TABLE_ID | NAME  | STATS_INITIALIZED | NUM_ROWS | CLUST_INDEX_SIZE | OTHER_INDEX_SIZE | MODIFIED_COUNTER | AUTOINC | REF_COUNT |
+----------+-------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
|       37 | d1/t1 | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         0 |
+----------+-------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
1 row in set (0.00 sec)

mysql 5.8 > exit
Bye

C:\dbs>57

C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.8-rc Source distribution PULL 2015/06/05

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > use test
Database changed
mysql 5.7 > show tables;
Empty set (0.00 sec)

mysql 5.7 > drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.7 > set global innodb_stats_auto_recalc=off;
Query OK, 0 rows affected (0.00 sec)

mysql 5.7 > create table t1 (i int);
Query OK, 0 rows affected (0.31 sec)

mysql 5.7 > select * from information_schema.innodb_sys_tablestats where name = 'test/t1';
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
| TABLE_ID | NAME    | STATS_INITIALIZED | NUM_ROWS | CLUST_INDEX_SIZE | OTHER_INDEX_SIZE | MODIFIED_COUNTER | AUTOINC | REF_COUNT |
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
|       37 | test/t1 | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         0 |
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
1 row in set (0.00 sec)

mysql 5.7 > insert into t1 values (1);
Query OK, 1 row affected (0.03 sec)

mysql 5.7 > insert into t1 values (2);
Query OK, 1 row affected (0.03 sec)

mysql 5.7 > select * from information_schema.innodb_sys_tablestats where name = 'test/t1';
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
| TABLE_ID | NAME    | STATS_INITIALIZED | NUM_ROWS | CLUST_INDEX_SIZE | OTHER_INDEX_SIZE | MODIFIED_COUNTER | AUTOINC | REF_COUNT |
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
|       37 | test/t1 | Initialized       |        2 |                1 |                0 |                2 |       0 |         1 |
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
1 row in set (0.00 sec)

mysql 5.7 > delete from t1 where i = 1;
Query OK, 1 row affected (0.01 sec)

mysql 5.7 > select * from information_schema.innodb_sys_tablestats where name = 'test/t1';
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
| TABLE_ID | NAME    | STATS_INITIALIZED | NUM_ROWS | CLUST_INDEX_SIZE | OTHER_INDEX_SIZE | MODIFIED_COUNTER | AUTOINC | REF_COUNT |
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
|       37 | test/t1 | Initialized       |        1 |                1 |                0 |                3 |       0 |         1 |
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
1 row in set (0.00 sec)

mysql 5.7 > update t1 set i = 4 where i = 2;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql 5.7 > select * from information_schema.innodb_sys_tablestats where name = 'test/t1';
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
| TABLE_ID | NAME    | STATS_INITIALIZED | NUM_ROWS | CLUST_INDEX_SIZE | OTHER_INDEX_SIZE | MODIFIED_COUNTER | AUTOINC | REF_COUNT |
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
|       37 | test/t1 | Initialized       |        1 |                1 |                0 |                3 |       0 |         1 |
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
1 row in set (0.00 sec)

mysql 5.7 > drop table t1;
Query OK, 0 rows affected (0.09 sec)

mysql 5.7 > select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.8-rc  |
+-----------+
1 row in set (0.00 sec)

mysql 5.7 >
[12 Jun 2015 10:28] MySQL Verification Team 
Versions 5.6 and 5.8 affected too:

mysql 5.6 > delete from t1 where i = 1;
Query OK, 1 row affected (0.05 sec)

mysql 5.6 > select * from information_schema.innodb_sys_tablestats where name = 'test/t1';
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
| TABLE_ID | NAME    | STATS_INITIALIZED | NUM_ROWS | CLUST_INDEX_SIZE | OTHER_INDEX_SIZE | MODIFIED_COUNTER | AUTOINC | REF_COUNT |
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
|       29 | test/t1 | Initialized       |        1 |                1 |                0 |                3 |       0 |         1 |
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
1 row in set (0.00 sec)

mysql 5.6 > update t1 set i = 4 where i = 2;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql 5.6 > select * from information_schema.innodb_sys_tablestats where name = 'test/t1';
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
| TABLE_ID | NAME    | STATS_INITIALIZED | NUM_ROWS | CLUST_INDEX_SIZE | OTHER_INDEX_SIZE | MODIFIED_COUNTER | AUTOINC | REF_COUNT |
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
|       29 | test/t1 | Initialized       |        1 |                1 |                0 |                3 |       0 |         1 |
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
1 row in set (0.00 sec)

mysql 5.6 > drop table t1;
Query OK, 0 rows affected (0.11 sec)

mysql 5.6 > select @@version;
+-----------+
| @@version |
+-----------+
| 5.6.26    |
+-----------+
1 row in set (0.00 sec)

mysql 5.8 > delete from t1 where i = 1;
Query OK, 1 row affected (0.03 sec)

mysql 5.8 > select * from information_schema.innodb_sys_tablestats where name = 'test/t1';
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
| TABLE_ID | NAME    | STATS_INITIALIZED | NUM_ROWS | CLUST_INDEX_SIZE | OTHER_INDEX_SIZE | MODIFIED_COUNTER | AUTOINC | REF_COUNT |
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
|       38 | test/t1 | Initialized       |        1 |                1 |                0 |                3 |       0 |         1 |
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
1 row in set (0.00 sec)

mysql 5.8 > update t1 set i = 4 where i = 2;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql 5.8 > select * from information_schema.innodb_sys_tablestats where name = 'test/t1';
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
| TABLE_ID | NAME    | STATS_INITIALIZED | NUM_ROWS | CLUST_INDEX_SIZE | OTHER_INDEX_SIZE | MODIFIED_COUNTER | AUTOINC | REF_COUNT |
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
|       38 | test/t1 | Initialized       |        1 |                1 |                0 |                3 |       0 |         1 |
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
1 row in set (0.00 sec)

mysql 5.8 > drop table t1;
Query OK, 0 rows affected (0.17 sec)

mysql 5.8 > select @@version;
+-----------+
| @@version |
+-----------+
| 5.8.0-m17 |
+-----------+
1 row in set (0.00 sec)
[26 Oct 2015 14:46] Erik Cederstrand 
Ping? AFAICS, there is no alternative low-cost way of monitoring InnoDB table for changes without filesystem access and with no presumptions about table contents. CHECKSUM TABLE ... QUICK is only fast for MyISAM.
[29 Oct 2015 16:50] Daniel Price 
Posted by developer:
 
The following information was added to:
https://dev.mysql.com/doc/refman/5.7/en/innodb-sys-tablestats-table.html

"Table statistics are only updated for DELETE or UPDATE operations that
modify indexed columns. Statistics are not updated by operations that only
modify non-indexed columns."

This is a known limitation. 

Thank you for the bug report.
[18 Jun 2016 21:26] Omer Barnir 
Posted by developer:
 
Reported version value updated to reflect release name change from 5.8 to 8.0