Possible bug in rlm_sqlcounter examples

Tim White timwhite88 at gmail.com
Wed Feb 22 00:04:39 CET 2012


Following on from my previous email, I've checked an x86 machine as 
well, and get the same behaviour.

Debug logs follow, the first being the initial login for the day, 
showing sqlcounter not finding an integer and hence returning noop. The 
second being after an initial login where a correct integer is returned.

Can anyone else confirm that the example sqlcounter queries are at fault 
and that we need ether an IFNULL or COALESCE surrounding the SUM? I'll 
be updating the Grase Hotspot files, but I'm wondering if a change was 
made in rlm_sqlcounter in the last few months (year) that has caused it 
to treat NULL as NULL and not as 0, and hence the SQL queries need to be 
updated?

Thanks

Tim
--

rlm_sqlcounter: Entering module authorize code
sqlcounter_expand:  'SELECT SUM(acctsessiontime -                  
GREATEST((1329832800 - UNIX_TIMESTAMP(acctstarttime)), 
0))                  FROM radacct WHERE username = '%{User-Name}' 
AND                  UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > 
'1329832800''
[dailycounter]     expand: SELECT SUM(acctsessiontime -                  
GREATEST((1329832800 - UNIX_TIMESTAMP(acctstarttime)), 
0))                  FROM radacct WHERE username = '%{User-Name}' 
AND                  UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > 
'1329832800' -> SELECT SUM(acctsessiontime -                  
GREATEST((1329832800 - UNIX_TIMESTAMP(acctstarttime)), 
0))                  FROM radacct WHERE username = 'timtest' 
AND                  UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > 
'1329832800'
sqlcounter_expand:  '%{sql:SELECT SUM(acctsessiontime -                  
GREATEST((1329832800 - UNIX_TIMESTAMP(acctstarttime)), 
0))                  FROM radacct WHERE username = 'timtest' 
AND                  UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > 
'1329832800'}'
[dailycounter] sql_xlat
[dailycounter]     expand: %{User-Name} -> timtest
[dailycounter] sql_set_user escaped user --> 'timtest'
[dailycounter]     expand: SELECT SUM(acctsessiontime -                  
GREATEST((1329832800 - UNIX_TIMESTAMP(acctstarttime)), 
0))                  FROM radacct WHERE username = 'timtest' 
AND                  UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > 
'1329832800' -> SELECT SUM(acctsessiontime -                  
GREATEST((1329832800 - UNIX_TIMESTAMP(acctstarttime)), 
0))                  FROM radacct WHERE username = 'timtest' 
AND                  UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > 
'1329832800'
rlm_sql (sql): Reserving sql socket id: 3
[dailycounter] row[0] returned NULL
rlm_sql (sql): Released sql socket id: 3
[dailycounter]     expand: %{sql:SELECT SUM(acctsessiontime 
-                  GREATEST((1329832800 - 
UNIX_TIMESTAMP(acctstarttime)), 0))                  FROM radacct WHERE 
username = 'timtest' AND                  UNIX_TIMESTAMP(acctstarttime) 
+ acctsessiontime > '1329832800'} ->
rlm_sqlcounter: No integer found in string ""
++[dailycounter] returns noop






--------------


rlm_sqlcounter: Entering module authorize code
sqlcounter_expand:  'SELECT SUM(acctsessiontime -                  
GREATEST((1329832800 - UNIX_TIMESTAMP(acctstarttime)), 
0))                  FROM radacct WHERE username = '%{User-Name}' 
AND                  UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > 
'1329832800''
[dailycounter]     expand: SELECT SUM(acctsessiontime -                  
GREATEST((1329832800 - UNIX_TIMESTAMP(acctstarttime)), 
0))                  FROM radacct WHERE username = '%{User-Name}' 
AND                  UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > 
'1329832800' -> SELECT SUM(acctsessiontime -                  
GREATEST((1329832800 - UNIX_TIMESTAMP(acctstarttime)), 
0))                  FROM radacct WHERE username = 'timtest' 
AND                  UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > 
'1329832800'
sqlcounter_expand:  '%{sql:SELECT SUM(acctsessiontime -                  
GREATEST((1329832800 - UNIX_TIMESTAMP(acctstarttime)), 
0))                  FROM radacct WHERE username = 'timtest' 
AND                  UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > 
'1329832800'}'
[dailycounter] sql_xlat
[dailycounter]     expand: %{User-Name} -> timtest
[dailycounter] sql_set_user escaped user --> 'timtest'
[dailycounter]     expand: SELECT SUM(acctsessiontime -                  
GREATEST((1329832800 - UNIX_TIMESTAMP(acctstarttime)), 
0))                  FROM radacct WHERE username = 'timtest' 
AND                  UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > 
'1329832800' -> SELECT SUM(acctsessiontime -                  
GREATEST((1329832800 - UNIX_TIMESTAMP(acctstarttime)), 
0))                  FROM radacct WHERE username = 'timtest' 
AND                  UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > 
'1329832800'
rlm_sql (sql): Reserving sql socket id: 3
[dailycounter] sql_xlat finished
rlm_sql (sql): Released sql socket id: 3
[dailycounter]     expand: %{sql:SELECT SUM(acctsessiontime 
-                  GREATEST((1329832800 - 
UNIX_TIMESTAMP(acctstarttime)), 0))                  FROM radacct WHERE 
username = 'timtest' AND                  UNIX_TIMESTAMP(acctstarttime) 
+ acctsessiontime > '1329832800'} -> 85
rlm_sqlcounter: Check item is greater than query result
rlm_sqlcounter: Authorized user timtest, check_item=600, counter=85
rlm_sqlcounter: Sent Reply-Item for user timtest, Type=Session-Timeout, 
value=515
++[dailycounter] returns ok





More information about the Freeradius-Users mailing list