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

          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.