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
Triage: Triaged: D3 (Medium) / R3 (Medium)

[9 Feb 2009 15:42] Peter Laursen
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.
[9 Feb 2009 19:05] Peter Laursen
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?
[9 Feb 2009 19:37] Sveta Smirnova
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;
[21 Mar 2011 20:04] James Young
Geir, please assess for 5.6, possible back port to 5.5, earlier.
[25 May 2011 9:30] Peter Laursen
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
[25 May 2011 12:40] Axel Schwenke
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.
[25 May 2011 12:52] Peter Laursen
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.
[19 Aug 2011 15:02] Paul Dubois
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.
[13 Jun 2014 7:39] Shane Bester
Next:  http://bugs.mysql.com/bug.php?id=72981