Bug #90351 GLOBAL STATUS variables drift after rollback
Submitted: 9 Apr 13:07 Modified: 24 Apr 9:28
Reporter: Iwo P Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S2 (Serious)
Version:5.7, 5.6, 5.7.21, 8.0.11 OS:Any
Assigned to: CPU Architecture:Any
Tags: Contribution

[9 Apr 13:07] Iwo P
Description:
Hi,

all global status variables are counters, values that only ever goes up, however, when executing SHOW GLOBAL STATUS it is possible to hit a data drift.

An example, for Handler_rollback:

The first execution of SGS:  16189
The second execution of SGS: 16194
The third execution of SGS:  16189

This is a significant issue because most of alerting/monitoring services assume that values are counters.

How to repeat:
1. Create a test table.

mysql> CREATE TABLE `test1` (id int, col1 text);

2. Fill it with a large amount of data

mysql> INSERT test1 SET id =1, col1=unix_timestamp();

$ for i in $(seq  1 22); do \
	mysql test -e "INSERT INTO test1 SELECT * FROM test.test1"; \
done;

3. Run delete queries in a loop:

while /bin/true; do
	mysql -BN -h 127.0.0.1 test -e "BEGIN; DELETE from test1 LIMIT 20000; ROLLBACK" 2>/dev/null
done;

4. Run kill query in a loop

while /bin/true; do
	mysql -BN -h 127.0.0.1 test -e "select CONCAT ( \"KILL QUERY \", ID, \";\" )  from information_schema.processlist where info like 'DELETE%';" 2>/dev/null |  mysql -BN -h 127.0.0.1 2>/dev/null
done;

5. Check GLOBAL STATUS for drift:

#!/bin/bash
prev=0
prevprev=0
declare -A globalstatus
while /bin/true; do
	while read h v; do
		if [ -z ${globalstatus["${h}_prev"]} ]; then
			globalstatus["${h}_prev"]=$v
			continue
		fi;

		if [ $v -lt ${globalstatus[${h}_prev]} ]; then
			echo "$v < ${globalstatus["${h}_prev"]} ($h)"
			echo ${globalstatus[${h}_prevprev]}
			echo ${globalstatus[${h}_prev]}
			echo $v
			#exit 0

		fi;

		globalstatus["${h}_prevprev"]=${globalstatus["${h}_prev"]}
		globalstatus["${h}_prev"]=$v
	done < <(mysql -BN -h 127.0.0.1 -e "SHOW GLOBAL STATUS LIKE 'Handler%';" 2>/dev/null)
done;

An example output:
1915 < 1916 (Handler_rollback)
1915
1916
1915

It is also possible to reproduce the issue _without_ killing a query (only rollback is needed), however, it happens very rarely. 

Suggested fix:
Values of SHOW GLOBAL STATUS variables should be counters.
[10 Apr 17:19] Umesh Shastry
Hello Iwo P,

Thank you for the report and test case.

Thanks,
Umes
[10 Apr 17:20] Umesh Shastry
test results

Attachment: 90351_5.7.21.results (application/octet-stream, text), 152.36 KiB.

[24 Apr 9:28] Iwo P
Just for the records, this issue is reproducible on 8.0.11.
[23 May 14:40] Zsolt Parragi
MTR test and bugfix for 5.6

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

Contribution: decreasing-counters-5.6.patch (text/x-patch), 3.31 KiB.

[23 May 14:41] Zsolt Parragi
MTR test and bugfix for 5.7

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

Contribution: decreasing-counters-5.7.patch (text/x-patch), 3.31 KiB.

[13 Jun 12:55] Laurynas Biveinis
Bug 90351 fix for 8.0.11 by Zsolt Parragi

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

Contribution: bug90351-8.0.11.patch (application/octet-stream, text), 11.68 KiB.

[14 Jun 4:59] Umesh Shastry
Thank you for the contributions!

Regards,
Umesh