Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-1189

LP #719368: log_slow_sp_statements interferes with log_slow_admin_statements

Details

    • Bug
    • Status: Done
    • High
    • Resolution: Fixed
    • None
    • None
    • None

    Description

      **Reported in Launchpad by Alexey Kopytov last update 18-10-2016 16:11:30

      When a stored routine calls an "administrative" command such as OPTIMIZE TABLE, ANALYZE TABLE, ALTER TABLE, CREATE/DROP INDEX, etc. the effective value of log_slow_sp_statements is overwritten by the value of log_slow_admin_statements.

      More specifically, mysql_execute_command() assigns the value of opt_log_slow_admin_statements to thd->enable_slow_log before execution of an administrative command. The original value of thd->enable_slow_log is not restored back until the next statement execution. This becomes a problem for "substatements" of a stored routine, since the code relies on thd->enable_slow_log to decide if log_slow_statement() should be called for individual statements in a stored routine.

      As a result, with log_slow_sp_statements ON and log_slow_admin_statements OFF, the first administrative command in a stored routine will disable slow logging for all subsequent statements in that routine.

      Conversely, with log_slow_sp_statements OFF and log_slow_admin_statements ON, the first administrative statement in a stored routine will enable slow logging for all subsequent statements in that routine.

      Steps to reproduce:

      1. ./mysqld --log-slow-admin-statements=0
      2. create the following table and SP:

      CREATE TABLE t1(a int);
      delimiter //
      CREATE PROCEDURE test() BEGIN INSERT INTO t1 VALUES(0); CREATE INDEX i ON t1(a); INSERT INTO t1 VALUES(1); END//
      delimiter ;

      3. in a debugger, set a breakpoint on log_slow_statement()

      4. execute "call test();"

      5. log_slow_statement() will only be called for INSERT before CREATE INDEX, but not for the one after it.

      Attachments

        Activity

          People

            Unassigned Unassigned
            lpjirasync lpjirasync (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Smart Checklist