Bug #24289 Status Variable "Questions" gets wrong values with Stored Routines
Submitted: 14 Nov 2006 8:32 Modified: 10 Nov 2008 17:56
Reporter: Daniel Jaenecke Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.0.30-BK, 5.0.x, 5.1.26 OS:Linux (Linux)
Assigned to: Kristofer Pettersson CPU Architecture:Any
Tags: status variables, stored routines
Triage: Triaged: D3 (Medium)

[14 Nov 2006 8:32] Daniel Jaenecke
Description:
When running Stored Routines the Status Variable "Questions" is wrongly incremented. According to the manual it should contain the "number of statements that clients have sent to the server" (http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html); and as far as I understand Stored Routines these are stored and executed on the server. But it seems that every line of code/statement executed within a Stored Routine increments the Variable by one.

How to repeat:
DELIMITER $$
CREATE FUNCTION testQuestion() RETURNS INTEGER 
BEGIN 
  DECLARE foo INTEGER; 
  DECLARE bar INTEGER; 
  SET foo=1;
  SET bar=2; 
  RETURN foo; 
END $$

SHOW STATUS LIKE 'Questions'; SELECT testQuestion(); SHOW STATUS LIKE 'Questions';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Questions     | 892783 |
+---------------+--------+
1 row in set (0.00 sec)

+----------------+
| testQuestion() |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)

+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Questions     | 892790 |
+---------------+--------+

SHOW STATUS LIKE 'Questions'; SELECT NOW(); SHOW STATUS LIKE 'Questions';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Questions     | 892791 |
+---------------+--------+
1 row in set (0.00 sec)

+---------------------+
| NOW()               |
+---------------------+
| 2006-11-14 09:28:14 |
+---------------------+
1 row in set (0.00 sec)

+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Questions     | 892793 |
+---------------+--------+

Suggested fix:
Calling a Stored Routine should increment the Question variable by 1, just like the manual implies it and as calling an inbuilt function like NOW() does.
[14 Nov 2006 9:11] Valeriy Kravchuk
Thank you for a problem report. Verified just as described with 5.0.30-BK. It is either a bug or, if intended behaviour, just a request for proper documenting Questions at http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html (as "number of statements executed by server").
[24 Jan 2007 15:38] Daniel Jaenecke
I was just interested if there will be any fixes (soon) regarding this bug? 
And I want to emphasize that this IMO should not be "fixed" by changing the documentation. If Stored Routines affect this value it becomes quite useless, since any change to a Routine would make previous data on e.g. average queries per seconds on a machine useless, thus making it very hard to detect increases in load.
[17 Jul 2007 20:30] Matthew Kent
Suprised not more people have noticed this or care.. still seeing this behaviour on 5.0.45 and its rendered my queries per second graphs rather useless after upgrading from 5.0.27. Queries per secs went from ~1000 to 32000+.

Had to revert back to 5.0.27 anyway after the server was crushed under cpu load with 5.0.45.. not sure if my issue is related to this counter sillyness but its probably not helping.

Though I'd imagine updating the Questions counter involves some kind of mutex, possibly being called 32000 times a second during the execution of a stored procedure. Just a guess but will try to replicate this behaviour and get a test case together.
[18 Jul 2007 21:23] Matthew Kent
Ignore my last reply sorry, my issues aren't linked to this one. I've documented them in Bug #29881
[15 Aug 2007 7:52] Konstantin Osipov
Please also keep in mind Bug#16422 when fixing this bug.
[2 Apr 2008 15:27] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44795

ChangeSet@1.2596, 2008-04-02 17:36:32+02:00, thek@adventure.(none) +5 -0
  Bug#24289 Status Variable "Questions" gets wrong values with Stored Routines
  
  When running Stored Routines the Status Variable "Questions" is wrongly incremented.
  According to the manual it should contain the "number of statements that clients have sent
  to the server"
  
  This patch introduces a new status variable which represents the number 
  of statements sent to the server by the client.
[3 Apr 2008 13:32] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44856

ChangeSet@1.2610, 2008-04-03 15:40:17+02:00, thek@adventure.(none) +3 -0
  Bug#24289 Status Variable "Questions" gets wrong values with Stored Routines
  
  When running Stored Routines the Status Variable "Questions" was wrongly incremented.
  According to the manual it should contain the "number of statements that clients have sent
  to the server"
  
  Introduced a new status variable 'questions' to replace the query_id
  variable which currently corresponds badly with the number of statements
  sent by the client.
  
  The new behavior is ment to be backward compatible with 4.0 and at the
  same time work with new features in a similar way.
[4 Apr 2008 12:34] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44917

ChangeSet@1.2610, 2008-04-04 14:42:41+02:00, thek@adventure.(none) +5 -0
  Bug#24289 Status Variable "Questions" gets wrong values with Stored Routines
  
  When running Stored Routines the Status Variable "Questions" was wrongly incremented.
  According to the manual it should contain the "number of statements that clients have sent
  to the server"
  
  Introduced a new status variable 'questions' to replace the query_id
  variable which currently corresponds badly with the number of statements
  sent by the client.
  
  The new behavior is ment to be backward compatible with 4.0 and at the
  same time work with new features in a similar way.
[9 Apr 2008 8:55] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/45112

ChangeSet@1.2610, 2008-04-09 11:04:34+02:00, thek@adventure.(none) +5 -0
  Bug#24289 Status Variable "Questions" gets wrong values with Stored Routines
  
  When running Stored Routines the Status Variable "Questions" was wrongly incremented.
  According to the manual it should contain the "number of statements that clients have sent
  to the server"
  
  Introduced a new status variable 'questions' to replace the query_id
  variable which currently corresponds badly with the number of statements
  sent by the client.
  
  The new behavior is ment to be backward compatible with 4.0 and at the
  same time work with new features in a similar way.
[20 Apr 2008 13:01] Bugs System
Pushed into 6.0.6-alpha
[28 Apr 2008 22:47] Paul Dubois
Noted in 6.0.6 changelog.

The Questions status variable is intended as a count of statements
sent by clients to the server, but was also counting statements
executed within stored routines.
[19 Aug 2008 15:17] Andrii Nikitin
verified in 5.1.26; please fix in 5.1 Cluster 6.2 also (requested by customer).
[3 Oct 2008 13:44] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/55259

2693 Kristofer Pettersson	2008-10-03
      Bug#24289 Status Variable "Questions" gets wrong values with Stored Routines
                 
      When running Stored Routines the Status Variable "Questions" was wrongly
      incremented. According to the manual it should contain the "number of
      statements that clients have sent to the server"
              
      Introduced a new status variable 'questions' to replace the query_id
      variable which currently corresponds badly with the number of statements
      sent by the client.
            
      The new behavior is ment to be backward compatible with 4.0 and at the
      same time work with new features in a similar way.
            
      This is a backport from 6.0
[10 Oct 2008 9:41] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/56002

2693 Kristofer Pettersson	2008-10-08
      Bug#24289 Status Variable "Questions" gets wrong values with Stored Routines
                       
      When running Stored Routines the Status Variable "Questions" was wrongly
      incremented. According to the manual it should contain the "number of
      statements that clients have sent to the server"
                   
      Introduced a new status variable 'questions' to replace the query_id
      variable which currently corresponds badly with the number of statements
      sent by the client.
                  
      The new behavior is ment to be backward compatible with 4.0 and at the
      same time work with new features in a similar way.
                
      This is a backport from 6.0
[10 Oct 2008 9:41] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/56003

2693 Kristofer Pettersson	2008-10-08
      Bug#24289 Status Variable "Questions" gets wrong values with Stored Routines
                       
      When running Stored Routines the Status Variable "Questions" was wrongly
      incremented. According to the manual it should contain the "number of
      statements that clients have sent to the server"
                    
      Introduced a new status variable 'questions' to replace the query_id
      variable which currently corresponds badly with the number of statements
      sent by the client.
                  
      The new behavior is ment to be backward compatible with 4.0 and at the
      same time work with new features in a similar way.
                  
      This is a backport from 6.0
[10 Oct 2008 10:39] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/56007

2693 Kristofer Pettersson	2008-10-08
      Bug#24289 Status Variable "Questions" gets wrong values with Stored Routines
                       
      When running Stored Routines the Status Variable "Questions" was wrongly
      incremented. According to the manual it should contain the "number of
      statements that clients have sent to the server"
                    
      Introduced a new status variable 'questions' to replace the query_id
      variable which currently corresponds badly with the number of statements
      sent by the client.
                  
      The new behavior is ment to be backward compatible with 4.0 and at the
      same time work with new features in a similar way.
                 
      This is a backport from 6.0
[10 Oct 2008 10:39] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/56008

2693 Kristofer Pettersson	2008-10-09
      Bug#24289 Status Variable "Questions" gets wrong values with Stored Routines
                       
      When running Stored Routines the Status Variable "Questions" was wrongly
      incremented. According to the manual it should contain the "number of
      statements that clients have sent to the server"
                    
      Introduced a new status variable 'questions' to replace the query_id
      variable which currently corresponds badly with the number of statements
      sent by the client.
                  
      The new behavior is ment to be backward compatible with 4.0 and at the
      same time work with new features in a similar way.
                  
      This is a backport from 6.0
[22 Oct 2008 10:51] Kristofer Pettersson
progress report: status2.test gives random results. Refactoring test.
[24 Oct 2008 8:42] Bugs System
Pushed into 5.0.72  (revid:kristofer.pettersson@sun.com-20081009072642-093kbl28wgu1kvr1) (version source revid:kgeorge@mysql.com-20081020132607-0xfdc16b9p1xrd83) (pib:5)
[27 Oct 2008 12:50] Joerg Bruehe
To clarify:
What got pushed into 5.0.72 is the changeset dated 2008-10-09 (commit 56008).
The "refactoring" mentioned on Oct 22 must be work in progress, it is not included in 5.0.72.

In 5.0.72, there are several failures of test "status2" whowing this symptom:

=====
status2                        [ fail ]

--- /PATH/mysql-test/r/status2.result
+++ /PATH/mysql-test/r/status2.reject
@@ -55,9 +55,9 @@
 Questions      16
 Global status updated; Assert diff == 5
 FLUSH STATUS;
-SELECT 5;
-5
-5
+SELECT 4;
+4
+4
 DROP TABLE t1,t2;
 DROP PROCEDURE p1;
 DROP FUNCTION f1;

mysqltest: Result content mismatch
=====
[7 Nov 2008 14:43] Kristofer Pettersson
Fix for status2 queued to 5.0-bugteam.
[10 Nov 2008 10:54] Bugs System
Pushed into 6.0.8-alpha  (revid:kristofer.pettersson@sun.com-20081009072642-093kbl28wgu1kvr1) (version source revid:kgeorge@mysql.com-20081021085131-2v86qbmg27inufx2) (pib:5)
[10 Nov 2008 11:37] Bugs System
Pushed into 5.1.30  (revid:kristofer.pettersson@sun.com-20081009072642-093kbl28wgu1kvr1) (version source revid:kristofer.pettersson@sun.com-20081020134518-hnks51sb3arlrtyp) (pib:5)
[10 Nov 2008 17:56] Paul Dubois
Noted in 5.1.31 changelog.
[11 Dec 2008 16:13] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/61384

2728 Sergey Glukhov	2008-12-11
      Bug#41131 "Questions" fails to increment - ignores statements instead stored procs
      bug#24289 fix backport from 6.0
[17 Dec 2008 13:24] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/61860

2739 Sergey Glukhov	2008-12-17
      Bug#24289 Status Variable "Questions" gets wrong values with Stored Routines(for 5.1)
[19 Dec 2008 1:14] Omer Barnir
Also see bug#41131
[12 Jan 2009 19:59] Paul Dubois
Noted in 5.0.72 changelog.
[15 Jan 2009 6:34] Bugs System
Pushed into 5.1.31 (revid:joro@sun.com-20090115053147-tx1oapthnzgvs1ro) (version source revid:azundris@mysql.com-20081230114838-cn52tu180wcrvh0h) (merge vers: 5.1.31) (pib:6)
[19 Jan 2009 11:26] Bugs System
Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090115073240-1wanl85vlvw2she1) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)
[19 Jan 2009 13:03] Bugs System
Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)
[19 Jan 2009 16:09] Bugs System
Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)
[20 Jan 2009 18:56] Bugs System
Pushed into 6.0.10-alpha (revid:joro@sun.com-20090119171328-2hemf2ndc1dxl0et) (version source revid:azundris@mysql.com-20081230114916-c290n83z25wkt6e4) (merge vers: 6.0.9-alpha) (pib:6)