Bug #39775 DBUG problems in 6.0
Submitted: 1 Oct 2008 14:39 Modified: 2 Oct 2008 18:43
Reporter: Ingo Strüwing Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Logging Severity:S3 (Non-critical)
Version:6.0.8,6.0 bzr,5.4 OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D3 (Medium) / R2 (Low) / E3 (Medium)

[1 Oct 2008 14:39] Ingo Strüwing
Description:
1. The semantics of the 'DEBUG' system variable is not well documented.
In 5.1 it behaves like so: To set a trace file, use SET GLOBAL, e.g. SET
GLOBAL debug='+O,../log/dbug.trace'. Per-session trace files are also
possible. To change other DBUG options, use SET SESSION, e.g. SET
debug='+d,query'. The latter does influence the current session only. In
contrast to other GLOBAL/SESSION behavior, SET GLOBAL DEBUG influences
all sessions, not just the current and all later established (new)
sessions.

2. In 6.0, global setting of a trace file works. But when a test is
started _without_ --debug, no other setting seems to take effect. The
race file is created, but stays empty, whatever one tries with setting
of the system variable.

3. In 6.0, when a test is started _with_ --debug, session settings take
global effect.

How to repeat:
Run the below test case in 5.1 and 6.0 with ./mtr and ./mtr --debug. In
all cases do:

egrep 'query:|close_cached_tables' mysql-test/var/log/dbug.trace
egrep 'query:|close_cached_tables' mysql-test/var/log/dbug1.trace
egrep 'query:|close_cached_tables' mysql-test/var/log/dbug2.trace

In 5.1 you will see the same output, regardsless if --debug is used.
Note that the first FLUSH TABLES proves that SET GLOBAL DEBUG *after*
connection (con1...) activates tracing in con1 too:

egrep 'query:|close_cached_tables' mysql-test/var/log/dbug.trace
T@3    : | | | | >close_cached_tables
T@3    : | | | | <close_cached_tables
T@4    : | | | | >close_cached_tables
T@4    : | | | | <close_cached_tables
T@4    : dispatch_command: query: SELECT 'con1 - now should be in dbug.trace'
T@4    : dispatch_command: query: SET SESSION debug='+O,../log/dbug1.trace'
T@5    : | | | | >close_cached_tables
T@5    : | | | | <close_cached_tables
T@5    : dispatch_command: query: SELECT 'con2 - now should be in dbug.trace'
T@5    : dispatch_command: query: SET SESSION debug='+O,../log/dbug2.trace'
T@7    : | | >close_cached_tables
T@7    : | | <close_cached_tables
egrep 'query:|close_cached_tables' mysql-test/var/log/dbug1.trace
T@4    : dispatch_command: query: SELECT 'con1 - now should be in dbug1.trace'
T@4    : dispatch_command: query: SELECT 'con1 - still should be in dbug1.trace'
T@4    : dispatch_command: query: SET SESSION debug='-d:-t:-i'
egrep 'query:|close_cached_tables' mysql-test/var/log/dbug2.trace
T@5    : dispatch_command: query: SELECT 'con2 - now should be in dbug2.trace'
T@5    : dispatch_command: query: SELECT 'con2 - still should be in dbug2.trace'
T@5    : dispatch_command: query: SET SESSION debug='-d:-t:-i'

In 6.0, _without_ --debug, you will see that dbug1.trace and dbug2.trace
don't exist. dbug.trace is empty.

In 6.0, _with_ --debug, you will see that all settings influence all
sessions.

egrep 'query:|close_cached_tables' mysql-test/var/log/dbug.trace
T@4    : | | | | >close_cached_tables
T@4    : | | | | <close_cached_tables
T@5    : | | | | >close_cached_tables
T@5    : | | | | <close_cached_tables
T@5    : dispatch_command: query: SELECT 'con1 - now should be in dbug.trace'
T@5    : dispatch_command: query: SET SESSION debug='+O,../log/dbug1.trace'
egrep 'query:|close_cached_tables' mysql-test/var/log/dbug1.trace
T@5    : dispatch_command: query: SELECT 'con1 - now should be in dbug1.trace'
T@6    : dispatch_command: query: FLUSH TABLES
T@6    : dispatch_command: query: SELECT 'con2 - should not be in dbug.trace'
T@6    : dispatch_command: query: SET SESSION debug='+d,query:-t'
T@6    : dispatch_command: query: SELECT 'con2 - now should be in dbug.trace'
T@6    : dispatch_command: query: SET SESSION debug='+O,../log/dbug2.trace'
egrep 'query:|close_cached_tables' mysql-test/var/log/dbug2.trace
T@6    : dispatch_command: query: SELECT 'con2 - now should be in dbug2.trace'
T@5    : dispatch_command: query: SELECT 'con1 - still should be in dbug1.trace'
T@5    : dispatch_command: query: SET SESSION debug='-d:-t:-i'

========================
Here is the test case:
========================

#
# Bug# - DBUG problems in 6.0
#
# Set a trace file and deactivate debugging and tracing, if activated.
#
SET GLOBAL debug='-d:-t:-i:+O,../log/dbug.trace';
SELECT 'default - should not be in dbug.trace';
#
# Create a session that should inherit the above setting, but not the below.
#
connect (con1,localhost,root,,);
--connection default
#
# Activate tracing for all sessions, except con1.
#
SET GLOBAL debug='+t:+i';
#
# Let an uncommon function appear in the trace.
#
FLUSH TABLES;
#
# Create sessions that should inherit the above settings.
#
connect (con2,localhost,root,,);
--connection default
#
    --connection con1
    # Let an uncommon function appear in the trace.
    FLUSH TABLES;
    SELECT 'con1 - should not be in dbug.trace';
    SET SESSION debug='+d,query:-t';
    SELECT 'con1 - now should be in dbug.trace';
    # Changing the trace file cannot be done per session
    SET SESSION debug='+O,../log/dbug1.trace';
    SELECT 'con1 - now should be in dbug1.trace';
#
        --connection con2
        # Let an uncommon function appear in the trace.
        FLUSH TABLES;
        SELECT 'con2 - should not be in dbug.trace';
        SET SESSION debug='+d,query:-t';
        SELECT 'con2 - now should be in dbug.trace';
        # Changing the trace file cannot be done per session
        SET SESSION debug='+O,../log/dbug2.trace';
        SELECT 'con2 - now should be in dbug2.trace';
#
    --connection con1
    SELECT 'con1 - still should be in dbug1.trace';
    SET SESSION debug='-d:-t:-i';
    SELECT 'con1 - should not be in a trace';
#
        --connection con2
        SELECT 'con2 - still should be in dbug2.trace';
        SET SESSION debug='-d:-t:-i';
        SELECT 'con2 - should not be in a trace';
#
# Cleanup
#
--connection default
disconnect con1;
disconnect con2;

========================
Here is the result file:
========================

SET GLOBAL debug='-d:-t:-i:+O,../log/dbug.trace';
SELECT 'default - should not be in dbug.trace';
default - should not be in dbug.trace
default - should not be in dbug.trace
SET GLOBAL debug='+t:+i';
FLUSH TABLES;
FLUSH TABLES;
SELECT 'con1 - should not be in dbug.trace';
con1 - should not be in dbug.trace
con1 - should not be in dbug.trace
SET SESSION debug='+d,query:-t';
SELECT 'con1 - now should be in dbug.trace';
con1 - now should be in dbug.trace
con1 - now should be in dbug.trace
SET SESSION debug='+O,../log/dbug1.trace';
SELECT 'con1 - now should be in dbug1.trace';
con1 - now should be in dbug1.trace
con1 - now should be in dbug1.trace
FLUSH TABLES;
SELECT 'con2 - should not be in dbug.trace';
con2 - should not be in dbug.trace
con2 - should not be in dbug.trace
SET SESSION debug='+d,query:-t';
SELECT 'con2 - now should be in dbug.trace';
con2 - now should be in dbug.trace
con2 - now should be in dbug.trace
SET SESSION debug='+O,../log/dbug2.trace';
SELECT 'con2 - now should be in dbug2.trace';
con2 - now should be in dbug2.trace
con2 - now should be in dbug2.trace
SELECT 'con1 - still should be in dbug1.trace';
con1 - still should be in dbug1.trace
con1 - still should be in dbug1.trace
SET SESSION debug='-d:-t:-i';
SELECT 'con1 - should not be in a trace';
con1 - should not be in a trace
con1 - should not be in a trace
SELECT 'con2 - still should be in dbug2.trace';
con2 - still should be in dbug2.trace
con2 - still should be in dbug2.trace
SET SESSION debug='-d:-t:-i';
SELECT 'con2 - should not be in a trace';
con2 - should not be in a trace
con2 - should not be in a trace
[2 Oct 2008 18:43] Sveta Smirnova
Thank you for the report.

Verified as described.