Hello, I have a couple of things going on. (1) My mysql based ippool is out of sync with the ips actually in use. This is leading to ip conflicts and immediate drops after successful login. (2) take a look at the sql errors. It apparently doesn't like "expiry_time IS NULL" in the update. For (1) how can I get the ippool in sync? Is the only way to reset the radippool table and drop all the connections from the NAS? For (2) is this an error a by product of the immediate disconnect? Is the mysql version of sqlippool known to work. Also for number (2) one problem I see is the schema doesn't account for callingstationid that are this large. I assume I''ll need to change the schema for this. Happy to send config file setup and/or radiusd -X output if it will help. Though the setup is very basic. Bill
tail -f /var/log/radius/radius.log Mon Dec 2 14:21:40 2013 : Info: rlm_sql_mysql: Starting connect to MySQL server for #4 Mon Dec 2 14:21:40 2013 : Info: rlm_sql (sql): Connected new DB handle, #4 Mon Dec 2 14:21:40 2013 : Info: Loaded virtual server <default> Mon Dec 2 14:21:40 2013 : Info: Ready to process requests. Mon Dec 2 14:28:24 2013 : Auth: Login OK: [abraham1078@epsiton.com] (from client ERX2 port 21068 cli GigabitEthernet 3/0/6.3220146:322-146#587449101#801 GE1 TOROON4415W-TOROON4425W##pppoe 68:15:90:3e:0b:81#) Mon Dec 2 14:28:24 2013 : Error: sqlippool_command: database query error in: 'UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', expiry_time IS NULL WHERE expiry_time <= NOW() - INTERVAL 1 SECOND AND nasipaddress = '204.101.13.1'' Mon Dec 2 14:28:24 2013 : Error: rlm_radutmp: Logout for NAS ERX2 port 21068, but no Login record Mon Dec 2 14:28:24 2013 : Error: sqlippool_command: database query error in: 'UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', expiry_time IS NULL WHERE nasipaddress = '204.101.13.1' AND pool_key = '21068' AND username = 'abraham1078@epsiton.com' AND callingstationid = 'GigabitEthernet 3/0/6.3220146:322-146=23587449101=23801 GE1 TOROON4415W-TOROON4425W=23=23pppoe 68:15:90:3e:0b:81=23' AND framedipaddress = ''' Mon Dec 2 14:28:24 2013 : Info: Released IP (did cli GigabitEthernet 3/0/6.3220146:322-146#587449101#801 GE1 TOROON4415W-TOROON4425W##pppoe 68:15:90:3e:0b:81# user abraham1078@epsiton.com) Mon Dec 2 14:28:24 2013 : Info: [sql] stop packet with zero session length. [user 'abraham1078@epsiton.com', nas '204.101.13.1'] Mon Dec 2 14:40:57 2013 : Auth: Login OK: [abraham1078@epsiton.com] (from client ERX2 port 21070 cli GigabitEthernet 3/0/6.3220146:322-146#587449101#801 GE1 TOROON4415W-TOROON4425W##pppoe 68:15:90:3e:0b:81#) query error here: Mon Dec 2 14:40:57 2013 : Error: sqlippool_command: database query error in: 'UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', expiry_time IS NULL WHERE expiry_time <= NOW() - INTERVAL 1 SECOND AND nasipaddress = '204.101.13.1'' Mon Dec 2 14:40:57 2013 : Error: rlm_radutmp: Logout for NAS ERX2 port 21070, but no Login record Mon Dec 2 14:40:57 2013 : Error: sqlippool_command: database query error in: 'UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', expiry_time IS NULL WHERE nasipaddress = '204.101.13.1' AND pool_key = '21070' AND username = 'abraham1078@epsiton.com' AND callingstationid = 'GigabitEthernet 3/0/6.3220146:322-146=23587449101=23801 GE1 TOROON4415W-TOROON4425W=23=23pppoe 68:15:90:3e:0b:81=23' AND framedipaddress = ''' Mon Dec 2 14:40:57 2013 : Info: Released IP (did cli GigabitEthernet 3/0/6.3220146:322-146#587449101#801 GE1 TOROON4415W-TOROON4425W##pppoe 68:15:90:3e:0b:81# user abraham1078@epsiton.com) Mon Dec 2 14:40:57 2013 : Info: [sql] stop packet with zero session length. [user 'abraham1078@epsiton.com', nas '204.101.13.1']
Bill Schoolfield wrote:
(1) My mysql based ippool is out of sync with the ips actually in use. This is leading to ip conflicts and immediate drops after successful login.
Blame the NAS. FreeRADIUS just assigns IPs, stores that assignment in the DB., and tells the NAS the user has that IP. If there's an expiry, it sends a Session-Timeout and asks the NAS to enforce it. i.e. the *only* way for things to get out of sync is if the NAS isn't hanging up on user sessions.
(2) take a look at the sql errors. It apparently doesn't like "expiry_time IS NULL" in the update.
Use a recent version of the server. The queries you post below were fixed in December 2010.
For (1) how can I get the ippool in sync? Is the only way to reset the radippool table and drop all the connections from the NAS?
Fix the NAS. Or, ensure that FreeRADIUS is sending Session-Timeouts, and that the NAS is enforcing them.
For (2) is this an error a by product of the immediate disconnect? Is the mysql version of sqlippool known to work.
It works if you use the correct queries.
Also for number (2) one problem I see is the schema doesn't account for callingstationid that are this large. I assume I''ll need to change the schema for this.
That's simple enough to do. Alan DeKok.
I did eventually find a thread that mentions the busted schema and querrys and I made the necessary changes. All is well. As for "fix the NAS", so noted. See below for one more comment... On 12/2/2013 3:35 PM, Alan DeKok wrote:
Bill Schoolfield wrote:
(1) My mysql based ippool is out of sync with the ips actually in use. This is leading to ip conflicts and immediate drops after successful login.
Blame the NAS. FreeRADIUS just assigns IPs, stores that assignment in the DB., and tells the NAS the user has that IP. If there's an expiry, it sends a Session-Timeout and asks the NAS to enforce it.
i.e. the *only* way for things to get out of sync is if the NAS isn't hanging up on user sessions.
(2) take a look at the sql errors. It apparently doesn't like "expiry_time IS NULL" in the update.
Use a recent version of the server. The queries you post below were fixed in December 2010.
For (1) how can I get the ippool in sync? Is the only way to reset the radippool table and drop all the connections from the NAS?
Fix the NAS. Or, ensure that FreeRADIUS is sending Session-Timeouts, and that the NAS is enforcing them.
For (2) is this an error a by product of the immediate disconnect? Is the mysql version of sqlippool known to work.
It works if you use the correct queries.
Also for number (2) one problem I see is the schema doesn't account for callingstationid that are this large. I assume I''ll need to change the schema for this.
That's simple enough to do.
I did this but noticed that # characters are being stored in the db as =23 Here is a calling station value in the db: GigabitEthernet 5/0/3.3170044:317-44=23587435726=23804GE1 TOROON44BBN-TOROON4415W=23=23pppoe d8:6c:e9:10:32:c5=23 In the radius -X output the above shows up as GigabitEthernet 5/0/3.3170044:317-44#587435726#804GE1 TOROON44BBN-TOROON4415W##pppoe d8:6c:e9:10:32:c5# Why is this? Bill
Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Bill Schoolfield wrote:
I did this but noticed that # characters are being stored in the db as =23
Here is a calling station value in the db:
GigabitEthernet 5/0/3.3170044:317-44=23587435726=23804GE1 TOROON44BBN-TOROON4415W=23=23pppoe d8:6c:e9:10:32:c5=23
In the radius -X output the above shows up as
GigabitEthernet 5/0/3.3170044:317-44#587435726#804GE1 TOROON44BBN-TOROON4415W##pppoe d8:6c:e9:10:32:c5#
Why is this?
Because '#' is a special character in SQL queries. See the "safe-characters" configuration in dialup.conf. Alan DeKok.
participants (2)
-
Alan DeKok -
Bill Schoolfield