Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0(EOL), 10.1(EOL)
-
10.0.14-MariaDB installed via homebrew on OS X 10.10.3
-
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
Issue Links
Activity
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.
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?
Assigning to jplindst to decide whether we want to do anything about it on MariaDB side.