Cases8 cases

6 to 8 of 8

1. IL-0565 Cookie Run: Kingdom's CockroachDB cluster failed when 16 of 24 nodes lost their filesystems, causing a 36-hour outage

   Devsisters2022

   Symptoms

   Right after a script ran on the CockroachDB nodes behind Cookie Run: Kingdom, hundreds of alerts fired at once for error rate, database connections and health checks, and the game could no longer be played normally. Emergency maintenance started at 16:52.

   Service outageData lossDatabase overload

   Trigger

   On the Monday after the launch weekend, storage use was growing with the number of users and the cluster had about 36 hours left before its disks would fill. The team ran a prepared script to create ballast files on every node, as the official guide for CockroachDB 19.1.11 recommends. The ballast file path in the script was set wrong and pointed at a block device, and the command had no check for an existing file at the target path, so it overwrote the partition.

   Root cause

   Operator error

   Component

   Database

   Contributing factor

   No safeguard on destructive changeOverlapping failuresRetry storm

   Detection

   Metrics and alerts set up in advance fired hundreds of alerts on error rate, database connections and health checks right after the script ran.

   Remediation

   • Started emergency maintenance of the game at 16:52
   • Wrote Spark code that extracts the contents of CockroachDB SST files to CSV, and modified the CockroachDB CLI to make it more than 10 times faster
   • Converted about 7.5TB of SST files from the 7 surviving nodes to CSV
   • Deduplicated the extracted rows and merged them into one large CSV file that could be imported
   • Imported the latest snapshot and the latest 300 journal entries per user into a new cluster with more storage
   • Restored the other tables from the latest backup and rebuilt the user table from snapshots and journals with server logic
   • Checked the restored data against a database restored from backup, against analytics logs for sampled users, and with snapshot replay for every user
   • Reopened the game at 23:37 on the second day, after 30 hours 50 minutes
   • Failed over the AWS RDS database of the DevPlay platform after the reopening traffic overloaded it
   • Built a third database cluster on more m5d instances and moved the data with the official backup and restore
   • Ended the maintenance at 8:30, after 36 hours 29 minutes

   Action items

   • Require at least two engineers for any work on production infrastructure, however small
   • Write the scripts and the scenario for each infrastructure task in advance and have the team review them

   Lessons

   Because a person's mistake caused the failure, Devsisters changed its process for infrastructure work rather than relying on individual care: even experienced engineers make mistakes, so the prevention has to be built into the system. The author credits the recovery to an incident policy that does not blame the individual and asks for detailed records, to past incidents that were written down so people without the experience knew what to do, and to having infrastructure operations and data analysis teams with the needed infrastructure and skills in one organisation.

   View case
2. IL-0332 A test run against the production database truncated every table and travis-ci.com was down for about 5.5 hours

   Travis CI2018

   Symptoms

   On 13 March 2018 at 12:14 UTC every table in travis-ci.com's production PostgreSQL database was truncated. Alerts fired right away, and customers reported missing account data; people who signed in saw blank user profiles.

   Data lossService outage

   Trigger

   At 12:04 UTC a developer ran a test suite in an old tmux shell that still had a DATABASEURL environment variable pointing at the production database, and the Database Cleaner gem sent a truncate query to it. The query was blocked for about 10 minutes and executed at 12:14 UTC.

   Root cause

   Operator error

   Component

   Database

   Contributing factor

   No safeguard on destructive changeHarmful manual action

   Detection

   Alerts fired immediately after the truncation, and customers reported missing account data.

   Remediation

   • Took all applications offline
   • Provisioned a replacement database from a snapshot taken that morning
   • Reattached the restored database and brought platform services back up
   • Worked through the backlog of builds until it cleared
   • Revoked all affected login tokens by 14:22 UTC on 14 March
   • Contacted affected customers and advised them to rotate stored credentials and check build logs

   Action items

   • Revoke the truncate permission on the databases
   • Make the internal spechelpers check for the DATABASEURL environment variable
   • Make the developer shell prompt warn when a DATABASE_URL is set
   • Submit a pull request to the Database Cleaner gem guarding against a remote DATABASE_URL
   • Create an alias for the follower database so it is easy to connect to for work against production data
   • Automate database failover and maintenance to cut manual recovery steps

   Lessons

   Developers connected to the production primary with write access because the tooling made the read-only follower hard to reach. While responding, Travis CI left user-facing applications running, which created duplicate user ids, and did not turn some alerts back on, which hid the stopped cron scheduler. Even the most experienced developers can make an inadvertent mistake that causes a large outage. The whole production database was recovered with only about 15 minutes of data loss.

   View case
3. IL-0506 UC Berkeley DataHub logins failed and students got empty home directories after the JupyterHub database was overwritten

   UC Berkeley2017

   Symptoms

   At about 1 AM on 9 February 2017, UC Berkeley's DataHub was reported down: users trying to log in got a proxy error. The hub pod was running, but its log was full of sqlite errors.

   Service outageData lossMisrouted requests

   Trigger

   While debugging, an operator ran `.save jupyterhub.sqlite` in sqlite3 inside the hub pod without having attached the database, which replaced the JupyterHub database file with an empty one.

   Root cause

   Operator error

   Component

   DatabasePersistent volumes and stateful setsJupyterHub

   Contributing factor

   No safeguard on destructive changeUnsafe failure behaviourHarmful manual action

   Detection

   DataHub was reported down at about 1 AM, when users got a proxy error at login. At 1:12 AM a user reported that he could log in but all his notebooks were gone.

   Remediation

   • Scaled the hub to 0 replicas
   • Rebuilt the JupyterHub sqlite database by matching Kubernetes PVC names back to user ids
   • Set cookie_id to null instead of a dummy value and attached the rebuilt database to the hub
   • Changed the cookie secret in the hub pod and restarted it to log all users out
   • Deleted all user pods to log everyone out
   • Ran a script that filled in cookie_id for all users, restarted the hub and deleted all user pods again
   • Dropped the new users with a '.' in their name and renamed the older users from '-' to '.'

   Action items

   • Report upstream that JupyterHub uses only part of the cookie secret and discards the rest
   • Report upstream that the cookie_id column of the users table should have UNIQUE and NOT NULL constraints
   • Ask KubeSpawner to support username hashes in PVC and pod names so the names stay the same when the database is deleted
   • Ask OAuthenticator to support using the id from the Google authenticator as the user id, giving stable user ids
   • Switch to Google Cloud SQL, a hosted and managed MySQL database
   • Take regular and tested backups of the database
   • Start an operational FAQ of things to do and not to do
   • Set up better monitoring and paging
   • Document escalation procedures explicitly

   View case

By root cause2