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

Polyglot Projects Percona Toolkit
1

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: [url]https://www.percona.com/blog/2012/08/28/hidden-columns-of-query_review_history/[/url]

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.

2

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”

3

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.