Fleet write failures after DB Failover

[BCTBB]

Fleet version: 4.79.1

Web browser and operating system: N/A

---

💥  Actual behavior

When Fleet has been configured with both a Writer and Read replicas, during an AWS Aurora Failover event, Fleet does not appear to be detecting the new Writer and re-establishing connections.

Started cross AZ failover to DB instance: <db_instance_name>
Completed RDS initiated failover to DB instance: <db_instance_name>

When Fleet does not detect the event through /healthz check failures, this results in Fleet staying attached to the (old) writer when it's been demoted to a reader.

During the time any requests triggering a write transaction appear to fail. It was reported that:

• Fleet logins did not work
• My Device page access did not work

We did observe a /healthz failure, 1 for each task in the ECS cluster, during 2 separate failover events:

{"component":"healthz","err":"invalid connection","health-checker":"mysql","ts":"<timestamp>"}
{"component":"healthz","err":"dial tcp 10.10.21.118:3306: connect: connection refused","health-checker":"mysql","ts":"<timestamp>"}

After the failover Fleet generates the following logs when write transactions are executed against the reader (and not limited to):

{"err":"MarkHostsSeen transaction: exec update: Error 1792 (25006): Cannot execute statement in a READ ONLY transaction.","level":"info","msg":"failed to update host seen times","ts":"<timestamp>"}

{"component":"http","err":"error in query ingestion || error in query ingestion || error in query ingestion || error in query ingestion || insert label query executions (<redacted>): Error 1792 (25006): Cannot execute statement in a READ ONLY transaction. || updating hosts policy updated at: Error 1792 (25006): Cannot execute statement in a READ ONLY transaction. || save host with id <id>: Error 1792 (25006): Cannot execute statement in a READ ONLY transaction.","host_id":<id>,"ingestion-err":"ingesting query users: update host users: insert users: Error 1792 (25006): Cannot execute statement in a READ ONLY transaction.",<redacted>}

🛠️ To fix

To force Fleet to establish new connections to the correct Writer and Read replicas, we restart all the Fleet tasks when the issue is detected.

A few things that may help address the issue (link to slack 🧵) with no human intervention.

• report a failure with /healthz (and monitoring can catch and trigger a Fleet restart)
• panic and have fleet exit, triggering task restarts
• Detect and handle the errors, triggering Fleet to re-lookup the host and re-connect/re-establish connections to Aurora MySQL reader and writers

Modify HealthCheck() to attempt a write operation.
Not sure what a minimal write operation will work. SELECT @@read_only or attempt a dummy insert and rollback should do the trick.

Detect error 1792 and panic/exit

  func isReadOnlyTransactionError(err error) bool {                                                                                                                                                                                                                                                                
      err = ctxerr.Cause(err)                                                                                                                                                                                                                                                                                      
      var mySQLErr *mysql.MySQLError                                                                                                                                                                                                                                                                               
      if errors.As(err, &mySQLErr) {                                                                                                                                                                                                                                                                               
          return mySQLErr.Number == ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION                                                                                                                                                                                                                                       
      }                                                                                                                                                                                                                                                                                                            
      return false                                                                                                                                                                                                                                                                                                 
  }

🧑‍💻  Steps to reproduce

I have not reproduced the issue, but I believe this can be reproduced by inducing a DB failover event. We can try to reproduce by promoting a database reader to a writer, which should lead to a similar outcome.

Victor's proposed repro steps:

1. Deploy Fleet with Aurora MySQL (writer + reader replica configured)
2. Configure ECS/load balancer health checks to hit /healthz
3. Trigger Aurora failover:
   aws rds failover-db-cluster --db-cluster-identifier <cluster-id>
4. Observe Fleet logs for Error 1792 messages
5. Confirm /healthz still returns 200 (the bug)

🕯️ More info (optional)

Related help-p1 alerts:

1. https://fleetdm.slack.com/archives/C03EG80BM2A/p1770058093332579
2. https://fleetdm.slack.com/archives/C03EG80BM2A/p1770075439043209

Additional context:

1. https://fleetdm.slack.com/archives/C062D0THVV1/p1770076322006369

QA

Note: the server's behavior during failover is not deterministic. Only the previous DB connections are "bad". Any new DB connections created after the failover work fine. This means the healthz endpoint may not deterministically return a failing status.

1. Deploy your branch to the loadtest environment

2. Identify the Aurora cluster

Find the Aurora cluster name:

aws rds describe-db-clusters \
  --query "DBClusters[?contains(DBClusterIdentifier, '<workspace-name>')].DBClusterIdentifier" \
  --output text

Verify it has 2 instances (one writer, one reader):

aws rds describe-db-clusters \
  --db-cluster-identifier <cluster-id> \
  --query "DBClusters[0].DBClusterMembers[*].{Instance:DBInstanceIdentifier,IsWriter:IsClusterWriter}" \
  --output table

3. Start write traffic

Create a script (or use the template below) that sends continuous write requests to Fleet. This is necessary because failover is only detectable when the application attempts a write operation against the now-read-only former primary.

Save as tmp/failover-test.sh:

#!/bin/bash
# Generate continuous write traffic against Fleet to exercise the read-only
# detection during an Aurora failover.

URL="https://fleet-<workspace-name>.loadtest.fleetdm.com"
TOKEN="<your-api-token>"
AUTH="Authorization: Bearer $TOKEN"

echo "=== Starting write traffic at $(date) ==="
echo "Sending PATCH /api/v1/fleet/config every 0.5s..."
echo "Watch for non-200 responses indicating read-only errors."
echo ""

i=0
while true; do
    i=$((i + 1))
    status=$(curl -s -o /dev/null -w '%{http_code}' --max-time 5 \
        -X PATCH "$URL/api/v1/fleet/config" \
        -H "$AUTH" \
        -H 'Content-Type: application/json' \
        -d "{\"org_info\":{\"org_name\":\"FailoverTest-$i\"}}")
    ts=$(date +%H:%M:%S)
    if [ "$status" != "200" ]; then
        echo "$ts [#$i] status=$status  <--- NON-200"
    else
        echo "$ts [#$i] status=$status"
    fi
    sleep 0.5
done

Run it:

chmod +x tmp/failover-test.sh
bash tmp/failover-test.sh

You should see a stream of status=200 responses.

4. Trigger the failover

In a separate terminal, initiate an Aurora failover:

aws rds failover-db-cluster --db-cluster-identifier <cluster-id>

This takes about 15-30 seconds for Aurora to complete. During this window:

• The current primary becomes read-only
• The reader is promoted to the new primary
• The cluster endpoint DNS updates to point to the new primary

5. Observe the results

In the write traffic output

You should see a pattern like:

1. All 200s - normal operation before failover
2. 401s and 502s - Fleet is returning errors because DB writes are failing (401s happen because MarkSessionAccessed fails, invalidating the session)
3. 000s - connection timeouts during the transition
4. All 200s again - recovery after ECS replaces the tasks

In CloudWatch logs

Open the CloudWatch log group fleet-<workspace-name> in the AWS console (us-east-2 region).

Search for the key log message:

"fatal database error detected, initiating graceful shutdown"

This confirms that Fleet detected the read-only error and triggered a graceful shutdown. The sequence should be:

1. MySQL read-only errors appear (error 1792, 1290, or 1836)
2. The "fatal database error detected" message appears
3. The server begins graceful shutdown
4. ECS detects the stopped task and launches a replacement

In ECS console

Check the ECS service events for the cluster fleet-<workspace-name>. You should see:

1. Tasks being deregistered from the target group
2. New tasks being launched
3. New tasks passing health checks and being registered

6. Expected timeline

Time	Event
T+0s	Failover initiated
T+15-30s	First read-only errors hit Fleet
T+15-35s	TriggerFatalError fires, graceful shutdown begins
T+30-60s	ECS deregisters old tasks, starts replacements
T+60-120s	New tasks healthy, all requests returning 200

Total disruption window: approximately 1-2 minutes.

[noahtalerman]

Hey @lukeheath I'm not sure how to triage this bug...Would the fix be implemented by a product group? If yes which one?

[noahtalerman]

I'm not sure how to triage this bug...Would the fix be implemented by a product group? If yes which one?

@georgekarrv @sharon-fdm do y'all know?

[sharon-fdm]

@noahtalerman, this is an issue for backend engineers.
The hardest part could be to replicate the environment and reproduce the issue.
e.g. I'm not sure how to reproduce an AWS Aurora Failover event. May require help from Infra team.

If I understand correctly, this event is when AWS decides to turn a reader or writer instance of the DB to a primary writer. It looks like fleet server does not know how to capture such events (thus not responding with reconnecting to the new primary writer)
Definitely need more work to spec, or simply take the bug and start debugging.

You can assign to S&C group to spec and estimate. (I moved to ready to spec. cc @getvictor )