pt-query-digest reports empty hosts when using query_history_review table

[Francisco Bordenave]

When using query_history_review table for historic results pt-query-digest prints empty host if reverse DNS failed in reported query.

The problem appeared when tried to store Host information in table, some queries were having this field empty.

After doing some research I've found that every slow query should be logged using this format:
# Time: 140103 17:00:11
# User@Host: prod[prod] @ ec2-54-225-196-230.compute-1.amazonaws.com [54.225.196.230]

*notice User@host

For some reasons, it's very often that some queries are being logged this way:
# Time: 140103 16:55:28
# User@Host: prod[prod] @ [192.168.100.114]

*notice the blank space between @ and [ probably due a reverse DNS failure.

pt-query-digest is having issues to report the host queries and it's printing this output:
# Databases pch

# Hosts

# Last errno 1264 (43/95%), 1158 (2/4%)

# User@Host: prod[prod] @ localhost []
# User@Host: prod[prod] @ [192.168.100.114]
# User@Host: prod[prod] @ ec2-54-225-196-230.compute-1.amazonaws.com [54.225.196.230]

I would suggest to add some logic to query parsing and in case the @host is empty pt-query-digest should print IP information inside [ ]

[Nilnandan Joshi (nilnandan-joshi)]

query_history_review table is deprecated in latest version of percona toolkit 2.2.10 (its only available in 2.1)
But still, its true that pt-query-digest reports empty hosts when queries are coming from another server with IP (not hostname)

I run query as a localhost, I'm getting this.

# Query 1: 0.00 QPS, 0.07x concurrency, ID 0x91B86F7C3B10948F at byte 1474
# This item is included in the report because it matches --limit.
# Scores: V/M = 3.84
# Time range: 2014-09-03 14:09:44 to 14:23:27
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 75 3
# Exec time 73 57s 11s 31s 19s 30s 9s 14s
# Lock time 85 984us 223us 384us 328us 366us 71us 366us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 79 19.42M 4.00M 10.28M 6.47M 10.25M 2.79M 4.93M
# Rows affecte 65 9.71M 2.00M 5.14M 3.24M 4.93M 1.30M 2.49M
# Bytes sent 93 186 62 62 62 62 0 62
# Query size 87 219 73 73 73 73 0 73
# String:
# Databases test
# Hosts localhost (2/66%), (1/33%)
# Last errno 1592
# Users root
# Query_time distribution

you can see this: # Hosts localhost (2/66%), (1/33%)

If I run with root@192.168.1.37 user, it gives,

# Query 2: 0 QPS, 0x concurrency, ID 0x37C3E49EAECFAC10 at byte 1474 _____
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2014-09-03 14:24:06
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 25 1
# Exec time 26 20s 20s 20s 20s 20s 0 20s
# Lock time 14 161us 161us 161us 161us 161us 0 161us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 20 5.14M 5.14M 5.14M 5.14M 5.14M 0 5.14M
# Rows affecte 34 5.14M 5.14M 5.14M 5.14M 5.14M 0 5.14M
# Bytes sent 7 14 14 14 14 14 0 14
# Query size 12 31 31 31 31 31 0 31
# String:
# Databases test
# Hosts
# Last errno 1592
# Users root

You can see that nothing mentioned in Hosts.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-1208