I would like to ignore queries in the mysql-slow log that originate from running the command mysqldumpslow by using SQL_NO_CACHE as the trigger word.

My experience using regex are limited at best and I'm unable to find any examples in the docs.

I've tried a few variations of the following:

mysqlslowdump -g '!(SQL_NO_CACHE)' /path/to/my/log
mysqlslowdump -g '^!(SQL_NO_CACHE)$' /path/to/my/log

... but always get back:

Died at /usr/bin/mysqldumpslow line 162

What is wrong in my regex?

  • Suggest switching to pt-query-digest. It may not do what you are asking for, but it may have better output in general. – Rick James Feb 10 '15 at 4:49
  • heh... it looks like it would actually be something along the lines of -g '(?{{ undef $_ if \/SQL_NO_CACHE\/ }})' ... now, I'm curious. Negative matching doesn't seem to be natively supported, but it "should" be possible (famous last words). – Michael - sqlbot Feb 11 '15 at 22:35
  • I believe the question is a duplicate of this: stackoverflow.com/questions/36444018/… – Federico Razzoli Mar 21 '18 at 1:44

OK - you should check out the page here,

    (Due to a limitation in the parser, a space character must precede 
and follow the SQL_NO_CACHE keyword; a nonspace such as a newline 
causes the server to check the query cache to see whether 
the result is already cached.)

Now, it is possible that the syntax that you are using doesn't correspond to the bit about "newlines" above - I'm not sure. Maybe you could try running your queries directly from the command line?

[EDIT] in response to OP's comment.

mysqlslowdump -g '!(SQL_NO_CACHE)' /path/to/my/log
mysqlslowdump -g '^!(SQL_NO_CACHE)$' /path/to/my/log

I've never come across this problem, but is it possible that rewriting your code with spaces surrounding "SQL_NO_CACHE" could have some effect?

  • Interesting tidbit, but I don't think it's applicable - or controllable. The queries are generated from a backup using mysqldump. The log file also is not displaying any indication that a newline is present (as is for other, non-targeted, queries. – KDrewiske Feb 11 '15 at 19:21
  • The question is about using an option in mysqlslowdump. The answer is about how MySQL executes some queries. Useful, but unrelated. – Federico Razzoli Mar 21 '18 at 1:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.