Failed to enforce packet log max count

[seh-len]

The migration completed successfully, meshmonitor is working. Thank you!
It's now only reporting an error regarding an unsupported DELETE statement in MariaDB:

meshmonitor | [ERROR] [MiscRepository] Failed to enforce packet log max count: DrizzleQueryError: Failed query: DELETE FROM packet_log WHERE id IN (SELECT id FROM packet_log ORDER BY timestamp ASC LIMIT ?)
meshmonitor | params: 7087
meshmonitor | at MySql2PreparedQuery.queryWithCache (file:///app/node_modules/drizzle-orm/mysql-core/session.js:25:15)
meshmonitor | at process.processTicksAndRejections (node:internal/process/task_queues:104:5)
meshmonitor | at async MySql2PreparedQuery.execute (file:///app/node_modules/drizzle-orm/mysql2/session.js:52:19)
meshmonitor | at async MiscRepository.enforcePacketLogMaxCount (file:///app/dist/db/repositories/misc.js:819:17)
meshmonitor | at async DatabaseService.insertPacketLogAsync (file:///app/dist/services/database.js:6484:9)
meshmonitor | at async PacketLogService.logPacket (file:///app/dist/server/services/packetLogService.js:40:20) {
meshmonitor | query: 'DELETE FROM packet_log WHERE id IN (SELECT id FROM packet_log ORDER BY timestamp ASC LIMIT ?)',
meshmonitor | params: [ 7087 ],
meshmonitor | cause: Error: This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
meshmonitor | at file:///app/node_modules/drizzle-orm/mysql2/session.js:53:29
meshmonitor | at MySql2PreparedQuery.queryWithCache (file:///app/node_modules/drizzle-orm/mysql-core/session.js:23:22)
meshmonitor | at MySql2PreparedQuery.execute (file:///app/node_modules/drizzle-orm/mysql2/session.js:52:30)
meshmonitor | at MySql2Session.execute (file:///app/node_modules/drizzle-orm/mysql-core/session.js:97:7)
meshmonitor | at MySql2Database.execute (file:///app/node_modules/drizzle-orm/mysql-core/db.js:228:25)
meshmonitor | at MiscRepository.executeRun (file:///app/dist/db/repositories/base.js:129:24)
meshmonitor | at MiscRepository.enforcePacketLogMaxCount (file:///app/dist/db/repositories/misc.js:819:28)
meshmonitor | at process.processTicksAndRejections (node:internal/process/task_queues:104:5)
meshmonitor | at async DatabaseService.insertPacketLogAsync (file:///app/dist/services/database.js:6484:9)
meshmonitor | at async PacketLogService.logPacket (file:///app/dist/server/services/packetLogService.js:40:20) {
meshmonitor | code: 'ER_NOT_SUPPORTED_YET',
meshmonitor | errno: 1235,
meshmonitor | sqlState: '42000',
meshmonitor | sqlMessage: "This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'",
meshmonitor | sql: 'DELETE FROM packet_log WHERE id IN (SELECT id FROM packet_log ORDER BY timestamp ASC LIMIT 7087)'
meshmonitor | }
meshmonitor | }

According to https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/subqueries/subquery-limitations, this is still not supported.

[sueun-dev]

yeah, this one repros locally.

I checked a tiny packet_log table against MariaDB 11.4.10. The current query in MiscRepository.enforcePacketLogMaxCount() fails with the same ERROR 1235 (42000) because MariaDB still does not allow LIMIT directly inside an IN subquery.

The derived-table join version does work and deletes the oldest rows:

DELETE pl
FROM packet_log pl
JOIN (
  SELECT id
  FROM (
    SELECT id
    FROM packet_log
    ORDER BY timestamp ASC
    LIMIT ?
  ) old_rows
) d ON d.id = pl.id

In the current code the error is caught/logged, so packet logging should keep going, but packet_log_max_count cleanup will not actually trim rows on MariaDB until that query shape changes.

[Yeraze]

#2857