Description:
QAN UI tab missing database name on the left sidebar, but it does show service name as well queries executed correctly.
Steps to Reproduce:
pmm-admin add mysql --username=[user] --password=[pass] --query-source=slowlog --size-slow-logs=1GiB mysql-custom 127.0.0.1:3306
after adding mysql to pmm-server, in UI → QAN database name doesn’t resolve.
Also trying to troubleshoot a bit why postgres works just fine and mysql doesnt - I inspected a query. And in fact mysql query missing Database metadata field, while postgres has it
Version:
pmm-server docker
percona/pmm-server:2
DIGEST:sha256:4a4ff78ae4fc42194f77d0361ba03687b24c81dfe5c1ea78bd720fc1167a0f70
client pmm-admin
Version: 2.38.1
PMMVersion: 2.38.1
Expected Result:
(couldn’t embed two pictures)
Actual Result:
Additional Information:
also same problem if using perfomance query source
also did try first with pmm-server 2.38.1 before upgrading server
and had purged docker volume pmm-data as well
for running docker image I used provided on dockerhub example
docker run --detach --restart always --publish 443:443 -v pmm-data:/srv --name pmm-server percona/pmm-server:2
in none of this it helped to solve it
Hello @Lee_Ardesh,
QAN gets all metadata about a query from the slow query log, which I see you have enabled. Can you please look at your slow query log for one of the queries in QAN and see if the database information is present? If not, that is the reason why it is not showing in QAN.
After Trying to Freshly re-add few times, the problem persists
I am able to view that such query has executed and how long it took as well as load of it. The problem is there no Database in Metadata for some reasons, and yes I am using slowlog as this:
pmm-admin config … pointing to my pmm-server setup
pmm-admin add mysql --username=[user] --password=[pass] --query-source=slowlog --size-slow-logs=1GiB mysql-slowlog 127.0.0.1:3306
the user has all permissions so it should not be the cause.
the error I get when using Explain tab in QAN for mysql query is:
Database name is not included in this query. Explain could not be triggered without this info: Error 1046 (3D000): No database selected
I assume it is the problem with pmm-server parsing slow logs and same for perfomance schema, because in second even queries are displayed with backtick escape:
I am using [backtick] instead of actual symbol due to inability to escape this character
SELECT [backtick]name[backtick] FROM [backtick]test[backtick]
And I did run queries both with USE db; as well as plainly SELECT … FROM db.table;
Hello @Lee_Ardesh,
Please read my previous comment again and look at the slow query log itself. Post your results.
SELECT * FROM test.test;
And there is no Database field in metadata
Excuse me for not mentioning it in my previous response, but I did investigate slow query log as well perfomance table;
This is the slow query log for this request
# Time: 2023-10-13T11:59:16.240633Z
# User@Host: root[root] @ localhost Id: 15
# Query_time: 0.000192 Lock_time: 0.000002 Rows_sent: 8 Rows_examined: 8
SET timestamp=1697198356;
SELECT * FROM test.test;
and yes there is no database field, except that I did hope it would be displayed in next to the user who executed the query
is there a way to enable this? and is it only available for slow query log then
if it is even supported
Try this test:
mysql> use test;
mysql> SELECT * FROM test.test;
Then look at slow log. Do you see the test database now? If so, that’s the issue. You need to use the database in order for MySQL to notice. This isn’t a PMM problem, it’s a mysql thing. You don’t need to enable anything, you simply need to use the database before you execute a query.
