LP #1376934: Setting max_statement_time per query has no effect
Description
Environment
Smart Checklist
Activity
lpjirasync January 22, 2018 at 10:21 AM
**Comment from Launchpad by: Muhammad Irfan on: 03-10-2014 10:27:45
I am able to reproduce this problem. max_statement_time works as expected when set on session level or global level (in my.cnf) but fails to work correctly when set on query level with SET STATEMENT. I tested on on Percona Server 5.6.16/5.6.20
mysql [localhost] {msandbox} (world) > show global variables like '%version%';
------------------------------------------------------------------------------------------+
Variable_name | Value |
------------------------------------------------------------------------------------------+
innodb_version | 5.6.16-rel64.1 |
protocol_version | 10 |
slave_type_conversions |
|
version | 5.6.16-64.1-rel64.1-log |
version_comment | Percona Server with XtraDB (GPL), Release rel64.1, Revision 563 |
version_compile_machine | x86_64 |
version_compile_os | Linux |
------------------------------------------------------------------------------------------+
mysql [localhost] {msandbox} (world) > show global variables like '%version%';
---------------------------------------------------------------------------+
Variable_name | Value |
---------------------------------------------------------------------------+
innodb_version | 5.6.20-rel68.0 |
protocol_version | 10 |
slave_type_conversions |
|
version | 5.6.20-68.0 |
version_comment | Percona Server (GPL), Release 68.0, Revision 656 |
version_compile_machine | x86_64 |
version_compile_os | Linux |
---------------------------------------------------------------------------+
When set max_statement_time variable in my.cnf it works as expected as per below test results.
mysql [localhost] {msandbox} (world) > SELECT * FROM Country GROUP BY HeadOfState ORDER BY Continent;
ERROR 1882 (70101): Query execution was interrupted, max_statement_time exceeded
mysql [localhost] {msandbox} (world) > SELECT * FROM CountryLanguage GROUP BY IsOfficial ORDER BY Percentage DESC;
ERROR 1882 (70101): Query execution was interrupted, max_statement_time exceeded
Again, when one set on session level, it works correctly.
mysql [localhost] {msandbox} (world) > SET SESSION max_statement_time=1;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {msandbox} ((none)) > SHOW VARIABLES LIKE 'max_statement_time';
---------------------------+
Variable_name | Value |
---------------------------+
max_statement_time | 1 |
---------------------------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} ((none)) > SHOW GLOBAL VARIABLES LIKE 'max_statement_time';
---------------------------+
Variable_name | Value |
---------------------------+
max_statement_time | 0 |
---------------------------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (world) > SELECT * FROM Country WHERE Continent NOT IN ('Africa','Oceania') AND Population > 10000 AND Name<>'Pakistan' GROUP BY Capital ASC, GNP DESC ORDER BY Code2 ASC, SurfaceArea DESC;
ERROR 1882 (70101): Query execution was interrupted, max_statement_time exceeded
But it fails on query level when set via SET STATEMENT
mysql [localhost] {msandbox} ((none)) > SHOW VARIABLES LIKE 'max_statement_time';
---------------------------+
Variable_name | Value |
---------------------------+
max_statement_time | 0 |
---------------------------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} ((none)) > SHOW GLOBAL VARIABLES LIKE 'max_statement_time';
---------------------------+
Variable_name | Value |
---------------------------+
max_statement_time | 0 |
---------------------------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (world) > SET STATEMENT max_statement_time=1 FOR SELECT * FROM Country WHERE Continent NOT IN ('Africa','Oceania') AND Population > 10000 AND Name<>'Pakistan' GROUP BY Capital ASC, GNP DESC ORDER BY Code2 ASC, SurfaceArea DESC;
142 rows in set (0.6 sec)
Details
Assignee
UnassignedUnassignedReporter
lpjirasynclpjirasync(Deactivated)Priority
High
Details
Details
Assignee
Reporter
Priority
HighSmart Checklist
Open Smart Checklist
Smart Checklist
Open Smart Checklist
Smart Checklist
**Reported in Launchpad by David Turner last update 14-01-2015 08:28:19
Cut and paste from developer:
> Do we have a good way of reporting bugs to Percona? I think I found some misbehavior.
>
> SET STATEMENT max_statement_time=1000 FOR SELECT * FROM server_file_journal
> seems to ignore the max_statement_time. Setting a max_statement_time at the session level works fine.
>
> http://www.percona.com/blog/2013/11/07/using-per-query-variable-statements-in-percona-server/)