boulder
boulder copied to clipboard
Change crlShards schema to not have auto_increment primary key
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.