# Troubleshooting Accounting Run Issues

Common failures, how to diagnose them, and how to recover.

## Triage

**I got paged or something looks wrong — where do I start?**

```
What do you see?
│
├─ Airflow DAG failed or stuck
│   → Which DAG?
│   ├─ sales_ingest           → "Get Eligible Sales" before ingest (#abacus-runs)
│   ├─ accounting_run_calculate → Calculation timeout or stuck "Running" (#abacus-runs)
│   ├─ accounting_run_commit    → Check Snowflake/Lambda logs, escalate to #accounting-run-priv
│   └─ Other                    → Check task logs in Airflow UI, post in #accounting-tech
│
├─ Abacus UI shows an error or unexpected state
│   ├─ Run stuck in "Running"  → Stuck run (#abacus-runs)
│   ├─ Unexpected run controllers → E2E controllers blocking (#abacus-runs)
│   └─ "Get Eligible" error    → Premature trigger or S3 collision (#abacus-runs)
│
├─ RDS CPU/IO spike (Datadog alert)
│   → Is a legacy run in progress? Check #accounting-run-priv
│   ├─ Yes → Expected during load/calculation. Check Memcached hit rate (#legacy-runs)
│   └─ No  → Investigate unexpected query load. Check slow query logs
│
├─ Customer reports wrong numbers in Customer Accounting
│   → Revenue mismatch (#customer-accounting)
│
├─ Attachments stuck generating
│   → Stuck attachments (#customer-accounting)
│
└─ Don't know / unclear alert
    1. Check the Health Dashboard (link in runbooks/README.md)
    2. Check if a run is in progress: look at #accounting-run-priv and Airflow UI
    3. Post in #accounting-tech with what you see
```

**Severity guide:**

| Keyword in this doc | What it means | Action |
|-------------------|---------------|--------|
| **Critical blocker** | Data corruption risk if you proceed | Stop immediately. Wake people up if needed. |
| **Blocker** | Run cannot continue until fixed | Fix before proceeding. Escalate during business hours. |
| No severity label | Run is impacted but recoverable | Follow the recovery steps. Note in handoff. |

---

## Abacus (DAG-Based) Runs

### "Get Eligible Sales" triggered before ingest completes

**Symptom:** Error in the Abacus UI after clicking "Get Eligible Sales". The run is blocked.

**Cause:** The `sales_ingest` DAG hasn't finished writing data to Snowflake staging.

**Diagnosis:** Check the `sales_ingest` DAG in Airflow — is it still running or has it failed?

**Recovery (QA/UAT):**

Option A — close the accounting period and create a new one.

Option B — delete the errant data and retry:

```sql
SET @SALES_FILE_ID = ...; -- find via: SELECT * FROM sales_file WHERE accounting_period_id = <id>;

DELETE FROM abacus_state
WHERE parent_table_id = @SALES_FILE_ID AND parent_table_name = 'sales_file';

DELETE FROM abacus_event
WHERE target_id = @SALES_FILE_ID AND target_type = 'sales_file';

DELETE FROM sales_file
WHERE sales_file_id = @SALES_FILE_ID;
```

**Recovery (Prod):** Submit a DB PR. Example: [ACC-8794](https://theorchard.atlassian.net/browse/ACC-8794), [PR #24234](https://github.com/theorchard/database/pull/24234/files).

---

### Calculation timeout (Snowflake query killed after ~2 hours)

**Symptom:** `accounting_run_calculate` DAG fails. The Snowflake query history shows a query running for ~2 hours on `CONTRACT_TRANSACTION_STAGING` insert, then terminated.

**Cause:** Cartesian explosion from contract terms with near-All explicit store/country/transaction_type selections. See [Calculation Timeout Investigation](../improvements/calculation-timeout.md).

**Diagnosis:** Run the contract size estimation query — see [Monitoring: Contract Size Check](monitoring.md#pre-calculation-contract-size-check).

**Recovery:**
1. Identify the problematic contract(s) from the query above
2. Work with the product team to convert near-All selections to empty arrays (meaning "All")
3. Invalidate the failed run and re-create after the contract is fixed
4. For future prevention, run the size estimation query before each production run

---

### Accounting run stuck in "Running" status

**Symptom:** The Abacus UI shows the run as "Running" indefinitely. No progress.

**Diagnosis:**
1. Check the Airflow DAG `accounting_run_calculate` — is it running, queued, or failed?
2. If failed, check the task logs for the specific error
3. If the DAG is not running at all, the event trigger may not have fired

**Recovery:**
1. If the DAG failed, fix the underlying issue, invalidate the run, and re-create
2. If the DAG is stuck/queued, check Airflow worker capacity and MWAA health

---

### E2E run controllers blocking the accounting period

**Symptom:** Unexpected run controllers appear in the Abacus UI. They may be from CI/test pipelines (FE build, GitHub Actions).

**Diagnosis:** Check if any CI jobs are actively using them:
- [FE build pipeline](https://pipeline.theorchard.io/job/theorchard/job/frontend-royalties/job/master/)
- [E2E test pipeline](https://pipeline.theorchard.io/job/abacus-run-e2e-tests/)

**Recovery:**
1. Verify no active CI/demos are using the controllers
2. Go to [`abacus-run-e2e-tests`](https://pipeline.theorchard.io/job/abacus-run-e2e-tests/)
3. Select action **Clear runcontrollers**
4. Click **Build**
5. Refresh the Abacus UI — controllers should be gone

---

### S3 data collision when redoing a run

**Symptom:** Error when clicking "Get Eligible Sales" on a period that was previously used.

**Cause:** S3 already has data for the same file name from a previous attempt.

**Recovery:**
- Option A: Use a different file display name
- Option B: Delete the old S3 data from `[env]-royalties-sales-files`. Find the `main_url` via:

```sql
SELECT * FROM sales_file WHERE file_name = '<file_name>';
```

Then delete the S3 objects at that path.

---

## Legacy Runs (Docker/ActiveMQ/PHP)

### Memcached GET hit percentage stays flat

**Symptom:** During `processAccountingQueue`, the Elasticache GET hit percentage doesn't rise.

**Cause:** Memcached is not caching vendor contract/payment data. Could be connection issue or full cache.

**Diagnosis:** Check [Elasticache console](https://us-east-1.console.aws.amazon.com/elasticache/home?region=us-east-1#/memcached/prod-cron-accounting) for evictions, connection count, and memory usage.

**Impact:** Without cache, the database gets hammered and the run takes days instead of hours.

---

### Data truncation warnings during load

**Symptom:** Datadog logs show `Warnings` during the python-accounting-run-utils load phase.

**Expected:** `Data truncated for column 'isrc'` is normal and can be ignored.

**Not expected:** Any other warning. Stop and post in `#accounting-run-priv` or `#accounting-calc`.

---

### in_content or ISRC validation fails

**Symptom:** Load scripts report that not all releases are `in_content`, or that tracks are missing ISRCs.

**Diagnosis:**
- Check Datadog logs for `validate_temp_dig_sales_statements_in_content.py` or `validate_temp_dig_sales_statements_null_isrc.py`
- The log output will contain the specific records that failed

**Recovery (ISRCs):**
1. Find an unused ISRC: `SELECT * FROM isrcs WHERE status = 'unused';` (pick one from the middle of the list, not the first)
2. Create a [DB PR to assign the ISRC](https://github.com/theorchard/database/pull/8411/files)
3. Run the dim-refresh job: https://scheduler.theorchard.io/job/dim-refresh-exec/

**Recovery (in_content):** Post the failing records in `#accounting-run-priv` for guidance.

---

### Missing currency exchange rates

**Symptom:** `sp_get_missing_exchange_rates` returns rows with NULL rate values.

**Cause:** Exchange rates for one or more currencies were not uploaded for this period.

**Impact:** This is a **blocker**. The calculation will produce incorrect results.

**Recovery:** Stop and post in `#accounting-run-priv`. Finance must upload the missing rates before the run can continue.

---

### Duplicate records in processed_dig_sales

**Symptom:** The Snowflake duplicate check returns rows:

```sql
SELECT statement_detail_id FROM FACTS.PROD.STAGING_FACT_SALES
WHERE period_id = <period_id>
GROUP BY statement_detail_id HAVING COUNT(*) > 1;
```

**Impact:** This is a **critical blocker**. Vendor accounting is nearly impossible to roll back.

**Recovery:**
1. **Stop immediately** — do not proceed to vendor accounting
2. Post in `#accounting-run-priv` and `#accounting-calc`
3. Most likely: delete duplicate records from `processed_dig_sales` in MySQL

---

### Manual adjustments created during calculation window

**Symptom:** After running `prepAccounting`, the `manual_adjustment` table shows entries added between prep start and now.

**Impact:** The prep snapshot may not include these adjustments, leading to incorrect results.

**Recovery:** Post in `#accounting-calc`. A DB PR will likely be needed.

---

### processAccountingQueue scripts not processing

**Symptom:** ActiveMQ shows messages enqueued but none being dequeued. Scripts exit immediately.

**Diagnosis:**
1. Check if ActiveMQ is running: the `/opt/apache-activemq-5.8.0/data/ldb-data` directory should exist
2. Check Docker container logs: `docker logs cron-accounting`
3. Check if the correct month/year arguments were passed

**Recovery:** Restart ActiveMQ if needed, ensuring the `ldb-data` directory was cleaned first.

---

### ActiveMQ running out of disk

**Symptom:** Publish scripts fail. The 400GB system disk is full.

**Cause:** Each batch creates ~10GB of messages. After ~35 batches the disk fills up.

**Recovery:**
1. Process all currently published batches first
2. Stop ActiveMQ: kill the process
3. Delete `/opt/apache-activemq-5.8.0/data/ldb-data`
4. Restart ActiveMQ
5. Continue with remaining batches

---

## Customer Accounting (Post-Run)

### Revenue figures don't match expected values

**Diagnosis path:**
1. Is the data coming from Snowflake or MySQL `royalty_accounting`?
   - Statement totals (revenue, adjustments, expenses) → MySQL with aggregation in `ows-moneyhub`
   - Revenue analysis / highlights → Snowflake via DBT materialized views
2. If Snowflake: check the underlying `VW_ABACUS_FACT_SALES_*` views, then the DBT-created tables
3. If MySQL: check `ledger_account_contract` and `ledger_accounting_run_balance`
4. Revenue analysis is cached in Redis — try clearing the cache for the affected account

### Stuck report/attachment generation

**Symptom:** Attachment shows as `in_progress` indefinitely.

**Diagnosis:** A cronjob monitors for stuck reports and posts to `#moneyhub`. Check there first.

**Recovery:** Use the [ows-moneyhub script runner](https://pipeline.theorchard.io/job/ows-moneyhub-script/) to target regeneration.

### DBT refresh didn't update customer-facing data

**Symptom:** Data appears in Abacus but not in Customer Accounting after DBT refresh.

**Cause:** Two refreshes are needed — one after adjustments, one after "Show to Customer" (because `moneyhub_unified_statement_periods` requires it).

**Recovery:** Trigger a second DBT refresh via [`dbt-accounting-scheduler`](https://scheduler.theorchard.io/job/dbt-accounting-scheduler/). Ensure `abacus_fact_sales_unified_dbt` is NOT in the exclude list.