This is an automated email from the ASF dual-hosted git repository.
gaoxingcun pushed a commit to branch feature/ai-sop-workflow
in repository https://gitbox.apache.org/repos/asf/hertzbeat.git
The following commit(s) were added to refs/heads/feature/ai-sop-workflow by
this push:
new b14391c7aa feat: add MySQL slow query diagnosis SOP and refactor
ToolExecutor
b14391c7aa is described below
commit b14391c7aaaebb89e637c06029c5f8a502a8d463
Author: TJxiaobao <[email protected]>
AuthorDate: Thu Jan 29 16:51:32 2026 +0800
feat: add MySQL slow query diagnosis SOP and refactor ToolExecutor
- Add mysql_slow_query_diagnosis.yml skill for MySQL diagnostics
- Add DatabaseTools interface and implementation
- Add ToolRegistry to auto-discover all @Tool annotated methods
---
.../hertzbeat/ai/sop/engine/SopEngineImpl.java | 10 +
.../hertzbeat/ai/sop/executor/ToolExecutor.java | 190 +------------
.../hertzbeat/ai/sop/registry/ToolRegistry.java | 282 +++++++++++++++++++
.../apache/hertzbeat/ai/tools/DatabaseTools.java | 50 ++++
.../hertzbeat/ai/tools/impl/DatabaseToolsImpl.java | 300 +++++++++++++++++++++
hertzbeat-ai/src/main/resources/skills/README.md | 19 +-
.../src/main/resources/skills/README_ZH.md | 15 ++
.../skills/mysql_slow_query_diagnosis.yml | 95 +++++++
8 files changed, 780 insertions(+), 181 deletions(-)
diff --git
a/hertzbeat-ai/src/main/java/org/apache/hertzbeat/ai/sop/engine/SopEngineImpl.java
b/hertzbeat-ai/src/main/java/org/apache/hertzbeat/ai/sop/engine/SopEngineImpl.java
index d307fb5d2a..44ac80da12 100644
---
a/hertzbeat-ai/src/main/java/org/apache/hertzbeat/ai/sop/engine/SopEngineImpl.java
+++
b/hertzbeat-ai/src/main/java/org/apache/hertzbeat/ai/sop/engine/SopEngineImpl.java
@@ -26,6 +26,7 @@ import org.apache.hertzbeat.ai.sop.executor.SopExecutor;
import org.apache.hertzbeat.ai.sop.model.OutputConfig;
import org.apache.hertzbeat.ai.sop.model.OutputType;
import org.apache.hertzbeat.ai.sop.model.SopDefinition;
+import org.apache.hertzbeat.ai.sop.model.SopParameter;
import org.apache.hertzbeat.ai.sop.model.SopResult;
import org.apache.hertzbeat.ai.sop.model.SopStep;
import org.apache.hertzbeat.ai.sop.model.StepResult;
@@ -119,6 +120,15 @@ public class SopEngineImpl implements SopEngine {
List<StepResult> stepResults = new ArrayList<>();
Map<String, Object> context = new HashMap<>(inputParams);
+ // Apply default values for parameters that are not provided
+ if (definition.getParameters() != null) {
+ for (SopParameter param : definition.getParameters()) {
+ if (!context.containsKey(param.getName()) &&
param.getDefaultValue() != null) {
+ context.put(param.getName(), param.getDefaultValue());
+ }
+ }
+ }
+
// Get output configuration first
OutputConfig outputConfig = definition.getOutput();
if (outputConfig == null) {
diff --git
a/hertzbeat-ai/src/main/java/org/apache/hertzbeat/ai/sop/executor/ToolExecutor.java
b/hertzbeat-ai/src/main/java/org/apache/hertzbeat/ai/sop/executor/ToolExecutor.java
index e1aba900fa..4e7c0ce29f 100644
---
a/hertzbeat-ai/src/main/java/org/apache/hertzbeat/ai/sop/executor/ToolExecutor.java
+++
b/hertzbeat-ai/src/main/java/org/apache/hertzbeat/ai/sop/executor/ToolExecutor.java
@@ -17,40 +17,28 @@
package org.apache.hertzbeat.ai.sop.executor;
+import java.util.HashMap;
+import java.util.Map;
import lombok.extern.slf4j.Slf4j;
import org.apache.hertzbeat.ai.sop.model.SopStep;
-import org.apache.hertzbeat.ai.tools.AlertDefineTools;
-import org.apache.hertzbeat.ai.tools.AlertTools;
-import org.apache.hertzbeat.ai.tools.MetricsTools;
-import org.apache.hertzbeat.ai.tools.MonitorTools;
+import org.apache.hertzbeat.ai.sop.registry.ToolRegistry;
import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.context.annotation.Lazy;
import org.springframework.stereotype.Component;
-import java.util.ArrayList;
-import java.util.HashMap;
-import java.util.List;
-import java.util.Map;
-
/**
* Executor for 'tool' type steps.
- * Calls existing MCP tools registered in the system.
+ * Uses ToolRegistry to dynamically discover and invoke @Tool annotated
methods.
*/
@Slf4j
@Component
public class ToolExecutor implements SopExecutor {
- private final MonitorTools monitorTools;
- private final AlertTools alertTools;
- private final AlertDefineTools alertDefineTools;
- private final MetricsTools metricsTools;
+ private final ToolRegistry toolRegistry;
@Autowired
- public ToolExecutor(MonitorTools monitorTools, AlertTools alertTools,
- AlertDefineTools alertDefineTools, MetricsTools
metricsTools) {
- this.monitorTools = monitorTools;
- this.alertTools = alertTools;
- this.alertDefineTools = alertDefineTools;
- this.metricsTools = metricsTools;
+ public ToolExecutor(@Lazy ToolRegistry toolRegistry) {
+ this.toolRegistry = toolRegistry;
}
@Override
@@ -66,8 +54,9 @@ public class ToolExecutor implements SopExecutor {
Map<String, Object> args = resolveArgs(step.getArgs(), context);
try {
- String result = invokeTool(toolName, args);
- log.debug("Tool {} returned result length: {}", toolName,
result.length());
+ String result = toolRegistry.invoke(toolName, args);
+ log.debug("Tool {} returned result length: {}", toolName,
+ result != null ? result.length() : 0);
return result;
} catch (Exception e) {
log.error("Failed to execute tool {}: {}", toolName,
e.getMessage());
@@ -75,92 +64,6 @@ public class ToolExecutor implements SopExecutor {
}
}
- private String invokeTool(String toolName, Map<String, Object> args) {
- switch (toolName) {
- // MonitorTools
- case "queryMonitors":
- return monitorTools.queryMonitors(
- getListArg(args, "ids"),
- getStringArg(args, "app"),
- getByteArg(args, "status"),
- getStringArg(args, "search"),
- getStringArg(args, "labels"),
- getStringArg(args, "sort"),
- getStringArg(args, "order"),
- getIntArg(args, "pageIndex"),
- getIntArg(args, "pageSize"),
- getBoolArg(args, "includeStats")
- );
- case "listMonitorTypes":
- return monitorTools.listMonitorTypes(getStringArg(args,
"language"));
- case "getMonitorParams":
- return monitorTools.getMonitorParams(getStringArg(args,
"app"));
- case "addMonitor":
- return monitorTools.addMonitor(
- getStringArg(args, "name"),
- getStringArg(args, "app"),
- getIntArg(args, "intervals"),
- getStringArg(args, "params"),
- getStringArg(args, "description")
- );
-
- // AlertTools
- case "queryAlerts":
- return alertTools.queryAlerts(
- getStringArg(args, "alertType"),
- getStringArg(args, "status"),
- getStringArg(args, "search"),
- getStringArg(args, "sort"),
- getStringArg(args, "order"),
- getIntArg(args, "pageIndex"),
- getIntArg(args, "pageSize")
- );
- case "getAlertsSummary":
- return alertTools.getAlertsSummary();
-
- // MetricsTools
- case "getRealtimeMetrics":
- return metricsTools.getRealtimeMetrics(
- getLongArg(args, "monitorId"),
- getStringArg(args, "metrics")
- );
- case "getHistoricalMetrics":
- return metricsTools.getHistoricalMetrics(
- getStringArg(args, "instance"),
- getStringArg(args, "app"),
- getStringArg(args, "metrics"),
- getStringArg(args, "metric"),
- getStringArg(args, "label"),
- getStringArg(args, "history"),
- getBoolArg(args, "interval")
- );
- case "getWarehouseStatus":
- return metricsTools.getWarehouseStatus();
-
- // AlertDefineTools
- case "listAlertRules":
- return alertDefineTools.listAlertRules(
- getStringArg(args, "search"),
- getStringArg(args, "monitorType"),
- getBoolArg(args, "enabled"),
- getIntArg(args, "pageIndex"),
- getIntArg(args, "pageSize")
- );
- case "getAlertRuleDetails":
- return alertDefineTools.getAlertRuleDetails(getLongArg(args,
"ruleId"));
- case "toggleAlertRule":
- return alertDefineTools.toggleAlertRule(
- getLongArg(args, "ruleId"),
- getBoolArg(args, "enabled")
- );
- case "getAppsMetricsHierarchy":
- return
alertDefineTools.getAppsMetricsHierarchy(getStringArg(args, "app"));
-
- default:
- throw new IllegalArgumentException("Unknown tool: " +
toolName);
- }
- }
-
private Map<String, Object> resolveArgs(Map<String, Object> args,
Map<String, Object> context) {
if (args == null) {
return new HashMap<>();
@@ -184,75 +87,4 @@ public class ToolExecutor implements SopExecutor {
}
return resolved;
}
-
- // Helper methods for argument extraction
- private String getStringArg(Map<String, Object> args, String key) {
- Object value = args.get(key);
- return value != null ? String.valueOf(value) : null;
- }
-
- private Integer getIntArg(Map<String, Object> args, String key) {
- Object value = args.get(key);
- if (value == null) {
- return null;
- }
- if (value instanceof Number) {
- return ((Number) value).intValue();
- }
- return Integer.valueOf(String.valueOf(value));
- }
-
- private Long getLongArg(Map<String, Object> args, String key) {
- Object value = args.get(key);
- if (value == null) {
- return null;
- }
- if (value instanceof Number) {
- return ((Number) value).longValue();
- }
- return Long.valueOf(String.valueOf(value));
- }
-
- private Byte getByteArg(Map<String, Object> args, String key) {
- Object value = args.get(key);
- if (value == null) {
- return null;
- }
- if (value instanceof Number) {
- return ((Number) value).byteValue();
- }
- return Byte.valueOf(String.valueOf(value));
- }
-
- private Boolean getBoolArg(Map<String, Object> args, String key) {
- Object value = args.get(key);
- if (value == null) {
- return null;
- }
- if (value instanceof Boolean) {
- return (Boolean) value;
- }
- return Boolean.valueOf(String.valueOf(value));
- }
-
- @SuppressWarnings("unchecked")
- private List<Long> getListArg(Map<String, Object> args, String key) {
- Object value = args.get(key);
- if (value == null) {
- return null;
- }
- if (value instanceof List) {
- return (List<Long>) value;
- }
- // Parse comma-separated string
- String str = String.valueOf(value);
- if (str.isEmpty()) {
- return new ArrayList<>();
- }
- List<Long> result = new ArrayList<>();
- for (String s : str.split(",")) {
- result.add(Long.valueOf(s.trim()));
- }
- return result;
- }
}
diff --git
a/hertzbeat-ai/src/main/java/org/apache/hertzbeat/ai/sop/registry/ToolRegistry.java
b/hertzbeat-ai/src/main/java/org/apache/hertzbeat/ai/sop/registry/ToolRegistry.java
new file mode 100644
index 0000000000..77f3a68bfa
--- /dev/null
+++
b/hertzbeat-ai/src/main/java/org/apache/hertzbeat/ai/sop/registry/ToolRegistry.java
@@ -0,0 +1,282 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hertzbeat.ai.sop.registry;
+
+
+import java.lang.reflect.Method;
+import java.lang.reflect.Parameter;
+import java.util.ArrayList;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+import java.util.Set;
+import lombok.extern.slf4j.Slf4j;
+import org.springframework.ai.tool.annotation.Tool;
+import org.springframework.ai.tool.annotation.ToolParam;
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.context.ApplicationContext;
+import org.springframework.stereotype.Component;
+
+/**
+ * Registry for all @Tool annotated methods.
+ * Automatically discovers and registers tool methods on first use.
+ */
+@Slf4j
+@Component
+public class ToolRegistry {
+
+ private final ApplicationContext applicationContext;
+ private final Map<String, ToolMethod> tools = new HashMap<>();
+ private volatile boolean initialized = false;
+
+ @Autowired
+ public ToolRegistry(ApplicationContext applicationContext) {
+ this.applicationContext = applicationContext;
+ }
+
+ /**
+ * Initialize the registry by scanning all beans for @Tool methods.
+ * Uses double-checked locking for thread safety.
+ */
+ private void ensureInitialized() {
+ if (!initialized) {
+ synchronized (this) {
+ if (!initialized) {
+ log.info("Scanning for @Tool annotated methods...");
+ scanAllBeans();
+ initialized = true;
+ log.info("Registered {} tool methods: {}", tools.size(),
tools.keySet());
+ }
+ }
+ }
+ }
+
+ private void scanAllBeans() {
+ String[] beanNames = applicationContext.getBeanDefinitionNames();
+ for (String beanName : beanNames) {
+ try {
+ Object bean = applicationContext.getBean(beanName);
+ scanBeanForTools(bean);
+ } catch (Exception e) {
+ // Skip beans that cannot be instantiated
+ log.trace("Skipping bean {}: {}", beanName, e.getMessage());
+ }
+ }
+ }
+
+ private void scanBeanForTools(Object bean) {
+ Class<?> clazz = bean.getClass();
+
+ // Handle Spring proxies
+ if (clazz.getName().contains("$$")) {
+ clazz = clazz.getSuperclass();
+ }
+
+ for (Method method : clazz.getMethods()) {
+ Tool toolAnnotation = method.getAnnotation(Tool.class);
+ if (toolAnnotation != null) {
+ String toolName = toolAnnotation.name();
+ if (toolName.isEmpty()) {
+ toolName = method.getName();
+ }
+
+ ToolMethod toolMethod = new ToolMethod(bean, method,
toolAnnotation);
+ tools.put(toolName, toolMethod);
+ log.debug("Registered tool: {} -> {}.{}",
+ toolName, clazz.getSimpleName(), method.getName());
+ }
+ }
+ }
+
+ /**
+ * Invoke a tool by name with the given arguments.
+ */
+ public String invoke(String toolName, Map<String, Object> args) {
+ ensureInitialized();
+ ToolMethod toolMethod = tools.get(toolName);
+ if (toolMethod == null) {
+ throw new IllegalArgumentException("Unknown tool: " + toolName
+ + ". Available tools: " + tools.keySet());
+ }
+ return toolMethod.invoke(args);
+ }
+
+ /**
+ * Check if a tool exists.
+ */
+ public boolean hasMethod(String toolName) {
+ ensureInitialized();
+ return tools.containsKey(toolName);
+ }
+
+ /**
+ * Get all registered tool names.
+ */
+ public Set<String> getToolNames() {
+ ensureInitialized();
+ return tools.keySet();
+ }
+
+ /**
+ * Get tool method info.
+ */
+ public ToolMethod getToolMethod(String toolName) {
+ ensureInitialized();
+ return tools.get(toolName);
+ }
+
+ /**
+ * Represents a registered tool method.
+ */
+ public static class ToolMethod {
+ private final Object bean;
+ private final Method method;
+ private final Tool annotation;
+ private final List<ParamInfo> paramInfos;
+
+ public ToolMethod(Object bean, Method method, Tool annotation) {
+ this.bean = bean;
+ this.method = method;
+ this.annotation = annotation;
+ this.paramInfos = extractParamInfos(method);
+ }
+
+ private List<ParamInfo> extractParamInfos(Method method) {
+ List<ParamInfo> infos = new ArrayList<>();
+ Parameter[] parameters = method.getParameters();
+
+ for (Parameter param : parameters) {
+ ToolParam toolParam = param.getAnnotation(ToolParam.class);
+ String name = (toolParam != null &&
!toolParam.description().isEmpty())
+ ? param.getName() : param.getName();
+ boolean required = toolParam != null && toolParam.required();
+
+ infos.add(new ParamInfo(name, param.getType(), required));
+ }
+
+ return infos;
+ }
+
+ /**
+ * Invoke this tool method with the given arguments.
+ */
+ public String invoke(Map<String, Object> args) {
+ try {
+ Object[] methodArgs = new Object[paramInfos.size()];
+
+ for (int i = 0; i < paramInfos.size(); i++) {
+ ParamInfo paramInfo = paramInfos.get(i);
+ Object value = args.get(paramInfo.name);
+ methodArgs[i] = convertValue(value, paramInfo.type);
+ }
+
+ Object result = method.invoke(bean, methodArgs);
+ return result != null ? result.toString() : "";
+
+ } catch (Exception e) {
+ log.error("Failed to invoke tool {}: {}", annotation.name(),
e.getMessage(), e);
+ throw new RuntimeException("Tool invocation failed: " +
annotation.name(), e);
+ }
+ }
+
+ private Object convertValue(Object value, Class<?> targetType) {
+ if (value == null) {
+ return null;
+ }
+
+ if (targetType.isAssignableFrom(value.getClass())) {
+ return value;
+ }
+
+ String strValue = String.valueOf(value);
+
+ if (targetType == String.class) {
+ return strValue;
+ } else if (targetType == Integer.class || targetType == int.class)
{
+ return strValue.isEmpty() ? null : Integer.valueOf(strValue);
+ } else if (targetType == Long.class || targetType == long.class) {
+ return strValue.isEmpty() ? null : Long.valueOf(strValue);
+ } else if (targetType == Boolean.class || targetType ==
boolean.class) {
+ return Boolean.valueOf(strValue);
+ } else if (targetType == Byte.class || targetType == byte.class) {
+ return strValue.isEmpty() ? null : Byte.valueOf(strValue);
+ } else if (targetType == Double.class || targetType ==
double.class) {
+ return strValue.isEmpty() ? null : Double.valueOf(strValue);
+ } else if (targetType == Float.class || targetType == float.class)
{
+ return strValue.isEmpty() ? null : Float.valueOf(strValue);
+ } else if (targetType == List.class) {
+ return parseList(strValue);
+ }
+
+ return value;
+ }
+
+ @SuppressWarnings("unchecked")
+ private List<Long> parseList(String value) {
+ if (value == null || value.isEmpty()) {
+ return new ArrayList<>();
+ }
+ List<Long> result = new ArrayList<>();
+ for (String s : value.split(",")) {
+ if (!s.trim().isEmpty()) {
+ result.add(Long.valueOf(s.trim()));
+ }
+ }
+ return result;
+ }
+
+ public String getName() {
+ return annotation.name();
+ }
+
+ public String getDescription() {
+ return annotation.description();
+ }
+
+ public List<ParamInfo> getParamInfos() {
+ return paramInfos;
+ }
+ }
+
+ /**
+ * Parameter information for a tool method.
+ */
+ public static class ParamInfo {
+ private final String name;
+ private final Class<?> type;
+ private final boolean required;
+
+ public ParamInfo(String name, Class<?> type, boolean required) {
+ this.name = name;
+ this.type = type;
+ this.required = required;
+ }
+
+ public String getName() {
+ return name;
+ }
+
+ public Class<?> getType() {
+ return type;
+ }
+
+ public boolean isRequired() {
+ return required;
+ }
+ }
+}
diff --git
a/hertzbeat-ai/src/main/java/org/apache/hertzbeat/ai/tools/DatabaseTools.java
b/hertzbeat-ai/src/main/java/org/apache/hertzbeat/ai/tools/DatabaseTools.java
new file mode 100644
index 0000000000..272a4f3677
--- /dev/null
+++
b/hertzbeat-ai/src/main/java/org/apache/hertzbeat/ai/tools/DatabaseTools.java
@@ -0,0 +1,50 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hertzbeat.ai.tools;
+
+/**
+ * Database Tools interface for AI-powered database diagnostics.
+ * Provides predefined safe queries for database health checks and
troubleshooting.
+ */
+public interface DatabaseTools {
+
+ /**
+ * Get MySQL slow query statistics from performance_schema.
+ */
+ String getMySqlSlowQueries(Long monitorId, Integer limit);
+
+ /**
+ * Get MySQL current process list.
+ */
+ String getMySqlProcessList(Long monitorId);
+
+ /**
+ * Get MySQL lock wait information.
+ */
+ String getMySqlLockWaits(Long monitorId);
+
+ /**
+ * Get MySQL global status variables.
+ */
+ String getMySqlGlobalStatus(Long monitorId, String pattern);
+
+ /**
+ * Explain a SELECT query for performance analysis.
+ */
+ String explainQuery(Long monitorId, String query);
+}
diff --git
a/hertzbeat-ai/src/main/java/org/apache/hertzbeat/ai/tools/impl/DatabaseToolsImpl.java
b/hertzbeat-ai/src/main/java/org/apache/hertzbeat/ai/tools/impl/DatabaseToolsImpl.java
new file mode 100644
index 0000000000..87a1fd5da9
--- /dev/null
+++
b/hertzbeat-ai/src/main/java/org/apache/hertzbeat/ai/tools/impl/DatabaseToolsImpl.java
@@ -0,0 +1,300 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hertzbeat.ai.tools.impl;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.Statement;
+import java.util.List;
+import java.util.Objects;
+
+import lombok.extern.slf4j.Slf4j;
+import org.apache.hertzbeat.ai.tools.DatabaseTools;
+import org.apache.hertzbeat.common.entity.manager.Monitor;
+import org.apache.hertzbeat.common.entity.manager.Param;
+import org.apache.hertzbeat.common.util.AesUtil;
+import org.apache.hertzbeat.manager.pojo.dto.MonitorDto;
+import org.apache.hertzbeat.manager.service.MonitorService;
+import org.springframework.ai.tool.annotation.Tool;
+import org.springframework.ai.tool.annotation.ToolParam;
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.stereotype.Service;
+
+/**
+ * Implementation of Database Tools for AI-powered database diagnostics.
+ * Provides predefined safe queries - does NOT allow arbitrary SQL execution.
+ */
+@Slf4j
+@Service
+public class DatabaseToolsImpl implements DatabaseTools {
+
+ private static final int QUERY_TIMEOUT_SECONDS = 10;
+ private static final int MAX_ROWS = 100;
+
+ // Allowed database types
+ private static final List<String> SUPPORTED_PLATFORMS = List.of("mysql",
"mariadb");
+
+ private final MonitorService monitorService;
+
+ @Autowired
+ public DatabaseToolsImpl(MonitorService monitorService) {
+ this.monitorService = monitorService;
+ }
+
+ @Override
+ @Tool(name = "getMySqlSlowQueries", description = "Get MySQL slow query
statistics from performance_schema. "
+ + "Returns top N slow queries sorted by average execution time. "
+ + "Requires the monitor to be a MySQL type with proper
credentials.")
+ public String getMySqlSlowQueries(
+ @ToolParam(description = "Monitor ID of the MySQL instance",
required = true) Long monitorId,
+ @ToolParam(description = "Maximum number of slow queries to return
(default: 10, max: 50)", required = false) Integer limit) {
+
+ int queryLimit = (limit == null || limit <= 0) ? 10 : Math.min(limit,
50);
+
+ String sql = "SELECT SCHEMA_NAME as db, "
+ + "DIGEST_TEXT as query, "
+ + "COUNT_STAR as exec_count, "
+ + "ROUND(AVG_TIMER_WAIT/1000000000, 2) as avg_time_ms, "
+ + "ROUND(SUM_TIMER_WAIT/1000000000, 2) as total_time_ms, "
+ + "SUM_ROWS_EXAMINED as rows_examined, "
+ + "SUM_ROWS_SENT as rows_sent "
+ + "FROM performance_schema.events_statements_summary_by_digest
"
+ + "WHERE SCHEMA_NAME IS NOT NULL "
+ + "ORDER BY AVG_TIMER_WAIT DESC "
+ + "LIMIT " + queryLimit;
+
+ return executeQuery(monitorId, sql, "MySQL Slow Query Statistics");
+ }
+
+ @Override
+ @Tool(name = "getMySqlProcessList", description = "Get MySQL current
process list. "
+ + "Shows all active connections and their current state. "
+ + "Useful for identifying blocking queries or connection issues.")
+ public String getMySqlProcessList(
+ @ToolParam(description = "Monitor ID of the MySQL instance",
required = true) Long monitorId) {
+
+ String sql = "SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, "
+ + "LEFT(INFO, 200) as QUERY "
+ + "FROM information_schema.PROCESSLIST "
+ + "WHERE COMMAND != 'Sleep' "
+ + "ORDER BY TIME DESC "
+ + "LIMIT 50";
+
+ return executeQuery(monitorId, sql, "MySQL Process List");
+ }
+
+ @Override
+ @Tool(name = "getMySqlLockWaits", description = "Get MySQL lock wait
information. "
+ + "Shows current lock waits and blocking transactions. "
+ + "Useful for diagnosing deadlocks and lock contention issues.")
+ public String getMySqlLockWaits(
+ @ToolParam(description = "Monitor ID of the MySQL instance",
required = true) Long monitorId) {
+
+ // Use performance_schema for MySQL 8.0+ (innodb_lock_waits was
removed)
+ String sql = "SELECT "
+ + "r.ENGINE_TRANSACTION_ID as waiting_trx_id, "
+ + "r.THREAD_ID as waiting_thread, "
+ + "r.OBJECT_SCHEMA as waiting_schema, "
+ + "r.OBJECT_NAME as waiting_table, "
+ + "r.LOCK_TYPE as waiting_lock_type, "
+ + "r.LOCK_MODE as waiting_lock_mode, "
+ + "b.ENGINE_TRANSACTION_ID as blocking_trx_id, "
+ + "b.THREAD_ID as blocking_thread "
+ + "FROM performance_schema.data_lock_waits w "
+ + "JOIN performance_schema.data_locks b ON b.ENGINE_LOCK_ID =
w.BLOCKING_ENGINE_LOCK_ID "
+ + "JOIN performance_schema.data_locks r ON r.ENGINE_LOCK_ID =
w.REQUESTING_ENGINE_LOCK_ID "
+ + "LIMIT 20";
+
+ return executeQuery(monitorId, sql, "MySQL Lock Waits");
+ }
+
+ @Override
+ @Tool(name = "getMySqlGlobalStatus", description = "Get MySQL global
status variables. "
+ + "Returns server status matching the given pattern. "
+ + "Examples: 'Slow%' for slow query stats, 'Threads%' for thread
stats.")
+ public String getMySqlGlobalStatus(
+ @ToolParam(description = "Monitor ID of the MySQL instance",
required = true) Long monitorId,
+ @ToolParam(description = "Pattern to filter status variables
(e.g., 'Slow%', 'Threads%')", required = false) String pattern) {
+
+ String filterPattern = (pattern == null || pattern.isEmpty()) ? "%" :
pattern;
+ // Sanitize pattern to prevent SQL injection
+ filterPattern = filterPattern.replaceAll("[^a-zA-Z0-9_%]", "");
+
+ String sql = "SHOW GLOBAL STATUS LIKE '" + filterPattern + "'";
+
+ return executeQuery(monitorId, sql, "MySQL Global Status");
+ }
+
+ @Override
+ @Tool(name = "explainQuery", description = "Explain a SELECT query for
performance analysis. "
+ + "ONLY SELECT queries are allowed for security reasons. "
+ + "Returns the execution plan to help optimize the query.")
+ public String explainQuery(
+ @ToolParam(description = "Monitor ID of the MySQL instance",
required = true) Long monitorId,
+ @ToolParam(description = "SELECT query to explain (must start with
SELECT)", required = true) String query) {
+
+ if (query == null || query.trim().isEmpty()) {
+ return "Error: Query cannot be empty";
+ }
+
+ String trimmedQuery = query.trim();
+
+ // Security check: only allow SELECT statements
+ if (!trimmedQuery.toUpperCase().startsWith("SELECT")) {
+ return "Error: Only SELECT queries are allowed for EXPLAIN. "
+ + "The query must start with 'SELECT'.";
+ }
+
+ // Additional security checks
+ String lowerQuery = trimmedQuery.toLowerCase();
+ if (lowerQuery.contains(";") || lowerQuery.contains("--")
+ || lowerQuery.contains("/*") || lowerQuery.contains("*/")) {
+ return "Error: Query contains invalid characters. "
+ + "Semicolons and comments are not allowed.";
+ }
+
+ String sql = "EXPLAIN " + trimmedQuery;
+
+ return executeQuery(monitorId, sql, "Query Execution Plan");
+ }
+
+ /**
+ * Execute a predefined safe query against the database.
+ */
+ private String executeQuery(Long monitorId, String sql, String title) {
+ log.info("Executing database query for monitor {}: {}", monitorId,
title);
+
+ try {
+ // Get monitor DTO which includes both monitor and params
+ MonitorDto monitorDto = monitorService.getMonitorDto(monitorId);
+ if (monitorDto == null || monitorDto.getMonitor() == null) {
+ return "Error: Monitor not found with ID: " + monitorId;
+ }
+
+ Monitor monitor = monitorDto.getMonitor();
+
+ if (!SUPPORTED_PLATFORMS.contains(monitor.getApp().toLowerCase()))
{
+ return "Error: This tool only supports MySQL/MariaDB. "
+ + "Current monitor type: " + monitor.getApp();
+ }
+
+ // Get connection parameters from MonitorDto
+ List<Param> params = monitorDto.getParams();
+ if (params == null || params.isEmpty()) {
+ return "Error: Monitor has no connection parameters
configured.";
+ }
+
+ String host = getParamValue(params, "host");
+ String port = getParamValue(params, "port");
+ String database = getParamValue(params, "database");
+ String username = getParamValue(params, "username");
+ String password = getParamValue(params, "password");
+
+ // Decrypt password if it's encrypted
+ if (password != null && AesUtil.isCiphertext(password)) {
+ password = AesUtil.aesDecode(password);
+ }
+
+ if (host == null || username == null) {
+ return "Error: Monitor connection parameters incomplete. "
+ + "Ensure host and username are configured.";
+ }
+
+ // Build connection URL
+ String url = buildJdbcUrl(monitor.getApp(), host, port, database);
+
+ // Execute query
+ return executeAndFormat(url, username, password, sql, title);
+
+ } catch (Exception e) {
+ log.error("Error executing database query: {}", e.getMessage(), e);
+ return "Error executing query: " + e.getMessage();
+ }
+ }
+
+ private String getParamValue(List<Param> params, String field) {
+ return params.stream()
+ .filter(p -> field.equals(p.getField()))
+ .map(Param::getParamValue)
+ .filter(Objects::nonNull)
+ .findFirst()
+ .orElse(null);
+ }
+
+ private String buildJdbcUrl(String platform, String host, String port,
String database) {
+ String effectivePort = (port == null || port.isEmpty()) ? "3306" :
port;
+ String effectiveDb = (database == null || database.isEmpty()) ? "" :
database;
+
+ return "jdbc:mysql://" + host + ":" + effectivePort + "/" + effectiveDb
+ + "?useUnicode=true&characterEncoding=utf-8&useSSL=false"
+ + "&allowPublicKeyRetrieval=true&connectTimeout=5000";
+ }
+
+ private String executeAndFormat(String url, String username, String
password,
+ String sql, String title) throws Exception {
+
+ StringBuilder result = new StringBuilder();
+ result.append("=== ").append(title).append(" ===\n\n");
+
+ try (Connection conn = DriverManager.getConnection(url, username,
password);
+ Statement stmt = conn.createStatement()) {
+
+ stmt.setQueryTimeout(QUERY_TIMEOUT_SECONDS);
+ stmt.setMaxRows(MAX_ROWS);
+
+ try (ResultSet rs = stmt.executeQuery(sql)) {
+ ResultSetMetaData meta = rs.getMetaData();
+ int columnCount = meta.getColumnCount();
+
+ // Build header
+ StringBuilder header = new StringBuilder();
+ StringBuilder separator = new StringBuilder();
+ for (int i = 1; i <= columnCount; i++) {
+ String columnName = meta.getColumnLabel(i);
+ header.append(String.format("%-20s", columnName));
+ separator.append("-".repeat(20));
+ }
+ result.append(header).append("\n");
+ result.append(separator).append("\n");
+
+ // Build rows
+ int rowCount = 0;
+ while (rs.next()) {
+ StringBuilder row = new StringBuilder();
+ for (int i = 1; i <= columnCount; i++) {
+ String value = rs.getString(i);
+ if (value == null) {
+ value = "NULL";
+ } else if (value.length() > 50) {
+ value = value.substring(0, 47) + "...";
+ }
+ row.append(String.format("%-20s", value));
+ }
+ result.append(row).append("\n");
+ rowCount++;
+ }
+
+ result.append("\n").append("Total:
").append(rowCount).append(" rows");
+ }
+ }
+
+ return result.toString();
+ }
+}
diff --git a/hertzbeat-ai/src/main/resources/skills/README.md
b/hertzbeat-ai/src/main/resources/skills/README.md
index a699073dd9..46616c7323 100644
--- a/hertzbeat-ai/src/main/resources/skills/README.md
+++ b/hertzbeat-ai/src/main/resources/skills/README.md
@@ -65,6 +65,21 @@ POST /api/ai/sop/execute/{skillName}/ai
```
YAML Definition → SkillRegistry → SopEngine → Executors → SopResult
- ↓
- ToolExecutor / LlmExecutor
+ ↓
+ ToolExecutor / LlmExecutor
+ ↓
+ ToolRegistry (auto-discover @Tool methods)
```
+
+## Adding New Tools
+
+Just add `@Tool` annotation to your method - no other code changes needed:
+
+```java
+@Tool(name = "myNewTool", description = "...")
+public String myNewTool(@ToolParam(...) Long param) {
+ // implementation
+}
+```
+
+ToolRegistry auto-discovers all `@Tool` methods at runtime.
diff --git a/hertzbeat-ai/src/main/resources/skills/README_ZH.md
b/hertzbeat-ai/src/main/resources/skills/README_ZH.md
index aa5116e2ea..dfad433185 100644
--- a/hertzbeat-ai/src/main/resources/skills/README_ZH.md
+++ b/hertzbeat-ai/src/main/resources/skills/README_ZH.md
@@ -67,8 +67,23 @@ POST /api/ai/sop/execute/{skillName}/ai
YAML 定义 → SkillRegistry → SopEngine → Executors → SopResult
↓
ToolExecutor / LlmExecutor
+ ↓
+ ToolRegistry（自动发现 @Tool 方法）
+```
+
+## 添加新工具
+
+只需添加 `@Tool` 注解，无需修改其他代码：
+
+```java
+@Tool(name = "myNewTool", description = "...")
+public String myNewTool(@ToolParam(...) Long param) {
+ // 实现逻辑
+}
```
+ToolRegistry 会在运行时自动发现所有 `@Tool` 方法。
+
## 配置说明
### output 配置
diff --git
a/hertzbeat-ai/src/main/resources/skills/mysql_slow_query_diagnosis.yml
b/hertzbeat-ai/src/main/resources/skills/mysql_slow_query_diagnosis.yml
new file mode 100644
index 0000000000..90c9c6468b
--- /dev/null
+++ b/hertzbeat-ai/src/main/resources/skills/mysql_slow_query_diagnosis.yml
@@ -0,0 +1,95 @@
+name: mysql_slow_query_diagnosis
+description: "Diagnose MySQL slow queries and provide optimization
recommendations"
+version: "1.0"
+
+# Output configuration
+output:
+ type: report
+ format: markdown
+ language: zh
+ contentStep: generate_diagnosis
+
+parameters:
+ - name: monitorId
+ type: long
+ description: "MySQL monitor ID to diagnose"
+ required: true
+ - name: slowQueryLimit
+ type: integer
+ description: "Number of slow queries to analyze (default: 10)"
+ required: false
+ defaultValue: "10"
+
+steps:
+ # Step 1: Get slow query statistics
+ - id: get_slow_queries
+ type: tool
+ tool: getMySqlSlowQueries
+ args:
+ monitorId: "${monitorId}"
+ limit: "${slowQueryLimit}"
+
+ # Step 2: Get current process list
+ - id: get_process_list
+ type: tool
+ tool: getMySqlProcessList
+ args:
+ monitorId: "${monitorId}"
+
+ # Step 3: Get lock wait information
+ - id: get_lock_waits
+ type: tool
+ tool: getMySqlLockWaits
+ args:
+ monitorId: "${monitorId}"
+
+ # Step 4: Get global status for slow query metrics
+ - id: get_slow_status
+ type: tool
+ tool: getMySqlGlobalStatus
+ args:
+ monitorId: "${monitorId}"
+ pattern: "Slow%"
+
+ # Step 5: LLM analyze and generate diagnosis report
+ - id: generate_diagnosis
+ type: llm
+ prompt: |
+ You are a senior MySQL DBA expert. Based on the following diagnostic
data, generate a comprehensive slow query diagnosis report.
+
+ ## Slow Query Statistics (Top N by average execution time)
+ ${get_slow_queries}
+
+ ## Current Process List (Active queries)
+ ${get_process_list}
+
+ ## Lock Wait Information
+ ${get_lock_waits}
+
+ ## Server Slow Query Status
+ ${get_slow_status}
+
+ Please provide a diagnosis report with the following sections:
+
+ 1. **Problem Summary**
+ - Overall slow query situation
+ - Most critical issues found
+
+ 2. **Slow Query Analysis**
+ - Top slow queries and their characteristics
+ - Common patterns (full table scans, missing indexes, etc.)
+
+ 3. **Lock & Concurrency Issues**
+ - Any lock contention found
+ - Long-running transactions
+
+ 4. **Optimization Recommendations**
+ - Index recommendations
+ - Query optimization suggestions
+ - Configuration tuning tips
+
+ 5. **Urgent Actions**
+ - Queries that need immediate attention
+ - Recommended immediate steps
+
+ Format the report in markdown. Be specific with SQL examples where
applicable.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
