The unique index we could not add, and the duplicates we had to find first

Two customer records with the same email address, merged by hand by support about once a month for seven years. The uniqueness rule existed in application code, at three entry points, two of which enforced it.

The symptom

$ mysql -e "SELECT LOWER(email) e, COUNT(*) n FROM customers
            WHERE deleted_at IS NULL
            GROUP BY e HAVING n > 1 ORDER BY n DESC LIMIT 5"
e                        n
[email protected]      4
[email protected]        3
...

$ mysql -e "SELECT COUNT(*) FROM (
              SELECT LOWER(email) e FROM customers
              WHERE deleted_at IS NULL
              GROUP BY e HAVING COUNT(*) > 1) x"
588        # distinct addresses
# 1,240 rows involved

the three entry points:
  registration form   checked, case-insensitively
  admin create form   checked, case-SENSITIVELY — so
                      "Someone@" and "someone@" both got in
  the CSV importer    did not check at all. added in 2018
                      for one migration, used monthly since.

Why it happens

A rule enforced in application code is enforced wherever somebody remembered to enforce it. The database is the only place a constraint applies to every writer including the ones nobody has written yet.

The fix

Finding them without exploding

-- the naive self-join on 2M rows: 40 minutes
SELECT a.id, b.id FROM customers a JOIN customers b
  ON LOWER(a.email) = LOWER(b.email) AND a.id < b.id;

-- the grouped version, with a functional index: 1.2s
ALTER TABLE customers
  ADD INDEX idx_email_lower ((LOWER(email)));

SELECT LOWER(email) AS e,
       GROUP_CONCAT(id ORDER BY created_at) AS ids,
       COUNT(*) AS n
FROM customers WHERE deleted_at IS NULL
GROUP BY e HAVING n > 1;

A functional index on the lowercased column is what makes the grouping fast and it is also part of the eventual solution — the unique index has to be on the same expression, or two addresses differing only in case remain distinct.

Deciding which row wins, which is not a technical question

the rule, agreed with the people who own customers:
  1  the row with the most recent order wins
  2  failing that, the most recently updated
  3  failing that, the oldest — likely the original

and the fields merged rather than discarded:
  marketing consent   the most RESTRICTIVE wins, not the
                      most recent. a legal conversation.
  addresses           all kept, deduplicated
  notes               concatenated with a marker

two meetings. the merge itself took three hours.

The consent rule is the one that could not be decided by engineering, and taking the most restrictive value rather than the most recent is the answer that survives a regulator asking. Every merge of personal data has a version of this question in it.

Merging the references

private const REFERENCING_TABLES = [
    'orders'          => 'customer_id',
    'addresses'       => 'customer_id',
    'support_tickets' => 'customer_id',
    'consents'        => 'customer_id',
];

DB::transaction(function () use ($winner, $losers) {
    foreach (self::REFERENCING_TABLES as $table => $column) {
        DB::table($table)->whereIn($column, $losers)
            ->update([$column => $winner]);
    }

    $this->mergeConsents($winner, $losers);
    $this->events->record($winner, 'customer.merged', ['merged' => $losers]);

    DB::table('customers')->whereIn('id', $losers)->delete();
});

Finding the four referencing tables was a query against information_schema for foreign keys plus a grep for customer_id, because two of them had no foreign key. Recording the merge in the event log is what makes it reversible in the sense that matters — the ids are known.

The soft-delete problem

-- a plain unique index would prevent a deleted
-- customer's address from ever being reused
ALTER TABLE customers
  ADD COLUMN deleted_marker BIGINT
    GENERATED ALWAYS AS (IF(deleted_at IS NULL, NULL, id)) STORED,
  ADD UNIQUE KEY uq_email_live ((LOWER(email)), deleted_marker);

-- live rows:    marker is NULL → NULL is not equal to
--               NULL for uniqueness, so one live row
--               per address
-- deleted rows: marker is the id → always unique

MySQL treating NULL as distinct in a unique index is the property this relies on, and it is the standard behaviour rather than a quirk. A partial index would say this directly and MySQL does not have them, which is why the generated column exists.

Adding it online, with the lock window measured

# on a copy of production, first
$ time mysql staging -e "ALTER TABLE customers
    ADD UNIQUE KEY uq_email_live ((LOWER(email)), deleted_marker),
    ALGORITHM=INPLACE, LOCK=NONE"
real    4m12s

# LOCK=NONE means writes continue. the metadata lock at
# the start and end is brief UNLESS a long query holds
# the table — so:
$ mysql -e "SET SESSION lock_wait_timeout = 10"
# fail fast rather than queueing everything behind it

Setting a short lock_wait_timeout is the precaution that turns the worst case from an outage into a failed migration. Without it, an ALTER waiting on a metadata lock blocks every subsequent query on the table, which is how a four-minute online change becomes a twenty-minute incident.

Simplifying the application path

// before: a check-then-insert, racy, in three places
if (Customer::whereRaw('LOWER(email) = ?', [$email])->exists()) {
    throw new DuplicateEmail($email);
}
Customer::create([...]);

// after: let the database decide
try {
    Customer::create([...]);
} catch (UniqueConstraintViolationException) {
    throw new DuplicateEmail($email);
}

The check-then-insert was racy in a way that had never been observed and was theoretically live at every entry point. Catching the constraint violation is shorter, correct under concurrency, and applies to the CSV importer without anybody remembering to add it there.

Verifying it worked

$ mysql -e "SHOW INDEX FROM customers WHERE Key_name='uq_email_live'G" 
  | grep -c Non_unique:.0
2

$ mysql -e "SELECT COUNT(*) FROM (SELECT LOWER(email) e FROM customers
            WHERE deleted_at IS NULL GROUP BY e HAVING COUNT(*)>1) x"
0

# the CSV importer, with a deliberate duplicate
$ php artisan customers:import fixtures/with-duplicate.csv
  row 41: DuplicateEmail [email protected] — skipped
  imported 199 of 200

# support tickets for duplicate customers
  before  ~1/month for 7 years
  after   0 in 4 months

The importer rejecting the duplicate is the assertion that the constraint reaches the writer that never had a check, which was the actual cause. Four months without a support ticket is a small sample and it is the metric the whole exercise was measured against.

What this costs

A data decision made once and applied to seven years of rows. Five hundred and eighty-eight merges, each one collapsing history that somebody might later have wanted separate — and the event log records the ids, which is not the same as being able to undo it.

The generated column is also a permanent piece of cleverness that needs a comment. Somebody reading the schema in three years will see a column called deleted_marker that is always null or the id, and nothing about it explains that it exists to make a unique index ignore soft-deleted rows.