SET STATEMENT min_examined_row_limit has no effect

Bug #1388526 reported by Elena Stepanova
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Server moved to https://jira.percona.com/projects/PS
Status tracked in 5.7
5.6
Triaged
Medium
Unassigned
5.7
Triaged
Medium
Unassigned

Bug Description

MySQL [test]> set global log_output = 'TABLE', global slow_query_log = 1;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> set min_examined_row_limit = 0;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> truncate table mysql.slow_log;
Query OK, 0 rows affected (0.17 sec)

MySQL [test]> set long_query_time = 0.000001;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> set statement min_examined_row_limit = 50 for select sleep(0.1) union select sleep(0.1);
+------------+
| sleep(0.1) |
+------------+
| 0 |
+------------+
1 row in set (0.37 sec)

MySQL [test]> select * from mysql.slow_log;
+---------------------+------------------------------+------------+-----------+-----------+---------------+------+----------------+-----------+-----------+-----------------------------------------------------------------------------------------+-----------+
| start_time | user_host | query_time | lock_time | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text | thread_id |
+---------------------+------------------------------+------------+-----------+-----------+---------------+------+----------------+-----------+-----------+-----------------------------------------------------------------------------------------+-----------+
| 2014-11-02 13:45:33 | root[root] @ localhost [::1] | 00:00:00 | 00:00:00 | 0 | 0 | test | 0 | 0 | 20 | set long_query_time = 0.000001 | 5 |
| 2014-11-02 13:45:33 | root[root] @ localhost [::1] | 00:00:00 | 00:00:00 | 1 | 1 | test | 0 | 0 | 20 | set statement min_examined_row_limit = 50 for select sleep(0.1) union select sleep(0.1) | 5 |
+---------------------+------------------------------+------------+-----------+-----------+---------------+------+----------------+-----------+-----------+-----------------------------------------------------------------------------------------+-----------+
2 rows in set (0.00 sec)

MySQL [test]> set min_examined_row_limit = 50;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> select sleep(0.1) union select sleep(0.1);
+------------+
| sleep(0.1) |
+------------+
| 0 |
+------------+
1 row in set (0.20 sec)

MySQL [test]> select * from mysql.slow_log;
+---------------------+------------------------------+------------+-----------+-----------+---------------+------+----------------+-----------+-----------+-----------------------------------------------------------------------------------------+-----------+
| start_time | user_host | query_time | lock_time | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text | thread_id |
+---------------------+------------------------------+------------+-----------+-----------+---------------+------+----------------+-----------+-----------+-----------------------------------------------------------------------------------------+-----------+
| 2014-11-02 13:45:33 | root[root] @ localhost [::1] | 00:00:00 | 00:00:00 | 0 | 0 | test | 0 | 0 | 20 | set long_query_time = 0.000001 | 5 |
| 2014-11-02 13:45:33 | root[root] @ localhost [::1] | 00:00:00 | 00:00:00 | 1 | 1 | test | 0 | 0 | 20 | set statement min_examined_row_limit = 50 for select sleep(0.1) union select sleep(0.1) | 5 |
| 2014-11-02 13:45:33 | root[root] @ localhost [::1] | 00:00:00 | 00:00:00 | 2 | 2 | test | 0 | 0 | 20 | select * from mysql.slow_log | 5 |
+---------------------+------------------------------+------------+-----------+-----------+---------------+------+----------------+-----------+-----------+-----------------------------------------------------------------------------------------+-----------+
3 rows in set (0.00 sec)

tags: added: set-statement
Revision history for this message
Valerii Kravchuk (valerii-kravchuk) wrote :
Download full text (4.8 KiB)

Reproduced on 5.6.21-70:

[openxs@centos ~]$ mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.21-70.0 Percona Server (GPL), Release 70.0, Revision 688

Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set global log_output = 'TABLE', global slow_query_log = 1;
Query OK, 0 rows affected (0.08 sec)

mysql> set min_examined_row_limit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> truncate table mysql.slow_log;
Query OK, 0 rows affected (0.04 sec)

mysql> set long_query_time = 0.000001;
Query OK, 0 rows affected (0.01 sec)

mysql> set statement min_examined_row_limit = 50 for select sleep(0.1) union select sleep(0.1);
+------------+
| sleep(0.1) |
+------------+
| 0 |
+------------+
1 row in set (0.23 sec)

mysql> select * from mysql.slow_log;
+---------------------+---------------------------+------------+-----------+-----------+---------------+------+----------------+-----------+-----------+-----------------------------------------------------------------------------------------+-----------+
| start_time | user_host | query_time | lock_time | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text | thread_id |
+---------------------+---------------------------+------------+-----------+-----------+---------------+------+----------------+-----------+-----------+-----------------------------------------------------------------------------------------+-----------+
| 2014-11-02 15:30:22 | root[root] @ localhost [] | 00:00:00 | 00:00:00 | 0 | 0 | test | 0 | 0 | 0 | set long_query_time = 0.000001 | 2 |
| 2014-11-02 15:30:34 | root[root] @ localhost [] | 00:00:00 | 00:00:00 | 1 | 1 | test | 0 | 0 | 0 | set statement min_examined_row_limit = 50 for select sleep(0.1) union select sleep(0.1) | 2 |
+---------------------+---------------------------+------------+-----------+-----------+---------------+------+----------------+-----------+-----------+-----------------------------------------------------------------------------------------+-----------+
2 rows in set (0.00 sec)

mysql> set min_examined_row_limit = 50;
Query OK, 0 rows affected (0.00 sec)

mysql> select sleep(0.1) union select sleep(0.1);
+------------+
| sleep(0.1) |
+------------+
| 0 |
+------------+
1 row in set (0.20 sec)

mysql> select * from mysql.slow_log;
+---------------------+---------------------------+------------+-----------+-----------+---------...

Read more...

Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1576

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.