Possible bug in rlm_sqlcounter examples
Hi All. I am using the following SQL in sqlcounter for a MySQL database in the Grase Hotspot project, as part of daily/hourly/monthly counters. query = "SELECT SUM(acctsessiontime - \ GREATEST((%b - UNIX_TIMESTAMP(acctstarttime)), 0)) \ FROM radacct WHERE username = '%{%k}' AND \ UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '%b'" This is taken directly out of the examples that come with Freeradius, and is also in the Wiki. http://wiki.freeradius.org/Rlm_sqlcounter#Example+Setup Recently I was having problems where the first login for a day, wasn't being limited to it's daily limit. However, subsequent logins for they day were. So for example, if they had a 4 hour limit, and the first login went over 4 hours, it could keep going as Session-Limit was being returned by freeradius. However, all subsequent logins would return a valid Session-Limit (timeout?) or an access denied if they had gone over the daily limit. Some poking around showed that if there was no logins for that day, the above SQL will return NULL, which Freeradius complains about, something along the lines of there not being an integer in the results (I can't get the exact error message right now), and so the sqlcounter just passes through as noop. To solve the problem, I needed to use an IFNULL (or COALESCE) to return a 0 instead of NULL and then Freeradius sqlcounter returns the correct attributes. query = "SELECT COALESCE( SUM(acctsessiontime - \ GREATEST((%b - UNIX_TIMESTAMP(acctstarttime)), 0)) ) \ FROM radacct WHERE username = '%{%k}' AND \ UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '%b'" This happens on the arm architecture, and so may be architecture dependent. A quick test on x86 MySQL shows it also returns NULL, however I've not had the chance to test how Freeradius interprets the NULL, as 0 or NULL. I will get out an x86 test machine shortly and test what Freeradius is returning. $ apt-cache policy freeradius freeradius: Installed: 2.1.10+dfsg-2 Debian 6.0.3 Linux Kernel 2.6.32 armv5tel Has anyone else run into this problem? Tim
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
On 02/21/2012 11:04 PM, Tim White wrote:
Following on from my previous email, I've checked an x86 machine as well, and get the same behaviour.
I should hope so; SQL is not architecture specific! Your original solution was correct as far as I could see; if there's any chance a column might be absent/null, coalesce or nullif are required. I don't use rlm_sqlcounter so can't say whether absent/null values are expected or a peculiarity of your setup, but a mix of both is possible.
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?
Try looking through the source code history: https://github.com/alandekok/freeradius-server
participants (2)
-
Phil Mayers -
Tim White