Bug #114180 innodb_activity_count counter in innodb_metrics goes negative

Submitted: 1 Mar 2024 9:21 Modified: 1 Mar 2024 12:16
Reporter: Ke Yu (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:8.0.36 OS:Any
Assigned to: CPU Architecture:Any

[1 Mar 2024 9:21] Ke Yu
Description:
The follow sql return a negative value.
mysql> SELECT NAME, COUNT FROM information_schema.INNODB_METRICS WHERE name='innodb_activity_count';
+-----------------------+------------+
| NAME                  | COUNT      |
+-----------------------+------------+
| innodb_activity_count | -429496559 |
+-----------------------+------------+

How to repeat:
From the 8.0.36 code, we can found that innodb_activity_count is obtained in srv0mon.cc:1940:

value = srv_get_activity_count();

The return type of srv_get_activity_count() is ulint, but the type of value is int64_t. 
innodb_activity_count will only increase, not decrease. When the innodb_activity_count
is more than 2147483648, it will be interpreted as a signed number(int64_t) in line srv0mon.cc:1940
of the code.

In a normal scenario, it takes a long time to increase innodb_activity_count above 2147483648,
resulting in a negative query result.
So I constructed a test case to repeat the problem, and you need to modify the code in debug mode to quickly
increase the innodb_activity_count value to repeat this problem.

Here is the code diff based on 8.0.36. You can import my diff file into 8.0.36, compile it into debug mode:

diff --git a/storage/innobase/srv/srv0srv.cc b/storage/innobase/srv/srv0srv.cc
index 3184176f7ae..44fd8dfe8ad 100644
--- a/storage/innobase/srv/srv0srv.cc
+++ b/storage/innobase/srv/srv0srv.cc
@@ -1975,6 +1975,10 @@ void srv_wake_master_thread(void) {
  reading this value as it is only used in heuristics.
  @return activity count. */
 ulint srv_get_activity_count(void) {
+  DBUG_EXECUTE_IF(
+          "srv_increase_activity_count",
+          srv_sys->activity_count.add(644245092););
+
   return (srv_sys == nullptr ? 0 : srv_sys->activity_count);
 }

Then run the follow test case:

DELIMITER |;

CREATE PROCEDURE select_innodb_activity_count()
BEGIN
    DECLARE i int DEFAULT 1;
    WHILE (i <= 6) DO
        SELECT NAME, COUNT FROM information_schema.INNODB_METRICS WHERE name='innodb_activity_count';
        SET i = i + 1;
    END WHILE;
END |

DELIMITER ;|

SET GLOBAL DEBUG= '+d, srv_increase_activity_count';
call select_innodb_activity_count();
SET GLOBAL DEBUG= '-d, srv_increase_activity_count';

DROP  PROCEDURE select_innodb_activity_count;

The result is:

call select_innodb_activity_count();
NAME	COUNT
innodb_activity_count	644245277
NAME	COUNT
innodb_activity_count	1288490369
NAME	COUNT
innodb_activity_count	1932735461
NAME	COUNT
innodb_activity_count	-1717986743
NAME	COUNT
innodb_activity_count	-1073741651
NAME	COUNT
innodb_activity_count	-429496559

Suggested fix:
Avoid the problem of innodb_activity_count being negative when unsigned numbers are converted to signed numbers.
[1 Mar 2024 12:16] MySQL Verification Team
Hello Ke Yu,

Thank you for the report and feedback.

regards,
Umesh
[2 Apr 2024 3:20] Ke Yu
This is my fix code, based on 8.0.36.

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug114180.diff (application/octet-stream, text), 4.26 KiB.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.050 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.