mirror of
https://github.com/ZoneMinder/zoneminder.git
synced 2026-09-12 21:27:39 -04:00
Isaac ConnorandClaude Opus 5
1e38c674fe
Events_Hour, Events_Day, Events_Week and Events_Month each carried their own update and delete trigger, and every one issued a separate UPDATE against the same Event_Summaries row. A single Event delete therefore locked that row five times: once from event_delete_trigger and once from each bucket's cascade. That is the deadlock zmstats.pl hits when it bulk deletes aged rows out of Events_Hour while zmc and zma are writing. event_update_trigger and event_delete_trigger now modify the bucket tables themselves and apply one consolidated UPDATE, using ROW_COUNT() after each bucket statement to tell whether the event was still in that bucket so aged-out events do not over-adjust the counters. Measured on a scratch database, an Event delete goes from 5 Event_Summaries row updates to 1. This also fixes a drift bug. The old event_update_trigger kept ArchivedEventDiskSpace correct only in a branch that cannot be reached: it sits under IF (NEW.Archived != OLD.Archived) and requires both to be false. The branch that does run when an already-archived event grows updated Events_Archived but not Event_Summaries, so the archived total drifted for the life of the install. Reproduced on a scratch database: after archiving a 250-byte event and growing it to 999, ArchivedEventDiskSpace still read 100. BEHAVIOUR CHANGE. A direct DELETE against a bucket table no longer adjusts Event_Summaries at all, and that is how zmstats.pl prunes. zmstats.pl already resyncs HourEvents/DayEvents/WeekEvents/MonthEvents and their disk space columns from COUNT(*)/SUM(DiskSpace) on any pass where it pruned, so those four pairs become eventually consistent within one ZM_STATS_UPDATE_INTERVAL instead of exact at every instant. The Total and Archived columns stay exact, because only the Events triggers touch them. The zmstats.pl comments are updated to describe the new arrangement; its code is unchanged. Migration is db/zm_update-1.39.25.sql.in: drop the eight cascade triggers, resync Event_Summaries from the events themselves so the new triggers start from ground truth and the archived drift above is repaired, then source db/triggers.sql. version.txt goes to 1.39.25. No schema change, so zm_create.sql.in needs no edit -- it already sources triggers.sql. Ported from the ai_server branch, where this migration sits at 1.39.7, a number master passed long ago and an existing install would never run. Repackaged above master's tip. The ai_server version also unwinds a views-and-SWR experiment that only ever existed on that branch, which is dropped here as a no-op on master, and carries an unrelated START_DELAY change, which is not taken. Tests: tests/perl/test_event_summaries_triggers.pl, 29 assertions against a real server, skipped when no scratch database is configured. Verified they fail on the current triggers, on exactly the two claims above: the archived drift, and 5 row updates per delete. Also verified the migration repairs a drifted install, is idempotent across a second run, and leaves a migrated install with byte-identical triggers to a fresh one. Generated zmstats.pl passes perl -Tc. Co-Authored-By: Claude Opus 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_015Y6FieTwEXuLhhR4e2yiax