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

information_schema.innodb_sys_tablestats.modified_counter doesn't change on UPDATE

    XMLWordPrintable

    Details

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

        Attachments

          Activity

            People

            Assignee:
            jplindst Jan Lindström
            Reporter:
            ErikCederstrand Erik Cederstrand
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: