From 60d005f8d174d21162cab2b029f74cfe2925acab Mon Sep 17 00:00:00 2001 From: Niall Sheridan Date: Tue, 7 Aug 2018 23:43:23 +0100 Subject: Change the primary key on the issued_certs table In retrospect a primary key that has no relation to the certificate is preferred to using the certificate KeyID. The KeyID is also very large for a primary index. This is a moderately tricky migration, especially for SQLite which has no means of altering the table in this fashion - it involves creating the new table and copying the data. Order of commands also matters - index names are global in SQLite, so the `idx_expires_at` index needs to be created at the correct stages. For MySQL migration the necessary steps are run as a single alter statement to minimise the risk of leaving the migration in an incomplete state if anything aborts. When tested on a table with 250,000 rows (MySQL 5.7) the migration took 3 seconds to complete. As certificates will be requested infrequently the risk of prolonged locking is minimal. --- server/store/store.go | 1 + 1 file changed, 1 insertion(+) (limited to 'server/store/store.go') diff --git a/server/store/store.go b/server/store/store.go index b620e6d..c93680b 100644 --- a/server/store/store.go +++ b/server/store/store.go @@ -36,6 +36,7 @@ type CertStorer interface { // A CertRecord is a representation of a ssh certificate used by a CertStorer. type CertRecord struct { + ID int `json:"-" db:"id"` KeyID string `json:"key_id" db:"key_id"` Principals StringSlice `json:"principals" db:"principals"` CreatedAt time.Time `json:"created_at" db:"created_at"` -- cgit v1.2.3