Bug #19764 SHOW commands end up in the slow log as table scans
Submitted: 12 May 2006 10:28 Modified: 9 Nov 2006 14:55
Reporter: Tobias Asplund Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Logging Severity:S3 (Non-critical)
Version:5.0.15 OS:Any (*)
Assigned to: Timothy Smith CPU Architecture:Any

[12 May 2006 10:28] Tobias Asplund
Description:
The SHOW commands will end up in the slow log if the --log-queries-not-using-indexes flag is used.

My guess is that it puts the entries in a tmp_table in memory and scans it.

How to repeat:
start server with flag above.
SHOW TABLES;
[12 May 2006 14:43] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6301
[7 Jun 2006 11:05] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7347
[19 Jun 2006 9:08] Michael Widenius
I don't like this patch as it's adds more complexity to often executed code
(Testing of if query should be in slow query log)
Instead of doing it this way, lets instead try to fix this by resetting "thd->server_status" at the same time we restore the global session variables (part of bug fix #10210).

In other words, lets change bug fix for #10210 so that this bug can be closed at the same time as #10210
[20 Jun 2006 10:19] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7910
[20 Jun 2006 10:27] Michael Widenius
Same bug fix as for Bug#10210.

Becasue of the extensive cleanups the patch required, this can not be pushed
into 5.0, so it will be pushed into the 5.1 tree instead.
[26 Jun 2006 13:12] Michael Widenius
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://www.mysql.com/doc/en/Installing_source_tree.html
[26 Jun 2006 13:14] Michael Widenius
Fix will be available in 5.1.12
[23 Jul 2006 3:46] Paul DuBois
Noted in 5.1.12 changelog.
[3 Oct 2006 0:51] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/12977

ChangeSet@1.2302, 2006-10-02 18:51:12-06:00, tsmith@siva.hindu.god +3 -0
  Bug #19764: SHOW commands end up in the slow log as table scans
  
  Set a flag when a SHOW command is parsed, and check it in MYSQL_LOG::write().  Do not write to the slow queries log file if it is a SHOW command.
[4 Oct 2006 3:27] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/13053

ChangeSet@1.2302, 2006-10-03 21:26:55-06:00, tsmith@siva.hindu.god +7 -0
  Bug #19764: SHOW commands end up in the slow log as table scans
  
  Set a flag when a SHOW command is parsed, and check it in log_slow_statement().  SHOW commands are not counted as slow queries, even if they use table scans.
[9 Oct 2006 23:13] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/13367

ChangeSet@1.2356, 2006-10-09 17:13:17-06:00, tsmith@siva.hindu.god +6 -0
  Merge siva.hindu.god:/usr/home/tim/m/bk/b19764/50
  into  siva.hindu.god:/usr/home/tim/m/bk/tmp/51
  
  Null-merge of code changes, but keep test case from 5.0 for bug #19764
  MERGE: 1.1810.2173.1
[12 Oct 2006 5:35] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/13548

ChangeSet@1.2303, 2006-10-11 23:35:52-06:00, tsmith@siva.hindu.god +7 -0
  Revert patch for bug #19764, which did not work with prepared statements.
[12 Oct 2006 5:46] Timothy Smith
The simple fix for this in 5.0 didn't work, because of the interaction with prepared statements.  For example, the following failed:

show status like 'slow_queries';
# Table scan query, to ensure that slow_queries does still get incremented
# (mysqld is started with --log-queries-not-using-indexes)
select 1 from information_schema.tables limit 1;
show status like 'slow_queries';

It *should* display '1' slow query, but instead it displays 0, because the flag which marks a SHOW command is not reset when using prepared statements.

Also, this fails in the other direction:

PREPARE s FROM 'show status like "slow_queries"';
EXECUTE s;  -- This increments the slow_queries status variable
[12 Oct 2006 23:10] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/13635

ChangeSet@1.2304, 2006-10-12 17:10:34-06:00, tsmith@siva.hindu.god +9 -0
  Bug #19764:  SHOW commands end up in the slow log as table scans
  
  Do not consider SHOW commands slow queries, just because they don't use proper indexes.
  
  This bug fix is not needed in 5.1, and the code changes will be null merged.  However, the test cases will be propogated up to 5.1.
[9 Nov 2006 14:55] Paul DuBois
Noted in 5.0.30 (not 5.0.29) changelog.
No 5.1.x changelog entry because the
fix for 5.1 was already made and logged
earlier.