Search code examples
mysqlcommentsprofilingmysql-slow-query-log

How to get Mysql comments to show up in the slow-log or processlist?

I'm using the mysql slow query log and SHOW PROCESSLIST; to debug some slow queries.

MySQL allows comments to be added with the standard /* ... */ syntax. I was hoping by adding a comment in front of my query, it will be easier to spot the origin of some of our queries. For example, many times in the slow-log I just see commit so I know the transaction is slow but I find it hard to determine more details about the origin of the queries.

To test this trick, I'm using:

 SELECT /* GREP-HOOK */ SLEEP(3);

I was hoping the "GREP-HOOK" comment would show up in SHOW [FULL] PROCESSLIST and in the log/slow-log file. However it doesn't.

Anyone know if there's a my.cnf setting that will allow comments to show in SHOW PROCESSLIST?

For reference:

> mysqld --version
mysqld  Ver 5.5.34-0ubuntu0.13.10.1-log for debian-linux-gnu on x86_64 ((Ubuntu))

(but I also want to try this in Amazon RDS 5.5).

Solution

  • I was testing with the mysql command line client. It turns out that the command line client strips the comments before sending to the server (which could be a bug).

    When I tested using a PHP script, the comment was retained fine, and appeared in the slow-log as I wanted.

    For reference, my mysql client version is:

    > mysql --version
mysql  Ver 14.14 Distrib 5.5.34, for debian-linux-gnu (x86_64) using readline 6.2