INSERT INTO Event_Summaries_New SELECT * FROM VIEW_Event_Summaries took
shared next-key locks on the source Events table (required for STATEMENT
binlog correctness under the default REPEATABLE READ). Concurrent DELETE
FROM Events deadlocked against those S locks.
Switch the procedure to READ COMMITTED so the source-side SELECT runs as
a consistent snapshot read with no row locks.
Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
The 1.37.78 update slot is already taken on master by the User Roles
feature, so move the views migration to the next free slot, 1.39.5.
CREATE INDEX IF NOT EXISTS / DROP INDEX IF EXISTS in views.sql are
MariaDB-only and fail to parse on MySQL. Replace both with the
INFORMATION_SCHEMA.STATISTICS conditional pattern used throughout the
ZoneMinder update scripts.
Also tighten the migration script to drop the SWR procedure, scheduled
event, and any prior view-named objects before sourcing views.sql, so
re-running on a partially-migrated database is safe.
Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
Replace the Event_Summaries view with a physical snapshot table
refreshed via Stale-While-Revalidate (SWR) pattern. A stored
procedure (Refresh_Summaries_SWR) uses GET_LOCK for non-blocking
concurrency and atomic table rename for zero-downtime refresh.
Database changes (db/views.sql):
- Rename Event_Summaries view to VIEW_Event_Summaries (source view)
- Add Event_Summaries snapshot table and Event_Summaries_Metadata table
- Add Refresh_Summaries_SWR stored procedure with GET_LOCK and
atomic rename pattern to prevent thundering herd
- Add MySQL EVENT for background refresh every 600 seconds
PHP call sites (web/):
- Add ensureSummariesFresh() helper in database.php with static
per-request dedup and 60s staleness check
- Call ensureSummariesFresh() before Event_Summaries queries in
console.php, _monitor_filters.php, and Monitor.php
- Add beforeFind() hook in CakePHP Event_Summary model
Perl call sites (scripts/):
- Add ensureSummariesFresh() sub in Event_Summary.pm with
per-process 60s rate-limiting
- Call ensureSummariesFresh() in Monitor.pm Event_Summary accessor
Upgrade path (db/zm_update-1.37.78.sql.in):
- Drop any prior Event_Summaries view or table before recreating
Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
Remove denormalized event summary tables and their associated triggers,
replacing them with views that query the Events table directly. This
eliminates trigger maintenance overhead and periodic reconciliation in
zmaudit/zmstats, since the views compute stats on the fly.
- Remove trigger definitions for event summary table maintenance
- Remove event summary table inserts from zm_event.cpp
- Remove event count reconciliation queries from zmaudit.pl
- Remove DELETE-on-views calls from zmstats.pl (views filter by date inherently)
- Remove Event_Summaries DELETE from Monitor.php (can't delete from a view)
- Add db/views.sql with view definitions and covering index
- Add upgrade script zm_update-1.37.78.sql.in (drop triggers, drop tables, create views)
- Update zm_create.sql.in to use views instead of tables for fresh installs
Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
