Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8297

information_schema.innodb_sys_tablestats.modified_counter doesn't change on UPDATE

        Details

        • Type: Bug
        • Status: Confirmed
        • Priority: Major
        • Resolution: Unresolved
        • Affects Version/s: 10.1, 10.0
        • Fix Version/s: 10.1, 10.0
        • Component/s: Storage Engine - InnoDB
        • Labels:
        • Environment:
          10.0.14-MariaDB installed via homebrew on OS X 10.10.3

          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.

            Gliffy Diagrams

              Attachments

                Issue Links

                links to

                Web Link Bug #77301 - I_S.innodb_sys_tablestats.modified_counter doesn't change on UPDATE

                  Activity

                  Ascending order - Click to sort in descending order
                  Hide
                  Permalink
                  elenst Elena Stepanova added a comment -

                  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)
                  Show
                  elenst Elena Stepanova added a comment - 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)
                  Hide
                  Permalink
                  elenst Elena Stepanova added a comment -

                  Erik Cederstrand,
                  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?

                  Show
                  elenst Elena Stepanova added a comment - Erik Cederstrand , 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?
                  Hide
                  Permalink
                  ErikCederstrand Erik Cederstrand added a comment -

                  Hello Elena,

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

                  Show
                  ErikCederstrand Erik Cederstrand added a comment - Hello Elena, Thanks for the confirmation. I would be grateful if you would report upstream on my behalf.
                  Hide
                  Permalink
                  elenst Elena Stepanova added a comment -

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

                  Show
                  elenst Elena Stepanova added a comment - Filed as http://bugs.mysql.com/bug.php?id=77301

                    People

                    • Assignee:
                      Unassigned
                      Reporter:
                      ErikCederstrand Erik Cederstrand
                    • Votes:
                      0 Vote for this issue
                      Watchers:
                      2 Start watching this issue

                      Dates

                      • Created:
                        Updated: