Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-7009

SET STATEMENT min_examined_row_limit has no effect

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Major
        • Resolution: Duplicate
        • Affects Version/s: N/A
        • Fix Version/s: 10.1.2
        • Component/s: Admin statements
        • Labels:
          None

          Description

          set global log_output = 'TABLE', global slow_query_log = 1;
set slow_query_log = 1;
set min_examined_row_limit = 0;
truncate table mysql.slow_log;
set long_query_time = 0.000001;
set statement min_examined_row_limit = 50 for select sleep(0.1) union select sleep(0.1);
select * from mysql.slow_log;
set min_examined_row_limit = 50;
select sleep(0.1) union select sleep(0.1);
select * from mysql.slow_log;

          MariaDB [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.27 sec)

MariaDB [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:48:49.526899 | root[root] @ localhost [::1] | 00:00:00.000089 | 00:00:00.000000 |         0 |             0 | test |              0 |         0 |         2 | set long_query_time = 0.000001                                                          |         5 |
| 2014-11-02 13:48:49.790968 | root[root] @ localhost [::1] | 00:00:00.201339 | 00:00:00.000000 |         1 |             1 | test |              0 |         0 |         2 | 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)

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

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

MariaDB [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:48:49.526899 | root[root] @ localhost [::1] | 00:00:00.000089 | 00:00:00.000000 |         0 |             0 | test |              0 |         0 |         2 | set long_query_time = 0.000001                                                          |         5 |
| 2014-11-02 13:48:49.790968 | root[root] @ localhost [::1] | 00:00:00.201339 | 00:00:00.000000 |         1 |             1 | test |              0 |         0 |         2 | set statement min_examined_row_limit = 50 for select sleep(0.1) union select sleep(0.1) |         5 |
| 2014-11-02 13:48:49.793186 | root[root] @ localhost [::1] | 00:00:00.000394 | 00:00:00.000112 |         2 |             2 | test |              0 |         0 |         2 | select * from mysql.slow_log                                                            |         5 |
+----------------------------+------------------------------+-----------------+-----------------+-----------+---------------+------+----------------+-----------+-----------+-----------------------------------------------------------------------------------------+-----------+
3 rows in set (0.00 sec)

          All the same in Percona server.

            Gliffy Diagrams

              Attachments

                Issue Links

                relates to

                Bug - A problem which impairs or prevents the functions of the product. MDEV-7006 SET STATEMENT does not work with slow log tuning (long_query_time, log_slow_filter, log_slow_verbosity, log_slow_rate_limit)

                • Major - Major loss of function.
                • Closed

                Task - A task that needs to be done. MDEV-5231 Per query variables from Percona Server [patch]

                • Critical - Crashes, loss of data, severe memory leak.
                • Closed

                Technical task - A technical task. MDEV-6923 Testing for SET STATEMENT .. FOR (MDEV-5231, per-query variables)

                • Major - Major loss of function.
                • Closed
                links to

                Web Link Bug#1388526 - SET STATEMENT min_examined_row_limit has no effect

                  Activity

                  Ascending order - Click to sort in descending order
                  Hide
                  Permalink
                  sanja Oleksandr Byelkin added a comment -

                  The variable prohibited as a workaround.

                  Show
                  sanja Oleksandr Byelkin added a comment - The variable prohibited as a workaround.
                  Hide
                  Permalink
                  sanja Oleksandr Byelkin added a comment -

                  Here (as well as all bugs connected to slow log) cause is that we write slow log after restoring variables values.

                  Show
                  sanja Oleksandr Byelkin added a comment - Here (as well as all bugs connected to slow log) cause is that we write slow log after restoring variables values.
                  Hide
                  Permalink
                  sanja Oleksandr Byelkin added a comment -

                  Duplicate of MDEV-7006

                  Show
                  sanja Oleksandr Byelkin added a comment - Duplicate of MDEV-7006

                    People

                    • Assignee:
                      sanja Oleksandr Byelkin
                      Reporter:
                      elenst Elena Stepanova
                    • Votes:
                      0 Vote for this issue
                      Watchers:
                      3 Start watching this issue

                      Dates

                      • Created:
                        Updated:
                        Resolved: