log_slow_sp_statements interferes with log_slow_admin_statements

Bug #719368 reported by Alexey Kopytov on 2011-02-15
12
This bug affects 2 people
Affects Status Importance Assigned to Milestone
Percona Server moved to https://jira.percona.com/projects/PS
Status tracked in 5.7
5.5
Fix Released
Medium
Nickolay Ihalainen
5.6
Fix Released
Medium
Nickolay Ihalainen
5.7
Fix Released
Medium
Nickolay Ihalainen

Bug Description

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.

description: updated
Stewart Smith (stewart) on 2011-05-19
Changed in percona-server:
status: New → Confirmed
Stewart Smith (stewart) on 2011-06-07
Changed in percona-server:
importance: Undecided → Medium
status: Confirmed → Triaged
Oleg Tsarev (tsarev) on 2011-06-28
Changed in percona-server:
assignee: nobody → Oleg Tsarev (tsarev)
status: Triaged → In Progress
Oleg Tsarev (tsarev) on 2011-08-17
Changed in percona-server:
status: In Progress → Triaged
Oleg Tsarev (tsarev) on 2011-08-23
tags: added: slow-extended
Stewart Smith (stewart) on 2012-03-27
Changed in percona-server:
assignee: Oleg Tsarev (tsarev) → nobody

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

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

Other bug subscribers