1--
2-- A stored procedure to reallocate a user's previous address, otherwise
3-- provide a free address.
4--
5-- NOTE: This version of the SP is intended for MySQL variants that do not
6--       support the SKIP LOCKED pragma, i.e. MariaDB and versions of MySQL
7--       prior to 8.0. It should be a lot faster than using the default SP
8--       without the SKIP LOCKED pragma under highly concurrent workloads
9--       and not result in thread starvation.
10--
11--       It is however a *useful hack* which should not be used if SKIP
12--       LOCKED is available.
13--
14-- WARNING: This query uses server-local, "user locks" (GET_LOCK and
15--          RELEASE_LOCK), without the need for a transaction, to emulate
16--          row locking with locked-row skipping. User locks are not
17--          supported on clusters such as Galera and MaxScale.
18--
19-- Using this SP reduces the usual set dialogue of queries to a single
20-- query:
21--
22--   START TRANSACTION; SELECT FOR UPDATE; UPDATE; COMMIT;  ->  CALL sp()
23--
24-- The stored procedure is executed within a single round trip which often
25-- leads to reduced deadlocking and significant performance improvements.
26--
27-- To use this stored procedure the corresponding queries.conf statements must
28-- be configured as follows:
29--
30-- allocate_begin = ""
31-- allocate_find = "\
32-- 	CALL fr_allocate_previous_or_new_framedipaddress( \
33-- 		'%{control:${pool_name}}', \
34-- 		'%{User-Name}', \
35-- 		'%{Calling-Station-Id}', \
36-- 		'%{NAS-IP-Address}', \
37-- 		'${pool_key}', \
38-- 		${lease_duration} \
39-- 	)"
40-- allocate_update = ""
41-- allocate_commit = ""
42--
43
44CREATE INDEX poolname_username_callingstationid ON radippool(pool_name,username,callingstationid);
45
46DELIMITER $$
47
48DROP PROCEDURE IF EXISTS fr_allocate_previous_or_new_framedipaddress;
49CREATE PROCEDURE fr_allocate_previous_or_new_framedipaddress (
50        IN v_pool_name VARCHAR(64),
51        IN v_username VARCHAR(64),
52        IN v_callingstationid VARCHAR(64),
53        IN v_nasipaddress VARCHAR(15),
54        IN v_pool_key VARCHAR(64),
55        IN v_lease_duration INT
56)
57SQL SECURITY INVOKER
58proc:BEGIN
59        DECLARE r_address VARCHAR(15);
60
61        -- Reissue an existing IP address lease when re-authenticating a session
62        --
63        SELECT framedipaddress INTO r_address
64        FROM radippool
65        WHERE pool_name = v_pool_name
66                AND expiry_time > NOW()
67                AND nasipaddress = v_nasipaddress
68                AND pool_key = v_pool_key
69        LIMIT 1;
70
71        -- Reissue an user's previous IP address, provided that the lease is
72        -- available (i.e. enable sticky IPs)
73        --
74        -- When using this SELECT you should delete the one above. You must also
75        -- set allocate_clear = "" in queries.conf to persist the associations
76        -- for expired leases.
77        --
78        -- SELECT framedipaddress INTO r_address
79        -- FROM radippool
80        -- WHERE pool_name = v_pool_name
81        --         AND nasipaddress = v_nasipaddress
82        --         AND pool_key = v_pool_key
83        -- LIMIT 1;
84
85        IF r_address IS NOT NULL THEN
86                UPDATE radippool
87                SET
88                        nasipaddress = v_nasipaddress,
89                        pool_key = v_pool_key,
90                        callingstationid = v_callingstationid,
91                        username = v_username,
92                        expiry_time = NOW() + INTERVAL v_lease_duration SECOND
93                WHERE
94                        framedipaddress = r_address;
95                SELECT r_address;
96                LEAVE proc;
97        END IF;
98
99        REPEAT
100
101                -- If we didn't reallocate a previous address then pick the least
102                -- recently used address from the pool which maximises the likelihood
103                -- of re-assigning the other addresses to their recent user
104                --
105                SELECT framedipaddress INTO r_address
106                FROM radippool
107                WHERE pool_name = v_pool_name
108                        AND expiry_time < NOW()
109                --
110                -- WHERE ... GET_LOCK(...,0) = 1 is a poor man's SKIP LOCKED that simulates
111                -- a row-level lock using a "user lock" that allows the locked "rows" to be
112                -- skipped. After the user lock is acquired and the SELECT retired it does
113                -- not mean that the entirety of the WHERE clause is still true: Another
114                -- thread may have updated the expiry time and released the lock after we
115                -- checked the expiry_time but before we acquired the lock since SQL is free
116                -- to reorder the WHERE condition. Therefore we must recheck the condition
117                -- in the UPDATE statement below to detect this race.
118                --
119                        AND GET_LOCK(CONCAT('radippool_', framedipaddress), 0) = 1
120                LIMIT 1;
121
122                IF r_address IS NULL THEN
123                        DO RELEASE_LOCK(CONCAT('radippool_', r_address));
124                        LEAVE proc;
125                END IF;
126
127                UPDATE radippool
128                SET
129                        nasipaddress = v_nasipaddress,
130                        pool_key = v_pool_key,
131                        callingstationid = v_callingstationid,
132                        username = v_username,
133                        expiry_time = NOW() + INTERVAL v_lease_duration SECOND
134                WHERE
135                        framedipaddress = r_address
136                --
137                -- Here we re-evaluate the original condition for selecting the address
138                -- to detect a race, in which case we try again...
139                --
140                        AND expiry_time<NOW();
141
142        UNTIL ROW_COUNT() <> 0 END REPEAT;
143
144        DO RELEASE_LOCK(CONCAT('radippool_', r_address));
145        SELECT r_address;
146
147END$$
148
149DELIMITER ;
150