IP-Assignment with sqlippool based on nas-ip-address
Sebastian Heil
s3b0 at gmx.de
Wed Jan 28 14:37:06 CET 2009
>
> It is a bug. That should be "... expiry_time = NULL WHERE ..."
>
Hi,
this is working fine.
But there seems to be a problem with this statement now:
-------------
SELECT framedipaddress FROM radippool WHERE pool_name = 'poolDE' AND expiry_time < NOW() ORDER BY (username <> 'peter2'), (callingstationid <> ''), expiry_time LIMIT 1 FOR UPDATE
------------
This statement should receive a free ip-adress, i think, but it doesn't. I think the problem is the part "expiry_time < NOW()". This is not working in mysql. Does anyone know how to fix this?
Thanks
Sebastian
complete debug:
rad_recv: Access-Request packet from host 10.98.6.33 port 2577, id=127, length=67
User-Name = "peter2"
User-Password = "peter2"
Called-Station-Id = "xxx"
+- entering group authorize {...}
++[preprocess] returns ok
[auth_log] expand: /var/log/radius/radacct/%{Client-IP-Address}/auth-detail-%Y%m%d -> /var/log/radius/radacct/10.98.6.33/auth-detail-20090128
[auth_log] /var/log/radius/radacct/%{Client-IP-Address}/auth-detail-%Y%m%d expands to /var/log/radius/radacct/10.98.6.33/auth-detail-20090128
[auth_log] expand: %t -> Wed Jan 28 14:27:45 2009
++[auth_log] returns ok
++[chap] returns noop
++[mschap] returns noop
[suffix] No '@' in User-Name = "peter2", looking up realm NULL
[suffix] No such realm "NULL"
++[suffix] returns noop
[eap] No EAP-Message, not doing EAP
++[eap] returns noop
++[unix] returns notfound
[files] users: Matched entry DEFAULT at line 183
++[files] returns ok
[sql] expand: %{User-Name} -> peter2
[sql] sql_set_user escaped user --> 'peter2'
rlm_sql (sql): Reserving sql socket id: 2
[sql] expand: SELECT id, username, attribute, value, op FROM radcheck WHERE username = '%{SQL-User-Name}' ORDER BY id -> SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'peter2' ORDER BY id
rlm_sql_mysql: query: SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'peter2' ORDER BY id
[sql] User found in radcheck table
[sql] expand: SELECT id, username, attribute, value, op FROM radreply WHERE username = '%{SQL-User-Name}' ORDER BY id -> SELECT id, username, attribute, value, op FROM radreply WHERE username = 'peter2' ORDER BY id
rlm_sql_mysql: query: SELECT id, username, attribute, value, op FROM radreply WHERE username = 'peter2' ORDER BY id
[sql] expand: SELECT groupname FROM radusergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority -> SELECT groupname FROM radusergroup WHERE username = 'peter2' ORDER BY priority
rlm_sql_mysql: query: SELECT groupname FROM radusergroup WHERE username = 'peter2' ORDER BY priority
[sql] expand: SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '%{Sql-Group}' ORDER BY id -> SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = 'Deutschland' ORDER BY id
rlm_sql_mysql: query: SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = 'Deutschland' ORDER BY id
[sql] User found in group Deutschland
[sql] expand: SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = '%{Sql-Group}' ORDER BY id -> SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = 'Deutschland' ORDER BY id
rlm_sql_mysql: query: SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = 'Deutschland' ORDER BY id
rlm_sql (sql): Released sql socket id: 2
++[sql] returns ok
++[expiration] returns noop
++[logintime] returns noop
++[pap] returns updated
Found Auth-Type = PAP
+- entering group PAP {...}
[pap] login attempt with password "peter2"
[pap] Using clear text password "peter2"
[pap] User authenticated successfully
++[pap] returns ok
+- entering group post-auth {...}
rlm_sql (sql): Reserving sql socket id: 1
[sqlippool] expand: %{User-Name} -> peter2
[sqlippool] sql_set_user escaped user --> 'peter2'
[sqlippool] expand: START TRANSACTION -> START TRANSACTION
rlm_sql_mysql: query: START TRANSACTION
[sqlippool] expand: UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', expiry_time = NULL WHERE expiry_time <= NOW() - INTERVAL 1 SECOND AND nasipaddress = '%{Nas-IP-Address}' -> UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', expiry_time = NULL WHERE expiry_time <= NOW() - INTERVAL 1 SECOND AND nasipaddress = '10.98.6.33'
rlm_sql_mysql: query: UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', expiry_time = NULL WHERE expiry_time <= NOW() - INTERVAL 1 SECOND AND nasipaddress = '10.98.6.33'
[sqlippool] expand: SELECT framedipaddress FROM radippool WHERE pool_name = '%{control:Pool-Name}' AND expiry_time < NOW() ORDER BY (username <> '%{User-Name}'), (callingstationid <> '%{Calling-Station-Id}'), expiry_time LIMIT 1 FOR UPDATE -> SELECT framedipaddress FROM radippool WHERE pool_name = 'poolDE' AND expiry_time < NOW() ORDER BY (username <> 'peter2'), (callingstationid <> ''), expiry_time LIMIT 1 FOR UPDATE
rlm_sql_mysql: query: SELECT framedipaddress FROM radippool WHERE pool_name = 'poolDE' AND expiry_time < NOW() ORDER BY (username <> 'peter2'), (callingstationid <> ''), expiry_time LIMIT 1 FOR UPDATE
[sqlippool] SQL query did not return any results
[sqlippool] expand: COMMIT -> COMMIT
rlm_sql_mysql: query: COMMIT
[sqlippool] expand: SELECT id FROM radippool WHERE pool_name='%{control:Pool-Name}' LIMIT 1 -> SELECT id FROM radippool WHERE pool_name='poolDE' LIMIT 1
rlm_sql_mysql: query: SELECT id FROM radippool WHERE pool_name='poolDE' LIMIT 1
rlm_sql (sql): Released sql socket id: 1
[sqlippool] pool appears to be full
[sqlippool] expand: IP Allocation FAILED from %{control:Pool-Name} (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name}) -> IP Allocation FAILED from poolDE (did xxx cli port user peter2)
IP Allocation FAILED from poolDE (did xxx cli port user peter2)
++[sqlippool] returns notfound
++[exec] returns noop
Sending Access-Accept of id 127 to 10.98.6.33 port 2577
Service-Type = Framed-User
Framed-Protocol = PPP
Framed-IP-Netmask = 255.255.255.0
Finished request 1.
Going to the next request
Waking up in 4.9 seconds.
Cleaning up request 1 ID 127 with timestamp +1000
Ready to process requests.
--
NUR NOCH BIS 31.01.! GMX FreeDSL - Telefonanschluss + DSL
für nur 16,37 EURO/mtl.!* http://dsl.gmx.de/?ac=OM.AD.PD003K11308T4569a
More information about the Freeradius-Users
mailing list