| 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: | |
| 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: | Sergei Glukhov | CPU Architecture: | Any |
[30 Nov 2008 10:54]
Robin Johnson
[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]
Sergei 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.
