Restore stgapi.moneymarche.com¶

Restored 19 Oct 00:25 IST (18:54 UTC). Release-C now runs under systemd unit mm-stgapi.service (enabled at boot, restarts on failure, runs as root as before because it loads the TLS key for Apache's https://127.0.0.1:5001 backend). /api/health, /api/schemes, /api/scheme/nav?scheme_code=120245, /api/index/nav?symbol=NIFTYM150MOMNTM50 and /category return 200 locally and publicly. Production (port 5000, PID 1740) was untouched.

What had happened: - The server rebooted on 24 Sep 17:01 UTC, and staging (a bare process) never came back. - On 18 Oct 10:01 UTC a 2024 copy (/opt/bitnami/api/api, branch feature/fund-list with local edits, run as root with nohup) was started on 5001. It served plain HTTP while Apache expects HTTPS, so every route returned 500. The restore stopped it; it must not be started again.

What is deployed: - Release-C = moneymarche/api commit 825127e0ffa8eb27cf57798cb4cf0e316177b3e8 (manifest release-c-manifest.txt, prepared 23 Sep). It sits on branch api-phase1-development, not on api-phase1-staging-release. - The development branch has since gained the Release-C product-resolver fix (!147), the security fixes (!132, !162), laptop-runner CI (!169), and docs. - mm_product_holdings errors are expected: the API docs say that legacy table must not be recreated.

Check, restore and output files: C:\Projects\_mmdep-inbox\stgapi-check*.sh, stgapi-restore.sh, *-out.txt on Atul's laptop.

1. What we know (from the TEA investigation)¶

Item	Value	Evidence (TEA chat)
Host	The same Lightsail server as production (`ip-172-26-9-240`, public 65.2.119.188), user `bitnami`	DNS check, 14 Sep
Public endpoint	`https://stgapi.moneymarche.com` → Apache → `127.0.0.1:5001`	vhost listing, 11 Sep
Apache vhosts	`/opt/bitnami/apache/conf/vhosts/stgapi-vhost.conf`, `stgapi-https-vhost.conf` (several `.bak-20260909*` copies; one `.disabled`)	11 Sep
Apache logs	`/opt/bitnami/apache2/logs/stgapi_access_log_app.txt`, `stgapi_error_log_app.txt`	11 Sep
App directory	`/opt/bitnami/api/api_stg_release/app` (owner `bitnami`, mode 755)	process cwd, 11 and 14 Sep
Start command	`/usr/bin/python3 app.py`, run from the app directory (Flask with gevent); no process manager	`/proc/<pid>/cmdline`
App logs	`app/logs/staging-api.log`, plus `critical`, `error`, `warning`, `info`, `debug` `.log`	open file handles
Environment file	`/opt/bitnami/api/staging-secrets/db.env`, `root:root`, mode 600 (566 bytes on 14 Sep). Holds `MYSQL_USERNAME`, `MYSQL_PASSWORD`, `MYSQL_DATABASE`, `MYSQL_NAV_USERNAME`, `MYSQL_NAV_PASSWORD`, `MYSQL_NAV_DATABASE`, `MYSQL_HOST`, `MYSQL_UNIX_SOCKET`, `APP_PORT=5001`, `ENV=STAGING`. Never print it.	14 Sep
Databases	Local MariaDB, socket `/opt/bitnami/mariadb/tmp/mysql.sock`: `mm_api_staging` (catalogue seed: one product, its owner, category and fund details) and `mm_nav_staging` (scheme 120245, 3,333 NAV rows; index NIFTYM150MOMNTM50, 299 rows). Users `mm_api_staging_app@localhost` and `mm_nav_staging_app@localhost`, each limited to its own database. Staging never uses `mm_data`, `moneymarche_www` or `wp1`.	13–14 Sep
Legacy schemas	`moneymarche_staging` and `moneymarche_staging_backup` also exist; they are not used by Release-B	13 Sep
Release	Release-B: `moneymarche/api`, branch `api-phase1-staging-release`, commit `614b0bef890a1b200c6464519374533f00cb440e`. Deployed by GitLab pipeline #423, job `deploy-staging`, on 15 Sep 16:10 UTC	16–18 Sep
CI deploy	Job `deploy-staging` uploads the artifact, activates it, restarts :5001 and checks the endpoints. CI variables: `SSH_PRIVATE_KEY`, `SSH_KNOWN_HOSTS`, `STAGING_ENV_FILE` (File), `STAGING_SSH_HOST=stgapi.moneymarche.com`, `STAGING_SSH_USER=bitnami`, `STAGING_APP_DIR=/opt/bitnami/api/api_stg_release/app`	14 Sep
Smoke tests	`/api/schemes`; `/api/scheme/nav?scheme_code=120245`; `/api/index/nav?symbol=NIFTYM150MOMNTM50`	Release-1 plan
Known defect	After idle time the API returns HTTP 500 "MySQL server has gone away" (stale SQLAlchemy connection). A restart clears it. A code fix (`pool_pre_ping`/`pool_recycle`, plus the NAV engine wrongly using `MYSQL_UNIX_SOCKET` instead of `MYSQL_NAV_UNIX_SOCKET`) is open	17–18 Sep

Why it is down now (most likely): it ran as a bare python3 app.py with no process manager, so it doesn't come back after the process dies or the server restarts. TEA also stopped it once deliberately (11 Sep, overload test), and CI restarted it later. Update 19 Oct: the collector now sees HTTP 500 (not 503) on every stgapi route, so a process is answering and failing (stale DB connection, or a newer release). Run C:\Projects\_mmdep-inbox\stgapi-check.sh first (§2, step 0).

Released branch: Release-B, from api-phase1-staging-release, was the last deployment TEA verified (15 Sep). API support items #44–#46 mention later staging comparisons (!161, !167), so a newer release may be live; the check script reports what is deployed.

2. Restore steps (Atul via `ssh moneymarche-aws`, or the GitLab `deploy-staging` job; staging only)¶

Step 0, the read-only check from the laptop (one line, Command Prompt or PowerShell): cmd /c 'ssh moneymarche-aws bash -s < C:\Projects\_mmdep-inbox\stgapi-check.sh > C:\Projects\_mmdep-inbox\stgapi-check-out.txt 2>&1'. MMDEP-PM then reads the output file through the laptop link.

Production (/opt/bitnami/api/api_prod, port 5000, PID 1147 in September) must not be touched. Stop and report if any step shows something unexpected.

Step 1: check (read-only)¶

echo '=== STGAPI RESTORE CHECK ==='
ls -ld /opt/bitnami/api/api_stg_release/app /opt/bitnami/api/api 2>&1
ls -l /opt/bitnami/api/api_stg_release/app/app.py 2>&1
sudo stat -c 'path=%n owner=%U:%G mode=%a size=%s' /opt/bitnami/api/staging-secrets/db.env
sudo ss -ltnp | grep -E ':(5000|5001) ' || true
pgrep -af 'python3 .*app.py' || true
sudo /opt/bitnami/mariadb/bin/mariadb -N -e "SELECT SCHEMA_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME IN ('mm_api_staging','mm_nav_staging');"
sudo /opt/bitnami/mariadb/bin/mariadb -N -e "SELECT COUNT(*) FROM mm_nav_staging.funds_nav;"
tail -20 /opt/bitnami/api/api_stg_release/app/logs/staging-api.log 2>/dev/null

Expected: the app directory and app.py exist; db.env is root:root 600; port 5000 is listening (production); port 5001 is not; both staging schemas exist; about 3,333 NAV rows.

Step 2: install a systemd service (restarts on failure and at boot)¶

systemd reads the root-only environment file itself, so the secret never has to be copied or printed.

sudo tee /etc/systemd/system/mm-stgapi.service >/dev/null <<'UNIT'
[Unit]
Description=MoneyMarche staging API (stgapi.moneymarche.com, port 5001)
After=network-online.target bitnami.service
Wants=network-online.target

[Service]
User=bitnami
Group=bitnami
WorkingDirectory=/opt/bitnami/api/api_stg_release/app
EnvironmentFile=/opt/bitnami/api/staging-secrets/db.env
ExecStart=/usr/bin/python3 app.py
Restart=on-failure
RestartSec=10
StandardInput=null

[Install]
WantedBy=multi-user.target
UNIT
sudo systemctl daemon-reload
sudo systemctl enable --now mm-stgapi.service
sleep 5
sudo systemctl --no-pager status mm-stgapi.service | head -15
sudo ss -ltnp | grep ':5001 ' || echo 'PORT 5001 NOT LISTENING'

If the service fails, show only sudo journalctl -u mm-stgapi --no-pager -n 40, and check that the output contains no secret values before sharing it.

Step 3: smoke test¶

for u in '/api/schemes' '/api/scheme/nav?scheme_code=120245' '/api/index/nav?symbol=NIFTYM150MOMNTM50'; do
  printf '%s -> ' "$u"; curl -sS -o /dev/null -w '%{http_code}\n' --max-time 20 "https://stgapi.moneymarche.com$u"
done

Expected: 200 for all three. The MMDEP collector checks the same routes hourly, so latest.md on MMDEP's control branch turns green on its next run.

Step 4: stale-connection safety net (until the code fix lands)¶

If an endpoint later returns 500 with "MySQL server has gone away", run sudo systemctl restart mm-stgapi and tell the API agent. The permanent fix (pool_pre_ping=True, a pool_recycle below MariaDB's wait_timeout, and the NAV-socket defect) is an API code item.

Rollback¶

sudo systemctl disable --now mm-stgapi.service && sudo rm /etc/systemd/system/mm-stgapi.service && sudo systemctl daemon-reload. This leaves staging stopped, as it is today.

3. Afterwards¶

CI: the deploy-staging job restarts the app its own way. The API agent should change it to call sudo systemctl restart mm-stgapi, a narrow sudoers rule for exactly that command, so CI and systemd don't fight over port 5001.
Data: the staging seed is tiny (one scheme). Copying production API tables into staging was suggested (relay, 18 Oct). Copy into mm_api_staging/mm_nav_staging only. Never write to mm_data, which production uses.
Rotate: TEA's chat copy shows the staging DB users' password hashes before they were rotated on 14 Sep. Rotating again is cheap; if done, update db.env and restart the service.