MySQL – Fix ‘No Database Selected’ or ‘Table Doesn’t Exist’ Errors in pt-upgrade

MySQLpercona-toolsupgrade

I collected queries on our Master using the following tcpdump command:

tcpdump -i any -s 65535 -x -n -nn -q -tttt 'port 3306' > tcpdump.tcp

The queries are executed amongst several databases.

I then ran this file through pt-query-digest using the following command:

pt-query-digest --output=slowlog --no-report --sample 100 --type tcpdump tcpdump.tcp > slow.log

Then I ran pt-upgrade against two Slaves like this:

pt-upgrade --user user --ask-pass --run-time=1h --upgrade-table percona.pt_upgrade h=10.1.1.1 h=10.1.1.2 slow.log

But I got a bunch of failures since it does not appear to specify which database the query should be executed against.

How is one supposed to use pt-upgrade when queries are collected amongst multiple DBs? AFAICT this isn't specified in the documentation anywhere.

Are you supposed to use –filter with pt-query-digest to just output queries for a particular database and then specify –database with pt-upgrade? Rinse and repeat per database.

It takes several hours to parse my gigantic tcpdump capture so any guidance here is appreciated.

Thanks!

P.S.

I used this article as a starting point but it is outdated. e.g. pt-query-digest doesn't have a –print option anymore.

Best Answer

  • Does it still fail to work if you omit --sample?

  • (from manual)

Also note that pt-query-digest may fail to report the database for queries when parsing tcpdump output. The database is discovered only in the initial connect events for a new client or when is executed. If the tcpdump output contains neither of these, then pt-query-digest cannot discover the database.

  • Consider using the general log instead of tcpdump?
Related Question