MySQL Bugs: #19046: slow query log should include the affected database

Bug #19046	slow query log should include the affected database
Submitted:	12 Apr 2006 12:18	Modified:	3 Oct 2009 16:19
Reporter:	Anders Henke	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Logging	Severity:	S4 (Feature request)
Version:	4.0.25 and 5.0.19	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any
Tags:	Contribution

Description:
The slow query look currently looks like this:

# Time: 060412 14:10:01
# User@Host: user123[user123] @  [127.0.0.1]
# Query_time: 4  Lock_time: 0  Rows_sent: 9  Rows_examined: 220965
select distinct p.products_id, pd.products_name from products p, products_description pd, products_to_categories p2c, categories c where p.products_id = pd.products_id and pd.language [...]

One of the most common reason for slow queries are bad database designs leading e.g.
to tablescans, so it were a good idea to chase down on those issues by taking a look at 
the slow query log. However, the slow query log does list the user, the sctx-user and
its ip address, but not the database.
If a database user has access to more than one database, it's can be quite a long 
search for the affected databse; even if he hasn't, it does still take an additional
lookup in the administrative database tables to check what database that user
might have been operating on.

Only certain php-based scripts seem to prepend a "use $database" command to 
their queries and so, they are the only ones which can be that easy found. Perl and
most other languages do directly connect to the database, so there's no
"use $database" command being issued later. 

How to repeat:
Take a look at a slow query log :-)

Suggested fix:
Add a "Database" field or line to the slow query output.

Thank you for a reasonable feature request.

This is a patch that does exactly that, it shows a new field 'Database' with the corresponding database name in use. Besides, i modified 'mysqldumpslow' to use the new format in its output, however it keeps the old one for current slow log files.

Patch can be found at:

http://noslave.net/holman/devel/mysql/patches/slow_log_write_5_0_41.patch
http://noslave.net/holman/devel/mysql/patches/mysqldumpslow_5_0_41.patch

I forgot it, the 'mysqldumpslow' patch includes a new -b DATABASE_NAME flag to show only the queries affecting the given database. You can use it as follows:

mysqldumpslow -b sakila

change 'sakila' to the database name you want to look for.

Hello Anders - 

In order for us to continue the process of reviewing your contribution to MySQL - We need you to review and sign the Sun|MySQL contributor agreement (the "SCA")

The process is explained here: 
http://forge.mysql.com/wiki/Sun_Contributor_Agreement

Getting a signed/approved SCA on file will help us facilitate your contribution-- this one, and others in the future.

Thank you ! 

Liz Drachnik  - Program Manager - MySQL

Hi Liz,

the patch in this bugreport has been sent by Holman Romero.
Please do contact him in order to settle this SCA-issue.

Thank you,

Anders

Hello  Holman

In order for us to continue the process of reviewing your contribution to MySQL 

http://bugs.mysql.com/19046

We need
you to review and sign the Sun|MySQL contributor agreement (the "SCA")

The process is explained here:
http://forge.mysql.com/wiki/Sun_Contributor_Agreement

Getting a signed/approved SCA on file will help us facilitate your contribution-- this
one, and others in the future.

Thank you !

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".