Description:
An UPDATE query that affects table related via foreign constraint, does not update the performance_schema.table_io_waits_summary_by_table (or sys.schema_table_statistics) view for the referenced table.
How to repeat:
Let's take two example tables with FK relationship as in https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html
mysql > SELECT * from product where category=487428737;
+-----------+-----------+-------+
| category | id | price |
+-----------+-----------+-------+
| 487428737 | 313912514 | 1 |
+-----------+-----------+-------+
1 row in set (0.00 sec)
mysql > select product_category,product_id from product_order where product_category=487428737;
+------------------+------------+
| product_category | product_id |
+------------------+------------+
| 487428737 | 313912514 |
| 487428737 | 313912514 |
| 487428737 | 313912514 |
| 487428737 | 313912514 |
| 487428737 | 313912514 |
| 487428737 | 313912514 |
| 487428737 | 313912514 |
| 487428737 | 313912514 |
| 487428737 | 313912514 |
| 487428737 | 313912514 |
+------------------+------------+
10 rows in set (0.00 sec)
When I update the product, the product_order will be updated via ON UPDATE CASCADE constraint:
mysql > update product set id=id+1 where category=487428737;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql > select product_category,product_id from product_order where product_category=487428737;
+------------------+------------+
| product_category | product_id |
+------------------+------------+
| 487428737 | 313912515 |
| 487428737 | 313912515 |
| 487428737 | 313912515 |
| 487428737 | 313912515 |
| 487428737 | 313912515 |
| 487428737 | 313912515 |
| 487428737 | 313912515 |
| 487428737 | 313912515 |
| 487428737 | 313912515 |
| 487428737 | 313912515 |
+------------------+------------+
10 rows in set (0.00 sec)
As a result, not 1 row, but 11 rows were updated in total. This is seen from:
mysql > show global status like 'Innodb_rows_updated';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| Innodb_rows_updated | 173 |
+---------------------+-------+
1 row in set (0.00 sec)
vs after the update (no other activity on the server):
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| Innodb_rows_updated | 184 |
+---------------------+-------+
However, the sys.schema_table_statistics show only one row update:
mysql > select table_name,rows_updated from sys.schema_table_statistics where table_schema="test" and table_name like 'product%';
+---------------+--------------+
| table_name | rows_updated |
+---------------+--------------+
| product_order | 1 |
| product | 15 |
| product1 | 8 |
+---------------+--------------+
3 rows in set (0.01 sec)
vs after update:
mysql > select table_name,rows_updated from sys.schema_table_statistics where table_schema="test" and table_name like 'product%';
+---------------+--------------+
| table_name | rows_updated |
+---------------+--------------+
| product_order | 1 |
| product | 16 |
| product1 | 8 |
+---------------+--------------+
3 rows in set (0.01 sec)
The same in original P_S view:
mysql > select OBJECT_NAME,COUNT_UPDATE from `performance_schema`.`table_io_waits_summary_by_table` where OBJECT_NAME like "product%";
+---------------+--------------+
| OBJECT_NAME | COUNT_UPDATE |
+---------------+--------------+
| product | 16 |
| product_order | 1 |
| product1 | 8 |
+---------------+--------------+
3 rows in set (0.00 sec)
Updates to the child tables are completely ignored in this P_S instrumentation.
Suggested fix:
Make the updated/deleted rows statistics accurate regardless of whether done directly or via FK constraints.
