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

    • 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.

      Attachments

        Activity

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

          elenst Elena Stepanova added a comment - Assigning to jplindst to decide whether we want to do anything about it on MariaDB side.

          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.

          ErikCederstrand Erik Cederstrand added a comment - 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.

          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.

          jplindst Jan Lindström (Inactive) added a comment - 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.

          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?

          ErikCederstrand Erik Cederstrand added a comment - 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?

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

          ErikCederstrand Erik Cederstrand added a comment - I opened MDEV-9963 to track the issues in the last comment.

          People

            jplindst Jan Lindström (Inactive)
            ErikCederstrand Erik Cederstrand
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.