AIRIOT智慧系统搭建平台经验交流

kzz

ws-data也更新了

kzz

新版本还是有内存泄漏，会导致平台重启。有发现吗。

kzz

flow-engine v4.14.8更新之后会因为缺少文件夹报错错，我手动解决了，但这需要反馈一下。
"ZoneInfo": "/app/configs/zoneinfo.zip"
}
�time=2026-06-30T17:01:23.938+08:00 level=INFO msg="服务启动: 运行模式=release,版本号=v4.14.8,进程号=1" tag=main logType=syslog service=flow-engine
1INFO msg=watcher's ctx cancel : context canceled
�redis: 2026/06/30 17:01:23 redis.go:478: auto mode fallback: maintnotifications disabled due to handshake error: ERR unknown subcommand 'maint_notifications'. Try CLIENT HELP.
�time=2026-06-30T17:01:23.941+08:00 level=INFO msg="创建etcd连接: {Endpoints:[etcd:2379] DialTimeout:10 Username:root Password:dell123 AutoSyncInterval:0s}"
>DEBUG msg=config loaded: /airiot/config/pro.json format: json
&DEBUG msg=config loaded: format:
'DEBUG msg=config loaded: PATH format:
+DEBUG msg=config loaded: HOSTNAME format:
6DEBUG msg=config loaded: Service.ExecTimeout format:
0DEBUG msg=config loaded: ETCD.PASSWORD format:
<DEBUG msg=config loaded: production_etcd__password format:
4DEBUG msg=config loaded: AIRIOT_CIPHER_KEY format:
0DEBUG msg=config loaded: App.Log.Level format:
'DEBUG msg=config loaded: HOME format:
+DEBUG msg=config loaded: ZONEINFO format:
1INFO msg=watcher's ctx cancel : context canceled
1INFO msg=watcher's ctx cancel : context canceled
mtime=2026-06-30T17:01:24.087+08:00 level=INFO msg="MQTT 已经连接" logType=syslog service=flow-engine
{time=2026-06-30T17:01:24.088+08:00 level=INFO msg="关闭部门redis publish检查" logType=syslog service=flow-engine
utime=2026-06-30T17:01:24.088+08:00 level=DEBUG msg="task停止: 退出注册" logType=syslog service=flow-engine
ttime=2026-06-30T17:01:24.088+08:00 level=DEBUG msg="task停止: Watch停止" logType=syslog service=flow-engine
�time=2026-06-30T17:01:24.089+08:00 level=ERROR msg=服务启动失败 tag=main logType=syslog service=flow-engine detail="lstat /app/configs/locale: no such file or directory"

创建临时容器
docker create --name fe-tmp airiot/flow-engine:v4.14.8
创建目标目录（使用确认后的正确路径）
mkdir -p /root/app/airiot/flow-engine/configs
提取配置
docker cp fe-tmp:/app/configs/. /root/app/airiot/flow-engine/configs/
清理临时容器
docker rm fe-tmp
创建 locale 目录
mkdir -p /root/app/airiot/flow-engine/configs/locale
从已有的 i18n 目录复制语言包
cp /root/app/airiot/i18n/*.yaml /root/app/airiot/flow-engine/configs/locale/
验证文件是否复制成功
ls -la /root/app/airiot/flow-engine/configs/locale/

kzz

core 进程占用 25.5~25.9 GB 后被系统强制杀掉

会持续增长

kzz

好的，拿到了，谢谢

kzz

ok，（ https://qa.airiot.tech/topic/2689/可视化数据源中-对报警表做-恢复时间为空-判断时-页面报错/6?_=1777449744652 ），
上次那个components模块能再发我一下吗

kzz

我尝试从 table_record 删了一个表的数据，好像没什么影响

kzz

准确来说所有表记录都在，但是设备表记录数量是正常。

设备表记录数量是正常。

只有表记录同步表，好像每次同步都会留下记录痕迹。

kzz

@核桃酥，看出来了，都是表记录同步表，都是不断从数据库源获取的数据，好像每次同步都会留下记录痕迹。

kzz

平台数据库 table_record 是什么表。占用空间越来越多，能清理吗。

kzz

我这个components模块已经是最新的了吧

kzz

@Zhang书书 ok，确实是这样

kzz

请求：

分组随便选，汇总个数。

版本：

kzz

@Zhang书书只能远程

kale邀请您进行远程控制
ToDesk设备代码:988 014 971
临时密码:gjp9jtj8
点击链接直接进行远程控制：
https://wechat.todesk.com/invite-page?id=hJQ9Rvsw7heBSTkkn-ejF

kzz

可视化，数据源中对报警表做恢复时间为空判断时，页面报错。
sql语句报错

kzz

这样的

kzz

设备表驱动。
db-driver数据库驱动，不能自动根据设备id去过滤值
我在驱动配置里面设置了对的id字段

点位

配置好对应设备编号

D1308设备下的数据点值，没有和设备编号有关联。

驱动版本

kzz

这个帖子是笔记，防止我自己忘记。
我的项目是6488246bd7c5cfb28a4405c1，如果你能看懂就可以操作，
如果看不懂，请在他人协助下完成以下操作。

获取报警恢复耗时计算。
报警恢复时间/时长统计。报警持续时间统计。

这是说明：

一个报警事件 = 从异常开始，到异常恢复的整个连续过程

无论中间触发多少次提醒，只要属于同一异常持续过程，就只算 一次报警。

查询通过以下规则进行“报警压缩”：

按 ruleid + desc + table + tableData 分组

如果时间间隔未超过 1.2 × interval，则认为仍属于同一次报警

如果前一条已恢复，则视为新报警

超过 1.2 × interval，则视为新报警

方法一：直接运行sql语句查询报警数据表。
直接查询报警数据表，查询出每次报警恢复耗时。

/* ============================================================
   报警压缩 + 自动计算恢复时间 + 计算恢复时长（单位：秒）

   逻辑说明：
   1. 按 ruleid + desc + table + tableData 分组
   2. 判断是否为新报警段：
        - 第一条数据
        - 前一条已经物理恢复
        - 时间间隔 > 1.2 × interval（interval 单位：秒）
   3. 通过窗口累计求和生成分段ID
   4. 每段聚合出：
        - 报警开始时间 start_time
        - 报警结束时间 end_time
        - 记录条数 alarm_count
   5. 计算最终恢复时间
   6. 计算恢复持续时长（秒）

   注意：
   interval 字段单位为 秒
   ============================================================ */

WITH base AS (

    /* ========================================================
       取同组内上一条报警时间和恢复时间
       ======================================================== */
    SELECT *,
        LAG("time") OVER (
            PARTITION BY ruleid, "desc", "table", "tableData"
            ORDER BY "time"
        ) AS prev_time,

        LAG("recoveryTime") OVER (
            PARTITION BY ruleid, "desc", "table", "tableData"
            ORDER BY "time"
        ) AS prev_recovery

    FROM "6488246bd7c5cfb28a4405c1".warning
),

/* ============================================================
   判断是否为新报警段
   ============================================================ */
mark AS (
    SELECT *,

        CASE
            WHEN prev_time IS NULL THEN 1

            WHEN prev_recovery IS NOT NULL
                 AND prev_recovery <= "time"
                 THEN 1

            WHEN EXTRACT(EPOCH FROM ("time" - prev_time))
                 > ("interval" * 1.2)
                 THEN 1

            ELSE 0
        END AS is_new_group

    FROM base
),

/* ============================================================
   生成报警分段ID
   ============================================================ */
grp AS (
    SELECT *,
        SUM(is_new_group) OVER (
            PARTITION BY ruleid, "desc", "table", "tableData"
            ORDER BY "time"
        ) AS grp_id
    FROM mark
),

/* ============================================================
   按分段ID进行聚合
   ============================================================ */
agg AS (
    SELECT
        ruleid,
        "desc",
        "table",
        "tableData",
        grp_id,

        MIN("time") AS start_time,
        MAX("time") AS end_time,

        /* 段内最大恢复时间 */
        MAX("recoveryTime") AS max_recovery,

        COUNT(*) AS alarm_count

    FROM grp
    GROUP BY
        ruleid, "desc", "table", "tableData", grp_id
),

/* ============================================================
   计算最终恢复时间
   ============================================================ */
final_result AS (
    SELECT
        ruleid,
        "desc",
        "table",
        "tableData",
        grp_id,
        start_time,
        end_time,
        alarm_count,

        /* 有恢复时间就用恢复时间，否则用 end_time */
        COALESCE(max_recovery, end_time) AS final_recovery_time

    FROM agg
)

/* ============================================================
   最终输出
   ============================================================ */
SELECT
    ruleid,
    "desc",
    "table",
    "tableData",
    grp_id,

    start_time,
    end_time,
    alarm_count,

    final_recovery_time,

    /* 恢复持续时长（单位：秒） */
    CAST(
        EXTRACT(EPOCH FROM (final_recovery_time - start_time))
    AS BIGINT) AS recovery_seconds

FROM final_result

ORDER BY
    ruleid,
    "desc",
    "table",
    "tableData",
    start_time;

方法二、
如果报警数据归档了，又想查询全部报警数据，需要创建两个视图来实现。
方法二第一步：

/* 创建全部报警数据视图 */
CREATE OR REPLACE VIEW "6488246bd7c5cfb28a4405c1".warning_all AS

SELECT
    id::text                     AS id,
    level,
    uid,
    type,
    status,
    processed,
    "desc",
    audio,
    interval,
    ruleid,
    remark,
    "table",
    "tableData",
    fields,
    "recoveryFields",
    "time",
    "recoveryTime",
    "confirmTime",
    "handleTime",
    other,
    "warnTag",
    "timesOfPlay",
    handle,
    alert,
    "handleUser",
    "confirmUser",
    "warnMode",
    "broadcastContent",
    "broadcastVoice",
    "audioAlert",
    "i18nProp"
FROM "6488246bd7c5cfb28a4405c1".warning

UNION ALL

SELECT
    id::text                     AS id,
    level,
    uid,
    type,
    status,
    processed,
    "desc",
    audio,
    interval,
    ruleid,
    remark,
    "table",
    "tableData",
    fields,
    "recoveryFields",
    "time",
    "recoveryTime",
    "confirmTime",
    "handleTime",
    other,
    "warnTag",
    "timesOfPlay",
    handle,
    alert,
    "handleUser",
    "confirmUser",
    "warnMode",
    "broadcastContent",
    "broadcastVoice",
    "audioAlert",
    "i18nProp"
FROM "6488246bd7c5cfb28a4405c1".warning_archive;

方法二第二步：

/* 创建报警恢复时间统计视图 */
CREATE OR REPLACE VIEW "6488246bd7c5cfb28a4405c1".warning_recovery_view AS

WITH base AS (
    /* ========================================================
       取同组内上一条报警时间和恢复时间
       LAG() 返回当前行的前一条数据
       ======================================================== */
    SELECT *,
        LAG("time") OVER (
            PARTITION BY ruleid, "desc", "table", "tableData"
            ORDER BY "time"
        ) AS prev_time,

        LAG("recoveryTime") OVER (
            PARTITION BY ruleid, "desc", "table", "tableData"
            ORDER BY "time"
        ) AS prev_recovery

    FROM "6488246bd7c5cfb28a4405c1".warning_all
),

mark AS (
    /* ========================================================
       判断是否为新报警段
       is_new_group = 1 表示当前记录是新报警段起始
       ======================================================== */
    SELECT *,
        CASE
            /* 第一条记录必定是新段 */
            WHEN prev_time IS NULL THEN 1

            /* 前一条已恢复，并且恢复时间早于当前报警时间 */
            WHEN prev_recovery IS NOT NULL
                 AND prev_recovery <= "time"
                 THEN 1

            /* 与上一条时间间隔超过 1.2 × interval */
            WHEN EXTRACT(EPOCH FROM ("time" - prev_time))
                 > ("interval" * 1.2)
                 THEN 1

            /* 否则属于同一报警段 */
            ELSE 0
        END AS is_new_group
    FROM base
),

grp AS (
    /* ========================================================
       生成报警分段ID
       SUM(is_new_group) 累加，得到 grp_id，表示连续报警段
       ======================================================== */
    SELECT *,
        SUM(is_new_group) OVER (
            PARTITION BY ruleid, "desc", "table", "tableData"
            ORDER BY "time"
        ) AS grp_id
    FROM mark
),

agg AS (
    /* ========================================================
       按分段ID聚合报警信息
       ======================================================== */
    SELECT
        ruleid,
        "desc",
        "table",
        "tableData",
        grp_id,

        /* 本段报警起始时间 */
        MIN("time") AS start_time,

        /* 本段报警最后触发时间 */
        MAX("time") AS end_time,

        /* 段内最大恢复时间，可能为批量确认 */
        MAX("recoveryTime") AS max_recovery,

        /* 本段记录条数 */
        COUNT(*) AS alarm_count

    FROM grp
    GROUP BY
        ruleid, "desc", "table", "tableData", grp_id
)

SELECT
    ruleid,
    "desc",
    "table",
    "tableData",
    grp_id,

    start_time,        -- 报警开始时间
    end_time,          -- 报警最后触发时间
    alarm_count,       -- 本段记录条数

    /* ========================================================
       最终恢复时间：
       - 有恢复时间使用 max_recovery
       - 无恢复时间（单条或多条）使用 end_time
       ======================================================== */
    COALESCE(max_recovery, end_time) AS final_recovery_time,

    /* ========================================================
       恢复持续时长（秒）
       final_recovery_time - start_time
       ======================================================== */
    CAST(
        EXTRACT(EPOCH FROM (COALESCE(max_recovery, end_time) - start_time))
    AS BIGINT) AS recovery_seconds

FROM agg

ORDER BY
    ruleid,
    "desc",
    "table",
    "tableData",
    start_time;

kzz

@核桃酥在角色管理里面，数据表记录权限取消不掉中说：

有外网地址吗？@kzz

没有，只能远程。
我的识别码：1610391542
m183cr
使用向日葵即可对我发起远程协助
向日葵下载地址:http://url.oray.com/tGJdas/

kzz

@核桃酥在角色管理里面，数据表记录权限取消不掉中说：

你是在表记录选择里面，取消选中的一个表记录，点击确认，然后点击外层的保存后，在编辑这个角色，刚刚取消的那个选中状态又变成选择了吗？

是这样的，而且相关用户登录之后，确实还能看到表记录。