MySQL Bugs: #42698: overflow in status variable

Bug #42698	overflow in status variable
Submitted:	9 Feb 2009 15:42	Modified:	19 Aug 2011 15:02
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.45, 5.0, 5.1, 6.0 bzr	OS:	Any
Assigned to:	Geir Høydalsvik	CPU Architecture:	Any
Tags:	qc

Description:
An old report existis in mailing lists:
http://www.mail-archive.com/mysql@lists.mysql.com/msg88663.html

quote: "All the status variables seem to start over
after hitting 4,294,967,295. I don't get why they're only using 32
bit integers for the variables that they know can go WAY over that
amount."

(but I do not find a report in this bugs system)

We have been hit by this now too:
http://www.webyog.com/forums//index.php?showtopic=4492

How to repeat:
see above!

Suggested fix:
Use 64 bit integers for (all or specific) status variables.

If you look at the last link I provided, you will now see our user reproting that this happened after 3 days of server uptime!

Now it is not fully documented that integer overflow is the issue here.  But if not, then it is a even more serious bug! And what else can it be?

Thank you for the report.

Verified as described reading source code (sql/sql_class.h).

Really there is similar bug #28149, but that bug was about bytes_received and bytes_sent;

Geir, please assess for 5.6, possible back port to 5.5, earlier.

One more complaint from a user/customer of ours:
http://www.webyog.com/forums/index.php?showtopic=6129

I did not verify but user claims that also "Handler_read_rnd_next" status variable oveflows when reaching 2^32

IMHO this is a feature request. Any fixed size counter will overflow, if the server only runs long enough. And all statistics tools I have come across yet, can cope with counter overflows.

The only hint that a counter is too short, would be that more than count_max events can happen in a sensible lookup interval.

For performance related counters I would guess a sensible lookup interval about 1 minute. But lets just say 1 hour. Then a 32-bit counter would be appropriate for any event with a frequency of at most 4G/hour. Network- and storage counters could reach that, when counting bytes. Handler_* variables certainly not.

I do not understand the last comment. A 'lookup' (SHOW GLOBAL STATUS) does not change any status variable.  But maybe I just did not understand the point?

Any fixed size will overflow - yes - but the difference between 2^32 and 2^64 is astronomical.  Nobody foresees centuries of uptime for a database server. But months is not unrealistic (and there are examples of overflows in days only). As hardware systems evolve servers can handle more requests, the status varirables will increase faster and I simply do not see why a 64 bit integer could not be used consequently for all status variables that may in principle increase forever. Isn't it simply a matter of changing the datatype in the variable declaration - and that's all? The few KB's additional memory consumption that may result from this is not an issue.

Noted in 5.6.3 changelog.

Some status variables rolled over to zero after reaching the
maximum 32-bit value. They have been changed to 64-bit values.

Next:  http://bugs.mysql.com/bug.php?id=72981