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

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

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!

Comments

  • MykolaMykola Percona Percona Staff Role
    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.
  • mirandaellenmirandaellen Entrant Inactive User Role Beginner
    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
  • MykolaMykola Percona Percona Staff Role
    Can you try to remove both current containers and create them from scratch?
  • mirandaellenmirandaellen Entrant Inactive User Role Beginner
    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
  • MykolaMykola Percona Percona Staff Role
    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?
  • mirandaellenmirandaellen Entrant Inactive User Role Beginner
    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
  • MykolaMykola Percona Percona Staff Role
    Can you provide output of the following command?
    docker exec -it pmm-server /usr/bin/mysql -e 'select * from pmm.instances;'
    
  • mirandaellenmirandaellen Entrant Inactive User Role Beginner
    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:

    [email protected]:~# 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 |
    +
    +
    +
    +
    +
    +
    +
    +
    +
    +
    +
  • MykolaMykola Percona Percona Staff Role
    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
    
  • mirandaellenmirandaellen Entrant Inactive User Role Beginner
    All hosts are now on v1.1.1

    [email protected]:~# 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

    [email protected]:~# 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

    [email protected]:~# 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

    [email protected]:~# 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`;
    # [email protected]: 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;
    # [email protected]: 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
  • MykolaMykola Percona Percona Staff Role
    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.
This discussion has been closed.

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