Hydra: Missing database indices

[aaslamin]

Missing indices

request_id column in the hydra_oauth2_access & hydra_oauth2_refresh tables

When hitting the token handler to refresh tokens, the following query is eventually made to both the access token and refresh token table:

DELETE FROM hydra_oauth2_<access|refresh> WHERE request_id=???

This can result in an outage as there is no index on the request_id column. To adhere to the ACID properties, the database needs to lock all the rows that are scanned as a result of this operation. In the absolute worst case, this can result in a full table scan to find the row to delete which will effectively lock the entire table 💥. By adding an index, the db can lock the individual row instead.

requested_at column in the hydra_oauth2_access table

When flushing expired tokens, the following query is made to the access token table:

DELETE FROM hydra_oauth2_access WHERE requested_at < ??? AND requested_at < ???

This can lead to disaster for the same reasoning as above.

Error that occurs when a table is locked and another txn (e.g. delete/update) is attempted

Error 1205: Lock wait timeout exceeded; try restarting transaction

Proposal

Create missing indices and add migration guide.

[aeneasr]

Yes, this should be fixed asap. Accepting PRs!

[aaslamin]

I can raise a PR tomorrow @aeneasr