This is an automated email from the ASF dual-hosted git repository.
lizhimins pushed a commit to branch rocketmq-studio
in repository https://gitbox.apache.org/repos/asf/rocketmq-dashboard.git
The following commit(s) were added to refs/heads/rocketmq-studio by this push:
new 668b59f8b fix: align demo SQL with the numeric schema (#2527)
668b59f8b is described below
commit 668b59f8bcdf680c6bb9f9cd15a3ab3b8e6e8027
Author: xdz997 <[email protected]>
AuthorDate: Sat Aug 22 15:32:27 2026 +0800
fix: align demo SQL with the numeric schema (#2527)
* fix: align demo SQL with numeric schema
* fix: preserve logical instance references in demo metadata
---
README.md | 4 +-
deploy/README.md | 13 +-
deploy/mysql/upgrade-demo-acl.sql | 207 ++++++++-----
deploy/mysql/upgrade-demo-alert.sql | 20 +-
deploy/mysql/upgrade-demo-instance.sql | 339 +++++++++++++--------
.../persistence/DemoDataSqlCompatibilityTest.java | 147 +++++++++
6 files changed, 519 insertions(+), 211 deletions(-)
diff --git a/README.md b/README.md
index 635ac4991..4ebe26507 100644
--- a/README.md
+++ b/README.md
@@ -20,7 +20,9 @@ The first command starts the bundled RocketMQ topology and
creates the
is healthy before starting Studio with `docker compose ps` from
`deploy/rocketmq`.
The default schema creates only Studio tables. It does not seed instances,
topics, consumer groups, or ACL
records. Development-only sample data can be imported explicitly from
`deploy/mysql/`; it is not part of the
-default deployment.
+default deployment. Import `upgrade-demo-instance.sql` first and then
`upgrade-demo-acl.sql`; both scripts
+target the current numeric-ID schema and are idempotent. They are sample-data
loaders, not upgrade migrations,
+and should never be imported into a production database.
**Studio ports:** Frontend 6789 (Nginx), Backend 8888 (Spring Boot)
diff --git a/deploy/README.md b/deploy/README.md
index 1713f1eea..f080791eb 100644
--- a/deploy/README.md
+++ b/deploy/README.md
@@ -49,8 +49,17 @@ RocketMQ、Prometheus 和云 API 故障由 Studio 的运行态诊断页面展示
故障触发 Studio 容器反复重启。
默认 schema 只创建 Studio 所需的表，不会写入实例、Topic、消费组或 ACL 示例数据。需要演示数据时，
-请在开发环境中显式导入 `deploy/mysql/upgrade-demo-instance.sql` 和
-`deploy/mysql/upgrade-demo-acl.sql`，不要在生产环境导入这些脚本。
+请先初始化当前 `server/src/main/resources/db/schema.sql`，再在开发环境中按顺序导入：
+
+```bash
+docker exec -i rocketmq-studio-mysql mysql -uroot -pstudio123 rocketmq \
+ < deploy/mysql/upgrade-demo-instance.sql
+docker exec -i rocketmq-studio-mysql mysql -uroot -pstudio123 rocketmq \
+ < deploy/mysql/upgrade-demo-acl.sql
+```
+
+两个脚本均按当前 numeric-ID schema 写入并可重复执行；它们只负责演示数据，不创建或迁移业务表，
+不要在生产环境导入。
## 开启登录保护
`studio.auth.login-required` 默认为 `false`，便于本地开发和演示环境直接访问。共享环境建议在
diff --git a/deploy/mysql/upgrade-demo-acl.sql
b/deploy/mysql/upgrade-demo-acl.sql
index ed9ae1977..148a08b1c 100644
--- a/deploy/mysql/upgrade-demo-acl.sql
+++ b/deploy/mysql/upgrade-demo-acl.sql
@@ -1,90 +1,147 @@
-- deploy/mysql/upgrade-demo-acl.sql
--- 存量 MySQL 数据卷增量迁移（2026-08-03）：ACL 规则/用户入库
--- 适用：数据卷已初始化、docker-entrypoint-initdb.d 不会再执行的存量部署。
--- 全新数据卷由 server/src/main/resources/db/schema.sql 直接覆盖，无需本脚本。
--- 幂等：可重复执行。
+-- Development-only sample ACL rules and users for the current Studio schema.
--
--- 用法（远程容器内执行）：
+-- Prerequisite: initialize the database with
server/src/main/resources/db/schema.sql first.
+-- Run upgrade-demo-instance.sql first so every scope and cluster name refers
to a visible demo instance.
+-- This script does not create or alter schema objects and must not be used as
a schema migration.
+-- It is safe to rerun: users use schema unique keys and rules use their full
logical identity.
+--
+-- Usage:
-- docker exec -i rocketmq-studio-mysql mysql -uroot -pstudio123 rocketmq <
upgrade-demo-acl.sql
--- 固定连接编码，防止 mysql 客户端以 latin1 解释 UTF-8 字节导致中文双重编码
SET NAMES utf8mb4;
--- 1. ACL 规则表
-CREATE TABLE IF NOT EXISTS rmq_acl_rule (
- id VARCHAR(64) PRIMARY KEY,
- principal VARCHAR(128) NOT NULL,
- resource VARCHAR(255) NOT NULL,
- resource_type VARCHAR(32) COMMENT 'Topic/Group/Cluster',
- resource_pattern VARCHAR(32) COMMENT 'LITERAL/PREFIX',
- actions VARCHAR(128) COMMENT '逗号分隔：PUB/SUB/ALL',
- decision VARCHAR(16) COMMENT 'ALLOW/DENY',
- scope VARCHAR(64) COMMENT '生效范围（集群名/实例 id）',
- acl_version VARCHAR(16) COMMENT '1.0/2.0',
- created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
- updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- INDEX idx_principal (principal)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+-- Rule IDs are numeric auto-increment values. Use logical-key checks instead
of carrying the obsolete
+-- acl-001-style string IDs from the pre-standardization schema.
+INSERT INTO rmq_acl_rule
+ (principal, resource, resource_type, resource_pattern, actions, decision,
scope, acl_version)
+SELECT 'user-order-service', 'order_*', 'Topic', 'PREFIX', 'PUB,SUB', 'ALLOW',
'instance-proxy-1', '2.0'
+WHERE NOT EXISTS (
+ SELECT 1 FROM rmq_acl_rule
+ WHERE principal = 'user-order-service' AND resource = 'order_*' AND
resource_type = 'Topic'
+ AND resource_pattern = 'PREFIX' AND scope = 'instance-proxy-1' AND
acl_version = '2.0'
+);
+
+INSERT INTO rmq_acl_rule
+ (principal, resource, resource_type, resource_pattern, actions, decision,
scope, acl_version)
+SELECT 'user-payment-service', 'payment_*', 'Topic', 'PREFIX', 'PUB,SUB',
'ALLOW', 'instance-proxy-1', '2.0'
+WHERE NOT EXISTS (
+ SELECT 1 FROM rmq_acl_rule
+ WHERE principal = 'user-payment-service' AND resource = 'payment_*' AND
resource_type = 'Topic'
+ AND resource_pattern = 'PREFIX' AND scope = 'instance-proxy-1' AND
acl_version = '2.0'
+);
--- 2. ACL 用户表（secret_key 为 base64 编码后的密码，禁止明文存储）
-CREATE TABLE IF NOT EXISTS rmq_acl_user (
- id VARCHAR(64) PRIMARY KEY,
- username VARCHAR(128) NOT NULL,
- access_key VARCHAR(255) NOT NULL,
- secret_key VARCHAR(512) NOT NULL COMMENT 'base64 编码的密码',
- admin TINYINT(1) DEFAULT 0,
- clusters VARCHAR(1024) COMMENT '逗号分隔的集群/实例 id',
- white_remote_address VARCHAR(255) COMMENT 'plain access 账号 IP 白名单，空表示不限制',
- created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
- updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- UNIQUE KEY uk_username (username)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+INSERT INTO rmq_acl_rule
+ (principal, resource, resource_type, resource_pattern, actions, decision,
scope, acl_version)
+SELECT 'user-admin', '*', 'Cluster', 'LITERAL', 'ALL', 'ALLOW',
'instance-proxy-1', '2.0'
+WHERE NOT EXISTS (
+ SELECT 1 FROM rmq_acl_rule
+ WHERE principal = 'user-admin' AND resource = '*' AND resource_type =
'Cluster'
+ AND resource_pattern = 'LITERAL' AND scope = 'instance-proxy-1' AND
acl_version = '2.0'
+);
--- 2.1 已建表的数据卷补列（幂等）：rmq_acl_user.white_remote_address
-SET @schema_name := DATABASE();
-SET @white_remote_address_column_exists := (
- SELECT COUNT(*)
- FROM information_schema.columns
- WHERE table_schema = @schema_name
- AND table_name = 'rmq_acl_user'
- AND column_name = 'white_remote_address'
+INSERT INTO rmq_acl_rule
+ (principal, resource, resource_type, resource_pattern, actions, decision,
scope, acl_version)
+SELECT 'user-log-collector', 'audit_operation_log', 'Topic', 'LITERAL', 'SUB',
'ALLOW', 'instance-direct-2', '1.0'
+WHERE NOT EXISTS (
+ SELECT 1 FROM rmq_acl_rule
+ WHERE principal = 'user-log-collector' AND resource = 'audit_operation_log'
AND resource_type = 'Topic'
+ AND resource_pattern = 'LITERAL' AND scope = 'instance-direct-2' AND
acl_version = '1.0'
);
-SET @white_remote_address_sql := IF(@white_remote_address_column_exists = 0,
- "ALTER TABLE rmq_acl_user ADD COLUMN white_remote_address VARCHAR(255)
COMMENT 'plain access 账号 IP 白名单，空表示不限制' AFTER clusters",
- 'SELECT 1');
-PREPARE white_remote_address_statement FROM @white_remote_address_sql;
-EXECUTE white_remote_address_statement;
-DEALLOCATE PREPARE white_remote_address_statement;
--- 3. 规则种子（与 schema.sql 一致）
-INSERT IGNORE INTO rmq_acl_rule
- (id, principal, resource, resource_type, resource_pattern, actions,
decision, scope, acl_version)
-VALUES
- ('acl-001', 'user-order-service', 'order_*',
'Topic', 'PREFIX', 'PUB,SUB', 'ALLOW', 'instance-proxy-1', '2.0'),
- ('acl-002', 'user-payment-service', 'payment_*',
'Topic', 'PREFIX', 'PUB,SUB', 'ALLOW', 'instance-proxy-1', '2.0'),
- ('acl-003', 'user-admin', '*',
'Cluster', 'LITERAL', 'ALL', 'ALLOW', 'instance-proxy-1', '2.0'),
- ('acl-004', 'user-log-collector', 'audit_operation_log',
'Topic', 'LITERAL', 'SUB', 'ALLOW', 'instance-direct-2', '1.0'),
- ('acl-005', 'user-order-service', 'GID_fulfillment_*',
'Group', 'PREFIX', 'SUB', 'ALLOW', 'instance-proxy-1', '2.0'),
- ('acl-006', 'user-inventory-service',
'inventory_deduct_command','Topic', 'LITERAL', 'PUB,SUB', 'ALLOW',
'instance-proxy-1', '2.0'),
- ('acl-007', 'user-guest', 'payment_result_notify',
'Topic', 'LITERAL', 'PUB,SUB', 'DENY', 'instance-proxy-1', '1.0'),
- ('acl-008', 'user-notification-service', 'sms_send_command',
'Topic', 'LITERAL', 'PUB', 'ALLOW', 'instance-proxy-2', '2.0'),
- ('acl-009', 'user-risk-control', 'risk_event_alert',
'Topic', 'LITERAL', 'SUB', 'ALLOW', 'instance-direct-2', '1.0'),
- ('acl-010', 'user-guest', '*',
'Cluster', 'LITERAL', 'PUB', 'DENY', 'instance-proxy-2', '2.0'),
- ('acl-011', 'user-payment-service', 'GID_payment_*',
'Group', 'PREFIX', 'SUB', 'ALLOW', 'instance-proxy-1', '2.0'),
- ('acl-012', 'user-monitor', 'user_behavior_log',
'Topic', 'LITERAL', 'SUB', 'ALLOW', 'instance-proxy-3', '1.0'),
- ('acl-013', 'user-ai-service', 'click_stream_etl',
'Topic', 'LITERAL', 'PUB,SUB', 'ALLOW', 'instance-proxy-3', '2.0');
+INSERT INTO rmq_acl_rule
+ (principal, resource, resource_type, resource_pattern, actions, decision,
scope, acl_version)
+SELECT 'user-order-service', 'GID_fulfillment_*', 'Group', 'PREFIX', 'SUB',
'ALLOW', 'instance-proxy-1', '2.0'
+WHERE NOT EXISTS (
+ SELECT 1 FROM rmq_acl_rule
+ WHERE principal = 'user-order-service' AND resource = 'GID_fulfillment_*'
AND resource_type = 'Group'
+ AND resource_pattern = 'PREFIX' AND scope = 'instance-proxy-1' AND
acl_version = '2.0'
+);
+
+INSERT INTO rmq_acl_rule
+ (principal, resource, resource_type, resource_pattern, actions, decision,
scope, acl_version)
+SELECT 'user-inventory-service', 'inventory_deduct_command', 'Topic',
'LITERAL', 'PUB,SUB', 'ALLOW', 'instance-proxy-1', '2.0'
+WHERE NOT EXISTS (
+ SELECT 1 FROM rmq_acl_rule
+ WHERE principal = 'user-inventory-service' AND resource =
'inventory_deduct_command' AND resource_type = 'Topic'
+ AND resource_pattern = 'LITERAL' AND scope = 'instance-proxy-1' AND
acl_version = '2.0'
+);
+
+INSERT INTO rmq_acl_rule
+ (principal, resource, resource_type, resource_pattern, actions, decision,
scope, acl_version)
+SELECT 'user-guest', 'payment_result_notify', 'Topic', 'LITERAL', 'PUB,SUB',
'DENY', 'instance-proxy-1', '1.0'
+WHERE NOT EXISTS (
+ SELECT 1 FROM rmq_acl_rule
+ WHERE principal = 'user-guest' AND resource = 'payment_result_notify' AND
resource_type = 'Topic'
+ AND resource_pattern = 'LITERAL' AND scope = 'instance-proxy-1' AND
acl_version = '1.0'
+);
+
+INSERT INTO rmq_acl_rule
+ (principal, resource, resource_type, resource_pattern, actions, decision,
scope, acl_version)
+SELECT 'user-notification-service', 'sms_send_command', 'Topic', 'LITERAL',
'PUB', 'ALLOW', 'instance-proxy-2', '2.0'
+WHERE NOT EXISTS (
+ SELECT 1 FROM rmq_acl_rule
+ WHERE principal = 'user-notification-service' AND resource =
'sms_send_command' AND resource_type = 'Topic'
+ AND resource_pattern = 'LITERAL' AND scope = 'instance-proxy-2' AND
acl_version = '2.0'
+);
+
+INSERT INTO rmq_acl_rule
+ (principal, resource, resource_type, resource_pattern, actions, decision,
scope, acl_version)
+SELECT 'user-risk-control', 'risk_event_alert', 'Topic', 'LITERAL', 'SUB',
'ALLOW', 'instance-direct-2', '1.0'
+WHERE NOT EXISTS (
+ SELECT 1 FROM rmq_acl_rule
+ WHERE principal = 'user-risk-control' AND resource = 'risk_event_alert' AND
resource_type = 'Topic'
+ AND resource_pattern = 'LITERAL' AND scope = 'instance-direct-2' AND
acl_version = '1.0'
+);
+
+INSERT INTO rmq_acl_rule
+ (principal, resource, resource_type, resource_pattern, actions, decision,
scope, acl_version)
+SELECT 'user-guest', '*', 'Cluster', 'LITERAL', 'PUB', 'DENY',
'instance-proxy-2', '2.0'
+WHERE NOT EXISTS (
+ SELECT 1 FROM rmq_acl_rule
+ WHERE principal = 'user-guest' AND resource = '*' AND resource_type =
'Cluster'
+ AND resource_pattern = 'LITERAL' AND scope = 'instance-proxy-2' AND
acl_version = '2.0'
+);
+
+INSERT INTO rmq_acl_rule
+ (principal, resource, resource_type, resource_pattern, actions, decision,
scope, acl_version)
+SELECT 'user-payment-service', 'GID_payment_*', 'Group', 'PREFIX', 'SUB',
'ALLOW', 'instance-proxy-1', '2.0'
+WHERE NOT EXISTS (
+ SELECT 1 FROM rmq_acl_rule
+ WHERE principal = 'user-payment-service' AND resource = 'GID_payment_*' AND
resource_type = 'Group'
+ AND resource_pattern = 'PREFIX' AND scope = 'instance-proxy-1' AND
acl_version = '2.0'
+);
+
+INSERT INTO rmq_acl_rule
+ (principal, resource, resource_type, resource_pattern, actions, decision,
scope, acl_version)
+SELECT 'user-monitor', 'user_behavior_log', 'Topic', 'LITERAL', 'SUB',
'ALLOW', 'instance-proxy-3', '1.0'
+WHERE NOT EXISTS (
+ SELECT 1 FROM rmq_acl_rule
+ WHERE principal = 'user-monitor' AND resource = 'user_behavior_log' AND
resource_type = 'Topic'
+ AND resource_pattern = 'LITERAL' AND scope = 'instance-proxy-3' AND
acl_version = '1.0'
+);
+
+INSERT INTO rmq_acl_rule
+ (principal, resource, resource_type, resource_pattern, actions, decision,
scope, acl_version)
+SELECT 'user-ai-service', 'click_stream_etl', 'Topic', 'LITERAL', 'PUB,SUB',
'ALLOW', 'instance-proxy-3', '2.0'
+WHERE NOT EXISTS (
+ SELECT 1 FROM rmq_acl_rule
+ WHERE principal = 'user-ai-service' AND resource = 'click_stream_etl' AND
resource_type = 'Topic'
+ AND resource_pattern = 'LITERAL' AND scope = 'instance-proxy-3' AND
acl_version = '2.0'
+);
--- 4. 用户种子（secret_key 为密码的 base64 编码）
+-- User IDs are numeric auto-increment values. Access keys and usernames are
unique in the
+-- canonical schema, so INSERT IGNORE preserves credentials an operator has
already changed.
INSERT IGNORE INTO rmq_acl_user
- (id, username, access_key, secret_key, admin, clusters)
+ (username, access_key, secret_key, admin, clusters)
VALUES
- ('u-001', 'user-admin', 'AKSTUDIOadmin0001',
'QWRtaW5AU3R1ZGlvIzIwMjY=', 1,
+ ('user-admin', 'AKSTUDIOadmin0001', 'QWRtaW5AU3R1ZGlvIzIwMjY=', 1,
'instance-proxy-1,instance-proxy-2,instance-proxy-3,instance-direct-1,instance-direct-2'),
- ('u-002', 'user-order-service', 'AKSTUDIOordr0002',
'T3JkZXJTdmNAMjAyNiNQcm9k', 0, 'instance-proxy-1'),
- ('u-003', 'user-payment-service', 'AKSTUDIOpaym0003',
'UGF5U3ZjQDIwMjYjUHJvZA==', 0, 'instance-proxy-1'),
- ('u-004', 'user-log-collector', 'AKSTUDIOlogs0004',
'TG9nQ29sbGVjdEAyMDI2I09wcw==', 0, 'instance-direct-2'),
- ('u-005', 'user-guest', 'AKSTUDIOgues0005',
'R3Vlc3RAMjAyNiNSZWFk', 0, 'instance-proxy-2'),
- ('u-006', 'user-inventory-service', 'AKSTUDIOinvn0006',
'SW52U3ZjQDIwMjYjUHJvZA==', 0, 'instance-proxy-1'),
- ('u-007', 'user-notification-service', 'AKSTUDIONtfy0007',
'Tm90aWZ5U3ZjQDIwMjYjTXNn', 0, 'instance-proxy-2'),
- ('u-008', 'user-monitor', 'AKSTUDIOmonr0008',
'TW9uaXRvckAyMDI2I09icw==', 0,
+ ('user-order-service', 'AKSTUDIOordr0002', 'T3JkZXJTdmNAMjAyNiNQcm9k', 0,
'instance-proxy-1'),
+ ('user-payment-service', 'AKSTUDIOpaym0003', 'UGF5U3ZjQDIwMjYjUHJvZA==', 0,
'instance-proxy-1'),
+ ('user-log-collector', 'AKSTUDIOlogs0004', 'TG9nQ29sbGVjdEAyMDI2I09wcw==',
0, 'instance-direct-2'),
+ ('user-guest', 'AKSTUDIOgues0005', 'R3Vlc3RAMjAyNiNSZWFk', 0,
'instance-proxy-2'),
+ ('user-inventory-service', 'AKSTUDIOinvn0006', 'SW52U3ZjQDIwMjYjUHJvZA==',
0, 'instance-proxy-1'),
+ ('user-notification-service', 'AKSTUDIONtfy0007',
'Tm90aWZ5U3ZjQDIwMjYjTXNn', 0, 'instance-proxy-2'),
+ ('user-monitor', 'AKSTUDIOmonr0008', 'TW9uaXRvckAyMDI2I09icw==', 0,
'instance-proxy-3,instance-direct-2');
diff --git a/deploy/mysql/upgrade-demo-alert.sql
b/deploy/mysql/upgrade-demo-alert.sql
index de524ade4..e946b0852 100644
--- a/deploy/mysql/upgrade-demo-alert.sql
+++ b/deploy/mysql/upgrade-demo-alert.sql
@@ -1,7 +1,7 @@
-- deploy/mysql/upgrade-demo-alert.sql
--- 存量 MySQL 数据卷增量迁移：告警规则与系统告警入库（rmq_alert_rule / rmq_system_alert）
--- 适用：数据卷已初始化、docker-entrypoint-initdb.d 不会再执行的存量部署。
--- 全新数据卷由 server/src/main/resources/db/schema.sql 直接覆盖，无需本脚本。
+-- Compatibility helper for deployments that predate the alert tables.
+-- Existing deployments must otherwise follow the canonical schema in
+-- server/src/main/resources/db/schema.sql. This helper only creates missing
alert tables.
-- 幂等：可重复执行。
--
-- 用法（远程容器内执行）：
@@ -11,7 +11,9 @@
SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS rmq_alert_rule (
- id VARCHAR(64) PRIMARY KEY,
+ `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
+ `gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
+ `gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP COMMENT '修改时间',
name VARCHAR(128) NOT NULL,
metric VARCHAR(128),
operator VARCHAR(16),
@@ -25,19 +27,19 @@ CREATE TABLE IF NOT EXISTS rmq_alert_rule (
broker_name VARCHAR(128),
cluster_name VARCHAR(128),
severity VARCHAR(32),
- created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
- updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
+ PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS rmq_system_alert (
- id VARCHAR(64) PRIMARY KEY,
+ `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
+ `gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
+ `gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP COMMENT '修改时间',
level VARCHAR(32),
title VARCHAR(255),
description TEXT,
time DATETIME,
acknowledged TINYINT(1) DEFAULT 0,
- created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
- updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+ PRIMARY KEY (`id`),
INDEX idx_level (level),
INDEX idx_acknowledged (acknowledged)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
diff --git a/deploy/mysql/upgrade-demo-instance.sql
b/deploy/mysql/upgrade-demo-instance.sql
index 493bdba7e..240203973 100644
--- a/deploy/mysql/upgrade-demo-instance.sql
+++ b/deploy/mysql/upgrade-demo-instance.sql
@@ -1,132 +1,223 @@
-- deploy/mysql/upgrade-demo-instance.sql
--- 存量 MySQL 数据卷增量迁移（2026-08-03）：实例持久化 + topic/group 增加 instance_id
--- 适用：数据卷已初始化、docker-entrypoint-initdb.d 不会再执行的存量部署。
--- 全新数据卷由 server/src/main/resources/db/schema.sql 直接覆盖，无需本脚本。
--- 幂等：可重复执行。
+-- Development-only sample instances, topics and consumer groups for the
current Studio schema.
--
--- 用法（远程容器内执行）：
+-- Prerequisite: initialize the database with
server/src/main/resources/db/schema.sql first.
+-- This script does not create or alter schema objects and must not be used as
a schema migration.
+-- It is safe to rerun: instance names and (cluster_id, instance_id, name)
metadata keys are unique.
+--
+-- Usage:
-- docker exec -i rocketmq-studio-mysql mysql -uroot -pstudio123 rocketmq <
upgrade-demo-instance.sql
--- 固定连接编码，防止 mysql 客户端以 latin1 解释 UTF-8 字节导致中文双重编码
SET NAMES utf8mb4;
--- 1. 实例注册表
-CREATE TABLE IF NOT EXISTS rmq_instance (
- id VARCHAR(64) PRIMARY KEY,
- name VARCHAR(128) NOT NULL,
- remark VARCHAR(255),
- type VARCHAR(32) NOT NULL COMMENT 'PROXY/PROXY_LOCAL/PROXY_CLUSTER/DIRECT',
- endpoint VARCHAR(512) NOT NULL,
- created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
- updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-
--- 2. rmq_topic / rmq_group 增加 instance_id 列与索引（MySQL 8.0 无 ADD COLUMN IF NOT
EXISTS，用 information_schema 判断）
-SET @sql = (SELECT IF(
- COUNT(*) > 0, 'SELECT ''rmq_topic.instance_id already exists'' AS msg',
- 'ALTER TABLE rmq_topic ADD COLUMN instance_id VARCHAR(64) COMMENT
''归属实例，引用 rmq_instance.id'' AFTER cluster_id, ADD INDEX idx_instance
(instance_id)')
- FROM information_schema.COLUMNS
- WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'rmq_topic' AND COLUMN_NAME
= 'instance_id');
-PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-
-SET @sql = (SELECT IF(
- COUNT(*) > 0, 'SELECT ''rmq_group.instance_id already exists'' AS msg',
- 'ALTER TABLE rmq_group ADD COLUMN instance_id VARCHAR(64) COMMENT
''归属实例，引用 rmq_instance.id'' AFTER cluster_id, ADD INDEX idx_instance
(instance_id)')
- FROM information_schema.COLUMNS
- WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'rmq_group' AND COLUMN_NAME
= 'instance_id');
-PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-
--- 3. 默认 5 个实例（幂等）
-INSERT IGNORE INTO rmq_instance (id, name, remark, type, endpoint) VALUES
- ('instance-direct-1', 'instance-direct-1', '直连实例 1，交易核心链路（NameServer 直连）',
'DIRECT', '10.0.1.11:9876'),
- ('instance-direct-2', 'instance-direct-2', '直连实例 2，风控与审计链路（NameServer 直连）',
'DIRECT', '10.0.1.12:9876'),
- ('instance-proxy-1', 'instance-proxy-1', 'Proxy 实例 1，电商交易主链路',
'PROXY_CLUSTER', '10.0.2.21:8080'),
- ('instance-proxy-2', 'instance-proxy-2', 'Proxy 实例 2，营销与会员链路',
'PROXY_CLUSTER', '10.0.2.22:8080'),
- ('instance-proxy-3', 'instance-proxy-3', 'Proxy 实例 3，物流与大数据链路',
'PROXY_CLUSTER', '10.0.2.23:8080');
-
--- 4. 旧种子数据回填 instance_id（旧部署里这 9 个 topic、8 个 group 已存在，INSERT IGNORE 不会更新它们）
-UPDATE rmq_topic SET instance_id = 'instance-proxy-1'
- WHERE instance_id IS NULL AND name IN (
- 'order_create_event', 'order_status_change', 'order_timeout_cancel',
- 'payment_result_notify', 'inventory_deduct_command');
-UPDATE rmq_topic SET instance_id = 'instance-proxy-2'
- WHERE instance_id IS NULL AND name IN ('marketing_coupon_issue');
-UPDATE rmq_topic SET instance_id = 'instance-proxy-3'
- WHERE instance_id IS NULL AND name IN ('logistics_tracking_update',
'settlement_daily_archive');
-UPDATE rmq_topic SET instance_id = 'instance-direct-2'
- WHERE instance_id IS NULL AND name IN ('risk_control_audit');
-
-UPDATE rmq_group SET instance_id = 'instance-proxy-1'
- WHERE instance_id IS NULL AND name IN (
- 'GID_fulfillment_order', 'GID_inventory_deduct', 'GID_payment_result');
-UPDATE rmq_group SET instance_id = 'instance-proxy-2'
- WHERE instance_id IS NULL AND name IN ('GID_marketing_coupon');
-UPDATE rmq_group SET instance_id = 'instance-proxy-3'
- WHERE instance_id IS NULL AND name IN (
- 'GID_logistics_tracking', 'GID_settlement_archive',
'GID_bi_realtime_report');
-UPDATE rmq_group SET instance_id = 'instance-direct-2'
- WHERE instance_id IS NULL AND name IN ('studio-trace-consumer');
-
--- 5. 补充新种子 topic/group（与 schema.sql 一致，已存在的行被 IGNORE 跳过）
-INSERT IGNORE INTO rmq_topic
- (cluster_id, instance_id, name, topic_type, read_queue_nums,
write_queue_nums, perm, remark, status, created_by)
+-- The application owns numeric auto-increment primary keys. The stable API
identifier is
+-- rmq_instance.name, so demo rows omit id and resolve child instance_id
values by name.
+INSERT IGNORE INTO rmq_instance
+ (name, remark, type, endpoint, vendor)
VALUES
- ('rocketmq-studio', 'instance-proxy-1', 'refund_apply_event',
'NORMAL', 4, 4, 6,
- '退款申请事件，客服与财务系统订阅', 'ACTIVE', 'seed'),
- ('rocketmq-studio', 'instance-proxy-1', 'cart_sync_event',
'NORMAL', 4, 4, 6,
- '购物车多端同步事件', 'ACTIVE', 'seed'),
- ('rocketmq-studio', 'instance-proxy-1', 'trade_close_archive',
'NORMAL', 2, 2, 4,
- '交易关单归档，只读供对账回溯', 'ACTIVE', 'seed'),
- ('rocketmq-studio', 'instance-proxy-2', 'marketing_campaign_push',
'NORMAL', 8, 8, 6,
- '大促活动 push 触达，按人群包分批投递', 'ACTIVE', 'seed'),
- ('rocketmq-studio', 'instance-proxy-2', 'member_register_event',
'NORMAL', 4, 4, 6,
- '新会员注册事件，积分与权益系统订阅', 'ACTIVE', 'seed'),
- ('rocketmq-studio', 'instance-proxy-2', 'member_points_change', 'FIFO',
4, 4, 6,
- '会员积分变动，按会员 ID 分区保序', 'ACTIVE', 'seed'),
- ('rocketmq-studio', 'instance-proxy-2', 'member_level_upgrade',
'DELAY', 4, 4, 6,
- '会员升级权益延迟发放', 'ACTIVE', 'seed'),
- ('rocketmq-studio', 'instance-proxy-2', 'sms_send_command',
'NORMAL', 8, 8, 6,
- '短信下发指令，网关限流后消费', 'ACTIVE', 'seed'),
- ('rocketmq-studio', 'instance-proxy-3', 'logistics_dispatch_order', 'FIFO',
8, 8, 6,
- '运单调度指令，同单有序', 'ACTIVE', 'seed'),
- ('rocketmq-studio', 'instance-proxy-3', 'bi_realtime_report',
'NORMAL', 16, 16, 6,
- '实时报表数据流，BI 大屏消费', 'ACTIVE', 'seed'),
- ('rocketmq-studio', 'instance-proxy-3', 'user_behavior_log',
'NORMAL', 16, 16, 6,
- '用户行为埋点日志，离线分析入湖', 'ACTIVE', 'seed'),
- ('rocketmq-studio', 'instance-proxy-3', 'click_stream_etl',
'NORMAL', 8, 8, 6,
- '点击流 ETL 中间结果', 'ACTIVE', 'seed'),
- ('rocketmq-studio', 'instance-direct-1', 'trade_core_order_flow', 'FIFO',
8, 8, 6,
- '交易核心订单流水，直连低延迟链路', 'ACTIVE', 'seed'),
- ('rocketmq-studio', 'instance-direct-1', 'payment_channel_callback',
'NORMAL', 8, 8, 6,
- '支付渠道回调通知', 'ACTIVE', 'seed'),
- ('rocketmq-studio', 'instance-direct-1', 'account_ledger_entry',
'TRANSACTION', 8, 8, 6,
- '账户记账分录，与账务落库同事务', 'ACTIVE', 'seed'),
- ('rocketmq-studio', 'instance-direct-1', 'ledger_reconcile_task',
'DELAY', 4, 4, 6,
- '对账任务延迟触发，T+1 凌晨执行', 'ACTIVE', 'seed'),
- ('rocketmq-studio', 'instance-direct-2', 'risk_event_alert',
'NORMAL', 4, 4, 6,
- '风控命中事件告警，实时推送处置平台', 'ACTIVE', 'seed'),
- ('rocketmq-studio', 'instance-direct-2', 'audit_operation_log',
'NORMAL', 8, 8, 6,
- '操作审计日志，合规留存 180 天', 'ACTIVE', 'seed'),
- ('rocketmq-studio', 'instance-direct-2', 'compliance_report_daily',
'DELAY', 2, 2, 6,
- '合规日报延迟生成任务', 'ACTIVE', 'seed');
-
-INSERT IGNORE INTO rmq_group
+ ('instance-direct-1', '直连实例 1，交易核心链路（NameServer 直连）', 'DIRECT',
'10.0.1.11:9876', 'APACHE'),
+ ('instance-direct-2', '直连实例 2，风控与审计链路（NameServer 直连）', 'DIRECT',
'10.0.1.12:9876', 'APACHE'),
+ ('instance-proxy-1', 'Proxy 实例 1，电商交易主链路', 'PROXY_CLUSTER',
'10.0.2.21:8080', 'APACHE'),
+ ('instance-proxy-2', 'Proxy 实例 2，营销与会员链路', 'PROXY_CLUSTER',
'10.0.2.22:8080', 'APACHE'),
+ ('instance-proxy-3', 'Proxy 实例 3，物流与大数据链路', 'PROXY_CLUSTER',
'10.0.2.23:8080', 'APACHE');
+
+-- Topic metadata keeps the stable instance name in instance_id, matching the
application contract.
+INSERT IGNORE INTO rmq_instance_topic
+ (cluster_id, instance_id, name, topic_type, read_queue_nums,
write_queue_nums, perm, remark, status, created_by)
+SELECT 'rocketmq-studio', name, 'refund_apply_event', 'NORMAL', 4, 4, 6,
+ '退款申请事件，客服与财务系统订阅', 'ACTIVE', 'seed'
+FROM rmq_instance WHERE name = 'instance-proxy-1';
+
+INSERT IGNORE INTO rmq_instance_topic
+ (cluster_id, instance_id, name, topic_type, read_queue_nums,
write_queue_nums, perm, remark, status, created_by)
+SELECT 'rocketmq-studio', name, 'cart_sync_event', 'NORMAL', 4, 4, 6,
+ '购物车多端同步事件', 'ACTIVE', 'seed'
+FROM rmq_instance WHERE name = 'instance-proxy-1';
+
+INSERT IGNORE INTO rmq_instance_topic
+ (cluster_id, instance_id, name, topic_type, read_queue_nums,
write_queue_nums, perm, remark, status, created_by)
+SELECT 'rocketmq-studio', name, 'trade_close_archive', 'NORMAL', 2, 2, 4,
+ '交易关单归档，只读供对账回溯', 'ACTIVE', 'seed'
+FROM rmq_instance WHERE name = 'instance-proxy-1';
+
+INSERT IGNORE INTO rmq_instance_topic
+ (cluster_id, instance_id, name, topic_type, read_queue_nums,
write_queue_nums, perm, remark, status, created_by)
+SELECT 'rocketmq-studio', name, 'marketing_campaign_push', 'NORMAL', 8, 8, 6,
+ '大促活动 push 触达，按人群包分批投递', 'ACTIVE', 'seed'
+FROM rmq_instance WHERE name = 'instance-proxy-2';
+
+INSERT IGNORE INTO rmq_instance_topic
+ (cluster_id, instance_id, name, topic_type, read_queue_nums,
write_queue_nums, perm, remark, status, created_by)
+SELECT 'rocketmq-studio', name, 'member_register_event', 'NORMAL', 4, 4, 6,
+ '新会员注册事件，积分与权益系统订阅', 'ACTIVE', 'seed'
+FROM rmq_instance WHERE name = 'instance-proxy-2';
+
+INSERT IGNORE INTO rmq_instance_topic
+ (cluster_id, instance_id, name, topic_type, read_queue_nums,
write_queue_nums, perm, remark, status, created_by)
+SELECT 'rocketmq-studio', name, 'member_points_change', 'FIFO', 4, 4, 6,
+ '会员积分变动，按会员 ID 分区保序', 'ACTIVE', 'seed'
+FROM rmq_instance WHERE name = 'instance-proxy-2';
+
+INSERT IGNORE INTO rmq_instance_topic
+ (cluster_id, instance_id, name, topic_type, read_queue_nums,
write_queue_nums, perm, remark, status, created_by)
+SELECT 'rocketmq-studio', name, 'member_level_upgrade', 'DELAY', 4, 4, 6,
+ '会员升级权益延迟发放', 'ACTIVE', 'seed'
+FROM rmq_instance WHERE name = 'instance-proxy-2';
+
+INSERT IGNORE INTO rmq_instance_topic
+ (cluster_id, instance_id, name, topic_type, read_queue_nums,
write_queue_nums, perm, remark, status, created_by)
+SELECT 'rocketmq-studio', name, 'sms_send_command', 'NORMAL', 8, 8, 6,
+ '短信下发指令，网关限流后消费', 'ACTIVE', 'seed'
+FROM rmq_instance WHERE name = 'instance-proxy-2';
+
+INSERT IGNORE INTO rmq_instance_topic
+ (cluster_id, instance_id, name, topic_type, read_queue_nums,
write_queue_nums, perm, remark, status, created_by)
+SELECT 'rocketmq-studio', name, 'logistics_dispatch_order', 'FIFO', 8, 8, 6,
+ '运单调度指令，同单有序', 'ACTIVE', 'seed'
+FROM rmq_instance WHERE name = 'instance-proxy-3';
+
+INSERT IGNORE INTO rmq_instance_topic
+ (cluster_id, instance_id, name, topic_type, read_queue_nums,
write_queue_nums, perm, remark, status, created_by)
+SELECT 'rocketmq-studio', name, 'bi_realtime_report', 'NORMAL', 16, 16, 6,
+ '实时报表数据流，BI 大屏消费', 'ACTIVE', 'seed'
+FROM rmq_instance WHERE name = 'instance-proxy-3';
+
+INSERT IGNORE INTO rmq_instance_topic
+ (cluster_id, instance_id, name, topic_type, read_queue_nums,
write_queue_nums, perm, remark, status, created_by)
+SELECT 'rocketmq-studio', name, 'user_behavior_log', 'NORMAL', 16, 16, 6,
+ '用户行为埋点日志，离线分析入湖', 'ACTIVE', 'seed'
+FROM rmq_instance WHERE name = 'instance-proxy-3';
+
+INSERT IGNORE INTO rmq_instance_topic
+ (cluster_id, instance_id, name, topic_type, read_queue_nums,
write_queue_nums, perm, remark, status, created_by)
+SELECT 'rocketmq-studio', name, 'click_stream_etl', 'NORMAL', 8, 8, 6,
+ '点击流 ETL 中间结果', 'ACTIVE', 'seed'
+FROM rmq_instance WHERE name = 'instance-proxy-3';
+
+INSERT IGNORE INTO rmq_instance_topic
+ (cluster_id, instance_id, name, topic_type, read_queue_nums,
write_queue_nums, perm, remark, status, created_by)
+SELECT 'rocketmq-studio', name, 'trade_core_order_flow', 'FIFO', 8, 8, 6,
+ '交易核心订单流水，直连低延迟链路', 'ACTIVE', 'seed'
+FROM rmq_instance WHERE name = 'instance-direct-1';
+
+INSERT IGNORE INTO rmq_instance_topic
+ (cluster_id, instance_id, name, topic_type, read_queue_nums,
write_queue_nums, perm, remark, status, created_by)
+SELECT 'rocketmq-studio', name, 'payment_channel_callback', 'NORMAL', 8, 8, 6,
+ '支付渠道回调通知', 'ACTIVE', 'seed'
+FROM rmq_instance WHERE name = 'instance-direct-1';
+
+INSERT IGNORE INTO rmq_instance_topic
+ (cluster_id, instance_id, name, topic_type, read_queue_nums,
write_queue_nums, perm, remark, status, created_by)
+SELECT 'rocketmq-studio', name, 'account_ledger_entry', 'TRANSACTION', 8, 8, 6,
+ '账户记账分录，与账务落库同事务', 'ACTIVE', 'seed'
+FROM rmq_instance WHERE name = 'instance-direct-1';
+
+INSERT IGNORE INTO rmq_instance_topic
+ (cluster_id, instance_id, name, topic_type, read_queue_nums,
write_queue_nums, perm, remark, status, created_by)
+SELECT 'rocketmq-studio', name, 'ledger_reconcile_task', 'DELAY', 4, 4, 6,
+ '对账任务延迟触发，T+1 凌晨执行', 'ACTIVE', 'seed'
+FROM rmq_instance WHERE name = 'instance-direct-1';
+
+INSERT IGNORE INTO rmq_instance_topic
+ (cluster_id, instance_id, name, topic_type, read_queue_nums,
write_queue_nums, perm, remark, status, created_by)
+SELECT 'rocketmq-studio', name, 'risk_event_alert', 'NORMAL', 4, 4, 6,
+ '风控命中事件告警，实时推送处置平台', 'ACTIVE', 'seed'
+FROM rmq_instance WHERE name = 'instance-direct-2';
+
+INSERT IGNORE INTO rmq_instance_topic
+ (cluster_id, instance_id, name, topic_type, read_queue_nums,
write_queue_nums, perm, remark, status, created_by)
+SELECT 'rocketmq-studio', name, 'audit_operation_log', 'NORMAL', 8, 8, 6,
+ '操作审计日志，合规留存 180 天', 'ACTIVE', 'seed'
+FROM rmq_instance WHERE name = 'instance-direct-2';
+
+INSERT IGNORE INTO rmq_instance_topic
+ (cluster_id, instance_id, name, topic_type, read_queue_nums,
write_queue_nums, perm, remark, status, created_by)
+SELECT 'rocketmq-studio', name, 'compliance_report_daily', 'DELAY', 2, 2, 6,
+ '合规日报延迟生成任务', 'ACTIVE', 'seed'
+FROM rmq_instance WHERE name = 'instance-direct-2';
+
+-- Consumer-group metadata uses the same stable-name reference.
+INSERT IGNORE INTO rmq_instance_group
(cluster_id, instance_id, name, consume_type, message_model, max_retry,
status, created_by)
-VALUES
- ('rocketmq-studio', 'instance-proxy-1', 'GID_refund_process', 'PUSH',
'CLUSTERING', 8, 'ACTIVE', 'seed'),
- ('rocketmq-studio', 'instance-proxy-1', 'GID_cart_sync', 'PUSH',
'CLUSTERING', 3, 'ACTIVE', 'seed'),
- ('rocketmq-studio', 'instance-proxy-1', 'GID_trade_archive', 'PULL',
'CLUSTERING', 3, 'ACTIVE', 'seed'),
- ('rocketmq-studio', 'instance-proxy-2', 'GID_campaign_push', 'PUSH',
'CLUSTERING', 3, 'ACTIVE', 'seed'),
- ('rocketmq-studio', 'instance-proxy-2', 'GID_member_points', 'PUSH',
'CLUSTERING', 16, 'ACTIVE', 'seed'),
- ('rocketmq-studio', 'instance-proxy-2', 'GID_member_benefit', 'PUSH',
'CLUSTERING', 8, 'ACTIVE', 'seed'),
- ('rocketmq-studio', 'instance-proxy-2', 'GID_sms_gateway', 'PUSH',
'CLUSTERING', 5, 'ACTIVE', 'seed'),
- ('rocketmq-studio', 'instance-proxy-3', 'GID_logistics_dispatch', 'PUSH',
'CLUSTERING', 16, 'ACTIVE', 'seed'),
- ('rocketmq-studio', 'instance-proxy-3', 'GID_behavior_ingest', 'PUSH',
'CLUSTERING', 3, 'ACTIVE', 'seed'),
- ('rocketmq-studio', 'instance-proxy-3', 'GID_click_stream_etl', 'PUSH',
'CLUSTERING', 3, 'ACTIVE', 'seed'),
- ('rocketmq-studio', 'instance-direct-1', 'GID_trade_core_flow', 'PUSH',
'CLUSTERING', 16, 'ACTIVE', 'seed'),
- ('rocketmq-studio', 'instance-direct-1', 'GID_pay_channel_cb', 'PUSH',
'CLUSTERING', 16, 'ACTIVE', 'seed'),
- ('rocketmq-studio', 'instance-direct-1', 'GID_ledger_entry', 'PUSH',
'CLUSTERING', 16, 'ACTIVE', 'seed'),
- ('rocketmq-studio', 'instance-direct-1', 'GID_reconcile_task', 'PULL',
'CLUSTERING', 3, 'ACTIVE', 'seed'),
- ('rocketmq-studio', 'instance-direct-2', 'GID_risk_alert', 'PUSH',
'CLUSTERING', 8, 'ACTIVE', 'seed'),
- ('rocketmq-studio', 'instance-direct-2', 'GID_audit_archive', 'PUSH',
'CLUSTERING', 3, 'ACTIVE', 'seed'),
- ('rocketmq-studio', 'instance-direct-2', 'GID_compliance_daily', 'PULL',
'CLUSTERING', 1, 'ACTIVE', 'seed');
+SELECT 'rocketmq-studio', name, 'GID_refund_process', 'PUSH', 'CLUSTERING', 8,
'ACTIVE', 'seed'
+FROM rmq_instance WHERE name = 'instance-proxy-1';
+
+INSERT IGNORE INTO rmq_instance_group
+ (cluster_id, instance_id, name, consume_type, message_model, max_retry,
status, created_by)
+SELECT 'rocketmq-studio', name, 'GID_cart_sync', 'PUSH', 'CLUSTERING', 3,
'ACTIVE', 'seed'
+FROM rmq_instance WHERE name = 'instance-proxy-1';
+
+INSERT IGNORE INTO rmq_instance_group
+ (cluster_id, instance_id, name, consume_type, message_model, max_retry,
status, created_by)
+SELECT 'rocketmq-studio', name, 'GID_trade_archive', 'PULL', 'CLUSTERING', 3,
'ACTIVE', 'seed'
+FROM rmq_instance WHERE name = 'instance-proxy-1';
+
+INSERT IGNORE INTO rmq_instance_group
+ (cluster_id, instance_id, name, consume_type, message_model, max_retry,
status, created_by)
+SELECT 'rocketmq-studio', name, 'GID_campaign_push', 'PUSH', 'CLUSTERING', 3,
'ACTIVE', 'seed'
+FROM rmq_instance WHERE name = 'instance-proxy-2';
+
+INSERT IGNORE INTO rmq_instance_group
+ (cluster_id, instance_id, name, consume_type, message_model, max_retry,
status, created_by)
+SELECT 'rocketmq-studio', name, 'GID_member_points', 'PUSH', 'CLUSTERING', 16,
'ACTIVE', 'seed'
+FROM rmq_instance WHERE name = 'instance-proxy-2';
+
+INSERT IGNORE INTO rmq_instance_group
+ (cluster_id, instance_id, name, consume_type, message_model, max_retry,
status, created_by)
+SELECT 'rocketmq-studio', name, 'GID_member_benefit', 'PUSH', 'CLUSTERING', 8,
'ACTIVE', 'seed'
+FROM rmq_instance WHERE name = 'instance-proxy-2';
+
+INSERT IGNORE INTO rmq_instance_group
+ (cluster_id, instance_id, name, consume_type, message_model, max_retry,
status, created_by)
+SELECT 'rocketmq-studio', name, 'GID_sms_gateway', 'PUSH', 'CLUSTERING', 5,
'ACTIVE', 'seed'
+FROM rmq_instance WHERE name = 'instance-proxy-2';
+
+INSERT IGNORE INTO rmq_instance_group
+ (cluster_id, instance_id, name, consume_type, message_model, max_retry,
status, created_by)
+SELECT 'rocketmq-studio', name, 'GID_logistics_dispatch', 'PUSH',
'CLUSTERING', 16, 'ACTIVE', 'seed'
+FROM rmq_instance WHERE name = 'instance-proxy-3';
+
+INSERT IGNORE INTO rmq_instance_group
+ (cluster_id, instance_id, name, consume_type, message_model, max_retry,
status, created_by)
+SELECT 'rocketmq-studio', name, 'GID_behavior_ingest', 'PUSH', 'CLUSTERING',
3, 'ACTIVE', 'seed'
+FROM rmq_instance WHERE name = 'instance-proxy-3';
+
+INSERT IGNORE INTO rmq_instance_group
+ (cluster_id, instance_id, name, consume_type, message_model, max_retry,
status, created_by)
+SELECT 'rocketmq-studio', name, 'GID_click_stream_etl', 'PUSH', 'CLUSTERING',
3, 'ACTIVE', 'seed'
+FROM rmq_instance WHERE name = 'instance-proxy-3';
+
+INSERT IGNORE INTO rmq_instance_group
+ (cluster_id, instance_id, name, consume_type, message_model, max_retry,
status, created_by)
+SELECT 'rocketmq-studio', name, 'GID_trade_core_flow', 'PUSH', 'CLUSTERING',
16, 'ACTIVE', 'seed'
+FROM rmq_instance WHERE name = 'instance-direct-1';
+
+INSERT IGNORE INTO rmq_instance_group
+ (cluster_id, instance_id, name, consume_type, message_model, max_retry,
status, created_by)
+SELECT 'rocketmq-studio', name, 'GID_pay_channel_cb', 'PUSH', 'CLUSTERING',
16, 'ACTIVE', 'seed'
+FROM rmq_instance WHERE name = 'instance-direct-1';
+
+INSERT IGNORE INTO rmq_instance_group
+ (cluster_id, instance_id, name, consume_type, message_model, max_retry,
status, created_by)
+SELECT 'rocketmq-studio', name, 'GID_ledger_entry', 'PUSH', 'CLUSTERING', 16,
'ACTIVE', 'seed'
+FROM rmq_instance WHERE name = 'instance-direct-1';
+
+INSERT IGNORE INTO rmq_instance_group
+ (cluster_id, instance_id, name, consume_type, message_model, max_retry,
status, created_by)
+SELECT 'rocketmq-studio', name, 'GID_reconcile_task', 'PULL', 'CLUSTERING', 3,
'ACTIVE', 'seed'
+FROM rmq_instance WHERE name = 'instance-direct-1';
+
+INSERT IGNORE INTO rmq_instance_group
+ (cluster_id, instance_id, name, consume_type, message_model, max_retry,
status, created_by)
+SELECT 'rocketmq-studio', name, 'GID_risk_alert', 'PUSH', 'CLUSTERING', 8,
'ACTIVE', 'seed'
+FROM rmq_instance WHERE name = 'instance-direct-2';
+
+INSERT IGNORE INTO rmq_instance_group
+ (cluster_id, instance_id, name, consume_type, message_model, max_retry,
status, created_by)
+SELECT 'rocketmq-studio', name, 'GID_audit_archive', 'PUSH', 'CLUSTERING', 3,
'ACTIVE', 'seed'
+FROM rmq_instance WHERE name = 'instance-direct-2';
+
+INSERT IGNORE INTO rmq_instance_group
+ (cluster_id, instance_id, name, consume_type, message_model, max_retry,
status, created_by)
+SELECT 'rocketmq-studio', name, 'GID_compliance_daily', 'PULL', 'CLUSTERING',
1, 'ACTIVE', 'seed'
+FROM rmq_instance WHERE name = 'instance-direct-2';
diff --git
a/server/src/test/java/org/apache/rocketmq/studio/persistence/DemoDataSqlCompatibilityTest.java
b/server/src/test/java/org/apache/rocketmq/studio/persistence/DemoDataSqlCompatibilityTest.java
new file mode 100644
index 000000000..293024751
--- /dev/null
+++
b/server/src/test/java/org/apache/rocketmq/studio/persistence/DemoDataSqlCompatibilityTest.java
@@ -0,0 +1,147 @@
+/*
+ * 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.rocketmq.studio.persistence;
+
+import org.junit.jupiter.api.Test;
+import org.springframework.core.io.ByteArrayResource;
+import org.springframework.core.io.ClassPathResource;
+import org.springframework.jdbc.datasource.init.ScriptUtils;
+
+import java.io.IOException;
+import java.nio.charset.StandardCharsets;
+import java.nio.file.Files;
+import java.nio.file.Path;
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+
+import static org.assertj.core.api.Assertions.assertThat;
+
+class DemoDataSqlCompatibilityTest {
+
+ @Test
+ void demoScriptsMatchCanonicalSchemaAndRemainIdempotent() throws Exception
{
+ try (Connection connection = DriverManager.getConnection(
+
"jdbc:h2:mem:demo-data-sql;MODE=MySQL;DATABASE_TO_LOWER=TRUE;DB_CLOSE_DELAY=-1",
"sa", "")) {
+ ScriptUtils.executeSqlScript(connection, new
ClassPathResource("db/schema.sql"));
+
+ for (int pass = 0; pass < 2; pass++) {
+ executeDeployScript(connection, "upgrade-demo-instance.sql");
+ executeDeployScript(connection, "upgrade-demo-acl.sql");
+ }
+
+ assertThat(count(connection, "rmq_instance")).isEqualTo(5);
+ assertThat(count(connection, "rmq_instance_topic")).isEqualTo(19);
+ assertThat(count(connection, "rmq_instance_group")).isEqualTo(17);
+ assertThat(count(connection, "rmq_acl_rule")).isEqualTo(13);
+ assertThat(count(connection, "rmq_acl_user")).isEqualTo(8);
+ assertThat(countOrphanedMetadata(connection,
"rmq_instance_topic")).isZero();
+ assertThat(countOrphanedMetadata(connection,
"rmq_instance_group")).isZero();
+ assertThat(countNumericMetadataReferences(connection,
"rmq_instance_topic")).isZero();
+ assertThat(countNumericMetadataReferences(connection,
"rmq_instance_group")).isZero();
+ assertThat(allIdsAreNumeric(connection, "rmq_instance")).isTrue();
+ assertThat(allIdsAreNumeric(connection, "rmq_acl_rule")).isTrue();
+ assertThat(allIdsAreNumeric(connection, "rmq_acl_user")).isTrue();
+ }
+ }
+
+ @Test
+ void alertCompatibilityHelperCreatesCanonicalNumericTables() throws
Exception {
+ try (Connection connection = DriverManager.getConnection(
+
"jdbc:h2:mem:demo-alert-sql;MODE=MySQL;DATABASE_TO_LOWER=TRUE;DB_CLOSE_DELAY=-1",
"sa", "")) {
+ executeDeployScript(connection, "upgrade-demo-alert.sql");
+ executeDeployScript(connection, "upgrade-demo-alert.sql");
+
+ try (Statement statement = connection.createStatement()) {
+ statement.executeUpdate("INSERT INTO rmq_alert_rule (name,
enabled) VALUES ('demo-rule', TRUE)");
+ statement.executeUpdate("INSERT INTO rmq_system_alert (level,
title, acknowledged)"
+ + " VALUES ('warning', 'demo-alert', FALSE)");
+ }
+
+ assertThat(count(connection, "rmq_alert_rule")).isEqualTo(1);
+ assertThat(count(connection, "rmq_system_alert")).isEqualTo(1);
+ assertThat(allIdsAreNumeric(connection,
"rmq_alert_rule")).isTrue();
+ assertThat(allIdsAreNumeric(connection,
"rmq_system_alert")).isTrue();
+ assertThat(hasColumn(connection, "rmq_alert_rule",
"gmt_create")).isTrue();
+ assertThat(hasColumn(connection, "rmq_alert_rule",
"gmt_modified")).isTrue();
+ assertThat(hasColumn(connection, "rmq_alert_rule",
"created_at")).isFalse();
+ assertThat(hasColumn(connection, "rmq_system_alert",
"updated_at")).isFalse();
+ }
+ }
+
+ private static void executeDeployScript(Connection connection, String
filename) throws IOException {
+ String script = Files.readString(resolveDeployScript(filename),
StandardCharsets.UTF_8)
+ .replace("SET NAMES utf8mb4;", "");
+ ScriptUtils.executeSqlScript(connection,
+ new ByteArrayResource(script.getBytes(StandardCharsets.UTF_8),
filename));
+ }
+
+ private static Path resolveDeployScript(String filename) {
+ Path fromModule = Path.of("..", "deploy", "mysql", filename);
+ if (Files.isRegularFile(fromModule)) {
+ return fromModule;
+ }
+ Path fromRepository = Path.of("deploy", "mysql", filename);
+ assertThat(Files.isRegularFile(fromRepository))
+ .as("deploy SQL script %s must be available from the Maven
working directory", filename)
+ .isTrue();
+ return fromRepository;
+ }
+
+ private static long count(Connection connection, String table) throws
SQLException {
+ try (Statement statement = connection.createStatement();
+ ResultSet result = statement.executeQuery("SELECT COUNT(*) FROM "
+ table)) {
+ result.next();
+ return result.getLong(1);
+ }
+ }
+
+ private static long countOrphanedMetadata(Connection connection, String
table) throws SQLException {
+ try (Statement statement = connection.createStatement();
+ ResultSet result = statement.executeQuery("SELECT COUNT(*) FROM "
+ table
+ + " child LEFT JOIN rmq_instance parent ON parent.name =
child.instance_id"
+ + " WHERE parent.id IS NULL")) {
+ result.next();
+ return result.getLong(1);
+ }
+ }
+
+ private static long countNumericMetadataReferences(Connection connection,
String table) throws SQLException {
+ try (Statement statement = connection.createStatement();
+ ResultSet result = statement.executeQuery("SELECT COUNT(*) FROM "
+ table
+ + " child JOIN rmq_instance parent ON CAST(parent.id AS
VARCHAR) = child.instance_id")) {
+ result.next();
+ return result.getLong(1);
+ }
+ }
+
+ private static boolean allIdsAreNumeric(Connection connection, String
table) throws SQLException {
+ try (Statement statement = connection.createStatement();
+ ResultSet result = statement.executeQuery("SELECT COUNT(*) FROM "
+ table + " WHERE id <= 0")) {
+ result.next();
+ return result.getLong(1) == 0;
+ }
+ }
+
+ private static boolean hasColumn(Connection connection, String table,
String column) throws SQLException {
+ try (ResultSet columns = connection.getMetaData().getColumns(null,
null, table, column)) {
+ return columns.next();
+ }
+ }
+}
