Cases14 cases

6 to 10 of 14

1. 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
2. IL-0431 During Mackerel's move to AWS, PostgreSQL failed over to a replica that had stopped replicating, losing an hour of data

   Hatena2017

   Symptoms

   On 7 August 2017, while checking the application after the maintenance that moved Mackerel from an on-premises data center to AWS, the team found that data updated during a specific time window was missing. Everything written to the PostgreSQL database between 14:30 and 15:30 JST was gone.

   Data lossReplication lagNetwork unreachable

   Trigger

   One hour after the application was switched to the newly promoted primary db001, db001 lost network connectivity and the database failed over to the standby db002.

   Root cause

   Operator error

   Component

   DatabaseDatabase replicationDatabase failover manager

   Contributing factor

   Overlapping failuresUnreviewed defaultsUnrepresentative stagingAutomation amplification

   Detection

   The team found the missing data while checking how the application behaved after the migration work.

   Remediation

   • Tried to restore the lost updates by hand from the table data left on db001 and gave up
   • Tried point-in-time recovery from the base backup and WAL and gave up
   • Built a new replica db003 with db002 as primary to restore redundancy
   • Kept db001 in maintenance state for investigation
   • Watched db002 for some time to confirm the problem did not recur, then completed the migration

   Action items

   • Change the PostgreSQL replica creation script to set recovery_target_timeline = 'latest' in recovery.conf by default
   • Add a check to a server configuration test tool such as Serverspec that recovery.conf contains recovery_target_timeline = 'latest'

   Lessons

   A setting needed only for a rare layout, such as a grandchild replica during a migration, was applied by hand through a script option and was forgotten. Because recovery_target_timeline = 'latest' is harmless on ordinary standbys too, Hatena now sets it by default whenever a replica is created. The staging rehearsal used the same layout and steps but had the setting right, so it could not catch the mistake. Hatena notes that a similar layout may be used again for a future PostgreSQL upgrade or a move to another region.

   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
4. IL-0218 GitLab.com's primary PostgreSQL data was wiped by mistake and, with no working backup, the site was down about 18 hours

   GitLab2017

   Symptoms

   From around 19:00 UTC on 31 January 2017, load on GitLab.com's PostgreSQL database rose, which GitLab suspected was spam, and many users could not post comments on issues and merge requests. Around 23:00 UTC the replication of the PostgreSQL secondary fell behind and then failed, because WAL segments it still needed had already been removed from the primary.

   Database overloadReplication lagData lossService outage

   Trigger

   While trying to restore replication to the secondary, an engineer ran the removal of the PostgreSQL data directory on the primary, db1.cluster.gitlab.com, believing it was the secondary.

   Root cause

   Operator error

   Component

   DatabaseDatabase replicationDatabase backups and snapshotsCloud block storage

   Contributing factor

   Untested backupSingle point of failureNo runbookPages not deliveredUnverified assumptionUnreviewed defaults

   Detection

   The engineer who ran the deletion on the primary noticed the mistake and stopped it a second or two later.

   Remediation

   • Raised max_wal_senders from 3 to 32 and lowered max_connections from 8000 to 2000 so PostgreSQL would restart
   • Stopped the deletion on the primary a second or two after noticing it
   • Restored the database from the LVM snapshot taken about 6 hours before the outage by copying the PostgreSQL data directory from staging
   • Restored the webhooks from a separate database built from the snapshot and imported them as a SQL dump
   • Incremented all database sequences by 100,000 so IDs used before the outage could not be reused
   • Re-enabled GitLab.com gradually
   • Rebuilt projects created after 17:20 UTC in basic form from their Git repositories on the NFS cluster

   Action items

   • Change the shell prompt (PS1) on all hosts to show more clearly which host and environment is in use
   • Monitor backups with Prometheus
   • Set PostgreSQL's max_connections to a sane value
   • Investigate point-in-time recovery and continuous archiving for PostgreSQL
   • Take LVM snapshots of the production databases every hour
   • Take Azure disk snapshots of the production databases
   • Move staging to the ARM environment
   • Recover the production replicas
   • Test recovery of PostgreSQL database backups automatically
   • Improve the PostgreSQL replication documentation and runbooks
   • Investigate pgbarman for PostgreSQL backups
   • Investigate WAL-E for database backup and realtime replication
   • Build a streaming database restore
   • Assign an owner for data durability
   • Set up multiple secondaries and balance load among them with pgpool-II
   • Provide a tool for developers to run and revert Rails migrations on staging
   • Build owned recovery procedures for the rest of the GitLab.com infrastructure, not only the database
   • Publish a backup monitoring dashboard and add more backup data to it
   • Improve the abuse reporting and response system so removal for spam does not hard delete
   • Redact engineers' names in future public incident documents

   Lessons

   GitLab split the incident into two problems: GitLab.com went down because the primary's data directory was removed by mistake during a manual, undocumented replication rebuild, and recovery took over 18 hours because the backups had failed silently, disk snapshots were not enabled and the only copy sat on slow staging disks. The backup procedure was never tested because no one owned it. GitLab chose to focus on fast, regularly practised recovery and on making it obvious which host an engineer is on, rather than on blocking commands, since a safer rm only protects against one of many ways to lose data.

   View case
5. IL-0573 A developer's mistake dropped nine main tables of the Baemin billing MariaDB database and payments briefly stopped

   Woowa Brothers2016

   Symptoms

   At 15:00 on 27 December 2016, while a developer was working on a payment system module, nine main tables of the billing database were dropped. The monitoring system turned red, failure alerts poured in, and payments in 배달의민족 (Baemin) stopped for a while.

   Data lossService outage

   Trigger

   While developing a payment system module, the developer who wrote the post dropped nine main tables of the billing database by mistake.

   Root cause

   Operator error

   Component

   DatabaseDatabase backups and snapshots

   Contributing factor

   –

   Detection

   The monitoring system turned red and failure alerts fired.

   Remediation

   • Pointed the application at a temporary database, which kept the outage short
   • Restored the dropped tables from the 6:00 mysqldump full backup
   • Replayed the binary log from 6:00 to 15:00 with mysqlbinlog, filtered to the nine tables and stopping before the DROP TABLE

   Action items

   • Replace mysqldump backups of the billing database with full backups taken by XtraBackup

   Lessons

   There are many points where such failures can be managed: separate database accounts for the service and for administration, check once more before running a query, and guard against rm -rf. Without the full backup and the binary log the data could not have been recovered, so every team should check that it has a full recovery point for a failure.

   View case

By root cause1