[MDEV-8297] information_schema.innodb_sys_tablestats.modified_counter doesn't change on UPDATE Created: 2015-06-10  Updated: 2016-04-21  Resolved: 2015-12-15

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.0, 10.1
Fix Version/s: 10.0.23, 10.1.10

Type: Bug Priority: Major
Reporter: Erik Cederstrand Assignee: Jan Lindström (Inactive)
Resolution: Fixed Votes: 0
Labels: upstream-wontfix
Environment:

10.0.14-MariaDB installed via homebrew on OS X 10.10.3

Sprint: 10.0.23

 Description   

According to https://dev.mysql.com/doc/refman/5.6/en/innodb-sys-tablestats-table.html (can't find the corresponding MariaDB documentation), 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:

MariaDB [baz]> SELECT `name`, `num_rows`, `modified_counter` FROM `information_schema`.`innodb_sys_tablestats` WHERE `name` = 'baz/foo';
 
+---------+----------+------------------+
 
| name    | num_rows | modified_counter |
 
+---------+----------+------------------+
 
| baz/foo |        8 |                3 |
 
+---------+----------+------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [baz]> insert into foo (id, bar) values (1, '');
 
Query OK, 1 row affected (0.00 sec)
 
 
 
MariaDB [baz]> SELECT `name`, `num_rows`, `modified_counter` FROM `information_schema`.`innodb_sys_tablestats` WHERE `name` = 'baz/foo';
 
+---------+----------+------------------+
 
| name    | num_rows | modified_counter |
 
+---------+----------+------------------+
 
| baz/foo |        9 |                4 |
 
+---------+----------+------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [baz]> update foo set bar='A' where id=1;
 
Query OK, 1 row affected (0.01 sec)
 
Rows matched: 1  Changed: 1  Warnings: 0
 
 
 
MariaDB [baz]> SELECT `name`, `num_rows`, `modified_counter` FROM `information_schema`.`innodb_sys_tablestats` WHERE `name` = 'baz/foo';
 
+---------+----------+------------------+
 
| name    | num_rows | modified_counter |
 
+---------+----------+------------------+
 
| baz/foo |        9 |                4 |
 
+---------+----------+------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [baz]> update foo set bar='B' where id=1;
 
Query OK, 1 row affected (0.01 sec)
 
Rows matched: 1  Changed: 1  Warnings: 0
 
 
 
MariaDB [baz]> SELECT `name`, `num_rows`, `modified_counter` FROM `information_schema`.`innodb_sys_tablestats` WHERE `name` = 'baz/foo';
 
+---------+----------+------------------+
 
| name    | num_rows | modified_counter |
 
+---------+----------+------------------+
 
| baz/foo |        9 |                4 |
 
+---------+----------+------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [baz]> delete from foo where id=1;
 
Query OK, 1 row affected (0.01 sec)
 
 
 
MariaDB [baz]> SELECT `name`, `num_rows`, `modified_counter` FROM `information_schema`.`innodb_sys_tablestats` WHERE `name` = 'baz/foo';
 
+---------+----------+------------------+
 
| name    | num_rows | modified_counter |
 
+---------+----------+------------------+
 
| baz/foo |        8 |                5 |
 
+---------+----------+------------------+
 
1 row in set (0.00 sec)

I was expecting modified_counter to also change on the UPDATE statements.

I'm trying to use these two values to emulate CHECKSUM TABLE foo QUICK; from MyISAM, to monitor for data changes to an InnoDB table.

 Comments   
Comment by Elena Stepanova [ 2015-06-10 ]

Same with MySQL 5.7:

MySQL [test]> drop table if exists t1;
 
Query OK, 0 rows affected (0.22 sec)
 
 
 
MySQL [test]> set global innodb_stats_auto_recalc=off;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
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)

Comment by Elena Stepanova [ 2015-06-10 ]

ErikCederstrand,
Thanks for the report.
Since it's an upstream issue, our normal routine is to report it to bugs.mysql.com as well. Are you willing to do so, or should I do it on your behalf?

Comment by Erik Cederstrand [ 2015-06-10 ]

Hello Elena,

Thanks for the confirmation. I would be grateful if you would report upstream on my behalf.

Comment by Elena Stepanova [ 2015-06-10 ]

Filed as http://bugs.mysql.com/bug.php?id=77301

Comment by Elena Stepanova [ 2015-10-29 ]

Response from upstream:

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.

Comment by Elena Stepanova [ 2015-10-29 ]

Assigning to jplindst to decide whether we want to do anything about it on MariaDB side.

Comment by Erik Cederstrand [ 2015-10-29 ]

I'm happy to accept a #wontfix here, too, if there is an alternative way of monitoring an InnoDB table for (any) changes without requiring filesystem access and with performance comparable to CHECKSUM TABLE ... QUICK for MyISAM.

Comment by Jan Lindström (Inactive) [ 2015-12-15 ]

commit 98c9fbfa215242f2930990a0a80b9c5642d23e58
Author: Jan Lindström <jan.lindstrom@mariadb.com>
Date: Tue Dec 15 11:27:08 2015 +0200

MDEV-8297: information_schema.innodb_sys_tablestats.modified_counter doesn't change on UPDATE

Update modified-counter also if update effects non-indexed columns.

Comment by Erik Cederstrand [ 2016-04-13 ]

Sorry to reopen this, but there's still something unexpected going on (for me, at least) with both indexed and non-indexed columns.

Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 1785
 
Server version: 10.1.11-MariaDB Homebrew
 
 
 
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MariaDB [test]> drop table t1;
 
Query OK, 0 rows affected (0.04 sec)
 
 
 
MariaDB [test]> create table t1 (i int primary key, j int);
 
Query OK, 0 rows affected (0.03 sec)
 
 
 
MariaDB [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 |
 
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
 
|    14999 | test/t1 | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         0 |
 
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
 
1 row in set (0.03 sec)
 
 
 
MariaDB [test]> insert into t1 values (1, 2);
 
Query OK, 1 row affected (0.00 sec)
 
 
 
MariaDB [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 |
 
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
 
|    14999 | test/t1 | Initialized       |        1 |                1 |                0 |                1 |       0 |         1 |
 
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
 
1 row in set (0.00 sec)

Looks good. num_rows is 1 and modified_counter is 1.

MariaDB [test]> update t1 set i=2;
 
Query OK, 1 row affected (0.02 sec)
 
Rows matched: 1  Changed: 1  Warnings: 0
 
 
 
MariaDB [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 |
 
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
 
|    14999 | test/t1 | Initialized       |        1 |                1 |                0 |                0 |       0 |         1 |
 
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
 
1 row in set (0.00 sec)

What? modified_counter counts backwards on UPDATE?

MariaDB [test]> update t1 set i=3;
 
Query OK, 1 row affected (0.01 sec)
 
Rows matched: 1  Changed: 1  Warnings: 0
 
 
 
MariaDB [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 |
 
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
 
|    14999 | test/t1 | Initialized       |        1 |                1 |                0 |                1 |       0 |         1 |
 
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
 
1 row in set (0.00 sec)

What? num_rows and modified_counter are now the same with table contents [(1, 2)] and [(3, 2)], even though I did two updates in a row.

MariaDB [test]> update t1 set i=3;
 
Query OK, 0 rows affected (0.00 sec)
 
Rows matched: 1  Changed: 0  Warnings: 0
 
 
 
MariaDB [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 |
 
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
 
|    14999 | test/t1 | Initialized       |        1 |                1 |                0 |                1 |       0 |         1 |
 
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
 
1 row in set (0.00 sec)

No-op UPDATE doesn't change values. Looks good.

MariaDB [test]> update t1 set j=3;
 
Query OK, 1 row affected (0.01 sec)
 
Rows matched: 1  Changed: 1  Warnings: 0
 
 
 
MariaDB [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 |
 
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
 
|    14999 | test/t1 | Initialized       |        1 |                1 |                0 |                2 |       0 |         1 |
 
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
 
1 row in set (0.00 sec)

Looks good, modified_counter got incremented.

MariaDB [test]> update t1 set j=3;
 
Query OK, 0 rows affected (0.00 sec)
 
Rows matched: 1  Changed: 0  Warnings: 0
 
 
 
MariaDB [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 |
 
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
 
|    14999 | test/t1 | Initialized       |        1 |                1 |                0 |                3 |       0 |         1 |
 
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
 
1 row in set (0.00 sec)

What? No-op UPDATE increments modified_counter even though MariaDB reports "Changed: 0".

But maybe this is intentional, and I'm just misunderstanding the way num_rows and modified_counter is supposed to work?

Comment by Erik Cederstrand [ 2016-04-21 ]

I opened MDEV-9963 to track the issues in the last comment.

Generated at Thu Feb 08 07:26:05 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.