Dear Sir this is the log in debugging mode , in query in using query = "SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM radacct WHERE UserName='%{%k}'" it works just fine Waking up in 5 seconds... rad_recv: Access-Request packet from host 192.168.22.2:52098, id=20, length=114 Service-Type = Framed-User Framed-Protocol = PPP User-Name = "wassim" CHAP-Challenge = 0x1d546fdf501eaf2909814861d0b4bd2fa8d51ae91946 RP-HURL = "\225\360[.\303\217v\241Ae\366\373\340\014\025\334G" Calling-Station-Id = "00:23:CD:10:99:A7" NAS-IP-Address = NAS-Port = 25 Processing the authorize section of radiusd.conf modcall: entering group authorize for request 10 modcall[authorize]: module "preprocess" returns ok for request 10 rlm_chap: Setting 'Auth-Type := CHAP' modcall[authorize]: module "chap" returns ok for request 10 modcall[authorize]: module "mschap" returns noop for request 10 rlm_realm: No '@' in User-Name = "wassim", looking up realm NULL rlm_realm: No such realm "NULL" modcall[authorize]: module "suffix" returns noop for request 10 radius_xlat: 'wassim' rlm_sql (sql): sql_set_user escaped user --> 'wassim' radius_xlat: 'SELECT id, UserName, Attribute, Value, op FROM radcheck WHERE Username = 'wassim' ORDER BY id' rlm_sql (sql): Reserving sql socket id: 1 rlm_sql_mysql: query: SELECT id, UserName, Attribute, Value, op FROM radcheck WHERE Username = 'wassim' ORDER BY id radius_xlat: 'SELECT radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op FROM radgroupcheck,usergroup WHERE usergroup.Username = 'wassim' AND usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id' rlm_sql_mysql: query: SELECT radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op FROM radgroupcheck,usergroup WHERE usergroup.Username = 'wassim' AND usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id radius_xlat: 'SELECT id, UserName, Attribute, Value, op FROM radreply WHERE Username = 'wassim' ORDER BY id' rlm_sql_mysql: query: SELECT id, UserName, Attribute, Value, op FROM radreply WHERE Username = 'wassim' ORDER BY id radius_xlat: 'SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE usergroup.Username = 'wassim' AND usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id' rlm_sql_mysql: query: SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE usergroup.Username = 'wassim' AND usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id rlm_sql (sql): Released sql socket id: 1 modcall[authorize]: module "sql" returns ok for request 10 rlm_sqlcounter: Entering module authorize code sqlcounter_expand: 'SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM radacct WHERE UserName='%{User-Name}'' radius_xlat: 'SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM radacct WHERE UserName='wassim'' sqlcounter_expand: '%{sql:SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM radacct WHERE UserName='wassim'}' radius_xlat: Running registered xlat function of module sql for string 'SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM radacct WHERE UserName='wassim'' rlm_sql (sql): - sql_xlat radius_xlat: 'wassim' rlm_sql (sql): sql_set_user escaped user --> 'wassim' radius_xlat: 'SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM radacct WHERE UserName='wassim'' radius_xlat: '/usr/local/var/log/radius/sqltrace.sql' rlm_sql (sql): Reserving sql socket id: 0 rlm_sql_mysql: query: SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM radacct WHERE UserName='wassim' rlm_sql (sql): - sql_xlat finished rlm_sql (sql): Released sql socket id: 0 radius_xlat: '2080402569' rlm_sqlcounter: (Check item - counter) is less than zero rlm_sqlcounter: Rejected user wassim, check_item=1000, counter=2080402569 modcall[authorize]: module "chillispot_max_bytes_daily" returns reject for request 10 modcall: leaving group authorize (returns reject) for request 10 Delaying request 10 for 5 seconds Finished request 10 Going to the next request Now if i replace the query with : sqlcounter chillispot_max_bytes_daily { counter-name = ChilliSpot-Max-Total-Octets check-name = ChilliSpot-Max-Total-Octets reply-name = ChilliSpot-Max-Total-Octets sqlmod-inst = sql key = User-Name reset = daily query = "SELECT SUM(AcctInputOctets - GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)) + SUM(AcctOutputOctets -GREATEST ((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='%{%k}' AND AcctStartTime > FROM_UNIXTIME('%b')" #query = "SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM radacct WHERE UserName='%{%k}'" } which should reset daily , it stops working and user can connect normally and it says i have mysql error, MySQL version 5.0.67 here is the log : rad_recv: Access-Request packet from host 192.168.22.2:58494, id=78, length=111 Service-Type = Framed-User Framed-Protocol = PPP User-Name = "wassim" CHAP-Challenge = 0xb358b401d225b450c30961eb7a583fee0788f8 RP-HURL = "{y\311\332n_"\271\345\016|\334`g\2531\326" Calling-Station-Id = "00:23:CD:10:99:A7" NAS-IP-Address = NAS-Port = 43 Processing the authorize section of radiusd.conf modcall: entering group authorize for request 3 modcall[authorize]: module "preprocess" returns ok for request 3 rlm_chap: Setting 'Auth-Type := CHAP' modcall[authorize]: module "chap" returns ok for request 3 modcall[authorize]: module "mschap" returns noop for request 3 rlm_realm: No '@' in User-Name = "wassim", looking up realm NULL rlm_realm: No such realm "NULL" modcall[authorize]: module "suffix" returns noop for request 3 radius_xlat: 'wassim' rlm_sql (sql): sql_set_user escaped user --> 'wassim' radius_xlat: 'SELECT id, UserName, Attribute, Value, op FROM radcheck WHERE Username = 'wassim' ORDER BY id' rlm_sql (sql): Reserving sql socket id: 3 rlm_sql_mysql: query: SELECT id, UserName, Attribute, Value, op FROM radcheck WHERE Username = 'wassim' ORDER BY id radius_xlat: 'SELECT radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op FROM radgroupcheck,usergroup WHERE usergroup.Username = 'wassim' AND usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id' rlm_sql_mysql: query: SELECT radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op FROM radgroupcheck,usergroup WHERE usergroup.Username = 'wassim' AND usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id radius_xlat: 'SELECT id, UserName, Attribute, Value, op FROM radreply WHERE Username = 'wassim' ORDER BY id' rlm_sql_mysql: query: SELECT id, UserName, Attribute, Value, op FROM radreply WHERE Username = 'wassim' ORDER BY id radius_xlat: 'SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE usergroup.Username = 'wassim' AND usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id' rlm_sql_mysql: query: SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE usergroup.Username = 'wassim' AND usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id rlm_sql (sql): Released sql socket id: 3 modcall[authorize]: module "sql" returns ok for request 3 rlm_sqlcounter: Entering module authorize code sqlcounter_expand: 'SELECT SUM(AcctInputOctets - GREATEST((1283979600 - UNIX_TIMESTAMP(AcctStartTime)), 0)) + SUM(AcctOutputOctets -GREATEST((1283979600 - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='%{User-Name}' AND AcctStartTime > FROM_UNIXTIME('1283979600')' radius_xlat: 'SELECT SUM(AcctInputOctets - GREATEST((1283979600 - UNIX_TIMESTAMP(AcctStartTime)), 0)) + SUM(AcctOutputOctets -GREATEST((1283979600 - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='wassim' AND AcctStartTime > FROM_UNIXTIME('1283979600')' sqlcounter_expand: '%{sql:SELECT SUM(AcctInputOctets - GREATEST((1283979600 - UNIX_TIMESTAMP(AcctStartTime)), 0)) + SUM(AcctOutputOctets -GREATEST((1283979600 - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='wassim' AND AcctStartTime > FROM_UNIXTIME('1283979600')}' radius_xlat: Running registered xlat function of module sql for string 'SELECT SUM(AcctInputOctets - GREATEST((1283979600 - UNIX_TIMESTAMP(AcctStartTime)), 0)) + SUM(AcctOutputOctets -GREATEST((1283979600 - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='wassim' AND AcctStartTime > FROM_UNIXTIME('12839796' rlm_sql (sql): - sql_xlat radius_xlat: 'wassim' rlm_sql (sql): sql_set_user escaped user --> 'wassim' radius_xlat: 'SELECT SUM(AcctInputOctets - GREATEST((1283979600 - UNIX_TIMESTAMP(AcctStartTime)), 0)) + SUM(AcctOutputOctets -GREATEST((1283979600 - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='wassim' AND AcctStartTime > FROM_UNIXTIME('12839796' radius_xlat: '/usr/local/var/log/radius/sqltrace.sql' rlm_sql (sql): Reserving sql socket id: 2 rlm_sql_mysql: query: SELECT SUM(AcctInputOctets - GREATEST((1283979600 - UNIX_TIMESTAMP(AcctStartTime)), 0)) + SUM(AcctOutputOctets -GREATEST((1283979600 - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='wassim' AND AcctStartTime > FROM_UNIXTIME('12839796 rlm_sql_mysql: MYSQL check_error: 1064 received rlm_sql (sql): database query error, SELECT SUM(AcctInputOctets - GREATEST((1283979600 - UNIX_TIMESTAMP(AcctStartTime)), 0)) + SUM(AcctOutputOctets -GREATEST((1283979600 - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='wassim' AND AcctStartTime > FROM_UNIXTIME('12839796: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''12839796' at line 1 rlm_sql (sql): Released sql socket id: 2 radius_xlat: '00')' rlm_sqlcounter: (Check item - counter) is greater than zero rlm_sqlcounter: Authorized user wassim, check_item=1000, counter=0 rlm_sqlcounter: Sent Reply-Item for user wassim, Type=Session-Timeout, value=1000 modcall[authorize]: module "chillispot_max_bytes_daily" returns ok for request 3 modcall: leaving group authorize (returns ok) for request 3 rad_check_password: Found Auth-Type CHAP auth: type "CHAP" Processing the authenticate section of radiusd.conf modcall: entering group CHAP for request 3 rlm_chap: login attempt by "wassim" with CHAP password rlm_chap: Using clear text password nokia for user wassim authentication. rlm_chap: chap user wassim authenticated succesfully modcall[authenticate]: module "chap" returns ok for request 3 modcall: leaving group CHAP (returns ok) for request 3 Processing the session section of radiusd.conf modcall: entering group session for request 3 radius_xlat: 'wassim' rlm_sql (sql): sql_set_user escaped user --> 'wassim' radius_xlat: 'SELECT COUNT(*) FROM radacct WHERE UserName='wassim' AND AcctStopTime = 0' rlm_sql (sql): Reserving sql socket id: 1 rlm_sql_mysql: query: SELECT COUNT(*) FROM radacct WHERE UserName='wassim' AND AcctStopTime = 0 rlm_sql (sql): Released sql socket id: 1 modcall[session]: module "sql" returns ok for request 3 modcall: leaving group session (returns ok) for request 3 Processing the post-auth section of radiusd.conf modcall: entering group post-auth for request 3 rlm_sql (sql): Processing sql_postauth radius_xlat: 'wassim' rlm_sql (sql): sql_set_user escaped user --> 'wassim' radius_xlat: 'INSERT into radpostauth (id, user, pass, reply, date) values ('', 'wassim', '', 'Access-Accept', NOW())' radius_xlat: '/usr/local/var/log/radius/sqltrace.sql' rlm_sql (sql) in sql_postauth: query is INSERT into radpostauth (id, user, pass, reply, date) values ('', 'wassim', '', 'Access-Accept', NOW()) rlm_sql (sql): Reserving sql socket id: 0 rlm_sql_mysql: query: INSERT into radpostauth (id, user, pass, reply, date) values ('', 'wassim', '', 'Access-Accept', NOW()) rlm_sql (sql): Released sql socket id: 0 modcall[post-auth]: module "sql" returns ok for request 3 modcall: leaving group post-auth (returns ok) for request 3 Sending Access-Accept of id 78 to 192.168.22.2 port 58494 Session-Timeout = 1000 Framed-IP-Address = 255.255.255.254 Framed-MTU = 1492 Idle-Timeout = 60 RP-Upstream-Speed-Limit := 512 RP-Downstream-Speed-Limit := 2048 ChilliSpot-Max-Total-Octets := 100 Finished request 3 Going to the next request --- Walking the entire request list --- Waking up in 1 seconds... rad_recv: Accounting-Request packet from host 192.168.22.2:39126, id=79, length=117 Acct-Session-Id = "4C8890FA157000" User-Name = "wassim" Acct-Status-Type = Start Service-Type = Framed-User Framed-Protocol = PPP Calling-Station-Id = "00:23:CD:10:99:A7" Acct-Authentic = RADIUS NAS-Port-Type = Async Framed-IP-Address = 192.1.10.228 NAS-IP-Address = 77.246.69.163 NAS-Port = 43 Acct-Delay-Time = 0 Processing the preacct section of radiusd.conf modcall: entering group preacct for request 4 modcall[preacct]: module "preprocess" returns noop for request 4 rlm_realm: No '@' in User-Name = "wassim", looking up realm NULL rlm_realm: No such realm "NULL" modcall[preacct]: module "suffix" returns noop for request 4 radius_xlat: '/usr/bin/php /root/1stlogin.php wassim 00:23:CD:10:99:A7 ' Exec-Program: /usr/bin/php /root/1stlogin.php wassim 00:23:CD:10:99:A7 PHP Warning: Unknown(): Unable to load dynamic library '/usr/lib/php/extensions/imap.so' - /usr/lib/php/extensions/imap.so: cannot open shared object file: No such file or directory in Unknown on line 0 PHP Warning: Unknown(): Unable to load dynamic library '/usr/lib/php/extensions/ldap.so' - libldap-2.2.so.7: cannot open shared object file: No such file or directory in Unknown on line 0 PHP Warning: Unknown(): Unable to load dynamic library '/usr/lib/php/extensions/mcrypt.so' - libmcrypt.so.4: cannot open shared object file: No such file or directory in Unknown on line 0 PHP Warning: Unknown(): Unable to load dynamic library '/usr/lib/php/extensions/odbc.so' - libodbc.so.1: cannot open shared object file: No such file or directory in Unknown on line 0 PHP Warning: Unknown(): Unable to load dynamic library '/usr/lib/php/extensions/pgsql.so' - libpq.so.3: cannot open shared object file: No such file or directory in Unknown on line 0 PHP Warning: Unknown(): Unable to load dynamic library '/usr/lib/php/extensions/snmp.so' - libnetsnmp.so.5: cannot open shared object file: No such file or directory in Unknown on line 0 Exec-Program output: No Mac Binding Exec-Program-Wait: plaintext: No Mac Binding Exec-Program: returned: 0 modcall[preacct]: module "1stlogin" returns ok for request 4 modcall: leaving group preacct (returns ok) for request 4 Processing the accounting section of radiusd.conf modcall: entering group accounting for request 4 rlm_acct_unique: Hashing 'NAS-Port = 43,Client-IP-Address = 192.168.22.2,NAS-IP-Address = 77.246.69.163,Acct-Session-Id = "4C8890FA157000",User-Name = "wassim"' rlm_acct_unique: Acct-Unique-Session-ID = "835b98f7bb6d18ff". modcall[accounting]: module "acct_unique" returns ok for request 4 radius_xlat: '/usr/local/var/log/radius/radacct/ 192.168.22.2/detail-20100909' rlm_detail: /usr/local/var/log/radius/radacct/%{Client-IP-Address}/detail-%Y%m%d expands to /usr/local/var/log/radius/radacct/192.168.22.2/detail-20100909 modcall[accounting]: module "detail" returns ok for request 4 modcall[accounting]: module "unix" returns ok for request 4 radius_xlat: 'wassim' rlm_sql (sql): sql_set_user escaped user --> 'wassim' radius_xlat: 'INSERT into radacct (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctStopTime, AcctSessionTime, AcctAuthentic, ConnectInfo_start, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay, AcctStopDelay) values('4C8890FA157000', '835b98f7bb6d18ff', 'wassim', '', '77.246.69.163', '43', 'Async', '2010-09-09 10:42:20', '0', '0', 'RADIUS', '', '', '0', '0', '', '00:23:CD:10:99:A7', '', 'Framed-User', 'PPP', '192.1.10.228', '0', '0')' radius_xlat: '/usr/local/var/log/radius/sqltrace.sql' rlm_sql (sql): Reserving sql socket id: 9 rlm_sql_mysql: query: INSERT into radacct (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctStopTime, AcctSessionTime, AcctAuthentic, ConnectInfo_start, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay, AcctStopDelay) values('4C8890FA157000', '835b98f7bb6d18ff', 'wassim', '', '77.246.69.163', '43', 'Async', '2010-09-09 10:42:20', '0', '0', 'RADIUS', '', '', '0', '0', '', '00:23:CD:10:99:A7', '', 'Framed-User', 'PPP', '192.1.10.228', '0', '0') rlm_sql (sql): Released sql socket id: 9 modcall[accounting]: module "sql" returns ok for request 4 modcall: leaving group accounting (returns ok) for request 4 Sending Accounting-Response of id 79 to 192.168.22.2 port 39126 Finished request 4 Going to the next request