There is no data for the selected MySQL instance, time range or search query.

Percona Monitoring and Management (PMM) PMM 1.x
1

Only seeing “There is no data for the selected MySQL instance, time range or search query.” message in Percona Query Analytics

– I am only seeing warn messages in the /var/log/qan-api.log for the docker container

~# docker exec 28f119760006 tail -200 /var/log/qan-api.log
…
2017/02/09 00:16:53 data.go:105: WARN: [qan.SaveData] agent_id=11: dbh.Write: cannot get instance ID of 37715fe9d3f54bd85ebdebfc6ce3107c: resource not found
2017/02/09 00:16:53.237 127.0.0.1 0 10.164359ms WS /agents/bdc1c3e092de451c4556b809d49aa524/data
2017/02/09 00:16:54.800 10.2.0.17 200 129.102µs GET /
2017/02/09 00:16:54.801 10.2.0.17 404 66.784µs GET /qan-api/agents/a237b7d933d243145fc2c78469912d48
2017/02/09 00:16:54.801 10.2.0.84 200 61.005µs GET /
2017/02/09 00:16:54.802 10.2.0.84 404 46.298µs GET /qan-api/agents/911b75ae967c44925a6931d9e22b0c97
2017/02/09 00:16:56 data.go:105: WARN: [qan.SaveData] agent_id=11: dbh.Write: cannot get instance ID of 37715fe9d3f54bd85ebdebfc6ce3107c: resource not found
2017/02/09 00:16:56.249 127.0.0.1 0 7.334172ms WS /agents/bdc1c3e092de451c4556b809d49aa524/data
2017/02/09 00:16:57.802 10.2.0.17 200 149.635µs GET /
…

– On the client side there also no errors

~# tail /var/log/pmm-mysql-queries-42001.log
2017/02/08 18:58:02.874976 WARNING data-sender Waiting for API to ack qan_1486551600102701177: EOF
2017/02/08 18:58:59.842433 WARNING data-sender Waiting for API to ack qan_1486551600102701177: EOF
2017/02/08 18:59:02.863205 WARNING data-sender Waiting for API to ack qan_1486551600102701177: EOF
2017/02/08 18:59:05.879229 WARNING data-sender Waiting for API to ack qan_1486551600102701177: EOF
2017/02/08 19:00:02.891185 WARNING data-sender Waiting for API to ack qan_1486551600102701177: EOF
2017/02/08 19:00:05.999314 WARNING data-sender Waiting for API to ack qan_1486551600102701177: EOF
2017/02/08 19:00:10.034412 WARNING data-sender Waiting for API to ack qan_1486551600102701177: EOF
2017/02/08 19:01:05.841785 WARNING data-sender Waiting for API to ack qan_1486551600102701177: EOF
2017/02/08 19:01:08.858094 WARNING data-sender Waiting for API to ack qan_1486551600102701177: EOF
2017/02/08 19:01:11.868625 WARNING data-sender Waiting for API to ack qan_1486551600102701177: EOF

– pmm list comes back okay mysql is version 5.6.32-78.1

~# pmm-admin list
pmm-admin 1.0.4

PMM Server | 10.2.100.68
Client Name | kamek
Client Address | 10.2.0.17
Service manager | unix-systemv

SERVICE TYPE NAME CLIENT PORT RUNNING DATA SOURCE OPTIONS

linux:metrics 10.2.0.17 42000 YES -
mysql:queries 10.2.0.17 42001 YES root:@unix(/var/run/mysqld/app.sock) query_source=slowlog
mysql:metrics 10.2.0.17 42002 YES root:@unix(/var/run/mysqld/app.sock) tablestats=OFF

– The slow log is being flooded with queries.

– I’ve tired removing and re-adding mysql:queries for the client but still don’t see results in Query Analytics.

~# pmm-admin remove mysql:queries 10.2.0.17
OK, removed MySQL queries 10.2.0.17 from monitoring.

rm /etc/init.d/pmm-mysql-queries-42001
rm -rf /usr/local/percona/qan-agent/data/
rm -rf /usr/local/percona/qan-agent/config/
rm -rf /usr/local/percona/qan-agent/instance/

~# pmm-admin add mysql:queries 10.2.0.17
OK, now monitoring MySQL queries from slowlog using DSN root:***@unix(/var/run/mysqld/app.sock)

Any suggestions as to what to try next?
Thanks!

2

Looks like you don’t have any new entries in mysql slowlog.
Can you switch data source from “Slow Log” to “Performance Schema” and wait several minutes?
It is needed to login to Percona Query Analytics Web Interface, press “SETTINGS” button and change “Collect from” value.

3

I tried switching to performance schema but got the following error:
QAN API error: “json cannot unmarshal number into go value of type string check the /var/log/qan-api.log file in docker container for more information”
When I tail /var/log/qan-api.log the logs look the same as what I pasted earlier.

Also, we are not using the performance schema we are using the slow log. New entries are flying through when I tail the slow log. The log is at /var/app.dbserver/mysqlSlow.log
Here are some of the values in the mysql cnf file:
datadir=/var/app.dbserver
slow-query-log=1
slow-query-log-file=mysqlSlow.log

4

Can you try to remove both current containers and create them from scratch?

5

I tried removing the docker container on the PMM server and clients and reinstalling both but I am still getting the message “There is no data for the selected MySQL instance, time range or search query.” An error also pops up for a couple seconds and then disappears “QAN API error: ‘MySQL Query Analytics configuration not found’. Check the /var/log/qan-api.log file in docker container for more information.”

Similar to before I see this warn message in the qan-api.log
:~# docker exec d1fdb0dd972e tail -20 /var/log/qan-api.log

2017/02/15 16:20:27 data.go:105: WARN: [qan.SaveData] agent_id=7: dbh.Write: cannot get instance ID of fe4a1bfc8a5747eb7496e9e89e267e51: resource not found

6

it is needed to remove both containers, pmm-server and pmm-data, and recreate both of them.
because all information is stored on pmm-data container.
can you try to remove it?

7

I tried removing both containers and the client and recreating all of them but still don’t see any data.
I am getting an exit status of 1 for the MySQL Summary and the following in the qan-api.log in the pmm-server docker container

2017/02/21 15:41:11 server.go:2317: http: response.WriteHeader on hijacked connection
2017/02/21 15:41:11 server.go:2317: http: response.Write on hijacked connection
2017/02/21 15:41:11.853 127.0.0.1 404 2.055153ms WS /agents/42308bb292c4409748deb9c0f60166c6/data
2017/02/21 15:41:13 data.go:105: WARN: [qan.SaveData] agent_id=2: dbh.Write: cannot get instance ID of fe4a1bfc8a5747eb7496e9e89e267e51: resource not found
2017/02/21 15:41:13.596 127.0.0.1 0 13.036971ms WS /agents/a339d71a0632413544f5af6479f4b2e5/data
2017/02/21 15:41:16 data.go:105: WARN: [qan.SaveData] agent_id=2: dbh.Write: cannot get instance ID of fe4a1bfc8a5747eb7496e9e89e267e51: resource not found
2017/02/21 15:41:16.615 127.0.0.1 0 3.106964ms WS /agents/a339d71a0632413544f5af6479f4b2e5/data

8

Can you provide output of the following command?

docker exec -it pmm-server /usr/bin/mysql -e 'select * from pmm.instances;'
9

kamek is the front and has an ip of 10.2.0.17 and chddb4 is a slave has an ip of 10.2.0.84.
I resolved the exit 1 status for the MySQL summary by creating /etc/percona-toolkit/pt-mysql-summary.conf with the right creds.

Here is the output:

root@chdmonv005:~# docker exec -it pmm-server /usr/bin/mysql -e ‘select * from pmm.instances;’
±------------±-------------±---------------------------------±---------------------------------±----------±--------------------------------------------------------±-----------------------------------------------------±----------------±--------------------±--------------------+
| instance_id | subsystem_id | parent_uuid | uuid | name | dsn | distro | version | created | deleted |
±------------±-------------±---------------------------------±---------------------------------±----------±--------------------------------------------------------±-----------------------------------------------------±----------------±--------------------±--------------------+
| 1 | 1 | | da1e1e51af824e734c0d6d7c1ebbfe5b | chddb4 | NULL | | | 2017-02-17 17:13:12 | NULL |
| 2 | 2 | da1e1e51af824e734c0d6d7c1ebbfe5b | a339d71a0632413544f5af6479f4b2e5 | chddb4 | NULL | | 1.0.7 | 2017-02-17 17:13:12 | NULL |
| 3 | 3 | da1e1e51af824e734c0d6d7c1ebbfe5b | 82471189c07e4b685a2303b1138cbd62 | 10.2.0.84 | root:@unix(/var/run/mysqld/app.sock) | Percona Server (GPL), Release 78.1, Revision 4c779b7 | 5.6.32-78.1-log | 2017-02-17 17:13:12 | 1970-01-01 00:00:01 |
| 4 | 1 | | 40a5f10c70bc48dd7de50c61d192774f | kamek | NULL | | | 2017-02-17 17:15:42 | NULL |
| 5 | 2 | 40a5f10c70bc48dd7de50c61d192774f | 313269da068143d14f663cacebb573cc | kamek | NULL | | 1.0.4 | 2017-02-17 17:15:42 | NULL |
| 6 | 3 | 40a5f10c70bc48dd7de50c61d192774f | 2bf7f4b88dc545d87364d43668b44252 | 10.2.0.17 | root:@unix(/var/run/mysqld/app.sock) | Percona Server (GPL), Release 78.1, Revision 4c779b7 | 5.6.32-78.1-log | 2017-02-17 17:15:53 | 2017-02-17 17:58:41 |
| 7 | 1 | | 827d891c331146b871f4bd2d5959de38 | centos-6 | NULL | | | 2017-02-17 17:43:27 | NULL |
| 8 | 2 | 827d891c331146b871f4bd2d5959de38 | 33e3fd5e3e25477a457caf14af3a8db2 | centos-6 | NULL | | 1.0.4 | 2017-02-17 17:43:27 | NULL |
| 9 | 3 | 827d891c331146b871f4bd2d5959de38 | 24cd826220ef4956421bce6c8ec5e901 | centos-6 | root:@unix(/var/lib/mysql/mysql.sock) | Percona Server (GPL), Release 78.1, Revision 4c779b7 | 5.6.32-78.1 | 2017-02-17 17:43:27 | NULL |
| 10 | 3 | 40a5f10c70bc48dd7de50c61d192774f | 5e89cea7c5684b5d43e12021bb5601c7 | 10.2.0.17 | root:@unix(/var/run/mysqld/app.sock)/?parseTime=true | Percona Server (GPL), Release 78.1, Revision 4c779b7 | 5.6.32-78.1-log | 2017-02-17 17:59:38 | 1970-01-01 00:00:01 |
±------------±-------------±---------------------------------±---------------------------------±----------±--------------------------------------------------------±-----------------------------------------------------±----------------±--------------------±--------------------+

10

as I can see some clients are v1.0.4.
1.0.4 clients works incorrectly with v1.0.7 server.
can you update pmm server and pmm-client on all hosts to the latest version? (v1.1.1)
and try to re-add mysql service

pmm-admin uninstall
pmm-admin add mysql
11

All hosts are now on v1.1.1

root@chdmonv005:~# docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
0036a357c903 percona/pmm-server:1.1.1 “/opt/entrypoint.sh” 19 minutes ago Up 19 minutes 0.0.0.0:80->80/tcp, 443/tcp pmm-server
af98e746030a percona/pmm-server:1.1.1 “/opt/entrypoint.sh” 19 minutes ago Created pmm-data

root@chdmonv005:~# docker exec 0036a357c903 tail -f /var/log/qan-api.log
2017/02/23 16:36:27 server.go:2317: http: response.Write on hijacked connection
2017/02/23 16:36:27.121 127.0.0.1 404 2.47209ms WS /agents/42308bb292c4409748deb9c0f60166c6/log
2017/02/23 16:36:27 server.go:2317: http: response.WriteHeader on hijacked connection
2017/02/23 16:36:27 server.go:2317: http: response.Write on hijacked connection
2017/02/23 16:36:27.121 127.0.0.1 404 3.104319ms WS /agents/42308bb292c4409748deb9c0f60166c6/cmd
2017/02/23 16:36:42 data.go:105: WARN: [qan.SaveData] agent_id=2: dbh.Write: cannot get instance ID of 37715fe9d3f54bd85ebdebfc6ce3107c: resource not found
2017/02/23 16:36:42.321 127.0.0.1 0 8.979134ms WS /agents/c94f8619bbe54422768483a66566a6c4/data

root@chdmonv005:~# docker exec -it pmm-server /usr/bin/mysql -e ‘select * from pmm.instances;’
±------------±-------------±---------------------------------±---------------------------------±-------±----------------------------------------±-----------------------------------------------------±----------------±--------------------±--------------------+
| instance_id | subsystem_id | parent_uuid | uuid | name | dsn | distro | version | created | deleted |
±------------±-------------±---------------------------------±---------------------------------±-------±----------------------------------------±-----------------------------------------------------±----------------±--------------------±--------------------+
| 1 | 1 | | 5b1eb1e4613c494657fc59cb1cfb2a10 | kamek | NULL | | | 2017-02-23 16:17:48 | NULL |
| 2 | 2 | 5b1eb1e4613c494657fc59cb1cfb2a10 | c94f8619bbe54422768483a66566a6c4 | kamek | NULL | | 1.1.1 | 2017-02-23 16:17:48 | NULL |
| 3 | 3 | 5b1eb1e4613c494657fc59cb1cfb2a10 | fb0f90b27db04c975736523ceb8c8ab2 | kamek | root:@unix(/var/run/mysqld/app.sock) | Percona Server (GPL), Release 78.1, Revision 4c779b7 | 5.6.32-78.1-log | 2017-02-23 16:17:48 | 1970-01-01 00:00:01 |
| 4 | 1 | | efc60e482b9446e0605a882483065c40 | chddb4 | NULL | | | 2017-02-23 16:26:50 | NULL |
| 5 | 2 | efc60e482b9446e0605a882483065c40 | 16531ba9c9ba421669c7b68e7a28a5bc | chddb4 | NULL | | 1.1.1 | 2017-02-23 16:26:50 | NULL |
| 6 | 3 | efc60e482b9446e0605a882483065c40 | 50be630dd50e44647f9805e3d37ef9e9 | chddb4 | root:@unix(/var/run/mysqld/app.sock) | Percona Server (GPL), Release 78.1, Revision 4c779b7 | 5.6.32-78.1-log | 2017-02-23 16:26:50 | 1970-01-01 00:00:01 |
±------------±-------------±---------------------------------±---------------------------------±-------±----------------------------------------±-----------------------------------------------------±----------------±--------------------±--------------------+

There is no data. There are also no errors.

We do performance testing on this box so there should be a ton of queries

root@kamek:~# tail -f /var/app.dbserver/mysqlSlow.log

Schema: la301 Last_errno: 0 Killed: 0

Query_time: 0.000093 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0 Rows_affected: 0

Bytes_sent: 11 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0

QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No

Filesort: No Filesort_on_disk: No Merge_passes: 0

No InnoDB statistics available for this query

Log_slow_rate_type: query Log_slow_rate_limit: 100

use la301;
SET timestamp=1487867928;
USE la301;

User@Host: camp[camp] @ [10.2.1.93] Id: 4316176

Schema: plta1perf091 Last_errno: 0 Killed: 0

Query_time: 0.000035 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 Rows_affected: 0

Bytes_sent: 56 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0

QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No

Filesort: No Filesort_on_disk: No Merge_passes: 0

No InnoDB statistics available for this query

Log_slow_rate_type: query Log_slow_rate_limit: 100

use plta1perf091;
SET timestamp=1487867928;
SELECT 1;

User@Host: camp[camp] @ [10.2.1.98] Id: 4316335

Schema: bt869 Last_errno: 0 Killed: 0

Query_time: 0.000152 Lock_time: 0.000045 Rows_sent: 0 Rows_examined: 1 Rows_affected: 0

Bytes_sent: 80 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0

InnoDB_trx_id: 3149C9DF8

QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No

Filesort: No Filesort_on_disk: No Merge_passes: 0

InnoDB_IO_r_ops: 0 InnoDB_IO_r_bytes: 0 InnoDB_IO_r_wait: 0.000000

InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000

InnoDB_pages_distinct: 4

Log_slow_rate_type: query Log_slow_rate_limit: 100

photoid=47378
photoid=473781185×202 19.5 KB

12

If you don’t see any queries in slow-log, it is needed to configure it inside mysql,
Query Analytics tool just read queries from slow log and don’t change mysql server configuration.