{"action":"created","comment":{"url":"https://api.github.com/repos/mozilla/fxa-oauth-server/pulls/comments/201467278","pull_request_review_id":135976094,"id":201467278,"node_id":"MDI0OlB1bGxSZXF1ZXN0UmV2aWV3Q29tbWVudDIwMTQ2NzI3OA==","diff_hunk":"@@ -189,6 +190,12 @@ const QUERY_CODE_DELETE_USER = 'DELETE FROM codes WHERE userId=?';\n const QUERY_DEVELOPER = 'SELECT * FROM developers WHERE email=?';\n const QUERY_DEVELOPER_DELETE = 'DELETE FROM developers WHERE email=?';\n const QUERY_PURGE_EXPIRED_TOKENS = 'DELETE FROM tokens WHERE clientId NOT IN (?) AND expiresAt < NOW() LIMIT ?;';\n+// N.B., this sort order with limit is not actually fully deterministic\n+const QUERY_EXPIRED_TOKENS =\n+  'SELECT expiresAt, token, clientId FROM tokens WHERE expiresAt > ? AND expiresAt < NOW() ORDER BY expiresAt ASC, clientId, token LIMIT ?';","path":"lib/db/mysql/index.js","position":14,"original_position":14,"commit_id":"e76314f19f30f75b96ef6ced7e3676f46e348376","original_commit_id":"e76314f19f30f75b96ef6ced7e3676f46e348376","user":{"login":"jrgm","id":758162,"node_id":"MDQ6VXNlcjc1ODE2Mg==","avatar_url":"https://avatars1.githubusercontent.com/u/758162?v=4","gravatar_id":"","url":"https://api.github.com/users/jrgm","html_url":"https://github.com/jrgm","followers_url":"https://api.github.com/users/jrgm/followers","following_url":"https://api.github.com/users/jrgm/following{/other_user}","gists_url":"https://api.github.com/users/jrgm/gists{/gist_id}","starred_url":"https://api.github.com/users/jrgm/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jrgm/subscriptions","organizations_url":"https://api.github.com/users/jrgm/orgs","repos_url":"https://api.github.com/users/jrgm/repos","events_url":"https://api.github.com/users/jrgm/events{/privacy}","received_events_url":"https://api.github.com/users/jrgm/received_events","type":"User","site_admin":false},"body":"Just to elaborate a bit on that cryptic comment, when I run the script against a million row table (generated with randomized expiresAt times), at then end, none of the protected clientId's are deleted and **almost** all of the target clientId rows **are** deleted.\r\n\r\nBut a few hundred remain.\r\n\r\nThe target rows that haven't been deleted are in tight bands of expiresAt time that are close to the end of each time window. I've dumped out all the rows that are found by each select and, yep, these \"boundary\" rows aren't found. But they should have been!. They have expiresAt that are older than rows that were found. I can't explain exactly why except to say 'indeterminate' (and some resources on the web do say that this type of range query with limit may exhibit this behaviour if using a non-unique index, expiresAt).\r\n\r\nAnyways, I think this purgeExpiredTokensById method can be used to eliminate the bulk of the backlog and get the table size down to something less huge. And then the original version of purgeExpiredTokens can be used to pick up the stragglers.","created_at":"2018-07-10T19:33:27Z","updated_at":"2018-07-10T19:33:27Z","html_url":"https://github.com/mozilla/fxa-oauth-server/pull/580#discussion_r201467278","pull_request_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/pulls/580","author_association":"MEMBER","_links":{"self":{"href":"https://api.github.com/repos/mozilla/fxa-oauth-server/pulls/comments/201467278"},"html":{"href":"https://github.com/mozilla/fxa-oauth-server/pull/580#discussion_r201467278"},"pull_request":{"href":"https://api.github.com/repos/mozilla/fxa-oauth-server/pulls/580"}}},"pull_request":{"url":"https://api.github.com/repos/mozilla/fxa-oauth-server/pulls/580","id":200497044,"node_id":"MDExOlB1bGxSZXF1ZXN0MjAwNDk3MDQ0","html_url":"https://github.com/mozilla/fxa-oauth-server/pull/580","diff_url":"https://github.com/mozilla/fxa-oauth-server/pull/580.diff","patch_url":"https://github.com/mozilla/fxa-oauth-server/pull/580.patch","issue_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/issues/580","number":580,"state":"open","locked":false,"title":"fix(purge): add purgeExpiredTokensById to select, then delete by primary key","user":{"login":"jrgm","id":758162,"node_id":"MDQ6VXNlcjc1ODE2Mg==","avatar_url":"https://avatars1.githubusercontent.com/u/758162?v=4","gravatar_id":"","url":"https://api.github.com/users/jrgm","html_url":"https://github.com/jrgm","followers_url":"https://api.github.com/users/jrgm/followers","following_url":"https://api.github.com/users/jrgm/following{/other_user}","gists_url":"https://api.github.com/users/jrgm/gists{/gist_id}","starred_url":"https://api.github.com/users/jrgm/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jrgm/subscriptions","organizations_url":"https://api.github.com/users/jrgm/orgs","repos_url":"https://api.github.com/users/jrgm/repos","events_url":"https://api.github.com/users/jrgm/events{/privacy}","received_events_url":"https://api.github.com/users/jrgm/received_events","type":"User","site_admin":false},"body":"r? - @mozilla/fxa-devs \r\n\r\n(Hopefully*) fixes #571 \r\n\r\n[*] it does what I intended it to do, but how it plays in performance/non-contention in a huge production table is TBD. ","created_at":"2018-07-10T19:16:02Z","updated_at":"2018-07-10T19:33:27Z","closed_at":null,"merged_at":null,"merge_commit_sha":"e6596383ad90f46406d37fb02708b28ff3e30000","assignee":{"login":"jrgm","id":758162,"node_id":"MDQ6VXNlcjc1ODE2Mg==","avatar_url":"https://avatars1.githubusercontent.com/u/758162?v=4","gravatar_id":"","url":"https://api.github.com/users/jrgm","html_url":"https://github.com/jrgm","followers_url":"https://api.github.com/users/jrgm/followers","following_url":"https://api.github.com/users/jrgm/following{/other_user}","gists_url":"https://api.github.com/users/jrgm/gists{/gist_id}","starred_url":"https://api.github.com/users/jrgm/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jrgm/subscriptions","organizations_url":"https://api.github.com/users/jrgm/orgs","repos_url":"https://api.github.com/users/jrgm/repos","events_url":"https://api.github.com/users/jrgm/events{/privacy}","received_events_url":"https://api.github.com/users/jrgm/received_events","type":"User","site_admin":false},"assignees":[{"login":"jrgm","id":758162,"node_id":"MDQ6VXNlcjc1ODE2Mg==","avatar_url":"https://avatars1.githubusercontent.com/u/758162?v=4","gravatar_id":"","url":"https://api.github.com/users/jrgm","html_url":"https://github.com/jrgm","followers_url":"https://api.github.com/users/jrgm/followers","following_url":"https://api.github.com/users/jrgm/following{/other_user}","gists_url":"https://api.github.com/users/jrgm/gists{/gist_id}","starred_url":"https://api.github.com/users/jrgm/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jrgm/subscriptions","organizations_url":"https://api.github.com/users/jrgm/orgs","repos_url":"https://api.github.com/users/jrgm/repos","events_url":"https://api.github.com/users/jrgm/events{/privacy}","received_events_url":"https://api.github.com/users/jrgm/received_events","type":"User","site_admin":false}],"requested_reviewers":[],"requested_teams":[],"labels":[{"id":346102213,"node_id":"MDU6TGFiZWwzNDYxMDIyMTM=","url":"https://api.github.com/repos/mozilla/fxa-oauth-server/labels/waffle:active","name":"waffle:active","color":"ededed","default":false}],"milestone":null,"commits_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/pulls/580/commits","review_comments_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/pulls/580/comments","review_comment_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/pulls/comments{/number}","comments_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/issues/580/comments","statuses_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/statuses/e76314f19f30f75b96ef6ced7e3676f46e348376","head":{"label":"mozilla:update-purge-expired-tokens","ref":"update-purge-expired-tokens","sha":"e76314f19f30f75b96ef6ced7e3676f46e348376","user":{"login":"mozilla","id":131524,"node_id":"MDEyOk9yZ2FuaXphdGlvbjEzMTUyNA==","avatar_url":"https://avatars2.githubusercontent.com/u/131524?v=4","gravatar_id":"","url":"https://api.github.com/users/mozilla","html_url":"https://github.com/mozilla","followers_url":"https://api.github.com/users/mozilla/followers","following_url":"https://api.github.com/users/mozilla/following{/other_user}","gists_url":"https://api.github.com/users/mozilla/gists{/gist_id}","starred_url":"https://api.github.com/users/mozilla/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mozilla/subscriptions","organizations_url":"https://api.github.com/users/mozilla/orgs","repos_url":"https://api.github.com/users/mozilla/repos","events_url":"https://api.github.com/users/mozilla/events{/privacy}","received_events_url":"https://api.github.com/users/mozilla/received_events","type":"Organization","site_admin":false},"repo":{"id":16819795,"node_id":"MDEwOlJlcG9zaXRvcnkxNjgxOTc5NQ==","name":"fxa-oauth-server","full_name":"mozilla/fxa-oauth-server","owner":{"login":"mozilla","id":131524,"node_id":"MDEyOk9yZ2FuaXphdGlvbjEzMTUyNA==","avatar_url":"https://avatars2.githubusercontent.com/u/131524?v=4","gravatar_id":"","url":"https://api.github.com/users/mozilla","html_url":"https://github.com/mozilla","followers_url":"https://api.github.com/users/mozilla/followers","following_url":"https://api.github.com/users/mozilla/following{/other_user}","gists_url":"https://api.github.com/users/mozilla/gists{/gist_id}","starred_url":"https://api.github.com/users/mozilla/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mozilla/subscriptions","organizations_url":"https://api.github.com/users/mozilla/orgs","repos_url":"https://api.github.com/users/mozilla/repos","events_url":"https://api.github.com/users/mozilla/events{/privacy}","received_events_url":"https://api.github.com/users/mozilla/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/mozilla/fxa-oauth-server","description":"OAuth server for Firefox Accounts","fork":false,"url":"https://api.github.com/repos/mozilla/fxa-oauth-server","forks_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/forks","keys_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/keys{/key_id}","collaborators_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/teams","hooks_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/hooks","issue_events_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/issues/events{/number}","events_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/events","assignees_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/assignees{/user}","branches_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/branches{/branch}","tags_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/tags","blobs_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/git/refs{/sha}","trees_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/git/trees{/sha}","statuses_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/statuses/{sha}","languages_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/languages","stargazers_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/stargazers","contributors_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/contributors","subscribers_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/subscribers","subscription_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/subscription","commits_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/commits{/sha}","git_commits_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/git/commits{/sha}","comments_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/comments{/number}","issue_comment_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/issues/comments{/number}","contents_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/contents/{+path}","compare_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/compare/{base}...{head}","merges_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/merges","archive_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/downloads","issues_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/issues{/number}","pulls_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/pulls{/number}","milestones_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/milestones{/number}","notifications_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/labels{/name}","releases_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/releases{/id}","deployments_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/deployments","created_at":"2014-02-13T22:41:50Z","updated_at":"2018-07-10T06:14:18Z","pushed_at":"2018-07-10T19:16:03Z","git_url":"git://github.com/mozilla/fxa-oauth-server.git","ssh_url":"git@github.com:mozilla/fxa-oauth-server.git","clone_url":"https://github.com/mozilla/fxa-oauth-server.git","svn_url":"https://github.com/mozilla/fxa-oauth-server","homepage":"","size":2149,"stargazers_count":45,"watchers_count":45,"language":"JavaScript","has_issues":true,"has_projects":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":39,"mirror_url":null,"archived":false,"open_issues_count":21,"license":{"key":"mpl-2.0","name":"Mozilla Public License 2.0","spdx_id":"MPL-2.0","url":"https://api.github.com/licenses/mpl-2.0","node_id":"MDc6TGljZW5zZTE0"},"forks":39,"open_issues":21,"watchers":45,"default_branch":"master"}},"base":{"label":"mozilla:master","ref":"master","sha":"b905b7cf63aeddc578c81ce4b4a761675ad8e742","user":{"login":"mozilla","id":131524,"node_id":"MDEyOk9yZ2FuaXphdGlvbjEzMTUyNA==","avatar_url":"https://avatars2.githubusercontent.com/u/131524?v=4","gravatar_id":"","url":"https://api.github.com/users/mozilla","html_url":"https://github.com/mozilla","followers_url":"https://api.github.com/users/mozilla/followers","following_url":"https://api.github.com/users/mozilla/following{/other_user}","gists_url":"https://api.github.com/users/mozilla/gists{/gist_id}","starred_url":"https://api.github.com/users/mozilla/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mozilla/subscriptions","organizations_url":"https://api.github.com/users/mozilla/orgs","repos_url":"https://api.github.com/users/mozilla/repos","events_url":"https://api.github.com/users/mozilla/events{/privacy}","received_events_url":"https://api.github.com/users/mozilla/received_events","type":"Organization","site_admin":false},"repo":{"id":16819795,"node_id":"MDEwOlJlcG9zaXRvcnkxNjgxOTc5NQ==","name":"fxa-oauth-server","full_name":"mozilla/fxa-oauth-server","owner":{"login":"mozilla","id":131524,"node_id":"MDEyOk9yZ2FuaXphdGlvbjEzMTUyNA==","avatar_url":"https://avatars2.githubusercontent.com/u/131524?v=4","gravatar_id":"","url":"https://api.github.com/users/mozilla","html_url":"https://github.com/mozilla","followers_url":"https://api.github.com/users/mozilla/followers","following_url":"https://api.github.com/users/mozilla/following{/other_user}","gists_url":"https://api.github.com/users/mozilla/gists{/gist_id}","starred_url":"https://api.github.com/users/mozilla/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mozilla/subscriptions","organizations_url":"https://api.github.com/users/mozilla/orgs","repos_url":"https://api.github.com/users/mozilla/repos","events_url":"https://api.github.com/users/mozilla/events{/privacy}","received_events_url":"https://api.github.com/users/mozilla/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/mozilla/fxa-oauth-server","description":"OAuth server for Firefox Accounts","fork":false,"url":"https://api.github.com/repos/mozilla/fxa-oauth-server","forks_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/forks","keys_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/keys{/key_id}","collaborators_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/teams","hooks_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/hooks","issue_events_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/issues/events{/number}","events_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/events","assignees_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/assignees{/user}","branches_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/branches{/branch}","tags_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/tags","blobs_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/git/refs{/sha}","trees_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/git/trees{/sha}","statuses_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/statuses/{sha}","languages_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/languages","stargazers_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/stargazers","contributors_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/contributors","subscribers_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/subscribers","subscription_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/subscription","commits_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/commits{/sha}","git_commits_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/git/commits{/sha}","comments_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/comments{/number}","issue_comment_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/issues/comments{/number}","contents_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/contents/{+path}","compare_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/compare/{base}...{head}","merges_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/merges","archive_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/downloads","issues_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/issues{/number}","pulls_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/pulls{/number}","milestones_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/milestones{/number}","notifications_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/labels{/name}","releases_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/releases{/id}","deployments_url":"https://api.github.com/repos/mozilla/fxa-oauth-server/deployments","created_at":"2014-02-13T22:41:50Z","updated_at":"2018-07-10T06:14:18Z","pushed_at":"2018-07-10T19:16:03Z","git_url":"git://github.com/mozilla/fxa-oauth-server.git","ssh_url":"git@github.com:mozilla/fxa-oauth-server.git","clone_url":"https://github.com/mozilla/fxa-oauth-server.git","svn_url":"https://github.com/mozilla/fxa-oauth-server","homepage":"","size":2149,"stargazers_count":45,"watchers_count":45,"language":"JavaScript","has_issues":true,"has_projects":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":39,"mirror_url":null,"archived":false,"open_issues_count":21,"license":{"key":"mpl-2.0","name":"Mozilla Public License 2.0","spdx_id":"MPL-2.0","url":"https://api.github.com/licenses/mpl-2.0","node_id":"MDc6TGljZW5zZTE0"},"forks":39,"open_issues":21,"watchers":45,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/mozilla/fxa-oauth-server/pulls/580"},"html":{"href":"https://github.com/mozilla/fxa-oauth-server/pull/580"},"issue":{"href":"https://api.github.com/repos/mozilla/fxa-oauth-server/issues/580"},"comments":{"href":"https://api.github.com/repos/mozilla/fxa-oauth-server/issues/580/comments"},"review_comments":{"href":"https://api.github.com/repos/mozilla/fxa-oauth-server/pulls/580/comments"},"review_comment":{"href":"https://api.github.com/repos/mozilla/fxa-oauth-server/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/mozilla/fxa-oauth-server/pulls/580/commits"},"statuses":{"href":"https://api.github.com/repos/mozilla/fxa-oauth-server/statuses/e76314f19f30f75b96ef6ced7e3676f46e348376"}},"author_association":"MEMBER"}}
{"actor":{"display_login":"jrgm"}}