1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
|
--
-- A stored procedure to reallocate a user's previous address, otherwise
-- provide a free address.
--
-- Using this SP reduces the usual set dialogue of queries to a single
-- query:
--
-- START TRANSACTION; SELECT FOR UPDATE; UPDATE; COMMIT; -> CALL sp()
--
-- The stored procedure is executed on an database instance within a single
-- round trip which often leads to reduced deadlocking and significant
-- performance improvements especially on multi-master clusters, perhaps even
-- by an order of magnitude or more.
--
-- To use this stored procedure the corresponding queries.conf statements must
-- be configured as follows:
--
-- allocate_begin = ""
-- allocate_find = "\
-- CALL fr_dhcp_allocate_previous_or_new_framedipaddress( \
-- '%{control:${pool_name}}', \
-- '%{DHCP-Gateway-IP-Address}', \
-- '${pool_key}', \
-- ${lease_duration}, \
-- '%{%{${req_attribute_name}}:-0.0.0.0}' \
-- )"
-- allocate_update = ""
-- allocate_commit = ""
--
DELIMITER $$
DROP PROCEDURE IF EXISTS fr_dhcp_allocate_previous_or_new_framedipaddress;
CREATE PROCEDURE fr_dhcp_allocate_previous_or_new_framedipaddress (
IN v_pool_name VARCHAR(30),
IN v_gateway VARCHAR(15),
IN v_pool_key VARCHAR(30),
IN v_lease_duration INT,
IN v_requested_address VARCHAR(15)
)
SQL SECURITY INVOKER
proc:BEGIN
DECLARE r_address VARCHAR(15);
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
RESIGNAL;
END;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
-- Reissue an existing IP address lease when re-authenticating a session
--
SELECT framedipaddress INTO r_address
FROM dhcpippool
WHERE pool_name = v_pool_name
AND expiry_time > NOW()
AND pool_key = v_pool_key
AND `status` IN ('dynamic', 'static')
LIMIT 1
FOR UPDATE;
-- FOR UPDATE SKIP LOCKED; -- Better performance, but limited support
-- NOTE: You should enable SKIP LOCKED here (as well as any other
-- instances) if your database server supports it. If it is not
-- supported and you are not running a multi-master cluster (e.g.
-- Galera or MaxScale) then you should instead consider using the
-- SP in procedure-no-skip-locked.sql which will be faster and
-- less likely to result in thread starvation under highly
-- concurrent load.
-- Reissue an user's previous IP address, provided that the lease is
-- available (i.e. enable sticky IPs)
--
-- When using this SELECT you should delete the one above. You must also
-- set allocate_clear = "" in queries.conf to persist the associations
-- for expired leases.
--
-- SELECT framedipaddress INTO r_address
-- FROM dhcpippool
-- WHERE pool_name = v_pool_name
-- AND pool_key = v_pool_key
-- AND `status` IN ('dynamic', 'static')
-- LIMIT 1
-- FOR UPDATE;
-- -- FOR UPDATE SKIP LOCKED; -- Better performance, but limited support
-- Issue the requested IP address if it is available
--
IF r_address IS NULL AND v_requested_address <> '0.0.0.0' THEN
SELECT framedipaddress INTO r_address
FROM dhcpippool
WHERE pool_name = v_pool_name
AND framedipaddress = v_requested_address
AND `status` = 'dynamic'
AND ( pool_key = v_pool_key OR expiry_time < NOW() )
FOR UPDATE;
-- FOR UPDATE SKIP LOCKED; -- Better performance, but limited support
END IF;
-- If we didn't reallocate a previous address then pick the least
-- recently used address from the pool which maximises the likelihood
-- of re-assigning the other addresses to their recent user
--
IF r_address IS NULL THEN
SELECT framedipaddress INTO r_address
FROM dhcpippool
WHERE pool_name = v_pool_name
AND expiry_time < NOW()
AND `status` = 'dynamic'
ORDER BY
expiry_time
LIMIT 1
FOR UPDATE;
-- FOR UPDATE SKIP LOCKED; -- Better performance, but limited support
END IF;
-- Return nothing if we failed to allocated an address
--
IF r_address IS NULL THEN
COMMIT;
LEAVE proc;
END IF;
-- Update the pool having allocated an IP address
--
UPDATE dhcpippool
SET
gateway = v_gateway,
pool_key = v_pool_key,
expiry_time = NOW() + INTERVAL v_lease_duration SECOND
WHERE framedipaddress = r_address;
COMMIT;
-- Return the address that we allocated
SELECT r_address;
END$$
DELIMITER ;
|
