Bug #41131 "Questions" fails to increment - ignores statements instead stored procs
Submitted: 30 Nov 2008 10:54 Modified: 28 Jan 2009 21:27
Reporter: Robin Johnson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Options Severity:S2 (Serious)
Version:5.0.72, 5.0, 6.0 bzr OS:Linux (Gentoo/2.6.28-rc2-00338-g65fc716)
Assigned to: Sergey Glukhov CPU Architecture:Any
Triage: Triaged: D5 (Feature request)

[30 Nov 2008 10:54] Robin Johnson
Description:
The "Questions" variable is not updated anymore in 5.0.72.

Actual result (5.0.72):
# for i in `seq 1 5`; do mysql -uroot -e 'show status like "%questions%"'   --skip-col --batch ; done ;
Questions	2
Questions	2
Questions	2
Questions	2
Questions	2

Expected result (5.0.70):
# for i in `seq 1 5`; do mysql -pseatbelt -uroot -e 'show status like "%questions%"'   --skip-col --batch ; done ;
Questions	32
Questions	35
Questions	38
Questions	41
Questions	44

How to repeat:
Install 5.0.72.

Configured with:
./configure --prefix=/usr --host=x86_64-pc-linux-gnu --mandir=/usr/share/man
--infodir=/usr/share/info --datadir=/usr/share --sysconfdir=/etc --localstatedir=/var/lib
--libexecdir=/usr/sbin --sysconfdir=/etc/mysql --localstatedir=/var/lib/mysql
--sharedstatedir=/usr/share/mysql --libdir=/usr/lib64/mysql
--includedir=/usr/include/mysql --with-low-memory --with-client-ldflags=-lstdc++
--enable-thread-safe-client --with-comment=Gentoo Linux mysql-5.0.72 --without-docs
--without-big-tables --enable-local-infile --with-extra-charsets=all
--with-mysqld-user=mysql --with-server --with-unix-socket-path=/var/run/mysqld/mysqld.sock
--without-libwrap --enable-shared --enable-static --without-debug --without-ndb-debug
--with-charset=utf8 --with-collation=utf8_general_ci --with-embedded-privilege-control
--with-embedded-server --with-bench --enable-assembler --with-extra-tools --with-innodb
--without-readline --with-openssl --without-berkeley-db --with-geometry --with-ndbcluster
--with-archive-storage-engine --with-csv-storage-engine --with-blackhole-storage-engine
--with-federated-storage-engine --build=x86_64-pc-linux-gnu CFLAGS="-march=nocona -O2
-pipe -g" CXXFLAGS="-march=nocona -O2 -pipe -g"

System details:

Portage 2.2_rc16 (!/etc/portage/profile, gcc-4.3.2, glibc-2.8_p20080602-r0,
2.6.28-rc2-00338-g65fc716 x86_64)
=================================================================
System uname:
Linux-2.6.28-rc2-00338-g65fc716-x86_64-Intel-R-_Core-TM-2_Quad_CPU_Q9550_@_2.83GHz-with-gl
ibc2.2.5

Suggested fix:
This might be related to bug 41066 that I opened.
[30 Nov 2008 18:16] Valeriy Kravchuk
Sorry, but in MySQL 5.0.x SHOW STATUS returns session status by default (see http://dev.mysql.com/doc/refman/5.0/en/show-status.html). So, "2" is correct value for your test. I doubt that you had any other value with 5.0.70.
[30 Nov 2008 23:03] Robin Johnson
Ok, the bug seems to be that 5.0.70 did not differentiate between GLOBAL/SESSION for that variable, while 5.0.72 does. Looks like the bug was actually in and earlier 5.0.70 not respecting the SESSION keyword, which is also implicit when not specified.

# cat >testcase.sql <<EOF
SHOW VARIABLES LIKE "version%";
SHOW STATUS LIKE 'questions';
SHOW GLOBAL STATUS LIKE 'questions';
SHOW SESSION STATUS LIKE 'questions';
EOF

(5.0.70):
# mysql -uroot <testcase.sql
Variable_name	Value
version	5.0.70-log
version_comment	Gentoo Linux mysql-5.0.70-r1
version_compile_machine	x86_64
version_compile_os	pc-linux-gnu
Variable_name	Value
Questions	414
Variable_name	Value
Questions	415
Variable_name	Value
Questions	416

(5.0.72)
#  mysql -uroot <testcase.sql
Variable_name	Value
version	5.0.72-log
version_comment	Gentoo Linux mysql-5.0.72
version_compile_machine	x86_64
version_compile_os	pc-linux-gnu
Variable_name	Value
Questions	3
Variable_name	Value
Questions	742
Variable_name	Value
Questions	5
[1 Dec 2008 0:31] Robin Johnson
Ok, here's a much more detailed testcase, that shows it really isn't incrementing like it did before, esp in the case of stored procedures.
Specifically, in 5.0.70 "questions" was incremented for each statement inside a stored procedure, while now in 5.0.72, it's only incremented for the calls by the clients, and NOT for the each statement inside a stored procedure, while the "com_*" status variables ARE still updated like that.

In terms of breaking things, that means you can't work out a percentage breakdown of your queries using the questions variable anymore, as you can't use the delta in "questions" to normalize "com_%"; This model is used by a lot of statistics processing code (incl mytop).

# cat >testcase3.sql <<EOF
use test
SHOW VARIABLES LIKE '%version%';
DROP PROCEDURE dorepeat;
delimiter //
CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
  SET @x = 0;
  REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END
//
delimiter ;
SHOW GLOBAL STATUS LIKE '%questions%';
SHOW GLOBAL STATUS LIKE '%com_set%';
CALL dorepeat(10000);
SHOW GLOBAL STATUS LIKE '%questions%';
SHOW GLOBAL STATUS LIKE '%com_set%';
EOF

5.0.70 output:
# mysql -uroot --batch <testcase3.sql
Variable_name	Value
protocol_version	10
version	5.0.70-log
version_comment	Gentoo Linux mysql-5.0.70-r1
version_compile_machine	x86_64
version_compile_os	pc-linux-gnu
Variable_name	Value
Questions	1265920
Variable_name	Value
Com_set_option	536184
Variable_name	Value
Questions	1285926
Variable_name	Value
Com_set_option	546186

# mysql -uroot --batch <testcase3.sql
Variable_name	Value
protocol_version	10
version	5.0.72-log
version_comment	Gentoo Linux mysql-5.0.72
version_compile_machine	x86_64
version_compile_os	pc-linux-gnu
Variable_name	Value
Questions	1188
Variable_name	Value
Com_set_option	560116
Variable_name	Value
Questions	1191
Variable_name	Value
Com_set_option	570118
[1 Dec 2008 10:20] Sveta Smirnova
Thank you for the feedback.

Verified as described.

6.0 behaves same as 5.0 and 5.1 has old behavior.
[11 Dec 2008 12:44] Sergey Glukhov
According to the manual Questions should contain the "number of statements that
clients have sent to the server"
(http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html);
Stored Routines are stored and executed on the server side.
So 5.1 tree behaviour is incorrect and 5.0 & 6.0 work in the proper way.
[11 Dec 2008 16:14] 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
[13 Dec 2008 8:45] Robin Johnson
Why do you consider it that 5.0 isn't broken?

Between 5.0.70 and 5.0.72 the behaviour of "Questions" changed, and I consider the new 5.0.72 behaviour to be a bug, because it breaks a LOT of statistics code.

It's important for statistics code that works out the percentage of queries.
%SELECT = delta(com_select)/delta(questions)
%INSERT = delta(com_insert)/delta(questions)
etc.

Please revert the Questions behaviour to increment for every statement like 5.0.70, and put the new behaviour in a new variable, because now there is no variable that contains the total number of statements executed by the server, including those inside stored procedures.
[19 Dec 2008 1:18] Omer Barnir
The change of behavior mentioned is a result of the fix to bug#24289 that corrects the behavior of this variable as documented. Extending the the server to include the requested behavior described in this bug, should be done in a way that allows both behaviors to co-exist.
[23 Dec 2008 12:46] 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/62250

2745 Sergey Glukhov	2008-12-23
      Bug#41131 "Questions" fails to increment - ignores statements instead stored procs(5.1 vers)
      Added global status variable 'Queries' which represents
      total amount of queries executed by server including
      statements executed by SPs.
      note: It's old behaviour of 'Questions' variable.
[23 Dec 2008 12:49] 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/62251

2719 Sergey Glukhov	2008-12-23
      Bug#41131 "Questions" fails to increment - ignores statements instead stored procs(5.0 ver)
      Added global status variable 'Queries' which represents
      total amount of queries executed by server including
      statements executed by SPs.
      note: It's old behaviour of 'Questions' variable.
[29 Dec 2008 12:10] 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/62405

2725 Sergey Glukhov	2008-12-29
      Bug#41131 "Questions" fails to increment - ignores statements instead stored procs(5.0 ver)
      Added global status variable 'Queries' which represents
      total amount of queries executed by server including
      statements executed by SPs.
      note: It's old behaviour of 'Questions' variable.
[6 Jan 2009 13:56] Bugs System
Pushed into 5.0.76 (revid:joro@sun.com-20090105160414-8q9j4bi1klkfwiup) (version source revid:azundris@mysql.com-20081230114734-nmsc37ak330zlygn) (merge vers: 5.0.76) (pib:6)
[12 Jan 2009 20:09] Paul Dubois
Noted in 5.0.76 changelog.

A new status variable, Queries, indicates the number of statements
executed by the server. This includes statements executed within
stored programs, unlike the Questions variable which includes only
statements sent to the server by clients.

Setting report to NDI pending push into 5.1.x/6.0.x.
[15 Jan 2009 6:33] 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:24] 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:02] 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 15:13] Jon Stephens
Changelog entry also tagged for 5.1.31.

Set status back to NDI pending merge to 6.0 tree.
[19 Jan 2009 16:08] 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:55] 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)
[28 Jan 2009 21:27] Paul Dubois
Noted in 6.0.10 changelog.