sqlcounter does not work

[Alan DeKok]

On Jan 10, 2019, at 10:52 AM, Philemon Jaomalaza <philemon.jaomalaza at gmail.com> wrote:
> 
>> but can reconnecte again on the same day
> 
>  <sigh>  Where is the debug log for THAT?
> 
> Because the debug is the same as a first login when I reconnecta again and again and again, this is bellow :

  It shouldn't be the *same*.  And if you read the debug output, it's not.  That's why we keep telling people to *read* the debug output.

  The first session says:

(1) Sent Access-Accept Id 74 from 154.126.XX.XXX:1812 to 154.126.XX.XXX:40633 length 0
(1)   Acct-Interim-Interval = 60
(1)   Session-Timeout = 83
(1) Finished request

  Which means that the user should be disconnected after 83 seconds.  Is the user disconnected?

  And the Accounting packet then says:

(3)   Acct-Session-Time = 5

  Which means that the next time the user logs in, he will be given 78 seconds.

  It *should* be that simple.

  Instead, we get:

> sqlcounter_expand: 'SELECT SUM(acctsessiontime - GREATEST((1547067600 - UNIX_TIMESTAMP(acctstarttime)), 0)) FROM radacct WHERE username = '%{User-Name}' AND UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '1547067600''
> (0) dailycounter: EXPAND %{%{Stripped-User-Name}:-%{%{User-Name}:-DEFAULT}}
> (0) dailycounter:    --> abcd
> (0) dailycounter: SQL-User-Name set to 'abcd'
> rlm_sql (sql): Reserved connection (1)
> (0) dailycounter: Executing select query: SELECT SUM(acctsessiontime - GREATEST((1547067600 - UNIX_TIMESTAMP(acctstarttime)), 0)) FROM radacct WHERE username = 'abcd' AND UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '1547067600'
> (0) dailycounter: NULL value in first column of result

  Which would indicate that the SQL query isn't returning anything useful.

  So... what happens when you run that SQL query manually?  That's why it's printed out in debug mode.  So you can *read it*, and see for yourself what's going on.

> rlm_sql (sql): Released connection (1)
> (0) dailycounter: EXPAND %{sql:SELECT SUM(acctsessiontime - GREATEST((1547067600 - UNIX_TIMESTAMP(acctstarttime)), 0)) FROM radacct WHERE username = '%{User-Name}' AND UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '1547067600'}
> (0) dailycounter:    -->
> (0) dailycounter: No integer found in result string "".  May be first session, setting counter to 0

  So either the SQL queries are wrong, or the SQL database is returning bad values.

  Again, the debug log is there for people to *read*.  Please, read it.  It is very helpful, and will tell you exactly what's going on.

  Alan DeKok.