MySQL Bugs: #90351: GLOBAL STATUS variables drift after rollback

Bug #90351	GLOBAL STATUS variables drift after rollback
Submitted:	9 Apr 2018 13:07	Modified:	17 Dec 2018 3:37
Reporter:	Iwo P	Email Updates:
Status:	Closed	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

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.

Hello Iwo P,

Thank you for the report and test case.

Thanks,
Umes

test results

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

Just for the records, this issue is reproducible on 8.0.11.

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.

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.

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.

Thank you for the contributions!

Regards,
Umesh

Posted by developer:
 
Fixed in 5.6.44, 5.7.26, 8.0.15.

Some status variable values could temporarily increase before
returning to their original value.

After I was able to reproduce it on 8.0.15 we figured out this was not fixed in 8.0.15 only in 8.0.16.

More details: https://github.com/mysql/mysql-server/commit/acdf2823d97787e4ccbfa9cbbfb72c4d65ab24fe

It is still possible to reproduce the issue on 5.7 and 8.0. 5.6 is not affected.

5.7 patch

Attachment: 5.7.patch (text/x-patch), 519 bytes.

8.0 patch

Attachment: 8.0.patch (text/x-patch), 632 bytes.