uNz3KRRd

1create procedure PR$DELETE_OLD_AUTH_TOKENS()
2BEGIN
3  DROP TEMPORARY TABLE IF EXISTS tmp_del_authtokens;
4  CREATE TEMPORARY TABLE tmp_del_authtokens
5  (
6    id         INT(11) NOT NULL PRIMARY KEY,
7    auth_token varchar(128)
8  );
9
10  drop temporary table if exists tmp_set_authlog_session_finish;
11  create temporary table tmp_set_authlog_session_finish
12  (
13    id int(11) not null primary key
14  );
15
16  SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
17  START TRANSACTION
18    ;
19    INSERT INTO tmp_del_authtokens(id, auth_token) (SELECT id, token
20                                                    FROM auth_token
21                                                    WHERE expiration_time < CURRENT_TIMESTAMP
22                                                    ORDER BY expiration_time
23                                                    LIMIT 50000);
24
25    insert into tmp_set_authlog_session_finish(id) (select id
26                                                    from authlog
27                                                    where authlog.auth_token in (tmp_del_authtokens.auth_token));
28
29    DELETE del
30    FROM `auth_token` del
31           JOIN tmp_del_authtokens t ON t.id = del.id;
32
33    update authlog
34    set authlog.session_finish = CURRENT_TIMESTAMP
35    where authlog.id in (tmp_set_authlog_session_finish.id)
36      and authlog.session_finish is null;
37  COMMIT;
38  DROP TEMPORARY TABLE IF EXISTS tmp_del_authtokens;
39  drop temporary table if exists tmp_set_authlog_session_finish;
40END;