<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii"><meta name=Generator content="Microsoft Word 14 (filtered medium)"><!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:"Arial CE";}
@font-face
{font-family:AharoniMF;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
text-align:right;
direction:rtl;
unicode-bidi:embed;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p.MsoAcetate, li.MsoAcetate, div.MsoAcetate
{mso-style-priority:99;
mso-style-link:"Balloon Text Char";
margin:0cm;
margin-bottom:.0001pt;
text-align:right;
direction:rtl;
unicode-bidi:embed;
font-size:8.0pt;
font-family:"Tahoma","sans-serif";}
span.BalloonTextChar
{mso-style-name:"Balloon Text Char";
mso-style-priority:99;
mso-style-link:"Balloon Text";
font-family:"Tahoma","sans-serif";}
span.EmailStyle19
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:windowtext;}
span.EmailStyle20
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 90.0pt 72.0pt 90.0pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link=blue vlink=purple><div class=WordSection1><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span style='color:#1F497D'>(sorry if this is duplicate)<o:p></o:p></span></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span style='color:#1F497D'>i have modified the sql queries and removed unnecessary whitespace,<o:p></o:p></span></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span style='color:#1F497D'>but am still getting some queries cut-off in the log.<o:p></o:p></span></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span style='color:#1F497D'>the main issue is with accounting stop requests.<o:p></o:p></span></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span style='color:#1F497D'>(i am using the default queries provided with freeradius 2.1.12 - dialup.conf)<o:p></o:p></span></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span lang=HE dir=RTL style='font-family:"Arial","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span style='color:#1F497D'>is there a way to increase the space/memory available for sql queries?<o:p></o:p></span></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span style='color:#1F497D'>the main issue is with accounting stop requests.<o:p></o:p></span></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span lang=HE dir=RTL style='font-family:"Arial","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span style='color:#1F497D'>in addition i have found the following in the logs:<o:p></o:p></span></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span style='color:#1F497D'>rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0<o:p></o:p></span></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span style='color:#1F497D'>++[sql] returns fail<o:p></o:p></span></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span lang=HE dir=RTL style='font-family:"Arial","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span style='color:#1F497D'>number of DB connections has already been raised from the default 5 to 25,<o:p></o:p></span></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span style='color:#1F497D'>this is a rare error, but it still exists, might be related.<o:p></o:p></span></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span lang=HE dir=RTL style='font-family:"Arial","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span style='color:#1F497D'>Amir.<o:p></o:p></span></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span style='color:#1F497D'><o:p> </o:p></span></p><div><div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm'><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>From:</span></b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'> Amir Tal <br><b>Sent:</b> Wednesday, July 11, 2012 4:48 PM<br><b>To:</b> 'freeradius-users@lists.freeradius.org'<br><b>Subject:</b> sql returns fail for some stop requests<o:p></o:p></span></p></div></div><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><o:p> </o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>Freeradius ver 2.1.12, configured to use ldap for auth, sql for acct.<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><o:p> </o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>Sometimes users' sessions get stuck and have to be closed manualy (simultaneous use is turned on for all users).<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>After extensive debugging I have found the following in the logs (radius –X)<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><o:p> </o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span style='font-family:"Arial","sans-serif"'><o:p> </o:p></span></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>[<thread>] # Executing section preacct from file /etc/raddb/sites-enabled/default<span lang=HE dir=RTL><o:p></o:p></span></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span dir=LTR></span><span dir=LTR></span>[<thread>] +- entering group preacct {...}<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>++[preprocess] returns ok<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>[acct_unique] Hashing 'NAS-Port = 14117776,Client-IP-Address = xx.xx.xx.xx,NAS-IP-Address = xx.xx.xx.xx,Acct-Session-Id = "erx ip:109.226.0.9:147.235.234.115:1e47:6248:14c2:8b6a:5dac845:0060992452",Use<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>r-Name = "xxxxxxxxx@ccc"'<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>[acct_unique] Acct-Unique-Session-ID = "d49ba42fa077f5f0".<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>++[acct_unique] returns ok<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>[suffix] Looking up realm "ccc" for User-Name = "xxxxxxxxx@ccc"<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>[suffix] Found realm "ccc"<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>[suffix] Adding Stripped-User-Name = "xxxxxxxxx"<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>[suffix] Adding Realm = "ccc"<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>[suffix] Accounting realm is LOCAL.<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>++[suffix] returns ok<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>++[files] returns noop<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'># Executing section accounting from file /etc/raddb/sites-enabled/default<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>+- entering group accounting {...}<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>[detail] expand: %{Packet-Src-IP-Address} -> xx.xx.xx.xx<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>[detail] expand: /var/log/radius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d -> /var/log/radius/radacct/xx.xx.xx.xx/detail-20120711<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>[detail] /var/log/radius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d expands to /var/log/radius/radacct/xx.xx.xx.xx/detail-20120711<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>[detail] expand: %t -> Wed Jul 11 02:03:45 2012<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>Cleaning up request 12612249 ID 93 with timestamp +729235<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>++[detail] returns ok<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>[detail.moreshet] expand: /var/log/radius/radacct/moreshet.relay -> /var/log/radius/radacct/moreshet.relay<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>[detail.moreshet] /var/log/radius/radacct/moreshet.relay expands to /var/log/radius/radacct/moreshet.relay<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>[detail.moreshet] expand: %t -> Wed Jul 11 02:03:45 2012<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>++[detail.moreshet] returns ok<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>++[unix] returns ok<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>[sql] expand: %{Stripped-User-Name} -> xxxxxxxxx<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>[sql] expand: %{%{Stripped-User-Name}:-%{%{User-Name}:-DEFAULT}} -> xxxxxxxxx<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>[sql] sql_set_user escaped user --> 'xxxxxxxxx'<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>[sql] expand: %{Acct-Input-Gigawords} -> 0<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>[sql] expand: %{Acct-Input-Octets} -> 4001<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>[sql] expand: %{Acct-Output-Gigawords} -> 0<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>[sql] expand: %{Acct-Output-Octets} -> 8134<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>[sql] expand: %{Acct-Delay-Time} -> 0<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>[sql] expand: UPDATE radacct SET acctstoptime = '%S', acctsessiontime = '%{Acct-Session-Time}', acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}', acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}', acctterminatecause = '%{Acct-Terminate-Cause}', acctstopdelay = '%{%{Acct-Delay-Time}:-0}', connectinfo_stop = '%{Connect-Info}' WHERE acctsessionid = '%{Acct-Session-Id}' AND username = '%{SQL-User-Name}' AND nasipaddress = '%{NAS-IP-Address}' -> UPDATE radacct SET acctstoptime = '2012-07-11 02:03:45', acctsessiontime = '517', acctinputoctets = '0' << 32 | '4001', acctoutputoctets = '0' << 32 |<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span style='font-family:"Arial","sans-serif"'><o:p> </o:p></span></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>[sql] expand: /var/log/radius/sqltrace.sql -> /var/log/radius/sqltrace.sql<span lang=HE dir=RTL><o:p></o:p></span></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>Cleaning up request 12612250 ID 95 with timestamp +729235<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>++[sql] returns fail<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>Thread 20 got semaphore<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>Thread 19 got semaphore<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><o:p> </o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>It seems the last SQL query line is cut off for some reason, this only happens on some connections, while others are stopped correctly.<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>Not specific to users or time of day.<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><o:p> </o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>Versions information: <o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><o:p> </o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>cat /etc/issue :<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>CentOS release 5.6 (Final)<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>Kernel \r on an \m<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span style='font-family:"Arial","sans-serif"'><o:p> </o:p></span></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>rpm -qa | grep radius :<span lang=HE dir=RTL><o:p></o:p></span></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>freeradius2-python-2.1.12-7<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>freeradius2-ldap-2.1.12-7<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>freeradius2-2.1.12-7<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>freeradius2-krb5-2.1.12-7<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>freeradius2-mysql-2.1.12-7<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>freeradius2-utils-2.1.12-7<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>freeradius2-postgresql-2.1.12-7<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>freeradius2-perl-2.1.12-7<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>freeradius2-unixODBC-2.1.12-7<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><o:p> </o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>additional logs and/or information can be provided if required.<o:p></o:p></p><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'>Help would be appreciated.<o:p></o:p></p><p class=MsoNormal dir=RTL><span dir=LTR><o:p> </o:p></span></p><div align=right><table class=MsoNormalTable border=0 cellspacing=10 cellpadding=0><tr><td colspan=2 style='padding:0cm 0cm 0cm 0cm'><p class=MsoNormal align=center style='text-align:center;direction:ltr;unicode-bidi:embed'><b><span style='font-size:18.0pt;font-family:AharoniMF;color:#198EDF'>The Cloud has no limit !</span></b><span style='font-size:12.0pt'><o:p></o:p></span></p></td></tr><tr><td style='padding:0cm 0cm 0cm 0cm'><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span style='font-family:"Arial","sans-serif"'><a href="http://www.ccc.co.il/"><span style='font-family:"Calibri","sans-serif";text-decoration:none'><img border=0 width=166 height=116 id="_x0000_i1025" src="cid:image001.jpg@01CD629C.B663DAB0" alt="Description: TCC"></span></a></span><span style='font-size:12.0pt'><o:p></o:p></span></p></td><td style='padding:0cm 0cm 0cm 0cm'><table class=MsoNormalTable border=0 cellpadding=0><tr><td style='padding:0cm 0cm 0cm 0cm'><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><b><span style='font-size:13.5pt;font-family:"Arial CE","sans-serif";color:#002060'>Amir Tal</span></b><span style='font-size:12.0pt'><o:p></o:p></span></p></td></tr><tr><td style='padding:0cm 0cm 0cm 0cm'><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span style='font-size:12.0pt;font-family:"Arial CE","sans-serif";color:#002060'>Systems Automation Expert</span><span style='font-size:12.0pt;font-family:"Times New Roman","serif"'><o:p></o:p></span></p></td></tr><tr><td style='padding:0cm 0cm 0cm 0cm'><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span style='font-size:12.0pt;font-family:"Arial CE","sans-serif";color:#002060'>Cloud Services</span><span style='font-size:12.0pt;font-family:"Times New Roman","serif"'><o:p></o:p></span></p></td></tr><tr><td style='padding:0cm 0cm 0cm 0cm'><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span style='font-size:10.0pt;font-family:"Arial CE","sans-serif";color:#0088E0'>Direct: 972-(0)3-9201471</span><span style='font-size:12.0pt'><o:p></o:p></span></p></td></tr><tr><td style='padding:0cm 0cm 0cm 0cm'><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span style='font-size:10.0pt;font-family:"Arial CE","sans-serif";color:#0088E0'>Fax: 972-(0)-3-9201442</span><span style='font-size:12.0pt'><o:p></o:p></span></p></td></tr><tr><td style='padding:0cm 0cm 0cm 0cm'><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><a href="http://www.ccc.co.il/"><b><span style='font-size:12.0pt;font-family:"Arial CE","sans-serif";color:#EC6C0A;text-decoration:none'>www.ccc.co.il</span></b></a> <span style='font-family:"Arial","sans-serif"'><a href="http://www.facebook.com/triplec.il"><span style='font-family:"Calibri","sans-serif";text-decoration:none'><img border=0 width=15 height=15 id="_x0000_i1026" src="cid:image002.png@01CD629C.B663DAB0" alt="Description: dvhxte `lipe ltiiqaew"></span></a></span><span style='font-size:12.0pt'><o:p></o:p></span></p></td></tr></table></td></tr></table></div><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><o:p> </o:p></p><p class=MsoNormal dir=RTL><span dir=LTR><o:p> </o:p></span></p></div></body></html>