> ## Documentation Index
> Fetch the complete documentation index at: https://test-8ad8522e-feat-ai-sre.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# MySQL

> Configure alert rules for MySQL data sources with standard SQL syntax support

Monitors supports using standard SQL syntax to query MySQL and trigger alerts based on query results.

## Core Concepts

| Config Item          | Description                                                                                             |
| -------------------- | ------------------------------------------------------------------------------------------------------- |
| **Query Language**   | Uses standard MySQL SQL syntax                                                                          |
| **Field Processing** | All field names are automatically converted to lowercase; please use lowercase letters when configuring |
| **Time Processing**  | Recommended to use `now()`, `unix_timestamp()` and other functions for time filtering                   |

## 1. Threshold Evaluation Mode

This mode is suitable for scenarios requiring threshold comparison on aggregated values.

### Configuration

1. **Query Statement**: Write SQL aggregate query, returning value columns and (optional) label columns.

* Example: Count error log quantity by service in the last 5 minutes (assuming there's a log table).
  ```sql theme={null}
  SELECT 
      service_name, 
      count(*) AS error_cnt 
  FROM app_log 
  WHERE log_time > now() - INTERVAL 5 MINUTE AND level = 'error'
  GROUP BY service_name
  ```

2. **Field Mapping**:

* **Value fields**: Select `error_cnt` for threshold evaluation.
* **Label fields**: Select `service_name` to identify the alert object. After you select label fields, other non-value fields are carried with the alert as additional information.
* See [Query Result Field Mapping](/en/monitors/alert-rules/query-result-fields) for the complete behavior.

3. **Threshold Conditions**:

* Use `$A.field_name` to reference values.
* Example: `Critical: $A.error_cnt > 50`, `Warning: $A.error_cnt > 10`.

### How It Works

Monitors distinguishes alert objects by their label fields and evaluates thresholds with their value fields. If Label fields is empty, every returned field except the value fields becomes a label.

### Recovery Logic

| Strategy                        | Description                                                                               |
| ------------------------------- | ----------------------------------------------------------------------------------------- |
| **Auto Recovery**               | When values no longer satisfy any alert threshold, automatically generates recovery event |
| **Specific Recovery Condition** | Configure recovery expression (e.g., `$A.error_cnt < 5`)                                  |
| **Recovery Query**              | Independent SQL for recovery evaluation, supports `${label_name}` variables               |
| **Manual Close**                | Keep the alert active until it is closed manually                                         |

## 2. Data Exists Mode

This mode is suitable for scenarios where filter logic is written directly in SQL.

### Configuration

1. **Query Statement**: Use `HAVING` clause in SQL to directly filter out anomalous data.

* Example: Directly query services with error count exceeding 50.
  ```sql theme={null}
  SELECT 
      service_name, 
      count(*) AS error_cnt 
  FROM app_log 
  WHERE log_time > now() - INTERVAL 5 MINUTE AND level = 'error'
  GROUP BY service_name
  HAVING count(*) > 50
  ```

2. **Evaluation Rules**: As long as SQL query returns data (Result Set is not empty), triggers alert.

### Pros and Cons Analysis

| Type     | Description                                                                             |
| -------- | --------------------------------------------------------------------------------------- |
| **Pros** | Leverages MySQL database's computing power for filtering, reducing network transmission |
| **Cons** | Cannot differentiate multi-level alerts                                                 |

### Recovery Logic

* **Recovery When Data Disappears**: When SQL query result is empty, determines recovery
* **Recovery Query**: Supports configuring additional query statements to assist in determining recovery status
* **Manual Close**: Keep the alert active until it is closed manually

## 3. No Data Mode

This mode is used to monitor scenarios where "data is expected but actually missing".

### Configuration

1. **Query Statement**: Write a SQL query that is expected to continuously return data.

* Example: Query heartbeat reports from all probes.
  ```sql theme={null}
  SELECT probe_id, max(check_time) as last_seen
  FROM probe_heartbeat
  WHERE check_time > now() - INTERVAL 5 MINUTE
  GROUP BY probe_id
  ```

2. **Evaluation Rules**: If a `probe_id` appeared in previous cycles but cannot be found in current and N consecutive cycles, triggers "No Data" alert.

### Recovery Logic

No-data alerts support configuring the **alert ending mode**, which decides how the alert ends:

| Ending Mode                                        | Description                                                                                                                                                                                                              |
| -------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| **End automatically when data reappears**          | Default. The alert ends automatically once the data reappears                                                                                                                                                            |
| **End when data reappears or the timeout expires** | The alert ends when the data reappears or the automatic close timeout is reached. The timeout is in seconds with a minimum of 1. Available only when the "Alert if previously found data is now missing" mode is enabled |
| **Manual close only**                              | The alert stays active until you close it manually                                                                                                                                                                       |

## 4. Best Practices

<AccordionGroup>
  <Accordion title="Index Optimization">
    Always include time range filtering in `WHERE` clause and ensure the time field has an index, otherwise it may cause full table scan.

    Recommended syntax: `log_time > now() - INTERVAL 5 MINUTE`
  </Accordion>

  <Accordion title="Field Case">
    Monitors converts column names returned by MySQL to lowercase. Use lowercase names for both label fields and value fields.
  </Accordion>
</AccordionGroup>