{"action":"created","issue":{"url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/issues/275","repository_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql","labels_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/issues/275/labels{/name}","comments_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/issues/275/comments","events_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/issues/275/events","html_url":"https://github.com/mozilla/fxa-auth-db-mysql/pull/275","id":255682104,"number":275,"title":"fix(tokens): prune old session tokens that have no device record","user":{"login":"philbooth","id":64367,"avatar_url":"https://avatars2.githubusercontent.com/u/64367?v=4","gravatar_id":"","url":"https://api.github.com/users/philbooth","html_url":"https://github.com/philbooth","followers_url":"https://api.github.com/users/philbooth/followers","following_url":"https://api.github.com/users/philbooth/following{/other_user}","gists_url":"https://api.github.com/users/philbooth/gists{/gist_id}","starred_url":"https://api.github.com/users/philbooth/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/philbooth/subscriptions","organizations_url":"https://api.github.com/users/philbooth/orgs","repos_url":"https://api.github.com/users/philbooth/repos","events_url":"https://api.github.com/users/philbooth/events{/privacy}","received_events_url":"https://api.github.com/users/philbooth/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2017-09-06T17:25:17Z","updated_at":"2017-09-08T06:30:36Z","closed_at":null,"author_association":"MEMBER","pull_request":{"url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/pulls/275","html_url":"https://github.com/mozilla/fxa-auth-db-mysql/pull/275","diff_url":"https://github.com/mozilla/fxa-auth-db-mysql/pull/275.diff","patch_url":"https://github.com/mozilla/fxa-auth-db-mysql/pull/275.patch"},"body":"Fixes #263. Supercedes #274.\r\n\r\nPlease let me know if there is a better way to do this.\r\n\r\nInitially I tried to do it all in one query, with a `LEFT JOIN` between `sessionTokens` and `devices` and an `INNER JOIN` between `sessionTokens` and `unverifiedTokens`. But that doesn't work because, [as per the docs](https://dev.mysql.com/doc/refman/5.7/en/delete.html#idm140189111263328):\r\n\r\n> You cannot use `ORDER BY` or `LIMIT` in a multiple-table `DELETE`.\r\n\r\nSo instead, I've broken it down to two queries. The first uses `NOT EXISTS` and a subquery to deselect session tokens with device records, which allows us to sanely delete from `sessionTokens` with `ORDER BY` and `LIMIT`. Then the second one just mops up any zombies left behind in `unverifiedTokens`, which we don't/shouldn't need to worry about limiting.\r\n\r\nI wasn't sure whether the two queries should be run together in a transaction. I've put them in one to be on the safe side but fwiw I don't *think* they need to be. `unverifiedTokens` is only ever used as the rhs of a `LEFT JOIN` with `sessionTokens`, so it should never matter if they exist after their associated session token has been deleted. And they're getting pruned regularly so it's not like they're going to pile up.\r\n\r\n@mozilla/fxa-devs r?\r\n\r\n/cc @jrgm, @jbuck"},"comment":{"url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/issues/comments/328011951","html_url":"https://github.com/mozilla/fxa-auth-db-mysql/pull/275#issuecomment-328011951","issue_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/issues/275","id":328011951,"user":{"login":"philbooth","id":64367,"avatar_url":"https://avatars2.githubusercontent.com/u/64367?v=4","gravatar_id":"","url":"https://api.github.com/users/philbooth","html_url":"https://github.com/philbooth","followers_url":"https://api.github.com/users/philbooth/followers","following_url":"https://api.github.com/users/philbooth/following{/other_user}","gists_url":"https://api.github.com/users/philbooth/gists{/gist_id}","starred_url":"https://api.github.com/users/philbooth/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/philbooth/subscriptions","organizations_url":"https://api.github.com/users/philbooth/orgs","repos_url":"https://api.github.com/users/philbooth/repos","events_url":"https://api.github.com/users/philbooth/events{/privacy}","received_events_url":"https://api.github.com/users/philbooth/received_events","type":"User","site_admin":false},"created_at":"2017-09-08T06:30:35Z","updated_at":"2017-09-08T06:30:35Z","author_association":"MEMBER","body":"> Please let me know if there is a better way to do this.\r\n\r\nOn the dubious premise that no news is good news, I'll assume this code is perfect and hit the green button."}}
{"actor":{"display_login":"philbooth"}}