Not the answer you need?
Register and ask your own question!

How to filter insert statement and load hostname in same time by pt-query-digest

Dillon_luDillon_lu ContributorCurrent User Role Novice
Hi all,

When I load slow log to database, I have to filter some insert statements. So, my command looks like:
pt-query-digest --filter '$event->{arg} !~ m/INSERT/i && ($event->{arg} !~ m/hisd/i || $event->{arg} !~ m/hisf/i)' --user=app --password=xxxxxx --no-report --history h=localhost,D=percona_db,t=query_history_test ./slow.log-20161226
The above works in my case.

However, when I follow percona's blog to add four columns, user_max, host_max, db_max and hostname_max. I failed to laod hostname to database. This is the URL of the blog: https://www.percona.com/blog/2012/08/28/hidden-columns-of-query_review_history/

To populcate hostname, I have to use filter like this: --filter " \$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}="$HOSTNAME"" But, if I merge hostname condition to my filter, I can't get the hostname. Sometimes, the hostname_max filed in database is blank or the pt-query-digest can't recognize the filter condition.

Could you please give me some advise how to put hostname to database in my case? Thanks.

Comments

  • carlos.salguerocarlos.salguero Percona Toolkit Developer Percona Staff Role
    Please try escaping the quotes around $HOSTNAME to "$HOSTNAME" and also consider that string comparison operator in Perl is eq, not =
    \$event->{hostname} eq "$HOSTNAME"
  • Dillon_luDillon_lu Contributor Current User Role Novice
    Hi carlos,

    Thanks. I did some changes one by one.
    If I use the following command:
    pt-query-digest --filter '$event->{arg} !~ m/INSERT/i && ($event->{arg} !~ m/hisd/i || $event->{arg} !~ m/hisf/i) && \$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}=\"$HOSTNAME\"' --user=app --password=xxx --no-report --history h=localhost,D=percona_db,t=query_history_test slow.log-20161121
    
    The error is :
    Error compiling --filter code: sub { my ( $args ) = @_; my $event = $args->{event}; ( $event->{arg} !~ m/INSERT/i && ($event->{arg} !~ m/hisd/i || $event->{arg} !~ m/hisf/i) && \$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}=\"$HOSTNAME\" ) && return $args; };
    Can't find string terminator '"' anywhere before EOF at (eval 33) line 1.
    

    It looked the filter condition was processed incorrectly.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.