nethvoice-report: CDR partition tables contaminated with cross-period rows

[edospadoni]

Problem

Two distinct issues in nethvoice-report cause incorrect counts and prevent the nightly reports scheduler from completing successfully.

A. CDR partition tables contaminated with cross-period rows

CDR partition tables in asteriskcdrdb (cdr_YYYY and cdr_YYYY-MM) accumulate rows belonging to periods other than the table's own period. This silently inflates counts returned by any API query whose date range spans more than one month, and inflates every CDR dashboard widget by approximately 2x.

Root cause — daily INSERT IGNORE in partition templates

root/opt/nethvoice-report/api/templates/cdr_year.sql and cdr_month.sql contain a daily INSERT IGNORE block that copies "yesterday's" rows from the source cdr table (or year table) into the partition target. The block filters by calldate >= NOW() - INTERVAL 1 DAY AND calldate < NOW() but lacks any constraint on the target table's own period.

The nightly tasks cdr job iterates every year/month from cdr_first_month to the current month, and for every iteration it executes the INSERT IGNORE block. As a result, yesterday's rows are inserted into every historical partition table, regardless of whether yesterday belongs to that partition's period.

Runtime symptom 1 — multi-month API queries

api/methods/queries.go buildCdrQuery builds a UNION ALL across the monthly partition tables that intersect the requested time range. Each subquery applies the same outer WHERE calldate BETWEEN start AND end. When a partition table contains rows belonging to other months that fall inside the requested range, those rows are returned by both their "rightful" partition and any contaminated partition, double-counting.

Example: with date range March–April, the outbound recap summary returns ~5992 calls instead of the correct ~4083 (March 2261 + April 1822).

Runtime symptom 2 — dashboard widgets

The precomputed dashboard tables dashboard_cdr_*_past_* are built by root/opt/nethvoice-report/api/views/dashboard_cdr_*.sql. These views read from year tables (cdr_YYYY) and UNION ALL the previous-year and current-year tables. When either year table contains rows of the other year (the contamination above), every dashboard widget for past_week, past_month, past_quarter, past_semester, past_year, current_* is inflated approximately 2x.

B. tasks views fails with "invalid connection"

The tasks views command in tasks/cmd/views.go runs each view SQL file in parallel (semaphore = 4 goroutines) with multiStatements=true. Most view files are multi-statement (a chain of DROP TABLE IF EXISTS ...; CREATE TABLE ... AS SELECT ...;). db.Query() returns only the first result set; subsequent result sets stay queued on the connection. The current code calls rows.Close() without iterating rows.NextResultSet() first, so the connection is returned to the pool while still holding pending results. When the next goroutine reuses that connection the mysql driver detects the bad state and the query fails with driver.ErrBadConn (surfaced as "invalid connection"), aborting the whole scheduler before views completes.

Combined effect

• Multi-month API CDR queries (pbx and personal sections) over-count by a factor proportional to the number of months covered.
• All 18 dashboard CDR widgets inflated by ~2x.
• Multi-month group-by-month charts show duplicated values on every visible month.
• The nightly reports-scheduler.service aborts on a random view file with "invalid connection", leaving dashboard_cdr_* and queue-derived precomputed tables not refreshed.

Fix

• fix(cdr): prevent cross-period contamination in partition tables nethesis/nethvoice-report#211 — (A) restores the period boundary filter in the INSERT IGNORE blocks of cdr_year.sql and cdr_month.sql; (A) adds an idempotent tasks cleanup command that removes out-of-period rows from all CDR partition tables; (B) drains pending result sets in tasks views before releasing connections to the pool.
• fix(reports): bump nethvoice-report and wire CDR cleanup task nethesis/ns8-nethvoice#843 — bumps NETHVOICE_REPORT_COMMIT to the fix and wires tasks cleanup into the nightly reports-scheduler.service between cost and views, so retroactive sanitization runs during the existing nightly window (not during package upgrade) and is safe to re-run.

[edospadoni]

Test cases

1. Deploy the branch

Update the nethvoice module on a test NS8 node to the branch image produced by CI for nethesis/ns8-nethvoice#843, or build locally with build-images.sh on the branch.

2. Capture the baseline (before fix runs)

On the test node, against asteriskcdrdb:

-- list contaminated partition tables and the number of out-of-period rows in each
SELECT GROUP_CONCAT(
  CONCAT(
    'SELECT ''', TABLE_NAME, ''' AS table_name, COUNT(*) AS dirty_rows ',
    'FROM `', TABLE_NAME, '` ',
    'WHERE DATE_FORMAT(calldate, ''%Y-%m'') != ''',
    SUBSTRING(TABLE_NAME, 5), ''''
  ) SEPARATOR ' UNION ALL '
) INTO @q
FROM information_schema.tables
WHERE table_schema = 'asteriskcdrdb'
  AND TABLE_NAME REGEXP '^cdr_[0-9]{4}-[0-9]{2}$';
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

Expectation: a non-zero dirty_rows value on multiple month partition tables.

Also capture a dashboard baseline for comparison:

SELECT outbound, total FROM dashboard_cdr_14_past_month ORDER BY total DESC LIMIT 5;

And the API symptom on a multi-month range (e.g. previous two months) — call the outbound recap summary endpoint and note the Total row.

3. Trigger the fix

Either let the nightly scheduler run, or force it:

# whole nightly sequence (cdr → cost → cleanup → views → queries → phonebook → values)
runagent -m <module_id> systemctl --user start reports-scheduler.service

# OR cleanup + dashboard rebuild only
runagent -m <module_id> podman exec reports-api tasks cleanup
runagent -m <module_id> podman exec reports-api tasks views

4. Verify

• Re-run the contamination check from step 2 — every row should show dirty_rows = 0.
• Re-run tasks cleanup a second time — should complete almost instantly and delete 0 rows (idempotency).
• Re-query dashboard_cdr_14_past_month: the trunk counts must drop to roughly half (no longer inflated 2x) and match a direct count on the partition table for the same period.
• Re-call the outbound recap summary endpoint over the multi-month range: the Total row must now equal the sum of the per-single-month totals.
• After at least one additional nightly run, repeat the contamination check — must still be all zeros (confirms the template fix prevents new pollution).