# Pmm-agent doesn't set database for some queries when analyzing mysql.slow-log

**URL:** <https://forums.percona.com/t/pmm-agent-doesnt-set-database-for-some-queries-when-analyzing-mysql-slow-log/12061>\
**Category:** PMM 2.x\
**Tags:** pmm, mysql, closed-no-reply\
**Created:** [September 7, 2021, 8:30am UTC](https://forums.percona.com/t/pmm-agent-doesnt-set-database-for-some-queries-when-analyzing-mysql-slow-log/12061 "2021-09-07T08:30:46Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![SpicyChicken](https://avatars.discourse-cdn.com/v4/letter/s/3bc359/32.png) [@SpicyChicken](https://forums.percona.com/u/SpicyChicken)\
**Post date:** [September 7, 2021, 8:30am UTC](https://forums.percona.com/t/pmm-agent-doesnt-set-database-for-some-queries-when-analyzing-mysql-slow-log/12061/1 "2021-09-07T08:30:46Z")

</div>

## What’s the problem?

we found that the field(schema) in most of records in the table(pmm.metrics) in Clickhouse is empty, which will affect when we want to filter for slow queries in a specific database.

from this [article](https://www.percona.com/blog/2014/03/14/mysql-slow-query-log-tools-and-tips/), we knew that pmm2 collect details of slow queries by analyzing MySQL slow query log, and we found the implementation for MySQL slow query log analyzing tool in [percona/pmm-agent](https://github.com/percona/pmm-agent/blob/master/agents/mysql/slowlog/parser/parser.go).

```go
// file:pmm-agent/agents/mysql/slowlog/parser/parser.go
// line: 110
func (p *SlowLogParser) Run() {
    //...
}

```

After observing the implementation of the source code of pmm-agent, we understand that the reason. The pmm-agent set field(schema) for queries that include declaration of database( **use** xxx;) in the slow query log.

We believe that pmm-agent will leave blanks for queries that are not clearly marked for the sake of rigor. However, we think that **after a certain reasoning, we can actually get the database (or potential database) corresponding to the query**.

## For example

We make an example (part from percona/pmm-agent) as follow:

```sql
# User@Host: bookblogs[bookblogs] @ localhost [] Id: 56601
# Query_time: 0.321092 Lock_time: 0.000038 Rows_sent: 0 Rows_examined: 0
use dbnamea;
SET timestamp=1415210700;
SELECT field FROM table_a WHERE some_other_field = 'yahoo' LIMIT 1;

# User@Host: bookblogs[bookblogs] @ localhost [] Id: 56601
# Query_time: 0.000297 Lock_time: 0.000141 Rows_sent: 1 Rows_examined: 1
SET timestamp=1415210700;
SELECT field FROM table_b WHERE another_field = 'bazinga' AND site_id = 1;

# User@Host: backup[backup] @ localhost [] Id: 56458
# Query_time: 0.000558 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
SET timestamp=1415210700;
use `dbnameb`;

# User@Host: bookblogs[bookblogs] @ localhost [] Id: 56601
# Query_time: 0.000164 Lock_time: 0.000059 Rows_sent: 1 Rows_examined: 1
SET timestamp=1415210700;
SELECT another_field FROM table_c WHERE a_third_field = 'tiruriru' AND site_id = 1;

# User@Host: bookblogs[bookblogs] @ localhost [] Id: 56601
# Query_time: 0.000297 Lock_time: 0.000141 Rows_sent: 1 Rows_examined: 1
SET timestamp=1415210700;
SELECT field FROM dbnamec.table_d JOIN dbnamed.table_e ON dbnamec.table_d.site_id = dbnamed.table_e.site_id;

```

and the parse result of pmm-agent will be like as follow:

| sql | schema |
| --- | --- |
| use dbnamea; SELECT field FROM table\_a WHERE some\_other\_field = ‘yahoo’ LIMIT 1; | dbnamea |
| SELECT field FROM table\_b WHERE another\_field = ‘bazinga’ AND site\_id = 1; | |
| use dbnameb; | dbnameb |
| SELECT another\_field FROM table\_c WHERE a\_third\_field = ‘tiruriru’ AND site\_id = 1; | |
| SELECT field FROM dbnamec.table\_d JOIN dbnamed.table\_e ON dbnamec.table\_d.site\_id = dbnamed.table\_e.site\_id; | |

after observing the logs we can complete rest of them:

| sql | schema |
| --- | --- |
| use dbnamea; SELECT field FROM table\_a WHERE some\_other\_field = ‘yahoo’ LIMIT 1; | dbnamea |
| SELECT field FROM table\_b WHERE another\_field = ‘bazinga’ AND site\_id = 1; | **dbnamea** |
| use dbnameb; | dbnameb |
| SELECT another\_field FROM table\_c WHERE a\_third\_field = ‘tiruriru’ AND site\_id = 1; | **dbnameb** |
| SELECT field FROM dbnamec.table\_d JOIN dbnamed.table\_e ON dbnamec.table\_d.site\_id = dbnamed.table\_e.site\_id; | **dbnamec, dbnamed** |

## Maybe some suggestion

We design a logic for database name completion

1. if `use xxx;` occurred;
  1. record the database name

2. when we found query, we parse the sql first:
  1. If there is a table in the statement that does not indicate the corresponding database, we will assign the database record in step-1.1 name to this table
  2. If there is a table in the statement that indicates the corresponding database, we directly use this database

However, the above logic will have such a problem, there may be more than one database name in a record. Can this problem be solved by adding new fields to the table(pmm.metric) ? Or is there any better way to solve this problem?