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