MySQL Bugs: #106012: Table stats in P_S not updated on rows updated via FK const...

Table stats in P_S not updated on rows updated via FK constraint

Bug #106012 Table stats in P_S not updated on rows updated via FK constraint Submitted: 30 Dec 2021 18:26 Modified: 5 Apr 19:26 Reporter: Przemyslaw Malkowski Email Updates: Status: Verified Impact on me: None  Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical) Version:8.0, 5.7 OS:Any Assigned to: CPU Architecture:Any

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.