Beta Slim External MariaDB Query Issue

[undaunt]

🛡️ Security Policy

• I agree to have read this project Security Policy

Description

When running beta-slim and the latest version of the LSIO mariadb image, I receive a SQL error about LIMIT not being supported in a subquery of IN/ALL/ANY/SUM. This appears to be when it's trying to purge old statistics, I think.

👟 Reproduction steps

Launch compose stack with external mariadb (included below) and watch the logs.

👀 Expected behavior

Not throw this error, sucessfully clean up db as desired.

😓 Actual Behavior

Logging throws various errors included below.

🐻 Uptime-Kuma Version

2.0.0-beta.0

💻 Operating System and Arch

TrueNAS Scale ElectricEel-24.10.0

🌐 Browser

Chrome 131.0.6778.109 (Official Build) (arm64)

🖥️ Deployment Environment

• Runtime: Docker 27.1.1
• Database: MariaDB External 10.11.11-R0-LS163
• Filesystem used to store the database on: ZFS RAIDZ2 (local)
• number of monitors: 8

services:
  uptime-kuma:
    image: louislam/uptime-kuma:beta-slim
    container_name: uptime-kuma
    restart: unless-stopped
    profiles: ["all", "kuma"]
    networks:
      - ${PROXY_NETWORK}
      - ${APP_NETWORK}
      - ${SOCKET_NETWORK}
      - ${GLUETUN_NETWORK}
    depends_on:
      - uptime-kuma-db
    deploy:
      resources:
        limits:
          memory: 512M
    volumes:
      - ${APPDATA_DIR}/uptime-kuma/data:/app/data
    environment:
      PUID: ${PUID}
      PGID: ${PGID}
  
  uptime-kuma-db:
    image: lscr.io/linuxserver/mariadb:latest
    container_name: uptime-kuma-db
    restart: always
    profiles: ["all", "kuma"]
    networks:
      - ${APP_NETWORK}
    volumes:
      - ${APPDATA_DIR}/uptime-kuma/db:/config
    environment:
      TZ: ${TZ}
      PUID: ${PUID}
      PGID: ${PGID}
      MYSQL_ROOT_PASSWORD: ${UPTIME_KUMA_MYSQL_ROOT_PASSWORD}
      MYSQL_DATABASE: ${UPTIME_KUMA_MYSQL_DB}
      MYSQL_USER: ${UPTIME_KUMA_MYSQL_USER}
      MYSQL_PASSWORD: ${UPTIME_KUMA_MYSQL_PASSWORD}

📝 Relevant log output

Trace: Error: 
                DELETE FROM heartbeat
                WHERE monitor_id = 30
                AND important = 0
                AND time < DATE_ADD(NOW(), INTERVAL -24 HOUR)
                AND id NOT IN (
                    SELECT id
                    FROM heartbeat
                    WHERE monitor_id = 30
                    ORDER BY time DESC
                    LIMIT 100
                )
             - This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
    at Packet.asError (/app/node_modules/mysql2/lib/packets/packet.js:738:17)
    at Query.execute (/app/node_modules/mysql2/lib/commands/command.js:29:26)
    at Connection.handlePacket (/app/node_modules/mysql2/lib/connection.js:481:34)
    at PacketParser.onPacket (/app/node_modules/mysql2/lib/connection.js:97:12)
    at PacketParser.executeStart (/app/node_modules/mysql2/lib/packet_parser.js:75:16)
    at Socket.<anonymous> (/app/node_modules/mysql2/lib/connection.js:104:25)
    at Socket.emit (node:events:519:28)
    at addChunk (node:internal/streams/readable:559:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:510:3)
    at Readable.push (node:internal/streams/readable:390:5) {
  code: 'ER_NOT_SUPPORTED_YET',
  errno: 1235,
  sqlState: '42000',
  sqlMessage: "This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'",
  sql: '\n' +
    '                DELETE FROM heartbeat\n' +
    '                WHERE monitor_id = 30\n' +
    '                AND important = 0\n' +
    '                AND time < DATE_ADD(NOW(), INTERVAL -24 HOUR)\n' +
    '                AND id NOT IN (\n' +
    '                    SELECT id\n' +
    '                    FROM heartbeat\n' +
    '                    WHERE monitor_id = 30\n' +
    '                    ORDER BY time DESC\n' +
    '                    LIMIT 100\n' +
    '                )\n' +
    '            '
}
    at process.unexpectedErrorHandler (/app/server/server.js:1872:13)
    at process.emit (node:events:519:28)
    at emitUnhandledRejection (node:internal/process/promises:250:13)
    at throwUnhandledRejectionsMode (node:internal/process/promises:385:19)
    at processPromiseRejections (node:internal/process/promises:470:17)
    at process.processTicksAndRejections (node:internal/process/task_queues:96:32)
If you keep encountering errors, please report to https://github.com/louislam/uptime-kuma/issues

[homelab-alpha]

@undaunt, This issue is addressed in

• Fix: [MariaDB] Unable to clean up heartbeat data #5425.

See the original post: MariaDB Error: "LIMIT & IN/ALL/ANY/SOME subquery" Not Supported

• MariaDB Error: "LIMIT & IN/ALL/ANY/SOME subquery" Not Supported #5381

[CommanderStorm]

🙏
Thanks for the report, let's focus the discussion in #5381