Skip to content

Commit ed54cd6

Browse files
shuzhou-gcaverikitsch
shuzhou-gc
and
averikitsch
authored
feat(tools/mysql-list-active-queries): Add a new tool to list ongoing queries in a MySQL instance (#1471)
## Description --- This PR introduces a new MySQL tool `mysql-list-active-queries`. It adds a new kind `mysql-list-active-queries` that returns the top N currently running queries, pulled from `information_schema.innodb_trx` and `information_schema.processlist`. The list is ordered by elapsed time in descending order. Parameters supported - `min_duration_secs` (optional) — only include queries running at least this many seconds - `limit` (optional) — max rows to return (default 10). ## PR Checklist --- > Thank you for opening a Pull Request! Before submitting your PR, there are a > few things you can do to make sure it goes smoothly: - [x] Make sure you reviewed [CONTRIBUTING.md](https://github.com/googleapis/genai-toolbox/blob/main/CONTRIBUTING.md) - [x] Make sure to open an issue as a [bug/issue](https://github.com/googleapis/genai-toolbox/issues/new/choose) before writing your code! That way we can discuss the change, evaluate designs, and agree on the general idea - [x] Ensure the tests and linter pass - [x] Code coverage does not decrease (if any source code was changed) - [x] Appropriate docs were updated (if necessary) - [x] Make sure to add `!` if this involves a breaking change --------- Co-authored-by: Averi Kitsch <akitsch@google.com>
1 parent e483cc7 commit ed54cd6

10 files changed

Lines changed: 617 additions & 22 deletions

File tree

‎cmd/root.go‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -121,6 +121,7 @@ import (
121121
_ "github.com/googleapis/genai-toolbox/internal/tools/mssql/mssqllisttables"
122122
_ "github.com/googleapis/genai-toolbox/internal/tools/mssql/mssqlsql"
123123
_ "github.com/googleapis/genai-toolbox/internal/tools/mysql/mysqlexecutesql"
124+
_ "github.com/googleapis/genai-toolbox/internal/tools/mysql/mysqllistactivequeries"
124125
_ "github.com/googleapis/genai-toolbox/internal/tools/mysql/mysqllisttables"
125126
_ "github.com/googleapis/genai-toolbox/internal/tools/mysql/mysqlsql"
126127
_ "github.com/googleapis/genai-toolbox/internal/tools/neo4j/neo4jcypher"

‎cmd/root_test.go‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1429,7 +1429,7 @@ func TestPrebuiltTools(t *testing.T) {
14291429
wantToolset: server.ToolsetConfigs{
14301430
"cloud_sql_mysql_database_tools": tools.ToolsetConfig{
14311431
Name: "cloud_sql_mysql_database_tools",
1432-
ToolNames: []string{"execute_sql", "get_query_plan", "list_tables"},
1432+
ToolNames: []string{"execute_sql", "list_tables", "get_query_plan", "list_active_queries"},
14331433
},
14341434
},
14351435
},
@@ -1469,7 +1469,7 @@ func TestPrebuiltTools(t *testing.T) {
14691469
wantToolset: server.ToolsetConfigs{
14701470
"mysql_database_tools": tools.ToolsetConfig{
14711471
Name: "mysql_database_tools",
1472-
ToolNames: []string{"execute_sql", "get_query_plan", "list_tables"},
1472+
ToolNames: []string{"execute_sql", "list_tables", "get_query_plan", "list_active_queries"},
14731473
},
14741474
},
14751475
},

‎docs/en/resources/sources/cloud-sql-mysql.md‎

Lines changed: 7 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -22,18 +22,21 @@ to a database by following these instructions][csql-mysql-quickstart].
2222

2323
## Available Tools
2424

25-
- [`mysql-sql`](../tools/mysql/mysql-sql.md)
25+
- [`mysql-sql`](../tools/mysql/mysql-sql.md)
2626
Execute pre-defined prepared SQL queries in MySQL.
2727

28-
- [`mysql-execute-sql`](../tools/mysql/mysql-execute-sql.md)
28+
- [`mysql-execute-sql`](../tools/mysql/mysql-execute-sql.md)
2929
Run parameterized SQL queries in Cloud SQL for MySQL.
3030

31-
- [`mysql-list-tables`](../tools/mysql/mysql-list-tables.md)
31+
- [`mysql-list-active-queries`](../tools/mysql/mysql-list-active-queries.md)
32+
List active queries in MySQL.
33+
34+
- [`mysql-list-tables`](../tools/mysql/mysql-list-tables.md)
3235
List tables in a Cloud SQL for MySQL database.
3336

3437
### Pre-built Configurations
3538

36-
- [Cloud SQL for MySQL using MCP](https://googleapis.github.io/genai-toolbox/how-to/connect-ide/cloud_sql_mysql_mcp/)
39+
- [Cloud SQL for MySQL using MCP](https://googleapis.github.io/genai-toolbox/how-to/connect-ide/cloud_sql_mysql_mcp/)
3740
Connect your IDE to Cloud SQL for MySQL using Toolbox.
3841

3942
## Requirements

‎docs/en/resources/sources/mysql.md‎

Lines changed: 6 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -16,13 +16,16 @@ reliability, performance, and ease of use.
1616

1717
## Available Tools
1818

19-
- [`mysql-sql`](../tools/mysql/mysql-sql.md)
19+
- [`mysql-sql`](../tools/mysql/mysql-sql.md)
2020
Execute pre-defined prepared SQL queries in MySQL.
2121

22-
- [`mysql-execute-sql`](../tools/mysql/mysql-execute-sql.md)
22+
- [`mysql-execute-sql`](../tools/mysql/mysql-execute-sql.md)
2323
Run parameterized SQL queries in MySQL.
2424

25-
- [`mysql-list-tables`](../tools/mysql/mysql-list-tables.md)
25+
- [`mysql-list-active-queries`](../tools/mysql/mysql-list-active-queries.md)
26+
List active queries in MySQL.
27+
28+
- [`mysql-list-tables`](../tools/mysql/mysql-list-tables.md)
2629
List tables in a MySQL database.
2730

2831
## Requirements

‎docs/en/resources/tools/mysql/mysql-list-active-queries.md‎

Lines changed: 59 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,59 @@
1+
---
2+
title: "mysql-list-active-queries"
3+
type: docs
4+
weight: 1
5+
description: >
6+
A "mysql-list-active-queries" tool lists active queries in a MySQL database.
7+
aliases:
8+
- /resources/tools/mysql-list-active-queries
9+
---
10+
11+
## About
12+
13+
A `mysql-list-active-queries` tool retrieves information about active queries in a MySQL database. It's compatible with
14+
15+
- [cloud-sql-mysql](../../sources/cloud-sql-mysql.md)
16+
- [mysql](../../sources/mysql.md)
17+
18+
`mysql-list-active-queries` outputs detailed information as JSON for current active queries, ordered by execution time in descending order.
19+
This tool takes 2 optional input parameters:
20+
21+
- `min_duration_secs` (optional): Only show queries running for at least this long in seconds, default `0`.
22+
- `limit` (optional): max number of queries to return, default `10`.
23+
24+
## Example
25+
26+
```yaml
27+
tools:
28+
list_active_queries:
29+
kind: mysql-list-active-queries
30+
source: my-mysql-instance
31+
description: Lists top N (default 10) ongoing queries from processlist and innodb_trx, ordered by execution time in descending order. Returns detailed information of those queries in json format, including process id, query, transaction duration, transaction wait duration, process time, transaction state, process state, username with host, transaction rows locked, transaction rows modified, and db schema.
32+
```
33+
34+
The response is a json array with the following fields:
35+
36+
```json
37+
{
38+
"proccess_id": "id of the MySQL process/connection this query belongs to",
39+
"query": "query text",
40+
"trx_started": "the time when the transaction (this query belongs to) started",
41+
"trx_duration_seconds": "the total elapsed time (in seconds) of the owning transaction so far",
42+
"trx_wait_duration_seconds": "the total wait time (in seconds) of the owning transaction so far",
43+
"query_time": "the time (in seconds) that the owning connection has been in its current state",
44+
"trx_state": "the transaction execution state",
45+
"proces_state": "the current state of the owning connection",
46+
"user": "the user who issued this query",
47+
"trx_rows_locked": "the approximate number of rows locked by the owning transaction",
48+
"trx_rows_modified": "the approximate number of rows modified by the owning transaction",
49+
"db": "the default database for the owning connection"
50+
}
51+
```
52+
53+
## Reference
54+
55+
| **field** | **type** | **required** | **description** |
56+
|-------------|:------------------------------------------:|:------------:|--------------------------------------------------------------------------------------------------|
57+
| kind | string | true | Must be "mysql-list-active-queries". |
58+
| source | string | true | Name of the source the SQL should execute on. |
59+
| description | string | true | Description of the tool that is passed to the LLM. |

‎internal/prebuiltconfigs/tools/cloud-sql-mysql.yaml‎

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -27,6 +27,10 @@ tools:
2727
kind: mysql-execute-sql
2828
source: cloud-sql-mysql-source
2929
description: Use this tool to execute SQL.
30+
list_active_queries:
31+
kind: mysql-list-active-queries
32+
source: cloud-sql-mysql-source
33+
description: Lists top N (default 10) ongoing queries from processlist and innodb_trx, ordered by execution time in descending order. Returns detailed information of those queries in json format, including process id, query, transaction duration, transaction wait duration, process time, transaction state, process state, username with host, transaction rows locked, transaction rows modified, and db schema.
3034
get_query_plan:
3135
kind: mysql-sql
3236
source: cloud-sql-mysql-source
@@ -46,5 +50,6 @@ tools:
4650
toolsets:
4751
cloud_sql_mysql_database_tools:
4852
- execute_sql
49-
- get_query_plan
5053
- list_tables
54+
- get_query_plan
55+
- list_active_queries

‎internal/prebuiltconfigs/tools/mysql.yaml‎

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -31,6 +31,10 @@ tools:
3131
kind: mysql-execute-sql
3232
source: mysql-source
3333
description: Use this tool to execute SQL.
34+
list_active_queries:
35+
kind: mysql-list-active-queries
36+
source: mysql-source
37+
description: Lists top N (default 10) ongoing queries from processlist and innodb_trx, ordered by execution time in descending order. Returns detailed information of those queries in json format, including process id, query, transaction duration, transaction wait duration, process time, transaction state, process state, username with host, transaction rows locked, transaction rows modified, and db schema.
3438
get_query_plan:
3539
kind: mysql-sql
3640
source: mysql-source
@@ -50,5 +54,6 @@ tools:
5054
toolsets:
5155
mysql_database_tools:
5256
- execute_sql
53-
- get_query_plan
5457
- list_tables
58+
- get_query_plan
59+
- list_active_queries

0 commit comments

Comments
 (0)