While triggering the same DAG repeatedly via the Airflow REST API for a staged rollout check, the request came back 4xx with no dag_run_id in the body — the DAG never actually ran — yet the script treated it as success.

Encountered this while building AI Analytics — LLM-powered analytics that surfaces market trends, user behavior, and sales data for precise operations strategy. The staged rollout of the ad-decision pipeline needed to trigger the same analysis repeatedly on Airflow for comparison, and some triggers were failing silently.

TL;DR​

Airflow enforces a unique constraint on each DAG's logical_date (dag_run_id must also be unique). POSTing /dags/{dag_id}/dagRuns with a logical_date that already exists gets rejected with a 4xx, and the response body contains no dag_run_id. If you only check the HTTP status code and don't inspect the returned dag_run_id, you'll mistake the rejection for success. Fix: use a distinct logical_date (and dag_run_id) on every trigger.

Symptoms​

To run a comparison test, the same DAG was triggered repeatedly with a fixed date 2026-01-01:

$ curl -s -X POST "$AIRFLOW/api/v2/dags/my_dag/dagRuns" \
    -H "Authorization: Bearer $TOKEN" \
    -H "Content-Type: application/json" \
    -d '{
      "dag_run_id": "manual-run-1",
      "logical_date": "2026-01-01T00:00:00Z"
    }'
# First time: returns a normal dag_run object with dag_run_id ✅

$ curl -s -X POST "$AIRFLOW/api/v2/dags/my_dag/dagRuns" \
    -H "Authorization: Bearer $TOKEN" \
    -H "Content-Type: application/json" \
    -d '{
      "dag_run_id": "manual-run-2",
      "logical_date": "2026-01-01T00:00:00Z"   # ⚠️ same logical_date
    }'
# Second time: returns an error object, no dag_run_id ❌
{
  "detail": "...",
  "status": 400,
  "title": "Bad Request",
  "type": "https://airflow.apache.org/docs/apache-airflow/2/stable-rest-api-ref.html#/default/Error"
}

If the caller only checks "is it 2xx" and stops there, or parses the JSON without verifying that dag_run_id exists, the second failure is silently swallowed — no error in the logs, no run in the Airflow UI.

Root Cause​

Airflow uses dag_run_id as the primary key for each run and maintains uniqueness on (dag_id, logical_date) in the metadata DB's dag_run table. logical_date is the "logical time" of a run — the scheduler uses it to decide whether a given schedule slot has already executed. Once a run with some logical_date exists for a DAG, triggering again with the same value is rejected to prevent duplicate execution.

The catch is that this failure is a 4xx with an error JSON, not a connection error or a 5xx. Many scripts only do a coarse response.status_code == 200 check, or grab the JSON and read fields without verifying dag_run_id is present — so "creation rejected" reads as "creation succeeded".

Solution​

Core idea: use a distinct logical_date (and dag_run_id) on every trigger. For replay / rollout-comparison scenarios, just append a counter to the date:

# Each iteration uses a different logical_date (2026-01-01 / 02 / 03 …)
for i in 1 2 3; do
  curl -s -X POST "$AIRFLOW/api/v2/dags/my_dag/dagRuns" \
    -H "Authorization: Bearer $TOKEN" \
    -H "Content-Type: application/json" \
    -d "{
      \"dag_run_id\": \"manual-run-$i\",
      \"logical_date\": \"2026-01-0${i}T00:00:00Z\"
    }"
done

Even better, use an incrementing timestamp so logical_date and dag_run_id never collide. More importantly: always verify the dag_run_id field in the response — treat it as the only proof the trigger actually succeeded:

import requests

def trigger_dag(dag_id: str, logical_date: str, conf: dict | None = None) -> str:
    resp = requests.post(
        f"{AIRFLOW}/api/v2/dags/{dag_id}/dagRuns",
        headers={"Authorization": f"Bearer {TOKEN}", "Content-Type": "application/json"},
        json={"dag_run_id": f"manual-{logical_date}", "logical_date": logical_date, "conf": conf or {}},
    )
    # ❌ Not enough: status-only check lets 4xx slip through as success
    # resp.raise_for_status()
    data = resp.json()
    # ✅ Correct: only count it as created if dag_run_id is present
    if "dag_run_id" not in data:
        raise RuntimeError(f"Trigger failed: {resp.status_code} {data}")
    return data["dag_run_id"]

# A distinct logical_date each time makes repeated triggers safe
for i in range(1, 4):
    trigger_dag("my_dag", f"2026-01-0{i}T00:00:00Z")

Keep dag_run_id unique too — it's the primary key, and duplicates are rejected outright. A "prefix + logical_date" convention is common: unique, and easy to spot in the UI.

FAQ​

How do you trigger a DAG with the Airflow REST API?​

POST /api/v2/dags/{dag_id}/dagRuns with a body containing at least dag_run_id and logical_date (plus an optional conf for parameters). Both must be unique within the same DAG, or Airflow returns 4xx. In code, prefer the TriggerDagRunOperator, which also generates a unique run id internally.

Why does triggering the same DAG repeatedly fail in Airflow?​

Because Airflow maintains a unique constraint on (dag_id, logical_date) in the dag_run table, and dag_run_id itself is a primary key. Duplicate logical_date or dag_run_id values are rejected with 4xx. For replays or staged comparisons, give each trigger a fresh logical_date (or incrementing timestamp).

After deleting a DAG's .py file in Airflow to retire it, the dag_id still hangs around in the Web UI list and the database; even stranger — if you clear the metadata first and delete the file second, the just-cleared rows "come back to life."

Encountered this while building AI Ops — an LLM-powered analytics pipeline where retiring an old report DAG required cleaning its metadata too, otherwise the UI list and scheduled scans stayed polluted by residual rows.

TL;DR​

Airflow's dag-processor periodically scans the DAG folder and re-registers DAGs, and airflow dags reserialize doesn't purge "file-already-deleted" orphan rows — so just deleting the .py file won't make the dag_id vanish from the UI or DB. Conversely, clearing metadata before deleting the file lets the processor re-register the cleared rows on its next scan ("revival"). Correct order: ①delete the file first so the processor stops registering → ②SQL DELETE in foreign-key order → ③run airflow dags reserialize to verify.

Symptoms​

Retiring the shop_report_aggregation DAG — after deleting its .py file:

$ ls /opt/airflow/project/airflow_dags/shop_report_aggregation.py
ls: cannot access '.../shop_report_aggregation.py': No such file or directory

$ # but it's still in the database
$ docker exec cclhub-db psql -U airflow -d airflow -c \
    "SELECT dag_id, is_paused, is_active FROM dag WHERE dag_id='shop_report_aggregation';"
       dag_id            | is_paused | is_active
--------------------------+-----------+-----------
 shop_report_aggregation | f         | t          ← still there

It's not just the dag table — the matching rows in serialized_dag, dag_code, and dag_version are all still there, so the Web UI keeps showing this "deleted" DAG.

Worse is the reverse order — clear metadata first, delete file second:

T0  DELETE FROM dag WHERE dag_id='shop_report_aggregation';   ← cleared
T1  (.py file not deleted yet)
T2  dag-processor scan fires; file exists, dag table has no row → re-registers
T3  SELECT ... FROM dag WHERE dag_id='shop_report_aggregation';   ← it's back (revival)

Root Cause​

Two mechanisms stack up:

1. dag-processor scans and re-registers periodically. Airflow's dag-processor (part of the Scheduler) scans dags_folder on processor_poll_interval (default ~5 min), parses each .py file, and upserts into the metadata tables (dag, serialized_dag, dag_version). As long as the file exists, the next scan rewrites those rows. That's the direct source of "revival" — you clear the row, the file is still there, and the processor re-registers it as a new DAG.

2. reserialize ignores "file-gone" orphan rows. airflow dags reserialize re-serializes existing DAG files and refreshes serialized_dag; it does not delete orphan dag rows whose files have vanished. And airflow dags cleanup only purges expired dag_run history by default — it also leaves the dag / serialized_dag / dag_code / dag_version metadata tables alone. So after you delete the file, the metadata rows become orphans nobody cleans.

┌─ dag-processor ──────────────────────────────┐
│  scans dags_folder                            │
│  ├─ file present → upsert dag / serialized... │  ← source of revival
│  └─ file absent  → skip, no row deletion      │  ← orphan residue
└───────────────────────────────────────────────┘

Conclusion: to actually remove the metadata, you must give the processor no file to register (delete the file first), then manually clean the residual rows.

Solution​

Step 1: Delete the file first​

Make the .py file disappear from the DAG folder so dag-processor stops registering it.

# In production this is usually synced to the volume-mounted DAG folder
# /opt/airflow/project/airflow_dags/ via git pull
git pull   # removes shop_report_aggregation.py from the repo and the folder

# or delete directly (after confirming nothing depends on it)
rm /opt/airflow/project/airflow_dags/shop_report_aggregation.py

Step 2: Clean metadata in foreign-key order​

DELETE in foreign-key dependency order to avoid constraint violations. Deleting dag_run CASCADEs to task_instance:

BEGIN;

-- 1. run history (CASCADEs to task_instance)
DELETE FROM dag_run      WHERE dag_id = 'shop_report_aggregation';

-- 2. serialized DAG
DELETE FROM serialized_dag WHERE dag_id = 'shop_report_aggregation';

-- 3. version
DELETE FROM dag_version  WHERE dag_id = 'shop_report_aggregation';

-- 4. dag main table
DELETE FROM dag          WHERE dag_id = 'shop_report_aggregation';

-- 5. dag_code is keyed by source hash; multiple DAGs may share the same code;
--    only delete hashes no longer referenced by any serialized_dag
DELETE FROM dag_code
WHERE dag_hash NOT IN (SELECT dag_hash FROM serialized_dag);

COMMIT;

Step 3: Verify​

airflow dags reserialize

# confirm the dag row is not rebuilt
docker exec cclhub-db psql -U airflow -d airflow -c \
  "SELECT count(*) FROM dag WHERE dag_id='shop_report_aggregation';"
#  count
# -------
#      0   ✅

After reserialize, dag / serialized_dag / dag_code / dag_version are all 0 for that dag_id, and the next processor scan doesn't rebuild them — the cleanup is stable.

As a side note, on the same pipeline, pandas NaN crashing XCom serialization is another pitfall worth bookmarking.

Notes​

FAQ​

Why does a DAG still show in Airflow after deleting its .py file?​

Deleting the file doesn't clean the database. Rows in dag / serialized_dag / dag_code / dag_version still exist, and the Web UI reads those tables to render the list, so the deleted DAG keeps showing. Airflow has no built-in command to purge these orphan rows automatically; you must SQL DELETE them manually in foreign-key order.

How do I completely delete an Airflow DAG and all its metadata?​

Three steps: 1) delete the .py file so dag-processor stops registering it; 2) SQL DELETE in foreign-key order (dag_run → serialized_dag → dag_version → dag → orphan dag_code); 3) run airflow dags reserialize, then query the dag table to confirm the dag_id row count stays at 0 and isn't rebuilt.

What's the correct order to clean Airflow DAG metadata, and why not clear metadata before deleting the file?​

Delete the file first, then clean metadata. If you reverse it, the .py file still exists, so dag-processor re-registers the cleared dag row on its next scan — the metadata "comes back to life." Only by making the file vanish first (so the processor has nothing to register) and then cleaning the residual rows can you fully retire the DAG.

When an Airflow task calls ti.xcom_push() to pass pandas-processed results downstream, the task crashes outright — ValueError: Out of range float values are not JSON compliant: nan — and the app's custom logs table shows no error at all.

Encountered this while building AI Ops — an LLM-powered analytics pipeline where Airflow DAGs pull SQL data, process it with pandas, and pass results between tasks via XCom.

TL;DR​

XCom serializes with JSON under the hood, and Airflow calls json.dumps(..., allow_nan=False) to follow the JSON spec strictly — which has no NaN / Infinity whatsoever. The moment a float NaN (converted from SQL NULL by pandas) enters the data passed to xcom_push, serialization throws ValueError. Fix: recursively walk the data before push and convert NaN / ±Inf to None (JSON null).

Symptoms​

A quarterly report DAG failed, but the symptom was baffling — in the app's custom logs table, steps 1–4 for that trace were all fine, "analysis done" even logged twice (a retry), then it cut off: step 5 missing, with no error row at all:

trace=91c126c3
├─ step 1  SQL fetch            ✅
├─ step 2  pandas process       ✅
├─ step 3  rule judge           ✅
├─ step 4  LLM analysis done    ✅  ← retried after this
└─ step 5  XCom push result     ❌  ← missing, no error record

The real traceback was only in the Airflow task log:

# inside container /opt/airflow/logs/dag_id=ai_analysis_v2/run_id=.../task_id=analyze_results/attempt=N.log
ValueError: Out of range float values are not JSON compliant: nan
  File ".../ai_analysis_tasks.py", line 142, in analyze_results
    ti.xcom_push(key='sql_metadata', value=result)

The crash landed exactly on ti.xcom_push — the instant the task pushed results into XCom.

Root Cause​

Three layers stack up, all required:

1. The JSON spec has no NaN / Infinity. RFC 8259 only allows finite numeric literals. Python's json.dumps will happily emit bare NaN and Infinity by default, but those are Python-specific extensions, not valid JSON — any strict parser (Airflow included) rejects them.

2. Airflow XCom serializes with allow_nan=False. XCom's default JSON serializer explicitly disables NaN tolerance, so encountering NaN throws ValueError: Out of range float values are not JSON compliant instead of silently emitting invalid JSON.

3. pandas reads SQL NULL as NaN. pandas.read_sql returns float('nan') for SQL NULL columns. Once such a column flows through computation and to_dict('records') into the result object, NaN hitches a ride into xcom_push:

import pandas as pd

# A SQL NULL cell → pandas reads it as NaN
df = pd.DataFrame({"ad_roi": [1.2, None, 0.8]})
records = df.to_dict("records")
# [{'ad_roi': 1.2}, {'ad_roi': nan}, {'ad_roi': 0.8}]   ← nan slipped in

# downstream task crashes on push
ti.xcom_push(key="result", value=records)
# ValueError: Out of range float values are not JSON compliant: nan

This stayed latent for a long time because the data usually had values in those columns; it only surfaced when a client had zero ad spend for an entire quarter and ad_roi came back NULL across the board — the first time NaN entered the XCom path at scale.

Why no error in the logs table? Because the crash happens during XCom serialization, outside the task function's try/except — the exception bubbles straight up to the Airflow scheduler and only lands in Airflow's own task log. The app's custom logs table catch never gets a chance to record it. That's what makes this failure so confusing: it looks "silent."

Solution​

Scrub all NaN / ±Inf from the data before it enters XCom.

1. Write a pure recursive cleaner​

import math

def json_safe_value(obj):
    """
    Recursively convert NaN / +Inf / -Inf to None so the data is
    strictly JSON-serializable. Handles dict / list / tuple / scalar;
    unknown types pass through unchanged.
    """
    if isinstance(obj, float):
        if math.isnan(obj) or math.isinf(obj):
            return None
        return obj
    if isinstance(obj, dict):
        return {k: json_safe_value(v) for k, v in obj.items()}
    if isinstance(obj, (list, tuple)):
        return [json_safe_value(v) for v in obj]
    return obj

Why not df.fillna(None)? Because fillna(None) on numeric columns is unstable across pandas versions and dtypes — sometimes it coerces the dtype instead of nulling values. It also only handles DataFrames, not floats already nested inside dicts/lists after to_dict. Recursive cleaning at the "data is now native Python structures" layer is the most robust fallback.

2. Centralize the guard before push​

The worry-free approach is to hang the cleanup on the single chokepoint all xcom_push calls go through, rather than remembering to call it at every push site:

def push_safe(ti, key, value):
    """Clean NaN/Inf before XCom push to prevent serialization crashes."""
    ti.xcom_push(key=key, value=json_safe_value(value))

# inside the task
push_safe(ti, "sql_metadata", result)
push_safe(ti, "processor_output", processor_result)

3. Fix the "silent failure" observability gap​

Fixing serialization alone isn't enough — the gap where exceptions outside try/except never reach the app's logs table must be closed too. Attach a failure decorator that logs the top-level exception to your table before re-raising:

import functools
import logging

logger = logging.getLogger(__name__)

def log_task_failure(fn):
    @functools.wraps(fn)
    def wrapper(*args, **kwargs):
        try:
            return fn(*args, **kwargs)
        except Exception:
            logger.error("task %s failed", fn.__name__, exc_info=True)
            # write the traceback into the app's custom logs table here
            raise
    return wrapper

@log_task_failure
def analyze_results(**context):
    ...

Now if another exception slips outside a catch, the app's logs table still gets an error row — no more "silent failure."

After the fix, rerunning the same conf: DAG all green, DB write success, and the formerly-NaN ad_roi lands as null in the database; downstream is happy.

On the same Airflow analytics pipeline, this isn't the only way data silently misbehaves — PostgresHook silently dropping multi-statement SQL results is another classic.

Notes​

FAQ​

How to fix Airflow "Out of range float values are not JSON compliant"?​

XCom serializes with json.dumps(allow_nan=False) and ran into NaN / Infinity, which the JSON spec does not allow. The usual root cause is pandas reading a SQL NULL into float('nan') that then flows into xcom_push. Fix it by recursively converting NaN / ±Inf to None (JSON null) before push, centralized in a pure json_safe_value helper.

Why does my Airflow task fail but my custom logs table has no error?​

If the exception happens during XCom serialization, outside the task function's try/except, it only bubbles up to the Airflow scheduler and lands in the Airflow task log (inside the container at /opt/airflow/logs/). The app's custom logs table catch never sees it, so it looks like a "silent failure." To triage, read the Airflow task log traceback directly instead of only checking app logs.

Can Airflow XCom store pandas NaN directly?​

No. XCom defaults to JSON serialization, and the JSON spec only has finite numbers — no NaN / Infinity. The right fix is to convert NaN to None (JSON null) before push. Switching to binary object serialization sidesteps the type limit but produces unreadable, non-portable values with deserialization security risk; not recommended for production.

While building a server health-check endpoint, systemctl is-active redis-server returned inactive — yet Redis was happily serving requests.

Encountered this while building AI Analytics — LLM-powered analytics that surfaces market trends, user behavior, and sales data for precise operations strategy. The monitoring dashboard needs to reflect the real status of every infrastructure component, and Redis was reporting a false negative from the start.

TL;DR​

systemctl is-active only works for services that systemd manages through unit files. If Redis (or any service) runs as a bare process with no .service unit, systemctl can never see its true state and returns inactive (exit code 3). Reliable probing has to bypass systemctl and ask the process (pgrep) or the port (ss) directly.

Symptoms​

The probe called systemctl is-active for both Nginx and Redis:

$ systemctl is-active nginx
active            # ✅ fine

$ systemctl is-active redis-server
inactive          # ❌ looks like Redis is down
$ echo $?
3                 # exit code 3 = inactive

But every business endpoint was reading and writing Redis fine — only /api/v1/server-monitor/status reported Redis as down.

Root Cause​

systemd is a process manager, and it only knows about units it started and owns. When you run systemctl start redis-server (or let systemd read redis-server.service), systemd records that unit's state and is-active can return active.

On this server, Redis was started as a bare process — a direct redis-server invocation, or launched via nohup / a custom script, never registered as a systemd service. So:

• There is no redis-server.service in systemd's unit list at all;
• systemctl is-active redis-server can't find the unit, treats it as inactive, and returns exit 3;
• Nginx, by contrast, is a standard systemd service, so is-active correctly reports active.

In one line: is-active reflects systemd's view, not the system process view. "The process is running" and "systemd knows it's running" are two different things.

Solution​

Switch the probe to a "systemctl first → process/port fallback" chain. If systemctl hits, use it; otherwise confirm the process is actually alive with pgrep or ss. Use execFileSync (no shell, args passed as an array) to avoid command injection:

import { execFileSync } from "node:child_process";

/** Run a single command safely (no shell); unify non-zero exit to null */
function sh(cmd: string, args: string[]): string | null {
  try {
    return execFileSync(cmd, args, {
      stdio: ["ignore", "pipe", "ignore"],
      timeout: 2000,
    })
      .toString()
      .trim();
  } catch {
    return null; // inactive / process missing / timeout all land here
  }
}

/**
 * Probe whether a service is up: systemctl first, bare-process fallback.
 * @param unit   systemd unit name (e.g. "nginx")
 * @param proc   process name (e.g. "redis") for the pgrep fallback
 * @param port   listening port (e.g. 6379) for the ss fallback
 */
function isServiceUp(unit: string, proc?: string, port?: number): boolean {
  // 1. Try systemctl first (standard systemd services)
  const st = sh("systemctl", ["is-active", unit]);
  if (st && st !== "inactive" && st !== "unknown") {
    return true; // active, or activating/reloading etc.
  }

  // 2. Fallback A: find a PID by process name
  if (proc && sh("pgrep", ["-f", proc])) return true;

  // 3. Fallback B: confirm a listening port
  if (port) {
    const listening = sh("ss", ["-lnt"]);
    if (listening && listening.includes(`:${port} `)) return true;
  }

  return false;
}

// Nginx: standard systemd service, systemctl hits directly
const nginxUp = isServiceUp("nginx");

// Redis: may be a bare process — pass process name + port as fallback
const redisUp = isServiceUp("redis-server", "redis", 6379);

The most robust final check at the application layer is to let the service answer for itself — Redis's PING, PostgreSQL's SELECT 1, an HTTP health endpoint. A listening port only proves "the process started", not "the service is ready", so on critical paths add one more application-level probe:

$ redis-cli ping
PONG    # process alive + responsive = truly up

FAQ​

Why does systemctl is-active show inactive when the process is actually running?​

systemctl only queries services that systemd manages through unit files. If the process was started directly or with nohup as a bare process with no .service unit, systemd neither knows about it nor tracks it, so is-active can only return inactive (exit 3). That's a blind spot in systemd's view, not the process being down.

How do you reliably check whether a process is running?​

Don't rely on systemctl alone. Use pgrep <name> to find the PID, or ss -lntp | grep <port> to confirm a listening port — these inspect the system process table / network stack, independent of systemd management. For critical services, add an application-level probe (e.g. redis-cli ping) to verify both that the process exists and that it responds.

When you validate LLM tool_call / function call output with Zod, the model occasionally emits an extra field — say you only defined amount / category, and it also fills in note — and the whole validation fails, the action is silently dropped, and the user just gets "not recognized." In reality an entire tool_call was whole-rejected.

Encountered this while building Life — a natural-language bookkeeping and wellness assistant where you just talk to record entries and the AI extracts amount, category, and account. When a user said "delete that coffee from yesterday," the model slipped an extra note: "coffee" into the delete locator, trying to locate by remark.

TL;DR​

Zod's .strict() means "this object must not contain any unknown keys — one extra throws." That constraint fits validating clients you fully control, but LLM function call output is model-generated and inherently uncontrollable — it fills in fields it "thinks should be there," especially when multiple tools share similar schemas. One irrelevant field kills the whole tool_call, validation returns null, and the action is silently lost. Fix: drop .strict() and use Zod's default strip (silently removes unknown keys) for tolerance, paired with safeParse as a fallback.

Symptoms​

The locator schema for delete/update defines a few known fields, tightened with .strict():

import { z } from "zod";

// ❌ dangerous: with .strict()
const LocatorSchema = z.object({
  date: z.string().optional(),
  category: z.string().optional(),
  noteContains: z.string().optional(),
}).strict();   // ← any unknown key throws

// parse the LLM's tool_call arguments
function parseToolCall(raw: unknown) {
  const parsed = LocatorSchema.safeParse(raw);
  if (!parsed.success) {
    return null;   // ← the whole tool_call is dropped
  }
  return parsed.data;
}

The user says "delete that coffee from yesterday," and the model produces a reasonable but extra-fielded output:

{
  "date": "yesterday",
  "noteContains": "coffee",
  "note": "coffee"
}

The model filled both noteContains (in schema) and note (out of schema, which it thought should exist). .strict() rejects the unknown key note outright, parseToolCall returns null, and the delete action is silently dropped — the user gets "not recognized" when it was actually a whole-reject.

Root Cause​

.strict() changes Zod's policy on unknown keys, and LLM output naturally carries unknown keys.

Zod z.object() has three policies for unknown keys:

.strict() is designed for "contract strictness" — the server defines which fields exist, the client should supply only those, and anything extra is a breach. That logic holds for traditional APIs because the client is developer-written and can be held to the contract.

But LLM function calling flips the premise:

1. The output comes from model generation, not a developer-written client. The model guesses what to fill based on the schema's description and examples; schemas reused across domains (e.g. a locator shared by budget / mood / todo) confuse it further, so it fills in fields it "thinks should be there."
2. Wrong fields are the norm, not an exception. The model occasionally emitting an extra note or omitting an optional field is expected behavior in LLM apps and shouldn't be punished by failing the whole call.
3. The failure is silently swallowed. After safeParse fails and returns null, the upstream can only vaguely say "not recognized," while the real cause (an unknown key) sits unseen in parsed.error.

LLM output { date, noteContains, note }
                        │
                        ▼
         .strict() hits unknown key "note"
                        │
                        ▼
           safeParse → { success: false }
                        │
                        ▼
          parseToolCall returns null (action dropped)
                        │
                        ▼
       user gets "not recognized" (actually a whole-reject)

Solution​

1. Drop .strict(), use default strip for tolerance​

// ✅ recommended: no .strict(), Zod defaults to stripping unknown keys
const LocatorSchema = z.object({
  date: z.string().optional(),
  category: z.string().optional(),
  noteContains: z.string().optional(),
});
// the extra "note" is silently removed; known fields parse normally

After dropping .strict(), "delete that coffee from yesterday" parses cleanly into { date, noteContains }; the extra note is stripped and the delete action runs correctly.

2. If unknown keys are useful, keep them with .passthrough()​

When the extra field actually carries semantics you want to use (e.g. the model filled note to express "locate by remark"), don't drop it — keep it and decide how to consume it:

const LocatorSchema = z.object({
  date: z.string().optional(),
  category: z.string().optional(),
  noteContains: z.string().optional(),
}).passthrough();   // keep unknown keys; parsed.data.note is still readable

Even better, promote it to a known field — if the model keeps filling some unknown key, the schema is missing a capability slot, so add it (here noteContains was added after absorbing the "locate by remark" need).

3. Make failures observable — don't silently return null​

Regardless of policy, when safeParse fails, log the specific error instead of swallowing it into null:

function parseToolCall(raw: unknown) {
  const parsed = LocatorSchema.safeParse(raw);
  if (!parsed.success) {
    // log Zod's concrete error (which key, what problem) for triage
    logger.warn(
      { raw, issues: parsed.error.issues },
      "locator parse failed"
    );
    return null;
  }
  return parsed.data;
}

Now when something goes wrong, the log has the full issues (including the unknown-key path), instead of an unactionable "not recognized."

After the fix, "delete that coffee from yesterday" → delete_record { locator: { noteContains: "coffee" } } parses correctly with no silent drop.

Notes​

FAQ​

Why does Zod .strict() make LLM output validation fail?​

.strict() requires an object to have no unknown keys — one extra throws. LLM function call output is model-generated, guessing from the schema description, and routinely includes fields it thinks should be there (especially with cross-domain schemas). The moment an unknown key appears, .strict() fails the entire validation and drops the whole tool_call.

Should I use strict when validating LLM function calling output with Zod?​

Not recommended. .strict() suits validating clients you fully control (developer-written code can be held to a contract), but LLM output is uncontrollable and over/under-filling is the norm. Drop .strict() and use Zod's default strip (silently removes unknown keys) for better tolerance; if unknown keys carry semantics you want, use .passthrough() to keep them, or promote them to known fields.

Does Zod strip or throw on unknown fields by default?​

Default is strip — it silently removes unknown keys without error. .strict() makes it throw on unknown keys; .passthrough() keeps them as-is. For uncontrollable output like LLM generations, prefer default strip or passthrough over .strict(), which kills the whole payload over a single irrelevant field.

Wanting to "just pass the exception up unchanged" from a catch block, I reflexively wrote throw; — the bare rethrow I was used to in C# — and tsx/esbuild immediately failed to transform it: Unexpected ";".

Encountered this while building AI Analytics — an LLM-powered analytics pipeline that surfaces market trends, user behavior, and sales data for precise operations.

TL;DR​

JavaScript has no bare rethrow syntax. throw; (a bare throw) is a compile-time SyntaxError in all three toolchains: Node, tsc, and esbuild. To rethrow a caught exception you must throw e (the catch block needs a binding); to throw a new one, throw new Error(...).

The symptom​

The same throw; produces differently-worded errors across toolchains, but all of them are syntax errors (not runtime errors):

try {
  something();
} catch {
  throw;   // ← bare rethrow
}

The misleading one is esbuild's Unexpected ";" — it's tempting to read as "esbuild/tsx doesn't support some newer syntax." But run the same snippet through native Node and you get the identical SyntaxError. This isn't a tool limitation; the language itself has no such form.

Root cause​

The ECMAScript throw statement mandates an expression:

ThrowStatement : throw Expression ;

That is, throw must be followed by a value (throw err, throw new Error(), throw "fail") — the slot before the semicolon cannot be empty. JavaScript has no "bare throw = rethrow the current exception" semantics, which is the key difference from C# / Java / Python:

One common confusion: ES2019 added optional catch binding (catch {} may omit the parameter), but that is orthogonal to bare throw. Even with a binding present, throw; still errors —

try { f(); } catch (e) { throw; }   // still a SyntaxError; e is NOT auto-fed to throw

Confirmed in tsx as Unexpected ";". The expression after throw cannot be omitted; there are no exceptions.

The fix​

Pick the form that matches your intent:

// 1. Rethrow the original exception — the most common need
try {
  doWork();
} catch (e) {
  log(e);
  throw e;            // ✅ include e
}

// 2. Wrap in a new exception
try {
  doWork();
} catch (e) {
  throw new Error(`failed: ${e.message}`);   // ✅ throw + expression
}

// 3. With ES2019 catch {} (no parameter), there is nothing to rethrow — throw new
try {
  doWork();
} catch {
  throw new Error("doWork failed");   // ✅ throw; here would be wrong
}

A minimal runnable repro and fix — run it directly with tsx:

function risky(): void {
  throw new Error("origin");
}

function rethrowOptional(): void {
  try {
    risky();
  } catch (e) {          // ← must receive e
    console.log("caught, rethrowing");
    throw e;             // ← not throw;
  }
}

try {
  rethrowOptional();
} catch (e) {
  console.log("recovered:", (e as Error).message);   // origin
}

On the call stack: throw e reuses the same error object, whose .stack was captured at new Error time; rethrow does not overwrite it. Only throw new Error(...) generates a fresh stack from the current throw site. So "does rethrow lose the stack?" — no, as long as you don't construct a new error.

Another common exception-handling pitfall is a catch block that swallows the error entirely, surfacing as a silent failure — see Python task marked failed but no error? try/except swallowed it. Worth watching across every language.

Caveats​

FAQ​

How do you rethrow a caught exception in JavaScript?​

Use throw e, and catch must take a binding: catch (e) { ...; throw e; }. JavaScript has no bare rethrow — a standalone throw; is a SyntaxError that Node, tsc, and esbuild all reject at compile time. It is not a limitation of any single tool.

Does rethrowing an exception in JavaScript preserve the original stack?​

Yes. throw e reuses the same error object, whose .stack was captured when the error was constructed with new Error; rethrow neither overwrites nor resets it. Only throw new Error(...) generates a fresh stack from the current throw site — so if you want the earliest origin during debugging, use throw e.

How do you correctly rethrow inside a JavaScript try/catch?​

catch must receive the error and throw it back: try { ... } catch (e) { log(e); throw e; }. With ES2019's catch {} (parameter omitted) there is no variable to throw, so you can only throw new Error(...). Either way, throw must be followed by an expression — throw; is always illegal.

While prefixing vector collections per tenant with {tenant_id}_{collection}, the very first request bounced straight back from Milvus — invalid collection name: the first character ... must be an underscore or letter — and the endpoint returned 500.

Encountered this while building AI Customer Service — 24/7 AI support that answers product usage questions with instant guidance and best practices.

TL;DR​

Milvus strictly validates collection names: the first character must be a letter or underscore, only [a-zA-Z0-9_] are allowed (no hyphens), and length ≤ 255 — otherwise it raises invalid collection name (error code 1100). A UUID typically starts with a digit and always contains hyphens -, tripping both rules, so you cannot concat a tenant_id UUID into a collection name for isolation. Use the original name plus a tenant field filter instead.

The symptom​

A query endpoint with collection=system_product_help returned 500, with a single line in the rag-service log:

pymilvus.exceptions.MilvusException: code=1100,
Invalid collection name: 00000000-0000-0000-0000-000000000001_system_product_help.
the first character of a collection name must be an underscore or letter

The strange part: another endpoint with the same parameter (/query-logs) returned 200 — because it only reads PostgreSQL and never touches Milvus. Only paths that actually call Milvus has_collection trigger the validation.

Root cause​

The code built the collection name with f"{tenant_id}_{collection}", yielding e.g. 00000000-0000-0000-0000-000000000001_system_product_help. This name breaks two rules at once:

00000000-0000-0000-0000-000000000001_system_product_help
^              ^ ^
│              │ └─ underscore is fine here, but...
│              └─── hyphen `-` is illegal
└────────────────── first char is digit `0` (must be letter/underscore)

Milvus's collection name rules (source nameutil.go, regex ^[a-zA-Z_][a-zA-Z0-9_]*$, length ≤ 255):

A UUID almost always violates this: the standard 8-4-4-4-12 form carries 4 hyphens, and the first segment usually starts with a digit. Prefixing a collection name with such a token gets every has_collection / describe_collection / create call rejected server-side with code 1100.

Worse: because the concatenated name was never valid, the supposed "per-tenant prefix isolation" never actually worked — the collections that really exist in the database all use the un-prefixed original names. The concat logic was systematically disconnected from the real data; an assumption baked into code that no one ever verified.

The fix​

Don't put tenant_id in the collection name. Always use the original name; let a regular field handle tenant isolation:

from pymilvus import MilvusClient

client = MilvusClient(uri="http://localhost:19530")

# ❌ Wrong: UUID prefix — starts with a digit + contains hyphens → code 1100
tenant_id = "00000000-0000-0000-0000-000000000001"
bad_name = f"{tenant_id}_system_product_help"   # illegal

# ✅ Right: collection keeps its original name; tenant_id is a schema field
client.create_collection(
    collection_name="system_product_help",        # legal, stable
    schema=client.create_schema(auto_id=True, enable_dynamic_field=False),
)
# Filter by tenant_id at write and query time, instead of renaming the collection
client.insert(
    collection_name="system_product_help",
    data=[{"tenant_id": tenant_id, "text": "...", "vector": [...]}],
)

If you genuinely need "a readable prefix" for multi-tenant or environment isolation, convert any arbitrary string into a safe slug before concatenating:

import re

def safe_slug(raw: str) -> str:
    # Replace anything outside [a-zA-Z0-9_] with underscore; prefix if first char is illegal
    s = re.sub(r"[^a-zA-Z0-9_]", "_", raw)
    if not re.match(r"^[a-zA-Z_]", s):
        s = "_" + s
    return s[:255]   # keep within the length cap

name = f"{safe_slug(tenant_id)}_system_product_help"   # legal

When debugging a 500 like this, first scan the service logs (PM2 or equivalent) for MilvusException — the error code and the "first character must be ..." hint pinpoint an illegal name almost immediately, so you don't need to dig into business logic.

As an aside, services that depend on Milvus have their own gotcha: containers without a restart policy take the whole RAG pipeline down after a crash — see Docker Compose service won't come back? Check the restart policy. On the query side, watch out for RRF scores being incompatible with the similarity threshold in hybrid search.

Caveats​

FAQ​

What are the Milvus collection name naming rules?​

The first character must be a letter or underscore; the remaining characters allow only letters, digits, and underscores ([a-zA-Z0-9_]). Hyphens and spaces are forbidden, and the maximum length is 255 characters. Milvus enforces this server-side with a regex; violations raise invalid collection name (error code 1100), failing both creation and lookup. snake_case is the safe choice.

What is the maximum length of a Milvus collection name?​

255 characters. Anything longer is rejected with invalid collection name (error code 1100). Real names rarely approach this limit — what usually pushes you over is concatenating long UUIDs or multi-segment paths into the name, which is itself a sign you shouldn't be putting that dynamic string in the collection name at all.

Why can't a UUID be used as a Milvus collection name prefix?​

The standard UUID form usually starts with a digit (violating "first char must be a letter/underscore") and always contains four hyphens - (not in the allowed charset) — both break the rules. Using tenant_id as a collection-name prefix for isolation is a common misuse: not only is the name illegal, it also makes the collection count balloon with tenants. The right approach is to put tenant_id in a regular field or partition key and keep the collection name stable.

Right after tightening a table's unique key — dropping a column that no longer discriminated between rows — every previously working UPSERT immediately failed in bulk with there is no unique or exclusion constraint matching the ON CONFLICT specification.

Encountered this while building AI Analytics — an LLM-powered analytics pipeline that surfaces market trends, user behavior, and sales data for precise operations.

TL;DR​

PostgreSQL's ON CONFLICT (cols) requires cols to exactly match an existing unique constraint or unique index (same columns, same order — otherwise SQL state 42P10). The moment you ALTER the unique key, every INSERT ... ON CONFLICT that references it must be updated; and once the migration lands, the write side must deploy immediately, because the in-between window keeps erroring.

The symptom​

As soon as the unique-key change went live, the scheduled import job failed across the board, with only this line in the write log:

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
SQL state: 42P10

Zero rows written to the business table, while plain SELECTs against the same table worked fine — the failure was isolated to the ON CONFLICT write path.

Root cause​

The column set you pass to ON CONFLICT (cols) is the arbiter. PostgreSQL requires it to exactly match some UNIQUE constraint or unique index on the table:

• the set of columns must be the same;
• the order of columns must be the same;
• for a partial unique index (one with a WHERE), ON CONFLICT must carry the same WHERE.

When nothing matches, PostgreSQL has no index to decide what "conflict" means, and raises 42P10.

The classic trigger is shrinking a unique key. The original key had 3 columns; you realize one of them (say audience) has 4 distinct values whose metric rows are 100% identical — pure redundancy — so you drop it down to 2 columns. That's the right optimization, but the old INSERT still says ON CONFLICT (c1, c2, c3) while only (c1, c2) remains as a unique constraint. The arbiter has no landing spot, and the statement errors out.

old unique key: UNIQUE (store_id, metric_key, audience)
new unique key: UNIQUE (store_id, metric_key)

old INSERT: ON CONFLICT (store_id, metric_key, audience)   ← no match

The fix​

Here is a minimal reproduction — create, trigger, and fix in one go, runnable directly in psql:

-- 1. A table with a 3-column unique key
CREATE TABLE daily_metric (
    store_id   TEXT NOT NULL,
    metric_key TEXT NOT NULL,
    audience   TEXT NOT NULL,
    value      NUMERIC,
    CONSTRAINT daily_metric_unique UNIQUE (store_id, metric_key, audience)
);

-- 2. Old UPSERT: ON CONFLICT includes audience
INSERT INTO daily_metric (store_id, metric_key, audience, value)
VALUES ('s1', 'revenue', 'visitor', 100)
ON CONFLICT (store_id, metric_key, audience)
DO UPDATE SET value = EXCLUDED.value;

-- 3. Shrink the unique key: drop audience
ALTER TABLE daily_metric
    DROP CONSTRAINT daily_metric_unique,
    ADD  CONSTRAINT daily_metric_unique_new UNIQUE (store_id, metric_key);

-- 4. Re-run the INSERT from step 2 — it now errors ↓
-- ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

The fix is to shrink the INSERT's ON CONFLICT columns to match the 2-column key. Since audience no longer discriminates, pin it to a literal on the write side so incoming parameters can't synthesize extra rows:

INSERT INTO daily_metric (store_id, metric_key, audience, value)
VALUES ('s1', 'revenue', 'visitor', 100)
ON CONFLICT (store_id, metric_key)         -- ← shrunk to match
DO UPDATE SET value = EXCLUDED.value;

The part that actually bites is the deployment order, not the SQL itself:

1. Ship the migration first (DROP old constraint + ADD new constraint);
2. Immediately ship the write-side code (the INSERT's ON CONFLICT becomes 2 columns);
3. Leave no gap between the two — old code against the new schema raises 42P10, and new code against the old schema raises 42P10 just the same (no 2-column unique constraint exists yet).

If you use an ORM like Drizzle, an ON CONFLICT column list baked into a sql template is easy to forget when the schema changes — the cost of schema/write-side drift shows up in another Drizzle + PostgreSQL pitfall too.

Caveats​

FAQ​

Does PostgreSQL ON CONFLICT require a unique constraint?​

Only when you name columns. ON CONFLICT (cols) must match an existing UNIQUE constraint or unique index exactly, or you get 42P10. If you just want "skip on any conflict" without caring which constraint fired, use ON CONFLICT DO NOTHING without columns — it needs no specific index.

Can PostgreSQL ON CONFLICT target multiple unique constraints?​

No. A single INSERT's ON CONFLICT can name only one arbiter constraint (one column set, or one index name). A table may have multiple unique keys, but a single statement picks exactly one for conflict detection. To handle different unique keys differently, either split into multiple writes or query first in the application layer before choosing INSERT vs UPDATE.

How to fix there is no unique or exclusion constraint matching the ON CONFLICT specification?​

That is error code 42P10: the ON CONFLICT column set has no matching unique index on the table. Check in order: a UNIQUE constraint covers those columns, the columns and their order match exactly, and any INSERT was updated after a recent unique-key change. If the arbiter is a partial unique index with a WHERE, add the same WHERE clause to ON CONFLICT.

When an Airflow DAG reads a .sql template file as a single string and passes it to PostgresHook.get_pandas_df(), prior SELECT results are silently dropped — the DAG reports "SQL query returned no results", but copying the same SQL into psql returns data normally.

Encountered this while building AI Analytics — an LLM-powered analytics pipeline where an Airflow DAG reads multi-query report templates from .sql files and executes them.

TL;DR​

PostgresHook.get_pandas_df(sql) internally calls pandas.read_sql(sql, conn) → psycopg2 cursor.execute(sql). When sql is a single string with multiple ;-separated SELECTs, the DBAPI only exposes the cursor of the last result set — prior query results are silently dropped with no error. Fix: split by top-level semicolons into a list[str] and call get_pandas_df per statement, or pass the list directly so DbApiHook runs them sequentially.

Symptom​

The DAG task executing shop_monthly_overview.sql reports "SQL query returned no results":

sql_count = 1   ← template clearly contains 4 queries
result = "❌ SQL query returned no results"

But the same SQL pasted into psql against the same database with the same parameters returns data for all 4 SELECTs.

Reproduction​

Verify get_pandas_df behavior with multi-statement SQL directly inside the Airflow container:

from airflow.providers.postgres.hooks.postgres import PostgresHook

hook = PostgresHook(postgres_conn_id="postgres_default")

# Three SELECTs concatenated into one string
sql = "SELECT 1 AS a; SELECT 2 AS b; SELECT 99 AS c WHERE 1=0;"

df = hook.get_pandas_df(sql)
print(df.columns.tolist())  # ['c']  ← only got the last statement's columns
print(df)                    # Empty  ← the last statement itself returns 0 rows

Expected three result sets, got only the last one (SELECT 99 ... WHERE 1=0, 0 rows). The first two completely disappear with no error or warning.

Root Cause​

The call chain is PostgresHook.get_pandas_df → DbApiHook.get_pandas_df → pandas.io.sql.read_sql → psycopg2 cursor.execute(sql).

The DBAPI protocol (PEP 249) allows execute to accept a string with multiple ;-separated statements. PostgreSQL executes all of them, but the cursor only exposes the last result set — this is inherent PostgreSQL wire protocol behavior, not an Airflow or pandas bug.

┌────────────────────────────────────────────────────┐
│ SELECT 1;  ← executed, result set 1 dropped at once │
│ SELECT 2;  ← executed, result set 2 dropped at once │
│ SELECT 99 WHERE 1=0;  ← executed, result set 3 exposed │
└────────────────────────────────────────────────────┘
                       ↓
            pandas.read_sql only fetches result set 3

The root cause in our code is task_execute_sql reading the entire .sql file as a single string and passing it to get_pandas_df:

# ❌ Problematic code
sql_text = open(sql_path).read()  # full text with 4 SELECTs
df = pg_hook.get_pandas_df(sql_text)  # only gets the last result

Why does psql return data? Because the psql frontend actively iterates through all result sets and prints them one by one, while a DBAPI cursor does not.

Solution​

Option A (Recommended): Split by top-level semicolon, execute one by one​

Suitable for .sql template files — they contain comments, quotes, and multiple queries that need robust splitting.

def split_sql_statements(sql: str) -> list:
    """
    Split SQL by top-level semicolons, correctly handling:
    - Semicolons inside single-quoted strings ('a;b' is not split)
    - SQL-standard '' escape ('it''s' is not split)
    - Semicolons inside -- line comments (-- note; not split is not split)
    """
    statements = []
    buf = []
    i, n = 0, len(sql)
    in_quote = False

    while i < n:
        ch = sql[i]

        # Inside a single-quoted string
        if in_quote:
            buf.append(ch)
            if ch == "'":
                # '' = literal single quote, does not end the string
                if i + 1 < n and sql[i + 1] == "'":
                    buf.append(sql[i + 1])
                    i += 2
                    continue
                in_quote = False
            i += 1
            continue

        # Top level
        if ch == "'":
            in_quote = True
            buf.append(ch)
        elif ch == '-' and i + 1 < n and sql[i + 1] == '-':
            # Line comment, swallow to end of line (; inside is not a split point)
            while i < n and sql[i] != '\n':
                buf.append(sql[i])
                i += 1
            continue
        elif ch == ';':
            stmt = ''.join(buf).strip()
            if stmt:
                statements.append(stmt)
            buf = []
            i += 1
            continue
        else:
            buf.append(ch)
        i += 1

    # Trailing block without a final semicolon
    stmt = ''.join(buf).strip()
    if stmt:
        statements.append(stmt)

    return statements


# Caller
sql_text = open(sql_path).read()
statements = split_sql_statements(sql_text)

# Execute one by one, collect all results
all_results = []
for idx, stmt in enumerate(statements, start=1):
    df = pg_hook.get_pandas_df(stmt)
    if not df.empty:
        all_results.append({
            "sql_index": idx,
            "sql": stmt,
            "data": df.to_dict("records"),
            "columns": df.columns.tolist(),
            "row_count": len(df),
        })

Option B: Pass a list directly to DbApiHook​

Airflow's DbApiHook.run and get_records accept list[str] and execute sequentially — but get_pandas_df return behavior in list mode is inconsistent across providers. For production, Option A gives you full control.

Why not sqlparse.split?​

Community answers often recommend sqlparse.split(sqlparse.format(sql, strip_comments=True)), but strip_comments=True discards comments. If your downstream processor depends on metadata in comments (e.g. -- dimension: shop), you lose context. A hand-rolled splitter preserves the original comment text and gives you control.

Caveats​

FAQ​

How do I execute multiple SQL statements in Airflow PostgresHook?​

Pass list[str] instead of a single string. DbApiHook.get_pandas_df and run accept sql as a list and execute sequentially; a single string with semicolon-separated statements causes psycopg2 to return only the last result set. For production, split yourself and call per-statement so you control result aggregation and sql_index.

Why does get_pandas_df only return the last result for multi-statement SQL?​

pandas.io.sql.read_sql calls psycopg2 cursor.execute with the full string; the DBAPI protocol only exposes the cursor of the last result set for multi-statement execution, and prior SELECT results are dropped by the server immediately, with no error or warning. psql returns data because its frontend actively iterates all result sets, while a DBAPI cursor does not.

How do I split SQL by semicolon safely with comments and quotes?​

Scan character by character and split only at top-level semicolons outside single-quoted strings and -- line comments. Single-quote literals use the SQL-standard '' escape; do not use str.split(';'), it will mis-cut semicolons inside comments and strings. If you use sqlparse.split, note that strip_comments=True discards the original comment text.

Encountered this while building AI Ops — LLM-powered analytics that surfaces market trends, user behavior, and sales data for precise operational strategy.

TL;DR​

dotenv treats any # in unquoted values as an inline comment. KEY=value#hash is actually loaded as value, with #hash dropped — no warning, no error. The fix has two steps: wrap any .env value containing # in double quotes, then run pm2 restart <app> --update-env to force-refresh the process environment — without the refresh the edit changes nothing, because PM2 re-injects its cached snapshot into the new process.

Symptom​

Backend calls to an upstream service keep returning 401 Invalid credentials:

POST /api/v1/dag/trigger → 500
Stack: Airflow JWT auth failed (401): {"detail":"Invalid credentials"}
  at getJwtToken (airflow-client.ts)

My first assumption was a wrong password or a disabled account. Investigation shows the password written in .env is 24 chars and contains # and &:

AIRFLOW_PASSWORD=Aq7#mZx&V3nKp9RtWu2yBc4d

But the value loaded into process.env.AIRFLOW_PASSWORD is only 3 chars long — the 21 characters after # are gone. Calling the upstream auth endpoint with the full password via curl returns 201; calling it with the truncated value parsed from .env returns 401. The credential is fine; the value loaded from .env is truncated. If you searched for ".env not loading", "environment variable value wrong" or "password is correct but auth fails", it's all the same root cause.

Root cause: dotenv treats any # in unquoted values as an inline comment​

When dotenv parses a .env file, an unquoted value ends at the first # — everything from # onward is discarded as an inline comment, with no warning at all. This behavior is documented, but the silence is what makes it nasty — no error, no warning; newer versions print a one-line injected-env summary at startup (verified on 18.0.4), but it only shows how many keys were loaded.

I ran the same parse matrix against dotenv 16.6.1 (the version pinned in the project) and 18.0.4 (current latest at the time); the results were identical:

Three things stand out:

• No space needed. In a shell, # only starts a comment when preceded by whitespace. dotenv doesn't care — val#hash with the # glued to the value is truncated all the same. Strong-random strings like JWT_SECRET, API_KEY, and DATABASE_URL frequently contain # in arbitrary positions — high-risk territory.
• Quotes are the literal switch. Inside single or double quotes, # is kept as a plain character; a # after the closing quote still starts a comment.
• & and spaces are safe in dotenv. Neither triggers truncation in the tests, and core dotenv does not expand $VAR (that's the dotenv-expand plugin's job).

The fix: quote the value, force-refresh the process environment​

First, wrap any value containing # in double quotes:

# truncated: the process actually gets Aq7
AIRFLOW_PASSWORD=Aq7#mZx&V3nKp9RtWu2yBc4d

# correct: preserved verbatim
AIRFLOW_PASSWORD="Aq7#mZx&V3nKp9RtWu2yBc4d"

Second, restart with --update-env:

pm2 restart analytics-api --update-env

That flag is not optional, because two "don't overwrite" defaults stack up:

1. PM2 snapshots the environment when a process is first started and injects that stale snapshot back into every restart;
2. dotenv does not overwrite keys that already exist in process.env (verified: set process.env.X='oldvalue', run dotenv.config(), and X is still oldvalue).

Editing the file without refreshing the process environment changes nothing — the process keeps reading PM2's cached snapshot. The same applies to docker compose and systemd: the service must actually rebuild its environment (docker compose up -d --force-recreate, systemctl restart).

After restarting, verify what the process actually loaded instead of guessing:

# print the length and compare it against the value in .env
node -e "console.log(process.env.AIRFLOW_PASSWORD.length)"

To go one step further, validate critical variable lengths at startup so a silent failure becomes a startup failure:

// Validate critical env vars at startup to catch truncation early
const required = ['AIRFLOW_PASSWORD', 'JWT_SECRET', 'DATABASE_URL'] as const;
for (const key of required) {
  const v = process.env[key];
  if (!v || v.length < 16) {
    throw new Error(`${key} not loaded correctly (length ${v?.length ?? 0}); check .env quoting`);
  }
}

Boundary cases: docker compose plays by different rules​

Truncation at # is not a universal env-parser convention — docker compose follows the opposite rule, and the same file can load differently across tools. The compose-spec states plainly: "Inline comments for unquoted values must be preceded with a space", and its official example shows VAR=VAL# not a comment loading as VAL# not a comment, kept verbatim.

For dotenv alone, only # is dangerous; but one .env file often serves several tools — local shell, docker compose, PM2, CI — each with its own parser. Rather than memorizing every parser's quirks, our team settled on one hard rule: any value containing #, & or spaces gets double quotes. Two extra characters buy cross-tool predictability.

FAQ​

Why does a password with # in .env get shorter?​

dotenv treats any # in unquoted values as an inline comment and drops everything after it — KEY=value#hash loads as value, with no warning (verified on dotenv 16.6.1 and 18.0.4). Wrap the whole value in double quotes to preserve it.

How do I debug dotenv not working?​

Three steps: first confirm dotenv.config() runs before all imports (ES Module imports are hoisted statically — see debugging silent JWT signature failures); then check .env values for unquoted #; finally print process.env.XXX and diff its length against the .env source file — a mismatch means truncation, not a missing load.

Does docker compose treat # the same way in env files?​

No — the rules differ. compose-spec requires inline comments in unquoted values to be preceded by a space, so VAL=B#C is kept as-is; dotenv truncates it to B regardless of position. Double quotes are the only form consistent across tools.