This is an automated email from the ASF dual-hosted git repository.
zhaoqingran pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/hertzbeat.git
The following commit(s) were added to refs/heads/master by this push:
new 6c0d5c0db4 [fix]Fix issues such as index failure (#3953)
6c0d5c0db4 is described below
commit 6c0d5c0db415b44df9ec967ad3b70b5686756345
Author: Duansg <[email protected]>
AuthorDate: Sun Jan 18 23:07:42 2026 +0800
[fix]Fix issues such as index failure (#3953)
Co-authored-by: shown <[email protected]>
Co-authored-by: Yang Chen <[email protected]>
Co-authored-by: Logic <[email protected]>
---
.../entity/alerter/AlertDefineMonitorBind.java | 4 +-
.../entity/manager/CollectorMonitorBind.java | 4 +-
.../hertzbeat/common/entity/manager/Monitor.java | 40 +-
.../common/entity/manager/MonitorBind.java | 4 +-
.../hertzbeat/common/entity/manager/Param.java | 9 +-
.../manager/StatusPageIncidentComponentBind.java | 5 +-
.../hertzbeat/common/entity/push/PushMetrics.java | 4 +-
.../hertzbeat/common/entity/warehouse/History.java | 10 +-
.../hertzbeat/manager/pojo/dto/PluginParam.java | 11 +-
.../db/migration/h2/V181__update_indexes.sql | 534 +++++++++++++++++++++
.../db/migration/mysql/V181__update_indexes.sql | 450 +++++++++++++++++
.../migration/postgresql/V181__update_indexes.sql | 247 ++++++++++
.../startup/EntityIndexIntegrationTest.java | 185 +++++++
13 files changed, 1466 insertions(+), 41 deletions(-)
diff --git
a/hertzbeat-common/src/main/java/org/apache/hertzbeat/common/entity/alerter/AlertDefineMonitorBind.java
b/hertzbeat-common/src/main/java/org/apache/hertzbeat/common/entity/alerter/AlertDefineMonitorBind.java
index 6ba5d6ee95..f40241c393 100644
---
a/hertzbeat-common/src/main/java/org/apache/hertzbeat/common/entity/alerter/AlertDefineMonitorBind.java
+++
b/hertzbeat-common/src/main/java/org/apache/hertzbeat/common/entity/alerter/AlertDefineMonitorBind.java
@@ -48,8 +48,8 @@ import
org.springframework.data.jpa.domain.support.AuditingEntityListener;
*/
@Entity
@Table(name = "hzb_alert_define_monitor_bind", indexes = {
- @Index(name = "index_alert_define_monitor", columnList =
"alertDefineId"),
- @Index(name = "index_alert_define_monitor", columnList = "monitor_id")
+ @Index(name = "idx_alert_define_id", columnList = "alert_define_id"),
+ @Index(name = "idx_monitor_id", columnList = "monitor_id")
})
@Data
@Builder
diff --git
a/hertzbeat-common/src/main/java/org/apache/hertzbeat/common/entity/manager/CollectorMonitorBind.java
b/hertzbeat-common/src/main/java/org/apache/hertzbeat/common/entity/manager/CollectorMonitorBind.java
index f961adb5d6..bad92c4a80 100644
---
a/hertzbeat-common/src/main/java/org/apache/hertzbeat/common/entity/manager/CollectorMonitorBind.java
+++
b/hertzbeat-common/src/main/java/org/apache/hertzbeat/common/entity/manager/CollectorMonitorBind.java
@@ -42,8 +42,8 @@ import
org.springframework.data.jpa.domain.support.AuditingEntityListener;
*/
@Entity
@Table(name = "hzb_collector_monitor_bind", indexes = {
- @Index(name = "index_collector_monitor", columnList = "collector"),
- @Index(name = "index_collector_monitor", columnList = "monitor_id")
+ @Index(name = "idx_collector_monitor_collector", columnList =
"collector"),
+ @Index(name = "idx_collector_monitor_monitor_id", columnList =
"monitor_id")
})
@Data
@Builder
diff --git
a/hertzbeat-common/src/main/java/org/apache/hertzbeat/common/entity/manager/Monitor.java
b/hertzbeat-common/src/main/java/org/apache/hertzbeat/common/entity/manager/Monitor.java
index 585a7018cf..95ff7a05bd 100644
---
a/hertzbeat-common/src/main/java/org/apache/hertzbeat/common/entity/manager/Monitor.java
+++
b/hertzbeat-common/src/main/java/org/apache/hertzbeat/common/entity/manager/Monitor.java
@@ -51,9 +51,9 @@ import
org.springframework.data.jpa.domain.support.AuditingEntityListener;
*/
@Entity
@Table(name = "hzb_monitor", indexes = {
- @Index(name = "monitor_query_index", columnList = "app"),
- @Index(name = "monitor_query_index", columnList = "instance"),
- @Index(name = "monitor_query_index", columnList = "name")
+ @Index(name = "idx_hzb_monitor_app", columnList = "app"),
+ @Index(name = "idx_hzb_monitor_instance", columnList = "instance"),
+ @Index(name = "idx_hzb_monitor_name", columnList = "name")
})
@Data
@Builder
@@ -62,51 +62,51 @@ import
org.springframework.data.jpa.domain.support.AuditingEntityListener;
@Schema(description = "Monitor Entity")
@EntityListeners(AuditingEntityListener.class)
public class Monitor {
-
+
@Id
@Schema(title = "Monitor task ID", example = "87584674384", accessMode =
READ_ONLY)
private Long id;
-
+
@Schema(title = "Collect task ID", example = "43243543543", accessMode =
READ_ONLY)
private Long jobId;
-
+
@Schema(title = "task name", example = "Api-TanCloud.cn", accessMode =
READ_WRITE)
@Size(max = 100)
private String name;
-
+
@Schema(title = "Type of monitoring", example = "TanCloud", accessMode =
READ_WRITE)
@Size(max = 100)
private String app;
-
+
@Schema(title = "Scrape type: static | http_sd | dns_sd | zookeeper_sd",
example = "static", accessMode = READ_WRITE)
@Size(max = 100)
private String scrape;
-
+
@Schema(title = "the monitor target: ip/domain+port or ip/domain", example
= "192.167.25.11:8081", accessMode = READ_WRITE)
@Size(max = 100)
@HostValid
private String instance;
-
+
@Schema(title = "Monitoring of the acquisition interval time in seconds",
example = "600", accessMode = READ_WRITE)
@Min(10)
private Integer intervals;
-
+
@Schema(title = "Schedule type: interval | cron", example = "interval",
accessMode = READ_WRITE)
@Size(max = 20)
private String scheduleType;
-
+
@Schema(title = "Cron expression when scheduleType is cron", example =
"0/5 * * * * ?", accessMode = READ_WRITE)
@Size(max = 100)
private String cronExpression;
-
+
@Schema(title = "Task status 0: Paused, 1: Up, 2: Down", accessMode =
READ_WRITE)
@Min(0)
@Max(4)
private byte status;
-
+
@Schema(title = "Task type 0: Normal, 1: push auto create, 2: discovery
auto create")
private byte type;
-
+
@Schema(title = "task label", example = "{env:test}", accessMode =
READ_WRITE)
@Convert(converter = JsonMapAttributeConverter.class)
@Column(length = 4096)
@@ -116,23 +116,23 @@ public class Monitor {
@Convert(converter = JsonMapAttributeConverter.class)
@Column(length = 4096)
private Map<String, String> annotations;
-
+
@Schema(title = "Monitor note description", example = "Availability
monitoring of the SAAS website TanCloud", accessMode = READ_WRITE)
@Size(max = 255)
private String description;
-
+
@Schema(title = "The creator of this record", example = "tom", accessMode
= READ_ONLY)
@CreatedBy
private String creator;
-
+
@Schema(title = "The modifier of this record", example = "tom", accessMode
= READ_ONLY)
@LastModifiedBy
private String modifier;
-
+
@Schema(title = "Record create time", example =
"2024-07-02T20:09:34.903217", accessMode = READ_ONLY)
@CreatedDate
private LocalDateTime gmtCreate;
-
+
@Schema(title = "Record modify time", example =
"2024-07-02T20:09:34.903217", accessMode = READ_ONLY)
@LastModifiedDate
private LocalDateTime gmtUpdate;
diff --git
a/hertzbeat-common/src/main/java/org/apache/hertzbeat/common/entity/manager/MonitorBind.java
b/hertzbeat-common/src/main/java/org/apache/hertzbeat/common/entity/manager/MonitorBind.java
index 9161447696..885c99b567 100644
---
a/hertzbeat-common/src/main/java/org/apache/hertzbeat/common/entity/manager/MonitorBind.java
+++
b/hertzbeat-common/src/main/java/org/apache/hertzbeat/common/entity/manager/MonitorBind.java
@@ -42,7 +42,7 @@ import
org.springframework.data.jpa.domain.support.AuditingEntityListener;
*/
@Entity
@Table(name = "hzb_monitor_bind", indexes = {
- @Index(name = "index_monitor_bind", columnList = "bizId"),
+ @Index(name = "index_monitor_bind", columnList = "biz_id"),
@Index(name = "index_monitor_bin", columnList = "monitor_id")
})
@Data
@@ -59,7 +59,7 @@ public class MonitorBind {
@Schema(title = "key string: ip:port")
private String keyStr;
-
+
@Schema(title = "connect bind id", example = "87432674384")
private Long bizId;
diff --git
a/hertzbeat-common/src/main/java/org/apache/hertzbeat/common/entity/manager/Param.java
b/hertzbeat-common/src/main/java/org/apache/hertzbeat/common/entity/manager/Param.java
index 220092ad63..7829ca831c 100644
---
a/hertzbeat-common/src/main/java/org/apache/hertzbeat/common/entity/manager/Param.java
+++
b/hertzbeat-common/src/main/java/org/apache/hertzbeat/common/entity/manager/Param.java
@@ -46,8 +46,11 @@ import
org.springframework.data.jpa.domain.support.AuditingEntityListener;
* Monitor parameter values
*/
@Entity
-@Table(name = "hzb_param", indexes = { @Index(columnList = "monitorId") },
- uniqueConstraints = @UniqueConstraint(columnNames = {"monitorId",
"field"}))
+@Table(
+ name = "hzb_param",
+ indexes = {@Index(name = "idx_hzb_param_monitor_id", columnList =
"monitor_id")},
+ uniqueConstraints = {@UniqueConstraint(name =
"uk_hzb_param_monitor_field", columnNames = {"monitor_id", "field"})}
+)
@Data
@Builder
@AllArgsConstructor
@@ -68,6 +71,7 @@ public class Param {
* Monitor ID
*/
@Schema(title = "Monitor task ID", example = "875846754543", accessMode =
READ_WRITE)
+ @Column(name = "monitor_id")
private Long monitorId;
/**
@@ -75,6 +79,7 @@ public class Param {
*/
@Schema(title = "Parameter identifier field", example = "port", accessMode
= READ_WRITE)
@Size(max = 100)
+ @Column(name = "field")
@NotBlank(message = "field can not null")
private String field;
diff --git
a/hertzbeat-common/src/main/java/org/apache/hertzbeat/common/entity/manager/StatusPageIncidentComponentBind.java
b/hertzbeat-common/src/main/java/org/apache/hertzbeat/common/entity/manager/StatusPageIncidentComponentBind.java
index f1f18074a7..a7941df0b5 100644
---
a/hertzbeat-common/src/main/java/org/apache/hertzbeat/common/entity/manager/StatusPageIncidentComponentBind.java
+++
b/hertzbeat-common/src/main/java/org/apache/hertzbeat/common/entity/manager/StatusPageIncidentComponentBind.java
@@ -41,7 +41,6 @@ import
org.springframework.data.jpa.domain.support.AuditingEntityListener;
@Entity
@Table(name = "hzb_status_page_incident_component_bind", indexes = {
@Index(name = "index_incident_component", columnList = "incident_id"),
- @Index(name = "index_incident_component", columnList = "component_id")
})
@Data
@Builder
@@ -59,7 +58,7 @@ public class StatusPageIncidentComponentBind {
@Schema(title = "Incident ID", example = "87432674384")
@Column(name = "incident_id")
private Long incidentId;
-
+
@Schema(title = "Component ID", example = "87432674336")
@Column(name = "component_id")
private Long componentId;
@@ -71,5 +70,5 @@ public class StatusPageIncidentComponentBind {
@Schema(title = "Record modify time", example = "1612198444000")
@LastModifiedDate
private LocalDateTime gmtUpdate;
-
+
}
diff --git
a/hertzbeat-common/src/main/java/org/apache/hertzbeat/common/entity/push/PushMetrics.java
b/hertzbeat-common/src/main/java/org/apache/hertzbeat/common/entity/push/PushMetrics.java
index 1f72f83dc8..5c58cfb0ba 100644
---
a/hertzbeat-common/src/main/java/org/apache/hertzbeat/common/entity/push/PushMetrics.java
+++
b/hertzbeat-common/src/main/java/org/apache/hertzbeat/common/entity/push/PushMetrics.java
@@ -35,8 +35,8 @@ import
org.springframework.data.jpa.domain.support.AuditingEntityListener;
*/
@Entity
@Table(name = "hzb_push_metrics", indexes = {
- @Index(name = "push_query_index", columnList = "monitorId"),
- @Index(name = "push_query_index", columnList = "time")
+ @Index(name = "idx_push_metrics_monitor_id", columnList =
"monitor_id"),
+ @Index(name = "idx_push_metrics_time", columnList = "time")
})
@Data
@Builder
diff --git
a/hertzbeat-common/src/main/java/org/apache/hertzbeat/common/entity/warehouse/History.java
b/hertzbeat-common/src/main/java/org/apache/hertzbeat/common/entity/warehouse/History.java
index 24729b16e8..6e29d33cbd 100644
---
a/hertzbeat-common/src/main/java/org/apache/hertzbeat/common/entity/warehouse/History.java
+++
b/hertzbeat-common/src/main/java/org/apache/hertzbeat/common/entity/warehouse/History.java
@@ -37,10 +37,10 @@ import lombok.NoArgsConstructor;
*/
@Entity
@Table(name = "hzb_history", indexes = {
- @Index(name = "history_query_index", columnList = "instance"),
- @Index(name = "history_query_index", columnList = "app"),
- @Index(name = "history_query_index", columnList = "metrics"),
- @Index(name = "history_query_index", columnList = "metric")
+ @Index(name = "idx_hzb_history_instance", columnList = "instance"),
+ @Index(name = "idx_hzb_history_app", columnList = "app"),
+ @Index(name = "idx_hzb_history_metrics", columnList = "metrics"),
+ @Index(name = "idx_hzb_history_metric", columnList = "metric")
})
@Data
@Builder
@@ -65,7 +65,7 @@ public class History {
@Schema(title = "Monitoring Metric usage speed count")
private String metric;
-
+
@Column(length = 5000)
private String metricLabels;
diff --git
a/hertzbeat-manager/src/main/java/org/apache/hertzbeat/manager/pojo/dto/PluginParam.java
b/hertzbeat-manager/src/main/java/org/apache/hertzbeat/manager/pojo/dto/PluginParam.java
index b92948f2b1..faae0fefa6 100644
---
a/hertzbeat-manager/src/main/java/org/apache/hertzbeat/manager/pojo/dto/PluginParam.java
+++
b/hertzbeat-manager/src/main/java/org/apache/hertzbeat/manager/pojo/dto/PluginParam.java
@@ -45,8 +45,11 @@ import
org.springframework.data.jpa.domain.support.AuditingEntityListener;
* PluginParam
*/
@Entity
-@Table(name = "hzb_plugin_param", indexes = { @Index(columnList =
"pluginMetadataId") },
- uniqueConstraints = @UniqueConstraint(columnNames =
{"pluginMetadataId", "field"}))
+@Table(
+ name = "hzb_plugin_param",
+ indexes = {@Index(name = "idx_hzb_plugin_param_plugin_metadata_id",
columnList = "plugin_metadata_id")},
+ uniqueConstraints = @UniqueConstraint(name =
"uk_hzb_plugin_param_metadata_field", columnNames = {"plugin_metadata_id",
"field"})
+)
@Data
@Builder
@AllArgsConstructor
@@ -65,8 +68,9 @@ public class PluginParam {
/**
* Monitor ID
*/
- @Schema(title = "Plugin task ID", example = "875846754543", accessMode =
READ_WRITE)
@NotNull
+ @Column(name = "plugin_metadata_id")
+ @Schema(title = "Plugin task ID", example = "875846754543", accessMode =
READ_WRITE)
private Long pluginMetadataId;
/**
@@ -75,6 +79,7 @@ public class PluginParam {
@Schema(title = "Parameter identifier field", example = "port", accessMode
= READ_WRITE)
@Size(max = 100)
@NotNull
+ @Column(name = "field")
private String field;
/**
diff --git
a/hertzbeat-startup/src/main/resources/db/migration/h2/V181__update_indexes.sql
b/hertzbeat-startup/src/main/resources/db/migration/h2/V181__update_indexes.sql
new file mode 100644
index 0000000000..dcf84b2794
--- /dev/null
+++
b/hertzbeat-startup/src/main/resources/db/migration/h2/V181__update_indexes.sql
@@ -0,0 +1,534 @@
+-- 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.
+
+-- Ensure every SQL can rerun without error
+-- This migration updates indexes to match the entity annotations
+
+-- ========================================
+-- hzb_alert_define_monitor_bind table
+-- ========================================
+CREATE ALIAS UPDATE_ALERT_DEFINE_MONITOR_BIND_INDEXES AS $$
+void updateAlertDefineMonitorBindIndexes(java.sql.Connection conn) throws
java.sql.SQLException {
+ boolean tableExists = false;
+ try (java.sql.ResultSet rs = conn.getMetaData().getTables(null, null,
"HZB_ALERT_DEFINE_MONITOR_BIND", null)) {
+ if (rs.next()) tableExists = true;
+ }
+
+ if (tableExists) {
+ try (java.sql.Statement stmt = conn.createStatement()) {
+ // Drop old index if exists
+ try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_ALERT_DEFINE_MONITOR_BIND", false, false)) {
+ while (rs.next()) {
+ String indexName = rs.getString("INDEX_NAME");
+ if
("INDEX_ALERT_DEFINE_MONITOR".equalsIgnoreCase(indexName)) {
+ stmt.execute("DROP INDEX INDEX_ALERT_DEFINE_MONITOR");
+ break;
+ }
+ }
+ } catch (Exception e) {
+ // Index may not exist, continue
+ }
+
+ // Create new indexes if not exist
+ boolean alertDefineIdIndexExists = false;
+ boolean monitorIdIndexExists = false;
+
+ try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_ALERT_DEFINE_MONITOR_BIND", false, false)) {
+ while (rs.next()) {
+ String indexName = rs.getString("INDEX_NAME");
+ if ("IDX_ALERT_DEFINE_ID".equalsIgnoreCase(indexName)) {
+ alertDefineIdIndexExists = true;
+ }
+ if ("IDX_MONITOR_ID".equalsIgnoreCase(indexName)) {
+ monitorIdIndexExists = true;
+ }
+ }
+ }
+
+ if (!alertDefineIdIndexExists) {
+ stmt.execute("CREATE INDEX IDX_ALERT_DEFINE_ID ON
HZB_ALERT_DEFINE_MONITOR_BIND(ALERT_DEFINE_ID)");
+ }
+
+ if (!monitorIdIndexExists) {
+ stmt.execute("CREATE INDEX IDX_MONITOR_ID ON
HZB_ALERT_DEFINE_MONITOR_BIND(MONITOR_ID)");
+ }
+ }
+ }
+}
+$$;
+CALL UPDATE_ALERT_DEFINE_MONITOR_BIND_INDEXES();
+DROP ALIAS UPDATE_ALERT_DEFINE_MONITOR_BIND_INDEXES;
+
+-- ========================================
+-- hzb_collector_monitor_bind table
+-- ========================================
+CREATE ALIAS UPDATE_COLLECTOR_MONITOR_BIND_INDEXES AS $$
+void updateCollectorMonitorBindIndexes(java.sql.Connection conn) throws
java.sql.SQLException {
+ boolean tableExists = false;
+ try (java.sql.ResultSet rs = conn.getMetaData().getTables(null, null,
"HZB_COLLECTOR_MONITOR_BIND", null)) {
+ if (rs.next()) tableExists = true;
+ }
+
+ if (tableExists) {
+ try (java.sql.Statement stmt = conn.createStatement()) {
+ // Drop old index if exists
+ try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_COLLECTOR_MONITOR_BIND", false, false)) {
+ while (rs.next()) {
+ String indexName = rs.getString("INDEX_NAME");
+ if ("INDEX_COLLECTOR_MONITOR".equalsIgnoreCase(indexName))
{
+ stmt.execute("DROP INDEX INDEX_COLLECTOR_MONITOR");
+ break;
+ }
+ }
+ } catch (Exception e) {
+ // Index may not exist, continue
+ }
+
+ // Create new indexes if not exist
+ boolean collectorIndexExists = false;
+ boolean monitorIdIndexExists = false;
+
+ try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_COLLECTOR_MONITOR_BIND", false, false)) {
+ while (rs.next()) {
+ String indexName = rs.getString("INDEX_NAME");
+ if
("IDX_COLLECTOR_MONITOR_COLLECTOR".equalsIgnoreCase(indexName)) {
+ collectorIndexExists = true;
+ }
+ if
("IDX_COLLECTOR_MONITOR_MONITOR_ID".equalsIgnoreCase(indexName)) {
+ monitorIdIndexExists = true;
+ }
+ }
+ }
+
+ if (!collectorIndexExists) {
+ stmt.execute("CREATE INDEX IDX_COLLECTOR_MONITOR_COLLECTOR ON
HZB_COLLECTOR_MONITOR_BIND(COLLECTOR)");
+ }
+
+ if (!monitorIdIndexExists) {
+ stmt.execute("CREATE INDEX IDX_COLLECTOR_MONITOR_MONITOR_ID ON
HZB_COLLECTOR_MONITOR_BIND(MONITOR_ID)");
+ }
+ }
+ }
+}
+$$;
+CALL UPDATE_COLLECTOR_MONITOR_BIND_INDEXES();
+DROP ALIAS UPDATE_COLLECTOR_MONITOR_BIND_INDEXES;
+
+-- ========================================
+-- hzb_monitor table
+-- ========================================
+CREATE ALIAS UPDATE_MONITOR_INDEXES AS $$
+void updateMonitorIndexes(java.sql.Connection conn) throws
java.sql.SQLException {
+ boolean tableExists = false;
+ try (java.sql.ResultSet rs = conn.getMetaData().getTables(null, null,
"HZB_MONITOR", null)) {
+ if (rs.next()) tableExists = true;
+ }
+
+ if (tableExists) {
+ try (java.sql.Statement stmt = conn.createStatement()) {
+ // Drop old index if exists
+ try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_MONITOR", false, false)) {
+ while (rs.next()) {
+ String indexName = rs.getString("INDEX_NAME");
+ if ("MONITOR_QUERY_INDEX".equalsIgnoreCase(indexName)) {
+ stmt.execute("DROP INDEX MONITOR_QUERY_INDEX");
+ break;
+ }
+ }
+ } catch (Exception e) {
+ // Index may not exist, continue
+ }
+
+ // Create new indexes if not exist
+ boolean appIndexExists = false;
+ boolean instanceIndexExists = false;
+ boolean nameIndexExists = false;
+
+ try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_MONITOR", false, false)) {
+ while (rs.next()) {
+ String indexName = rs.getString("INDEX_NAME");
+ if ("IDX_HZB_MONITOR_APP".equalsIgnoreCase(indexName)) {
+ appIndexExists = true;
+ }
+ if
("IDX_HZB_MONITOR_INSTANCE".equalsIgnoreCase(indexName)) {
+ instanceIndexExists = true;
+ }
+ if ("IDX_HZB_MONITOR_NAME".equalsIgnoreCase(indexName)) {
+ nameIndexExists = true;
+ }
+ }
+ }
+
+ if (!appIndexExists) {
+ stmt.execute("CREATE INDEX IDX_HZB_MONITOR_APP ON
HZB_MONITOR(APP)");
+ }
+
+ if (!instanceIndexExists) {
+ stmt.execute("CREATE INDEX IDX_HZB_MONITOR_INSTANCE ON
HZB_MONITOR(INSTANCE)");
+ }
+
+ if (!nameIndexExists) {
+ stmt.execute("CREATE INDEX IDX_HZB_MONITOR_NAME ON
HZB_MONITOR(NAME)");
+ }
+ }
+ }
+}
+$$;
+CALL UPDATE_MONITOR_INDEXES();
+DROP ALIAS UPDATE_MONITOR_INDEXES;
+
+-- ========================================
+-- hzb_monitor_bind table
+-- ========================================
+CREATE ALIAS UPDATE_MONITOR_BIND_INDEXES AS $$
+void updateMonitorBindIndexes(java.sql.Connection conn) throws
java.sql.SQLException {
+ boolean tableExists = false;
+ try (java.sql.ResultSet rs = conn.getMetaData().getTables(null, null,
"HZB_MONITOR_BIND", null)) {
+ if (rs.next()) tableExists = true;
+ }
+
+ if (tableExists) {
+ try (java.sql.Statement stmt = conn.createStatement()) {
+ // Create new index if not exist
+ boolean bindIndexExists = false;
+
+ try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_MONITOR_BIND", false, false)) {
+ while (rs.next()) {
+ String indexName = rs.getString("INDEX_NAME");
+ if ("INDEX_MONITOR_BIND".equalsIgnoreCase(indexName)) {
+ bindIndexExists = true;
+ }
+ }
+ }
+
+ if (!bindIndexExists) {
+ stmt.execute("CREATE INDEX INDEX_MONITOR_BIND ON
HZB_MONITOR_BIND(BIZ_ID)");
+ }
+ }
+ }
+}
+$$;
+CALL UPDATE_MONITOR_BIND_INDEXES();
+DROP ALIAS UPDATE_MONITOR_BIND_INDEXES;
+
+-- ========================================
+-- hzb_status_page_incident_component_bind table
+-- Special handling: component_id might have auto-created index from FK
+-- ========================================
+CREATE ALIAS UPDATE_STATUS_PAGE_INCIDENT_COMPONENT_BIND_INDEXES AS $$
+void updateStatusPageIncidentComponentBindIndexes(java.sql.Connection conn)
throws java.sql.SQLException {
+ boolean tableExists = false;
+ try (java.sql.ResultSet rs = conn.getMetaData().getTables(null, null,
"HZB_STATUS_PAGE_INCIDENT_COMPONENT_BIND", null)) {
+ if (rs.next()) tableExists = true;
+ }
+
+ if (tableExists) {
+ try (java.sql.Statement stmt = conn.createStatement()) {
+ // Check if component_id column already has any index
+ boolean hasComponentIdIndex = false;
+ try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_STATUS_PAGE_INCIDENT_COMPONENT_BIND", false, false)) {
+ while (rs.next()) {
+ String columnName = rs.getString("COLUMN_NAME");
+ if (columnName != null &&
"COMPONENT_ID".equalsIgnoreCase(columnName)) {
+ hasComponentIdIndex = true;
+ break;
+ }
+ }
+ }
+
+ // Create index on component_id only if no index exists on this
column
+ if (!hasComponentIdIndex) {
+ stmt.execute("CREATE INDEX IDX_INCIDENT_COMPONENT_COMPONENT_ID
ON HZB_STATUS_PAGE_INCIDENT_COMPONENT_BIND(COMPONENT_ID)");
+ }
+ }
+ }
+}
+$$;
+CALL UPDATE_STATUS_PAGE_INCIDENT_COMPONENT_BIND_INDEXES();
+DROP ALIAS UPDATE_STATUS_PAGE_INCIDENT_COMPONENT_BIND_INDEXES;
+
+-- ========================================
+-- hzb_push_metrics table
+-- ========================================
+CREATE ALIAS UPDATE_PUSH_METRICS_INDEXES AS $$
+void updatePushMetricsIndexes(java.sql.Connection conn) throws
java.sql.SQLException {
+ boolean tableExists = false;
+ try (java.sql.ResultSet rs = conn.getMetaData().getTables(null, null,
"HZB_PUSH_METRICS", null)) {
+ if (rs.next()) tableExists = true;
+ }
+
+ if (tableExists) {
+ try (java.sql.Statement stmt = conn.createStatement()) {
+ // Drop old index if exists
+ try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_PUSH_METRICS", false, false)) {
+ while (rs.next()) {
+ String indexName = rs.getString("INDEX_NAME");
+ if ("PUSH_QUERY_INDEX".equalsIgnoreCase(indexName)) {
+ stmt.execute("DROP INDEX PUSH_QUERY_INDEX");
+ break;
+ }
+ }
+ } catch (Exception e) {
+ // Index may not exist, continue
+ }
+
+ // Create new indexes if not exist
+ boolean monitorIdIndexExists = false;
+ boolean timeIndexExists = false;
+
+ try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_PUSH_METRICS", false, false)) {
+ while (rs.next()) {
+ String indexName = rs.getString("INDEX_NAME");
+ if
("IDX_PUSH_METRICS_MONITOR_ID".equalsIgnoreCase(indexName)) {
+ monitorIdIndexExists = true;
+ }
+ if ("IDX_PUSH_METRICS_TIME".equalsIgnoreCase(indexName)) {
+ timeIndexExists = true;
+ }
+ }
+ }
+
+ if (!monitorIdIndexExists) {
+ stmt.execute("CREATE INDEX IDX_PUSH_METRICS_MONITOR_ID ON
HZB_PUSH_METRICS(MONITOR_ID)");
+ }
+
+ if (!timeIndexExists) {
+ stmt.execute("CREATE INDEX IDX_PUSH_METRICS_TIME ON
HZB_PUSH_METRICS(TIME)");
+ }
+ }
+ }
+}
+$$;
+CALL UPDATE_PUSH_METRICS_INDEXES();
+DROP ALIAS UPDATE_PUSH_METRICS_INDEXES;
+
+-- ========================================
+-- hzb_history table
+-- ========================================
+CREATE ALIAS UPDATE_HISTORY_INDEXES AS $$
+void updateHistoryIndexes(java.sql.Connection conn) throws
java.sql.SQLException {
+ boolean tableExists = false;
+ try (java.sql.ResultSet rs = conn.getMetaData().getTables(null, null,
"HZB_HISTORY", null)) {
+ if (rs.next()) tableExists = true;
+ }
+
+ if (tableExists) {
+ try (java.sql.Statement stmt = conn.createStatement()) {
+ // Drop old index if exists
+ try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_HISTORY", false, false)) {
+ while (rs.next()) {
+ String indexName = rs.getString("INDEX_NAME");
+ if ("HISTORY_QUERY_INDEX".equalsIgnoreCase(indexName)) {
+ stmt.execute("DROP INDEX HISTORY_QUERY_INDEX");
+ break;
+ }
+ }
+ } catch (Exception e) {
+ // Index may not exist, continue
+ }
+
+ // Create new indexes if not exist
+ boolean instanceIndexExists = false;
+ boolean appIndexExists = false;
+ boolean metricsIndexExists = false;
+ boolean metricIndexExists = false;
+
+ try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_HISTORY", false, false)) {
+ while (rs.next()) {
+ String indexName = rs.getString("INDEX_NAME");
+ if
("IDX_HZB_HISTORY_INSTANCE".equalsIgnoreCase(indexName)) {
+ instanceIndexExists = true;
+ }
+ if ("IDX_HZB_HISTORY_APP".equalsIgnoreCase(indexName)) {
+ appIndexExists = true;
+ }
+ if ("IDX_HZB_HISTORY_METRICS".equalsIgnoreCase(indexName))
{
+ metricsIndexExists = true;
+ }
+ if ("IDX_HZB_HISTORY_METRIC".equalsIgnoreCase(indexName)) {
+ metricIndexExists = true;
+ }
+ }
+ }
+
+ if (!instanceIndexExists) {
+ stmt.execute("CREATE INDEX IDX_HZB_HISTORY_INSTANCE ON
HZB_HISTORY(INSTANCE)");
+ }
+
+ if (!appIndexExists) {
+ stmt.execute("CREATE INDEX IDX_HZB_HISTORY_APP ON
HZB_HISTORY(APP)");
+ }
+
+ if (!metricsIndexExists) {
+ stmt.execute("CREATE INDEX IDX_HZB_HISTORY_METRICS ON
HZB_HISTORY(METRICS)");
+ }
+
+ if (!metricIndexExists) {
+ stmt.execute("CREATE INDEX IDX_HZB_HISTORY_METRIC ON
HZB_HISTORY(METRIC)");
+ }
+ }
+ }
+}
+$$;
+CALL UPDATE_HISTORY_INDEXES();
+DROP ALIAS UPDATE_HISTORY_INDEXES;
+
+-- ========================================
+-- hzb_param table
+-- ========================================
+CREATE ALIAS UPDATE_PARAM_TABLE_INDEXES AS $$
+void updateParamTableIndexes(java.sql.Connection conn) throws
java.sql.SQLException {
+ boolean tableExists = false;
+ try (java.sql.ResultSet rs = conn.getMetaData().getTables(null, null,
"HZB_PARAM", null)) {
+ if (rs.next()) tableExists = true;
+ }
+
+ if (tableExists) {
+ try (java.sql.Statement stmt = conn.createStatement()) {
+ // Drop old index if exists
+ try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_PARAM", false, false)) {
+ while (rs.next()) {
+ String indexName = rs.getString("INDEX_NAME");
+ if
("IDX_HZB_PARAM_MONITOR_ID".equalsIgnoreCase(indexName)) {
+ stmt.execute("DROP INDEX IDX_HZB_PARAM_MONITOR_ID");
+ break;
+ }
+ }
+ } catch (Exception e) {
+ // Index may not exist, continue
+ }
+
+ // Create new index if not exist
+ boolean indexExists = false;
+ try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_PARAM", false, false)) {
+ while (rs.next()) {
+ if
("IDX_HZB_PARAM_MONITOR_ID".equalsIgnoreCase(rs.getString("INDEX_NAME"))) {
+ indexExists = true;
+ break;
+ }
+ }
+ }
+ if (!indexExists) {
+ stmt.execute("CREATE INDEX IDX_HZB_PARAM_MONITOR_ID ON
HZB_PARAM(MONITOR_ID)");
+ }
+
+ // Drop old unique constraint if exists
+ try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_PARAM", true, false)) {
+ while (rs.next()) {
+ String indexName = rs.getString("INDEX_NAME");
+ if (indexName != null &&
indexName.equalsIgnoreCase("UK_HZB_PARAM_MONITOR_FIELD")) {
+ stmt.execute("DROP INDEX UK_HZB_PARAM_MONITOR_FIELD");
+ break;
+ }
+ }
+ } catch (Exception e) {
+ // Constraint may not exist, continue
+ }
+
+ // Create new unique constraint if not exist
+ boolean constraintExists = false;
+ try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_PARAM", true, false)) {
+ while (rs.next()) {
+ String indexName = rs.getString("INDEX_NAME");
+ if (indexName != null &&
indexName.equalsIgnoreCase("UK_HZB_PARAM_MONITOR_FIELD")) {
+ constraintExists = true;
+ break;
+ }
+ }
+ }
+ if (!constraintExists) {
+ stmt.execute("CREATE UNIQUE INDEX UK_HZB_PARAM_MONITOR_FIELD
ON HZB_PARAM(MONITOR_ID, FIELD)");
+ }
+ }
+ }
+}
+$$;
+CALL UPDATE_PARAM_TABLE_INDEXES();
+DROP ALIAS UPDATE_PARAM_TABLE_INDEXES;
+
+-- ========================================
+-- hzb_plugin_param table
+-- ========================================
+CREATE ALIAS UPDATE_PLUGIN_PARAM_TABLE_INDEXES AS $$
+void updatePluginParamTableIndexes(java.sql.Connection conn) throws
java.sql.SQLException {
+ boolean tableExists = false;
+ try (java.sql.ResultSet rs = conn.getMetaData().getTables(null, null,
"HZB_PLUGIN_PARAM", null)) {
+ if (rs.next()) tableExists = true;
+ }
+
+ if (tableExists) {
+ try (java.sql.Statement stmt = conn.createStatement()) {
+ // Drop old index if exists
+ try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_PLUGIN_PARAM", false, false)) {
+ while (rs.next()) {
+ String indexName = rs.getString("INDEX_NAME");
+ if
("IDX_HZB_PLUGIN_PARAM_PLUGIN_METADATA_ID".equalsIgnoreCase(indexName)) {
+ stmt.execute("DROP INDEX
IDX_HZB_PLUGIN_PARAM_PLUGIN_METADATA_ID");
+ break;
+ }
+ }
+ } catch (Exception e) {
+ // Index may not exist, continue
+ }
+
+ // Create new index if not exist
+ boolean indexExists = false;
+ try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_PLUGIN_PARAM", false, false)) {
+ while (rs.next()) {
+ if
("IDX_HZB_PLUGIN_PARAM_PLUGIN_METADATA_ID".equalsIgnoreCase(rs.getString("INDEX_NAME")))
{
+ indexExists = true;
+ break;
+ }
+ }
+ }
+ if (!indexExists) {
+ stmt.execute("CREATE INDEX
IDX_HZB_PLUGIN_PARAM_PLUGIN_METADATA_ID ON
HZB_PLUGIN_PARAM(PLUGIN_METADATA_ID)");
+ }
+
+ // Drop old unique constraint if exists
+ try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_PLUGIN_PARAM", true, false)) {
+ while (rs.next()) {
+ String indexName = rs.getString("INDEX_NAME");
+ if (indexName != null &&
indexName.equalsIgnoreCase("UK_HZB_PLUGIN_PARAM_METADATA_FIELD")) {
+ stmt.execute("DROP INDEX
UK_HZB_PLUGIN_PARAM_METADATA_FIELD");
+ break;
+ }
+ }
+ } catch (Exception e) {
+ // Constraint may not exist, continue
+ }
+
+ // Create new unique constraint if not exist
+ boolean constraintExists = false;
+ try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_PLUGIN_PARAM", true, false)) {
+ while (rs.next()) {
+ String indexName = rs.getString("INDEX_NAME");
+ if (indexName != null &&
indexName.equalsIgnoreCase("UK_HZB_PLUGIN_PARAM_METADATA_FIELD")) {
+ constraintExists = true;
+ break;
+ }
+ }
+ }
+ if (!constraintExists) {
+ stmt.execute("CREATE UNIQUE INDEX
UK_HZB_PLUGIN_PARAM_METADATA_FIELD ON HZB_PLUGIN_PARAM(PLUGIN_METADATA_ID,
FIELD)");
+ }
+ }
+ }
+}
+$$;
+CALL UPDATE_PLUGIN_PARAM_TABLE_INDEXES();
+DROP ALIAS UPDATE_PLUGIN_PARAM_TABLE_INDEXES;
diff --git
a/hertzbeat-startup/src/main/resources/db/migration/mysql/V181__update_indexes.sql
b/hertzbeat-startup/src/main/resources/db/migration/mysql/V181__update_indexes.sql
new file mode 100644
index 0000000000..88442f98c6
--- /dev/null
+++
b/hertzbeat-startup/src/main/resources/db/migration/mysql/V181__update_indexes.sql
@@ -0,0 +1,450 @@
+-- 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.
+
+-- Ensure every SQL can rerun without error
+-- This migration updates indexes to match the entity annotations
+
+-- ========================================
+-- hzb_alert_define_monitor_bind table
+-- ========================================
+DELIMITER //
+CREATE PROCEDURE UpdateAlertDefineMonitorBindIndexes()
+BEGIN
+ DECLARE table_exists INT;
+
+ SELECT COUNT(*) INTO table_exists
+ FROM INFORMATION_SCHEMA.TABLES
+ WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME =
'hzb_alert_define_monitor_bind';
+
+ IF table_exists = 1 THEN
+ -- Drop old index if exists
+ IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_alert_define_monitor_bind'
+ AND INDEX_NAME = 'index_alert_define_monitor') THEN
+ SET @drop_sql = 'DROP INDEX index_alert_define_monitor ON
hzb_alert_define_monitor_bind';
+ PREPARE stmt FROM @drop_sql;
+ EXECUTE stmt;
+ DEALLOCATE PREPARE stmt;
+ END IF;
+
+ -- Create new indexes if not exist
+ IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_alert_define_monitor_bind'
+ AND INDEX_NAME = 'idx_alert_define_id') THEN
+ CREATE INDEX idx_alert_define_id ON
hzb_alert_define_monitor_bind(alert_define_id);
+ END IF;
+
+ IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_alert_define_monitor_bind'
+ AND INDEX_NAME = 'idx_monitor_id') THEN
+ CREATE INDEX idx_monitor_id ON
hzb_alert_define_monitor_bind(monitor_id);
+ END IF;
+ END IF;
+END //
+DELIMITER ;
+
+CALL UpdateAlertDefineMonitorBindIndexes();
+DROP PROCEDURE IF EXISTS UpdateAlertDefineMonitorBindIndexes;
+
+-- ========================================
+-- hzb_collector_monitor_bind table
+-- ========================================
+DELIMITER //
+CREATE PROCEDURE UpdateCollectorMonitorBindIndexes()
+BEGIN
+ DECLARE table_exists INT;
+
+ SELECT COUNT(*) INTO table_exists
+ FROM INFORMATION_SCHEMA.TABLES
+ WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME =
'hzb_collector_monitor_bind';
+
+ IF table_exists = 1 THEN
+ -- Drop old index if exists
+ IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_collector_monitor_bind'
+ AND INDEX_NAME = 'index_collector_monitor') THEN
+ SET @drop_sql = 'DROP INDEX index_collector_monitor ON
hzb_collector_monitor_bind';
+ PREPARE stmt FROM @drop_sql;
+ EXECUTE stmt;
+ DEALLOCATE PREPARE stmt;
+ END IF;
+
+ -- Create new indexes if not exist
+ IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_collector_monitor_bind'
+ AND INDEX_NAME = 'idx_collector_monitor_collector') THEN
+ CREATE INDEX idx_collector_monitor_collector ON
hzb_collector_monitor_bind(collector);
+ END IF;
+
+ IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_collector_monitor_bind'
+ AND INDEX_NAME = 'idx_collector_monitor_monitor_id') THEN
+ CREATE INDEX idx_collector_monitor_monitor_id ON
hzb_collector_monitor_bind(monitor_id);
+ END IF;
+ END IF;
+END //
+DELIMITER ;
+
+CALL UpdateCollectorMonitorBindIndexes();
+DROP PROCEDURE IF EXISTS UpdateCollectorMonitorBindIndexes;
+
+-- ========================================
+-- hzb_monitor table
+-- ========================================
+DELIMITER //
+CREATE PROCEDURE UpdateMonitorIndexes()
+BEGIN
+ DECLARE table_exists INT;
+
+ SELECT COUNT(*) INTO table_exists
+ FROM INFORMATION_SCHEMA.TABLES
+ WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'hzb_monitor';
+
+ IF table_exists = 1 THEN
+ -- Drop old index if exists
+ IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_monitor'
+ AND INDEX_NAME = 'monitor_query_index') THEN
+ SET @drop_sql = 'DROP INDEX monitor_query_index ON hzb_monitor';
+ PREPARE stmt FROM @drop_sql;
+ EXECUTE stmt;
+ DEALLOCATE PREPARE stmt;
+ END IF;
+
+ -- Create new indexes if not exist
+ IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_monitor'
+ AND INDEX_NAME = 'idx_hzb_monitor_app') THEN
+ CREATE INDEX idx_hzb_monitor_app ON hzb_monitor(app);
+ END IF;
+
+ IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_monitor'
+ AND INDEX_NAME = 'idx_hzb_monitor_instance') THEN
+ CREATE INDEX idx_hzb_monitor_instance ON hzb_monitor(instance);
+ END IF;
+
+ IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_monitor'
+ AND INDEX_NAME = 'idx_hzb_monitor_name') THEN
+ CREATE INDEX idx_hzb_monitor_name ON hzb_monitor(name);
+ END IF;
+ END IF;
+END //
+DELIMITER ;
+
+CALL UpdateMonitorIndexes();
+DROP PROCEDURE IF EXISTS UpdateMonitorIndexes;
+
+-- ========================================
+-- hzb_monitor_bind table
+-- ========================================
+DELIMITER //
+CREATE PROCEDURE UpdateMonitorBindIndexes()
+BEGIN
+ DECLARE table_exists INT;
+
+ SELECT COUNT(*) INTO table_exists
+ FROM INFORMATION_SCHEMA.TABLES
+ WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'hzb_monitor_bind';
+
+ IF table_exists = 1 THEN
+ -- Create new index if not exist
+ IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_monitor_bind'
+ AND INDEX_NAME = 'index_monitor_bind') THEN
+ CREATE INDEX index_monitor_bind ON hzb_monitor_bind(biz_id);
+ END IF;
+ END IF;
+END //
+DELIMITER ;
+
+CALL UpdateMonitorBindIndexes();
+DROP PROCEDURE IF EXISTS UpdateMonitorBindIndexes;
+
+-- ========================================
+-- hzb_status_page_incident_component_bind table
+-- Special handling: component_id might have auto-created index from FK
+-- ========================================
+DELIMITER //
+CREATE PROCEDURE UpdateStatusPageIncidentComponentBindIndexes()
+BEGIN
+ DECLARE table_exists INT;
+ DECLARE component_id_has_index INT;
+
+ SELECT COUNT(*) INTO table_exists
+ FROM INFORMATION_SCHEMA.TABLES
+ WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME =
'hzb_status_page_incident_component_bind';
+
+ IF table_exists = 1 THEN
+ -- Check if component_id column already has any index (including
auto-created by FK)
+ SELECT COUNT(*) INTO component_id_has_index
+ FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_status_page_incident_component_bind'
+ AND COLUMN_NAME = 'component_id'
+ AND INDEX_NAME != 'PRIMARY';
+
+ -- Create index on component_id only if no index exists on this column
+ IF component_id_has_index = 0 THEN
+ CREATE INDEX idx_incident_component_component_id ON
hzb_status_page_incident_component_bind(component_id);
+ END IF;
+ END IF;
+END //
+DELIMITER ;
+
+CALL UpdateStatusPageIncidentComponentBindIndexes();
+DROP PROCEDURE IF EXISTS UpdateStatusPageIncidentComponentBindIndexes;
+
+-- ========================================
+-- hzb_push_metrics table
+-- ========================================
+DELIMITER //
+CREATE PROCEDURE UpdatePushMetricsIndexes()
+BEGIN
+ DECLARE table_exists INT;
+
+ SELECT COUNT(*) INTO table_exists
+ FROM INFORMATION_SCHEMA.TABLES
+ WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'hzb_push_metrics';
+
+ IF table_exists = 1 THEN
+ -- Drop old index if exists
+ IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_push_metrics'
+ AND INDEX_NAME = 'push_query_index') THEN
+ SET @drop_sql = 'DROP INDEX push_query_index ON hzb_push_metrics';
+ PREPARE stmt FROM @drop_sql;
+ EXECUTE stmt;
+ DEALLOCATE PREPARE stmt;
+ END IF;
+
+ -- Create new indexes if not exist
+ IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_push_metrics'
+ AND INDEX_NAME = 'idx_push_metrics_monitor_id') THEN
+ CREATE INDEX idx_push_metrics_monitor_id ON
hzb_push_metrics(monitor_id);
+ END IF;
+
+ IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_push_metrics'
+ AND INDEX_NAME = 'idx_push_metrics_time') THEN
+ CREATE INDEX idx_push_metrics_time ON hzb_push_metrics(time);
+ END IF;
+ END IF;
+END //
+DELIMITER ;
+
+CALL UpdatePushMetricsIndexes();
+DROP PROCEDURE IF EXISTS UpdatePushMetricsIndexes;
+
+-- ========================================
+-- hzb_history table
+-- ========================================
+DELIMITER //
+CREATE PROCEDURE UpdateHistoryIndexes()
+BEGIN
+ DECLARE table_exists INT;
+
+ SELECT COUNT(*) INTO table_exists
+ FROM INFORMATION_SCHEMA.TABLES
+ WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'hzb_history';
+
+ IF table_exists = 1 THEN
+ -- Drop old index if exists
+ IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_history'
+ AND INDEX_NAME = 'history_query_index') THEN
+ SET @drop_sql = 'DROP INDEX history_query_index ON hzb_history';
+ PREPARE stmt FROM @drop_sql;
+ EXECUTE stmt;
+ DEALLOCATE PREPARE stmt;
+ END IF;
+
+ -- Create new indexes if not exist
+ IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_history'
+ AND INDEX_NAME = 'idx_hzb_history_instance') THEN
+ CREATE INDEX idx_hzb_history_instance ON hzb_history(instance);
+ END IF;
+
+ IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_history'
+ AND INDEX_NAME = 'idx_hzb_history_app') THEN
+ CREATE INDEX idx_hzb_history_app ON hzb_history(app);
+ END IF;
+
+ IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_history'
+ AND INDEX_NAME = 'idx_hzb_history_metrics') THEN
+ CREATE INDEX idx_hzb_history_metrics ON hzb_history(metrics);
+ END IF;
+
+ IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_history'
+ AND INDEX_NAME = 'idx_hzb_history_metric') THEN
+ CREATE INDEX idx_hzb_history_metric ON hzb_history(metric);
+ END IF;
+ END IF;
+END //
+DELIMITER ;
+
+CALL UpdateHistoryIndexes();
+DROP PROCEDURE IF EXISTS UpdateHistoryIndexes;
+
+-- ========================================
+-- hzb_param table
+-- ========================================
+DELIMITER //
+CREATE PROCEDURE UpdateParamTableIndexes()
+BEGIN
+ DECLARE table_exists INT;
+
+ SELECT COUNT(*) INTO table_exists
+ FROM INFORMATION_SCHEMA.TABLES
+ WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'hzb_param';
+
+ IF table_exists = 1 THEN
+ -- Drop old index if exists
+ IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_param'
+ AND INDEX_NAME = 'idx_hzb_param_monitor_id') THEN
+ SET @drop_sql = 'DROP INDEX idx_hzb_param_monitor_id ON hzb_param';
+ PREPARE stmt FROM @drop_sql;
+ EXECUTE stmt;
+ DEALLOCATE PREPARE stmt;
+ END IF;
+
+ -- Create new index if not exist
+ IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_param'
+ AND INDEX_NAME = 'idx_hzb_param_monitor_id') THEN
+ CREATE INDEX idx_hzb_param_monitor_id ON hzb_param(monitor_id);
+ END IF;
+
+ -- Drop old unique constraint if exists
+ IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_param'
+ AND CONSTRAINT_TYPE = 'UNIQUE'
+ AND CONSTRAINT_NAME = 'uk_hzb_param_monitor_field') THEN
+ SET @drop_sql = 'ALTER TABLE hzb_param DROP INDEX
uk_hzb_param_monitor_field';
+ PREPARE stmt FROM @drop_sql;
+ EXECUTE stmt;
+ DEALLOCATE PREPARE stmt;
+ END IF;
+
+ -- Create new unique constraint if not exist
+ IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_param'
+ AND CONSTRAINT_TYPE = 'UNIQUE'
+ AND CONSTRAINT_NAME = 'uk_hzb_param_monitor_field') THEN
+ ALTER TABLE hzb_param
+ ADD CONSTRAINT uk_hzb_param_monitor_field
+ UNIQUE (monitor_id, field);
+ END IF;
+ END IF;
+END //
+DELIMITER ;
+
+CALL UpdateParamTableIndexes();
+DROP PROCEDURE IF EXISTS UpdateParamTableIndexes;
+
+-- ========================================
+-- hzb_plugin_param table
+-- ========================================
+DELIMITER //
+CREATE PROCEDURE UpdatePluginParamTableIndexes()
+BEGIN
+ DECLARE table_exists INT;
+
+ SELECT COUNT(*) INTO table_exists
+ FROM INFORMATION_SCHEMA.TABLES
+ WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'hzb_plugin_param';
+
+ IF table_exists = 1 THEN
+ -- Drop old index if exists
+ IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_plugin_param'
+ AND INDEX_NAME = 'idx_hzb_plugin_param_plugin_metadata_id')
THEN
+ SET @drop_sql = 'DROP INDEX
idx_hzb_plugin_param_plugin_metadata_id ON hzb_plugin_param';
+ PREPARE stmt FROM @drop_sql;
+ EXECUTE stmt;
+ DEALLOCATE PREPARE stmt;
+ END IF;
+
+ -- Create new index if not exist
+ IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_plugin_param'
+ AND INDEX_NAME =
'idx_hzb_plugin_param_plugin_metadata_id') THEN
+ CREATE INDEX idx_hzb_plugin_param_plugin_metadata_id ON
hzb_plugin_param(plugin_metadata_id);
+ END IF;
+
+ -- Drop old unique constraint if exists
+ IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_plugin_param'
+ AND CONSTRAINT_TYPE = 'UNIQUE'
+ AND CONSTRAINT_NAME = 'uk_hzb_plugin_param_metadata_field')
THEN
+ SET @drop_sql = 'ALTER TABLE hzb_plugin_param DROP INDEX
uk_hzb_plugin_param_metadata_field';
+ PREPARE stmt FROM @drop_sql;
+ EXECUTE stmt;
+ DEALLOCATE PREPARE stmt;
+ END IF;
+
+ -- Create new unique constraint if not exist
+ IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_plugin_param'
+ AND CONSTRAINT_TYPE = 'UNIQUE'
+ AND CONSTRAINT_NAME =
'uk_hzb_plugin_param_metadata_field') THEN
+ ALTER TABLE hzb_plugin_param
+ ADD CONSTRAINT uk_hzb_plugin_param_metadata_field
+ UNIQUE (plugin_metadata_id, field);
+ END IF;
+ END IF;
+END //
+DELIMITER ;
+
+CALL UpdatePluginParamTableIndexes();
+DROP PROCEDURE IF EXISTS UpdatePluginParamTableIndexes;
+
+COMMIT;
diff --git
a/hertzbeat-startup/src/main/resources/db/migration/postgresql/V181__update_indexes.sql
b/hertzbeat-startup/src/main/resources/db/migration/postgresql/V181__update_indexes.sql
new file mode 100644
index 0000000000..da88582de3
--- /dev/null
+++
b/hertzbeat-startup/src/main/resources/db/migration/postgresql/V181__update_indexes.sql
@@ -0,0 +1,247 @@
+-- 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.
+
+-- Ensure every SQL can rerun without error
+-- This migration updates indexes to match the entity annotations
+
+-- ========================================
+-- hzb_alert_define_monitor_bind table
+-- ========================================
+DO $$
+BEGIN
+ IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name =
'hzb_alert_define_monitor_bind') THEN
+ -- Drop old index if exists
+ IF EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_alert_define_monitor_bind' AND indexname = 'index_alert_define_monitor')
THEN
+ DROP INDEX IF EXISTS index_alert_define_monitor;
+ END IF;
+
+ -- Create new indexes if not exist
+ IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_alert_define_monitor_bind' AND indexname = 'idx_alert_define_id') THEN
+ CREATE INDEX idx_alert_define_id ON
hzb_alert_define_monitor_bind(alert_define_id);
+ END IF;
+
+ IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_alert_define_monitor_bind' AND indexname = 'idx_monitor_id') THEN
+ CREATE INDEX idx_monitor_id ON
hzb_alert_define_monitor_bind(monitor_id);
+ END IF;
+ END IF;
+END $$;
+
+-- ========================================
+-- hzb_collector_monitor_bind table
+-- ========================================
+DO $$
+BEGIN
+ IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name =
'hzb_collector_monitor_bind') THEN
+ -- Drop old index if exists
+ IF EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_collector_monitor_bind' AND indexname = 'index_collector_monitor') THEN
+ DROP INDEX IF EXISTS index_collector_monitor;
+ END IF;
+
+ -- Create new indexes if not exist
+ IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_collector_monitor_bind' AND indexname = 'idx_collector_monitor_collector')
THEN
+ CREATE INDEX idx_collector_monitor_collector ON
hzb_collector_monitor_bind(collector);
+ END IF;
+
+ IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_collector_monitor_bind' AND indexname =
'idx_collector_monitor_monitor_id') THEN
+ CREATE INDEX idx_collector_monitor_monitor_id ON
hzb_collector_monitor_bind(monitor_id);
+ END IF;
+ END IF;
+END $$;
+
+-- ========================================
+-- hzb_monitor table
+-- ========================================
+DO $$
+BEGIN
+ IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name =
'hzb_monitor') THEN
+ -- Drop old index if exists
+ IF EXISTS (SELECT 1 FROM pg_indexes WHERE tablename = 'hzb_monitor'
AND indexname = 'monitor_query_index') THEN
+ DROP INDEX IF EXISTS monitor_query_index;
+ END IF;
+
+ -- Create new indexes if not exist
+ IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_monitor' AND indexname = 'idx_hzb_monitor_app') THEN
+ CREATE INDEX idx_hzb_monitor_app ON hzb_monitor(app);
+ END IF;
+
+ IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_monitor' AND indexname = 'idx_hzb_monitor_instance') THEN
+ CREATE INDEX idx_hzb_monitor_instance ON hzb_monitor(instance);
+ END IF;
+
+ IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_monitor' AND indexname = 'idx_hzb_monitor_name') THEN
+ CREATE INDEX idx_hzb_monitor_name ON hzb_monitor(name);
+ END IF;
+ END IF;
+END $$;
+
+-- ========================================
+-- hzb_monitor_bind table
+-- ========================================
+DO $$
+BEGIN
+ IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name =
'hzb_monitor_bind') THEN
+ -- Create new index if not exist
+ IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_monitor_bind' AND indexname = 'index_monitor_bind') THEN
+ CREATE INDEX index_monitor_bind ON hzb_monitor_bind(biz_id);
+ END IF;
+ END IF;
+END $$;
+
+-- ========================================
+-- hzb_status_page_incident_component_bind table
+-- Special handling: component_id might have auto-created index from FK
+-- ========================================
+DO $$
+BEGIN
+ IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name =
'hzb_status_page_incident_component_bind') THEN
+ -- Check if component_id column already has any index, create if not
+ IF NOT EXISTS (
+ SELECT 1 FROM pg_indexes
+ WHERE tablename = 'hzb_status_page_incident_component_bind'
+ AND indexdef LIKE '%component_id%'
+ AND indexname NOT LIKE '%_pkey'
+ ) THEN
+ CREATE INDEX idx_incident_component_component_id ON
hzb_status_page_incident_component_bind(component_id);
+ END IF;
+ END IF;
+END $$;
+
+-- ========================================
+-- hzb_push_metrics table
+-- ========================================
+DO $$
+BEGIN
+ IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name =
'hzb_push_metrics') THEN
+ -- Drop old index if exists
+ IF EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_push_metrics' AND indexname = 'push_query_index') THEN
+ DROP INDEX IF EXISTS push_query_index;
+ END IF;
+
+ -- Create new indexes if not exist
+ IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_push_metrics' AND indexname = 'idx_push_metrics_monitor_id') THEN
+ CREATE INDEX idx_push_metrics_monitor_id ON
hzb_push_metrics(monitor_id);
+ END IF;
+
+ IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_push_metrics' AND indexname = 'idx_push_metrics_time') THEN
+ CREATE INDEX idx_push_metrics_time ON hzb_push_metrics(time);
+ END IF;
+ END IF;
+END $$;
+
+-- ========================================
+-- hzb_history table
+-- ========================================
+DO $$
+BEGIN
+ IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name =
'hzb_history') THEN
+ -- Drop old index if exists
+ IF EXISTS (SELECT 1 FROM pg_indexes WHERE tablename = 'hzb_history'
AND indexname = 'history_query_index') THEN
+ DROP INDEX IF EXISTS history_query_index;
+ END IF;
+
+ -- Create new indexes if not exist
+ IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_history' AND indexname = 'idx_hzb_history_instance') THEN
+ CREATE INDEX idx_hzb_history_instance ON hzb_history(instance);
+ END IF;
+
+ IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_history' AND indexname = 'idx_hzb_history_app') THEN
+ CREATE INDEX idx_hzb_history_app ON hzb_history(app);
+ END IF;
+
+ IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_history' AND indexname = 'idx_hzb_history_metrics') THEN
+ CREATE INDEX idx_hzb_history_metrics ON hzb_history(metrics);
+ END IF;
+
+ IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_history' AND indexname = 'idx_hzb_history_metric') THEN
+ CREATE INDEX idx_hzb_history_metric ON hzb_history(metric);
+ END IF;
+ END IF;
+END $$;
+
+-- ========================================
+-- hzb_param table
+-- ========================================
+DO $$
+BEGIN
+ IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name =
'hzb_param') THEN
+ -- Drop old index if exists
+ IF EXISTS (SELECT 1 FROM pg_indexes WHERE tablename = 'hzb_param' AND
indexname = 'idx_hzb_param_monitor_id') THEN
+ DROP INDEX IF EXISTS idx_hzb_param_monitor_id;
+ END IF;
+
+ -- Create new index if not exist
+ IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename = 'hzb_param'
AND indexname = 'idx_hzb_param_monitor_id') THEN
+ CREATE INDEX idx_hzb_param_monitor_id ON hzb_param(monitor_id);
+ END IF;
+
+ -- Drop old unique constraint if exists
+ IF EXISTS (SELECT 1 FROM information_schema.table_constraints
+ WHERE table_name = 'hzb_param'
+ AND constraint_type = 'UNIQUE'
+ AND constraint_name = 'uk_hzb_param_monitor_field') THEN
+ ALTER TABLE hzb_param DROP CONSTRAINT IF EXISTS
uk_hzb_param_monitor_field;
+ END IF;
+
+ -- Create new unique constraint if not exist
+ IF NOT EXISTS (SELECT 1 FROM information_schema.table_constraints
+ WHERE table_name = 'hzb_param'
+ AND constraint_type = 'UNIQUE'
+ AND constraint_name = 'uk_hzb_param_monitor_field') THEN
+ ALTER TABLE hzb_param
+ ADD CONSTRAINT uk_hzb_param_monitor_field
+ UNIQUE (monitor_id, field);
+ END IF;
+ END IF;
+END $$;
+
+-- ========================================
+-- hzb_plugin_param table
+-- ========================================
+DO $$
+BEGIN
+ IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name =
'hzb_plugin_param') THEN
+ -- Drop old index if exists
+ IF EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_plugin_param' AND indexname = 'idx_hzb_plugin_param_plugin_metadata_id')
THEN
+ DROP INDEX IF EXISTS idx_hzb_plugin_param_plugin_metadata_id;
+ END IF;
+
+ -- Create new index if not exist
+ IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_plugin_param' AND indexname = 'idx_hzb_plugin_param_plugin_metadata_id')
THEN
+ CREATE INDEX idx_hzb_plugin_param_plugin_metadata_id ON
hzb_plugin_param(plugin_metadata_id);
+ END IF;
+
+ -- Drop old unique constraint if exists
+ IF EXISTS (SELECT 1 FROM information_schema.table_constraints
+ WHERE table_name = 'hzb_plugin_param'
+ AND constraint_type = 'UNIQUE'
+ AND constraint_name = 'uk_hzb_plugin_param_metadata_field')
THEN
+ ALTER TABLE hzb_plugin_param DROP CONSTRAINT IF EXISTS
uk_hzb_plugin_param_metadata_field;
+ END IF;
+
+ -- Create new unique constraint if not exist
+ IF NOT EXISTS (SELECT 1 FROM information_schema.table_constraints
+ WHERE table_name = 'hzb_plugin_param'
+ AND constraint_type = 'UNIQUE'
+ AND constraint_name =
'uk_hzb_plugin_param_metadata_field') THEN
+ ALTER TABLE hzb_plugin_param
+ ADD CONSTRAINT uk_hzb_plugin_param_metadata_field
+ UNIQUE (plugin_metadata_id, field);
+ END IF;
+ END IF;
+END $$;
+
+COMMIT;
diff --git
a/hertzbeat-startup/src/test/java/org/apache/hertzbeat/startup/EntityIndexIntegrationTest.java
b/hertzbeat-startup/src/test/java/org/apache/hertzbeat/startup/EntityIndexIntegrationTest.java
new file mode 100644
index 0000000000..43c82cc818
--- /dev/null
+++
b/hertzbeat-startup/src/test/java/org/apache/hertzbeat/startup/EntityIndexIntegrationTest.java
@@ -0,0 +1,185 @@
+/*
+ * 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.startup;
+
+import jakarta.persistence.Index;
+import jakarta.persistence.Table;
+import jakarta.persistence.UniqueConstraint;
+import lombok.extern.slf4j.Slf4j;
+import org.apache.hertzbeat.common.entity.alerter.AlertDefineMonitorBind;
+import org.apache.hertzbeat.common.entity.manager.CollectorMonitorBind;
+import org.apache.hertzbeat.common.entity.manager.Monitor;
+import org.apache.hertzbeat.common.entity.manager.MonitorBind;
+import org.apache.hertzbeat.common.entity.manager.Param;
+import
org.apache.hertzbeat.common.entity.manager.StatusPageIncidentComponentBind;
+import org.apache.hertzbeat.common.entity.push.PushMetrics;
+import org.apache.hertzbeat.common.entity.warehouse.History;
+import org.apache.hertzbeat.manager.pojo.dto.PluginParam;
+import org.junit.jupiter.api.DisplayName;
+import org.junit.jupiter.api.Test;
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.boot.autoconfigure.web.WebProperties;
+import org.springframework.boot.test.context.SpringBootTest;
+import org.springframework.boot.test.context.TestConfiguration;
+import org.springframework.context.annotation.Bean;
+import org.springframework.test.context.ActiveProfiles;
+import org.springframework.test.context.TestPropertySource;
+
+import javax.sql.DataSource;
+import java.sql.Connection;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.Arrays;
+import java.util.HashMap;
+import java.util.HashSet;
+import java.util.List;
+import java.util.Map;
+import java.util.Set;
+import java.util.stream.Collectors;
+
+import static org.junit.jupiter.api.Assertions.assertTrue;
+
+/**
+ * Integration test for validating that entity indexes are actually created in
the database.
+ * This test uses H2 in-memory database to verify DDL generation and index
creation.
+ */
+@Slf4j
+@SpringBootTest(classes = HertzBeatApplication.class, webEnvironment =
SpringBootTest.WebEnvironment.NONE)
+@ActiveProfiles("test")
+@TestPropertySource(properties = {
+ "spring.jpa.hibernate.ddl-auto=create-drop",
+ "spring.datasource.url=jdbc:h2:mem:testdb;MODE=MySQL;DB_CLOSE_DELAY=-1",
+ "spring.datasource.driver-class-name=org.h2.Driver",
+ "spring.jpa.show-sql=false"
+})
+@DisplayName("Entity Index Creation Integration Test")
+class EntityIndexIntegrationTest {
+
+ @Autowired
+ private DataSource dataSource;
+
+ public List<Class<?>> entities = List.of(
+ AlertDefineMonitorBind.class,
+ CollectorMonitorBind.class,
+ Monitor.class,
+ MonitorBind.class,
+ Param.class,
+ StatusPageIncidentComponentBind.class,
+ PushMetrics.class,
+ History.class,
+ PluginParam.class
+ );
+
+ @Test
+ @DisplayName("All entity indexes should be created in database")
+ public void testAllEntityIndexes() throws SQLException {
+ for (Class<?> entity : entities) {
+ List<ExpectedIndex> expectedIndexes = parseExpectedIndexes(entity);
+ if (expectedIndexes.isEmpty()) {
+ continue;
+ }
+ Map<String, IndexMeta> actual =
getIndexes(expectedIndexes.get(0).tableName());
+
+ for (ExpectedIndex expected : expectedIndexes) {
+ assertIndexExists(expected, actual);
+ }
+ }
+
+ }
+
+ private List<ExpectedIndex> parseExpectedIndexes(Class<?> entityClass) {
+ Table table = entityClass.getAnnotation(Table.class);
+ if (table == null) {
+ return List.of();
+ }
+ String tableName = table.name().toUpperCase();
+ List<ExpectedIndex> result = new ArrayList<>();
+
+ for (Index idx : table.indexes()) {
+ Set<String> columns = Arrays.stream(idx.columnList().split(","))
+ .map(String::trim)
+ .map(String::toUpperCase)
+ .collect(Collectors.toSet());
+
+ result.add(new ExpectedIndex(tableName, columns, false));
+ }
+ for (UniqueConstraint uc : table.uniqueConstraints()) {
+ Set<String> columns = Arrays.stream(uc.columnNames())
+ .map(String::toUpperCase).collect(Collectors.toSet());
+ result.add(new ExpectedIndex(tableName, columns, true));
+ }
+ return result;
+ }
+
+ private void assertIndexExists(ExpectedIndex expected, Map<String,
IndexMeta> actualIndexes) {
+ boolean found = actualIndexes.values().stream().filter(idx ->
!expected.unique || idx.unique).anyMatch(idx ->
idx.columns.containsAll(expected.columns));
+ assertTrue(found, "Expected index not found. Table=" +
expected.tableName + ", columns=" + expected.columns + ", unique=" +
expected.unique + ", actual=" + actualIndexes);
+ }
+
+ private Map<String, IndexMeta> getIndexes(String tableName) throws
SQLException {
+ Map<String, IndexMeta> indexes = new HashMap<>();
+
+ String sql = """
+ SELECT
+ INDEX_NAME,
+ COLUMN_NAME,
+ IS_UNIQUE
+ FROM INFORMATION_SCHEMA.INDEX_COLUMNS
+ WHERE TABLE_NAME = ?
+ """;
+ try (Connection conn = dataSource.getConnection(); var ps =
conn.prepareStatement(sql)) {
+ ps.setString(1, tableName.toUpperCase());
+
+ try (ResultSet rs = ps.executeQuery()) {
+ while (rs.next()) {
+ String indexName = rs.getString("INDEX_NAME");
+ String column = rs.getString("COLUMN_NAME");
+ boolean unique = rs.getBoolean("IS_UNIQUE");
+ if ("PRIMARY".equalsIgnoreCase(indexName)) {
+ continue;
+ }
+ indexes.computeIfAbsent(indexName, k -> new
IndexMeta(unique)).columns.add(column.toUpperCase());
+ }
+ }
+ }
+ return indexes;
+ }
+
+ static class IndexMeta {
+ final boolean unique;
+ final Set<String> columns = new HashSet<>();
+
+ IndexMeta(boolean unique) {
+ this.unique = unique;
+ }
+ }
+
+ record ExpectedIndex(String tableName, Set<String> columns, boolean
unique) {
+
+ }
+
+ @TestConfiguration
+ static class TestConfig {
+
+ @Bean
+ public WebProperties webProperties() {
+ return new WebProperties();
+ }
+ }
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
