|
Issue MDEV-8297 was closed, but there are still unexpected (for me, at least) things going on with MODIFIED_COUNTER, 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?
|