letsencrypt / letsencrypt/boulder

Change crlShards schema to not have auto_increment primary key

Open
#8,031 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
Go
Stars
5.8k
Forks
649
Avg merge
3d 23h
Merged PRs (30d)
24

Description

The crlShards table schema is currently
https://github.com/letsencrypt/boulder/blob/a2141cb695de88970bd843aaee91bd8a3c605be5/sa/db/boulder_sa/20230519000000_CrlShards.sql#L4-L13

I now believe that this schema was a mistake. This table does not grow arbitrarily and does not need to be partitioned, so the existence of an auto_increment primary key is a waste.

Instead, this should be a simple key-value table, where the keys are of the form fmt.Sprintf(%d:%d", issuerID, idx). The overall schema should be something like:

CREATE TABLE `crlShards` (
  `id` tinytext NOT NULL,
  `thisUpdate` datetime,
  `nextUpdate` datetime,
  `leasedUntil` datetime NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

When this work is done, the lease functions which select-and-uodate this table should be changed to use SELECT...FOR UPDATE as suggested by the InnoDB doc.

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with sa/db/boulder_sa/20230519000000_CrlShards.sql and inspect the lease functions that select and update crlShards. Verify the key format and transaction behavior against the MySQL InnoDB documentation, then update the schema and lease queries so the table uses the specified string key and SELECT...FOR UPDATE.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, mysql
Domain
database
Issue type
Refactor
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.