Re: log loading of configuration files [was Re: Accounting to MySQL not working]
Josip Rodin wrote: Then again, there is no clear indication to users which part of the large debug output is important and which part is ignorable, so even if they don't ignore it, it may still actually be too complicated for them to handle. The alternative is to run a system that you don't understand. This isn't recommended. For example, add a debug level which would make all references to module instances also include the config filename they were read from. The solution for too much debug output is even *more* debug output? I see your point, but I *still* think there will be issues reading the debugging output, even with having it print filenames. It would be a long output, but doesn't seem very hard to implement (just keep a mapping of module-to-configfilename sets in memory) and would be a good option to have in these cases. Ditto for other setting blocks - virtual hosts, home servers, home server pools. The filename is already cached internally. I think it's safer just to *occasionally* print out the filename, and then ask people to *read* the debug output. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: log loading of configuration files [was Re: Accounting to MySQL not working]
On Mon, Jun 07, 2010 at 10:49:01AM +0200, Alan DeKok wrote: Josip Rodin wrote: Then again, there is no clear indication to users which part of the large debug output is important and which part is ignorable, so even if they don't ignore it, it may still actually be too complicated for them to handle. The alternative is to run a system that you don't understand. This isn't recommended. For example, add a debug level which would make all references to module instances also include the config filename they were read from. The solution for too much debug output is even *more* debug output? I see your point, but I *still* think there will be issues reading the debugging output, even with having it print filenames. If the debugging output isn't particularly straightforward - and with regard to filenames it doesn't really have to be, because you can put practically any kind of setting into what is nominally a virtual host file, it can be clients or home servers or realms or whatever - then people will have issues reading it. Add to that this inherent ability to *silently* override one setting with another from a different file, and then even more people will have issues reading the debug output. In other words, there was too much debug output of the wrong kind, and too little debug output of the right kind, to debug that problem. So the solution is to expand it in the areas where it needs expansion, and reduce it in the areas where it's too verbose already. IOW, don't just blurt out the list of files in the beginning, but put them down there next to the settings that come from those files. Though I think probably the best way forward in the matter of restructuring debug output is to generally straighten out like with those new patches you mentioned a few months back. What happened to that? -- 2. That which causes joy or happiness. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: log loading of configuration files [was Re: Accounting to MySQL not working]
On Wed, Jun 02, 2010 at 01:46:02PM +0200, Alan DeKok wrote: When they run it with -X, they'll see the packets as they come in and that's good for the debugging of the per-request logic, but a lot of this initial text will scroll down the screen as if everything in it is all right, and they might miss important information in it. Well... it shouldn't be too much to ask people to read the debug output. It prints out filenames for a *reason*. Anyone ignoring that because it's too complicated is responsible for their own choice. Then again, there is no clear indication to users which part of the large debug output is important and which part is ignorable, so even if they don't ignore it, it may still actually be too complicated for them to handle. The issues seen recently here with unusual behavior in the config are partly because the server is flexible, in that it allows multiple overlapping configuration sections. It's also partly the responsibility of people who don't examine the configuration files the server is using. On this specific issue - if, perhaps, there was a message about which setting was read from which file, rather than two asynchronous sets of messages, it would be easier to understand and see any unwanted duplication. For example, add a debug level which would make all references to module instances also include the config filename they were read from. It would be a long output, but doesn't seem very hard to implement (just keep a mapping of module-to-configfilename sets in memory) and would be a good option to have in these cases. Ditto for other setting blocks - virtual hosts, home servers, home server pools. -- 2. That which causes joy or happiness. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
log loading of configuration files [was Re: Accounting to MySQL not working]
On Tue, Jun 01, 2010 at 02:19:31PM +0200, joy wrote: On Sat, May 29, 2010 at 10:42:06AM +0200, Alan DeKok wrote: Even Apache reads the entire directory: ... # Include the virtual host configurations: Include /etc/apache2/sites-enabled/ ... So what's the solution? Why isn't this a problem for Apache, and is a problem here? It stands to reason that they have their own share of people making these mistakes, we just don't hear about it here. :) It occurs to me that it would be a good idea to add an option that would allow users to log the entire initial configuration parsing on startup to the main log file, IOW log that part even if the server did not run with -X. When they run it with -X, they'll see the packets as they come in and that's good for the debugging of the per-request logic, but a lot of this initial text will scroll down the screen as if everything in it is all right, and they might miss important information in it. This option could default to on in the default config files, for the sake of newbies. Since server (re)starts aren't common, let alone a per-packet event (hopefully! :), this would not be a deviation from the practice of keeping the logs quiet by default. And the advanced users would simply keep their old configs and/or turn the new option off. On related note, OpenLDAP provides loglevel 'config' for a very similar functionality, it logs the parsing of slapd.conf on start. -- 2. That which causes joy or happiness. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: log loading of configuration files [was Re: Accounting to MySQL not working]
Josip Rodin wrote: It occurs to me that it would be a good idea to add an option that would allow users to log the entire initial configuration parsing on startup to the main log file, IOW log that part even if the server did not run with -X. Hmm... OK. When they run it with -X, they'll see the packets as they come in and that's good for the debugging of the per-request logic, but a lot of this initial text will scroll down the screen as if everything in it is all right, and they might miss important information in it. Well... it shouldn't be too much to ask people to read the debug output. It prints out filenames for a *reason*. Anyone ignoring that because it's too complicated is responsible for their own choice. The issues seen recently here with unusual behavior in the config are partly because the server is flexible, in that it allows multiple overlapping configuration sections. It's also partly the responsibility of people who don't examine the configuration files the server is using. On related note, OpenLDAP provides loglevel 'config' for a very similar functionality, it logs the parsing of slapd.conf on start. That's what debug mode does... Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Accounting to MySQL not working
On Sat, May 29, 2010 at 10:42:06AM +0200, Alan DeKok wrote: Even Apache reads the entire directory: ... # Include the virtual host configurations: Include /etc/apache2/sites-enabled/ ... So what's the solution? Why isn't this a problem for Apache, and is a problem here? It stands to reason that they have their own share of people making these mistakes, we just don't hear about it here. :) -- 2. That which causes joy or happiness. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Accounting to MySQL not working
John Dennis wrote: Hmm... we just had a discussion about how loading every configuration file in a directory trips folks up. Yet another example :-) We need a solution that is (a) easy, and (b) doesn't involve a massive re-name of files in the middle of a stable release? Even Apache reads the entire directory: ... # Include the virtual host configurations: Include /etc/apache2/sites-enabled/ ... So what's the solution? Why isn't this a problem for Apache, and is a problem here? Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Accounting to MySQL not working
Hi Alan, thank you for your response. according to the debug: +- entering group accounting {...} [detail]expand: /var/log/freeradius/radacct/%{Client-IP-Address}/detail-%Y%m%d - +/var/log/freeradius/radacct/192.168.1.10/detail-20100527 [detail] /var/log/freeradius/radacct/%{Client-IP-Address}/detail-%Y%m%d expands to +/var/log/freeradius/radacct/192.168.1.10/detail-20100527 [detail]expand: %t - Thu May 27 23:32:23 2010 ++[detail] returns ok ++[unix] returns ok [radutmp] expand: /var/log/freeradius/radutmp - /var/log/freeradius/radutmp [radutmp] expand: %{User-Name} - chrissql ++[radutmp] returns ok [attr_filter.accounting_response] expand: %{User-Name} - chrissql attr_filter: Matched entry DEFAULT at line 12 ++[attr_filter.accounting_response] returns updated Sending Accounting-Response of id 77 to 192.168.1.10 port 1646 Finished request 19. so, its drops into accouting section...it does detail unix radutmp attr_filter.accounting_response but where oh were was the SQL being called? hmm, from here is doesnt look like you are calling it. check the sites-enabled/* files (I dont know what virtual servers you have running or what you've called them) and please uncomment the 'sql' It comes after the lines that say: # # Log traffic to an SQL database. # # See Accounting queries in sql.conf this part is (and was) enabled please find below the configuration of the files (which is the same configuration as for 2.0.4 - this configuration is working for 2.0.4) however, it seems the sql line is ignored by the radius server file: default authorize { preprocess chap mschap suffix eap { ok = return } unix files sql expiration logintime pap } authenticate { Auth-Type PAP { pap } Auth-Type CHAP { chap } Auth-Type MS-CHAP { mschap } unix eap } preacct { preprocess acct_unique suffix files } accounting { detail unix radutmp sql attr_filter.accounting_response } session { radutmp } post-auth { exec Post-Auth-Type REJECT { attr_filter.access_reject } } pre-proxy { } post-proxy { eap } -- fiel: inner-tunnel server inner-tunnel { authorize { chap mschap unix suffix update control { Proxy-To-Realm := LOCAL } eap { ok = return } files sql expiration logintime pap } authenticate { Auth-Type PAP { pap } Auth-Type CHAP { chap } Auth-Type MS-CHAP { mschap } unix eap } session { radutmp } post-auth { Post-Auth-Type REJECT { attr_filter.access_reject } } pre-proxy { } post-proxy { eap } BTW: If I uncomment all 'sql' entries, I get data into the radpostauth table, but I never get data into the radacct table. Has somebody a working configuration for logging into the radacct mysql table with freeradius v2.1.8 (under Debian Lenny)? br Christoph -- GMX.at - Österreichs FreeMail-Dienst mit über 2 Mio Mitgliedern E-Mail, SMS mehr! Kostenlos: http://portal.gmx.net/de/go/atfreemail - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Accounting to MySQL not working
Christoph Schwabl wrote: this part is (and was) enabled please find below the configuration of the files (which is the same configuration as for 2.0.4 - this configuration is working for 2.0.4) however, it seems the sql line is ignored by the radius server This happens in one of two cases: 1) the server is reading a *different* file than the one you're editing 2) there are *two* accounting sections, and the server is using the first rather than the second. BTW: If I uncomment all 'sql' entries, I get data into the radpostauth table, but I never get data into the radacct table. Then either it isn't receiving accounting packets, *or* it's not using the sql configuration you think it's using. Has somebody a working configuration for logging into the radacct mysql table with freeradius v2.1.8 (under Debian Lenny)? $ cd /etc/raddb/sites-enabled $ grep sql * And un-comment the lines that look like: default:# sql This is the default config, and it does work. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Accounting to MySQL not working
however, it seems the sql line is ignored by the radius server This happens in one of two cases: 1) the server is reading a *different* file than the one you're editing this exactly was the problem I had 2 files in /etc/raddb/sites-enabled default.original default It seems the server was reading default.original Since I deleted default.original its working fine. thx br Christoph -- GMX.at - Österreichs FreeMail-Dienst mit über 2 Mio Mitgliedern E-Mail, SMS mehr! Kostenlos: http://portal.gmx.net/de/go/atfreemail - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Accounting to MySQL not working
On 05/28/2010 04:33 PM, Christoph Schwabl wrote: however, it seems the sql line is ignored by the radius server This happens in one of two cases: 1) the server is reading a *different* file than the one you're editing this exactly was the problem I had 2 files in /etc/raddb/sites-enabled default.original default It seems the server was reading default.original Since I deleted default.original its working fine. Hmm... we just had a discussion about how loading every configuration file in a directory trips folks up. Yet another example :-) -- John Dennis jden...@redhat.com Looking to carve out IT costs? www.redhat.com/carveoutcosts/ - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Accounting to MySQL not working
:) Sure, its good idea to create backups but do not store them in the sites-enabled dir :) John Dennis wrote: On 05/28/2010 04:33 PM, Christoph Schwabl wrote: however, it seems the sql line is ignored by the radius server This happens in one of two cases: 1) the server is reading a *different* file than the one you're editing this exactly was the problem I had 2 files in /etc/raddb/sites-enabled default.original default It seems the server was reading default.original Since I deleted default.original its working fine. Hmm... we just had a discussion about how loading every configuration file in a directory trips folks up. Yet another example :-) - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Accounting to MySQL not working
Making a backup of the file in sites-available would make more sense since the files in sites-enabled are only links to the files in sites-enabled. It is documented. ;) Marinko Tarlac mangi...@gmail.com wrote: :) Sure, its good idea to create backups but do not store them in the sites-enabled dir :) John Dennis wrote: On 05/28/2010 04:33 PM, Christoph Schwabl wrote: however, it seems the sql line is ignored by the radius server This happens in one of two cases: 1) the server is reading a *different* file than the one you're editing this exactly was the problem I had 2 files in /etc/raddb/sites-enabled default.original default It seems the server was reading default.original Since I deleted default.original its working fine. Hmm... we just had a discussion about how loading every configuration file in a directory trips folks up. Yet another example :-) - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Accounting to MySQL not working
hi, according to the debug: +- entering group accounting {...} [detail]expand: /var/log/freeradius/radacct/%{Client-IP-Address}/detail-%Y%m%d - +/var/log/freeradius/radacct/192.168.1.10/detail-20100527 [detail] /var/log/freeradius/radacct/%{Client-IP-Address}/detail-%Y%m%d expands to +/var/log/freeradius/radacct/192.168.1.10/detail-20100527 [detail]expand: %t - Thu May 27 23:32:23 2010 ++[detail] returns ok ++[unix] returns ok [radutmp] expand: /var/log/freeradius/radutmp - /var/log/freeradius/radutmp [radutmp] expand: %{User-Name} - chrissql ++[radutmp] returns ok [attr_filter.accounting_response] expand: %{User-Name} - chrissql attr_filter: Matched entry DEFAULT at line 12 ++[attr_filter.accounting_response] returns updated Sending Accounting-Response of id 77 to 192.168.1.10 port 1646 Finished request 19. so, its drops into accouting section...it does detail unix radutmp attr_filter.accounting_response but where oh were was the SQL being called? hmm, from here is doesnt look like you are calling it. check the sites-enabled/* files (I dont know what virtual servers you have running or what you've called them) and please uncomment the 'sql' It comes after the lines that say: # # Log traffic to an SQL database. # # See Accounting queries in sql.conf alan - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Accounting into MySQL
Yes, both. 2009/5/14 EasyHorpak.com i...@easyhorpak.com: is it freeradius and freeradius-mysql ? I'm doing some more testing using the suggestions from Edvin and Ivan (almost like the two ivan's), and will follow up. I think its just my misunderstanding of what accounting actually does. ;) Cheers, - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: Accounting into MySQL
1. Using NTRadPING, should I get entries in my radacct table? Are you sending accounting packets? If not - why should freeradius pass any request to accounting parts. 2. I have uncommented the sql_log, and all the detail log sections, and in the accounting section, and uncommented the sql in the accounting section. What other changed are required assuming that I am already successfully authenticating from the radcheck table? Did you set the right user/password combination for access to mysql server? ( you probably did, else you would have seen errors while starting freeradius with -X switch ) Regards, E:S - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Accounting into MySQL
I have some basic questions to better understand accounting into SQL. 1. Using NTRadPING, should I get entries in my radacct table? No. Those are authentication, not accounting requests. 2. I have uncommented the sql_log, and all the detail log sections, and in the accounting section, and uncommented the sql in the accounting section. What other changed are required assuming that I am already successfully authenticating from the radcheck table? You don't need all that. Just sql in accounting. Detail writes to files. 3. Is there a more detailed how-to on the internet (I guess my google-foo is not as good as others) specifically on accounting in mysql? Configure sql connection details in sql.conf. Uncomment sql in accounting section of the default virtual server. That's it. 3.b) ... sepecifically on MAC authentication? http://wiki.freeradius.org/Mac-Auth Ivan Kalik Kalik Informatika ISP - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Accounting into MySQL
is it freeradius and freeradius-mysql ? - Original Message - From: Tim Dressel tjdres...@gmail.com To: freeradius-users@lists.freeradius.org Sent: Friday, May 15, 2009 3:53 AM Subject: Accounting into MySQL Hi folks, I have an Ubuntu server 8.04.2 LTS running (pretty bare bones). I installed freeradius and freeradius-sql from the repositories (MySQL 5, all dependiencies, etc). For configuration I followed the SQL HOWTO on the FreeRADIUS Wiki page. I am able to successfully use NTRadPing Test Utility against the radius server when configured to use SQL as the back end. I also followed the instructions on the Frontios website and a few other websites, and by searching this groups mail archive on how to configure accounting into SQL, but I'm not getting any accounting information. Running freeradius -X I see the queries going to the database for authentication, but I don't see any insert/select into statements indicating accounting information being written to the database. I have some basic questions to better understand accounting into SQL. 1. Using NTRadPING, should I get entries in my radacct table? 2. I have uncommented the sql_log, and all the detail log sections, and in the accounting section, and uncommented the sql in the accounting section. What other changed are required assuming that I am already successfully authenticating from the radcheck table? 3. Is there a more detailed how-to on the internet (I guess my google-foo is not as good as others) specifically on accounting in mysql? 3.b) ... sepecifically on MAC authentication? Thanks so much! Tim - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: Accounting in MySQL
No, I don´t have connection problems, I have actually my FreeRADIUS users in the radcheck and radreply tables, and working fine, but accounting do not. Germán P. Santillán Administrador de Redes Jefe del Dpto. Técnico DESETech Argentina S.A. San Martín 133 - CP: B8000FIC Bahía Blanca - Argentina Tel/Fax: +54 (291) 456-5642 [EMAIL PROTECTED] http://www.desetech.com.ar From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] .org] On Behalf Of satish patel Sent: Tuesday, January 23, 2007 4:56 AM To: FreeRadius users mailing list Subject: Re: Accounting in MySQL Dear First check your radiusd -X debug log and find mysql connectivity debug if there any problem regarding connection ??? then check radius.conf file there is accounting option and put sql key word in it and u can also find some document on my website http://geocities.com/satish_patel_2000_2000/ Satish Patel DESEtech - German P. Santillan [EMAIL PROTECTED] wrote: I actually have my Users DB in MySQL Server and my FreeRADIUS use the radcheck and radreply tables to read (SELECT) records, in my radiusd.conf I have... authorize { sql } accounting { sql } But I don´t hace records in radacct Table. What is the problem? Thanks in advance and sorry for my English Germán P. Santillán Administrador de Redes Jefe del Dpto. Técnico DESETech Argentina S.A. San Martín 133 - CP: B8000FIC Bahía Blanca - Argentina Tel/Fax: +54 (291) 456-5642 [EMAIL PROTECTED] http://www.desetech.com.ar - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html _ Heres a new way to find what you're looking for - Yahoo! http://us.rd.yahoo.com/mail/in/yanswers/*http:/in.answers.yahoo.com/ Answers _ Heres a new way to find what you're looking for - Yahoo! http://us.rd.yahoo.com/mail/in/yanswers/*http:/in.answers.yahoo.com/ Answers - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: Accounting in MySQL
Dear What NAS device are u using ? when user authenticate from radius thn nas send acct-start packet to radius if your NAS not sending start accounting packet to freeradius then radius not start accounting I have cisco Router for NAS aaa accounting start-stop radius --- commnad i m useing plz see my document there is some more help regrading NAS Satish Patel DESEtech - German P. Santillan [EMAIL PROTECTED] wrote: v\:* {behavior:url(#default#VML);} o\:* {behavior:url(#default#VML);} w\:* {behavior:url(#default#VML);} .shape {behavior:url(#default#VML);} No, I don´t have connection problems, I have actually my FreeRADIUS users in the radcheck and radreply tables, and working fine, but accounting do not. Germán P. Santillán Administrador de Redes Jefe del Dpto. Técnico DESETech Argentina S.A. San Martín 133 - CP: B8000FIC Bahía Blanca - Argentina Tel/Fax: +54 (291) 456-5642 [EMAIL PROTECTED] http://www.desetech.com.ar From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of satish patel Sent: Tuesday, January 23, 2007 4:56 AM To: FreeRadius users mailing list Subject: Re: Accounting in MySQL Dear First check your radiusd -X debug log and find mysql connectivity debug if there any problem regarding connection ??? then check radius.conf file there is accounting option and put sql key word in it and u can also find some document on my website http://geocities.com/satish_patel_2000_2000/ Satish Patel DESEtech - German P. Santillan [EMAIL PROTECTED] wrote: I actually have my Users DB in MySQL Server and my FreeRADIUS use the radcheck and radreply tables to read (SELECT) records, in my radiusd.conf I have... authorize { sql } accounting { sql } But I don´t hace records in radacct Table. What is the problem? Thanks in advance and sorry for my English Germán P. Santillán Administrador de Redes Jefe del Dpto. Técnico DESETech Argentina S.A. San Martín 133 - CP: B8000FIC Bahía Blanca - Argentina Tel/Fax: +54 (291) 456-5642 [EMAIL PROTECTED] http://www.desetech.com.ar - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - Heres a new way to find what you're looking for - Yahoo! Answers - Heres a new way to find what you're looking for - Yahoo! Answers - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - Heres a new way to find what you're looking for - Yahoo! Answers - Heres a new way to find what you're looking for - Yahoo! Answers - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Accounting in MySQL
Dear Find freeradius + Mysql document on my website http://geocities.com/satish_patel_2000_2000/ Satish Patel Alan DeKok [EMAIL PROTECTED] wrote: DESEtech - German P. Santillan wrote: But I don´t hace records in radacct Table. What is the problem? See the FAQ. Is the server receiving accounting packets? Alan DeKok. -- http://deployingradius.com - The web site of the book http://deployingradius.com/blog/ - The blog - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - Heres a new way to find what you're looking for - Yahoo! Answers - Heres a new way to find what you're looking for - Yahoo! Answers - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Accounting in MySQL
Dear First check your radiusd -X debug log and find mysql connectivity debug if there any problem regarding connection ??? then check radius.conf file there is accounting option and put sql key word in it and u can also find some document on my website http://geocities.com/satish_patel_2000_2000/ Satish Patel DESEtech - German P. Santillan [EMAIL PROTECTED] wrote: I actually have my Users DB in MySQL Server and my FreeRADIUS use the radcheck and radreply tables to read (SELECT) records, in my radiusd.conf I have... authorize { sql } accounting { sql } But I don´t hace records in radacct Table. What is the problem? Thanks in advance and sorry for my English Germán P. Santillán Administrador de Redes Jefe del Dpto. Técnico DESETech Argentina S.A. San Martín 133 - CP: B8000FIC Bahía Blanca - Argentina Tel/Fax: +54 (291) 456-5642 [EMAIL PROTECTED] http://www.desetech.com.ar - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - Heres a new way to find what you're looking for - Yahoo! Answers - Heres a new way to find what you're looking for - Yahoo! Answers - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Accounting in MySQL
DESEtech - German P. Santillan wrote: But I don´t hace records in radacct Table. What is the problem? See the FAQ. Is the server receiving accounting packets? Alan DeKok. -- http://deployingradius.com - The web site of the book http://deployingradius.com/blog/ - The blog - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: accounting to mysql database
I have no idea how this posting ending up on freeRADIUS!! Kirt -Original Message- From: Kirti S. Bajwa [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 31, 2004 10:46 AM To: '[EMAIL PROTECTED]' Subject: RE: accounting to mysql database Hello List: I have installed and tested freeRADIUS 0.9.3 MySQL 5.?.? (latest dev version). Now I want to install test freeRADIUS MySQL with InnoDB tables. I have researched GOOGLE MySQL web site for information on installing MySQL with InnoDB tables without success. I would appreciate if somebody direct me to: What version of MySQL do I need to install (with freeRADIUS) so I can use InnoDB tables? I understand that installing MySQL with freeRADIUS requires a specific MySQL version? Is it correct? I would be more than happy to read the procedure if someone point me to the chapter in MySQL Doc! I have not been able to find out myself. Is it possible that there is no special separate procedure and that is why I am missing it? Thanks in advance. Kirt - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: accounting to mysql database
This is a good suggestion. The MySQL schema should be changed, so that raddb is by default InnoDB. This is exactly the reason I have been using PostgreSQL for the last 6 or so years. Rather than using a different database for archived records just put the archived data into different tables. The SQL accounting system I built for Cistron Radius creates its tables as required automaticaly every month. I use a version of xlat that I wrote myself to define table and log names based on the timestamp of the event. I archive ancient detail tables, but keep summary tables for up to three years, because our customers want to check how their usage patterns change {I have no idea why, but when I changed it to a year I got lots of complaints}. This method works very well for my current system because no table holds more than one months data. When I move to FreeRadius in the near future I will have to come up with an inovative way to handle this problem. I have been helping the development team with the PostgreSQL driver while ensuring none of my entries break the MySQL driver. The main issue I come across with tables that span no more than one month, is that if a user is online past the end of the month, their time is accounted in the next month. I need to come up with a way to split the records that overlap the end of a month {or year} that does not damage the integrity of the record. I have been considering a termination code of Split or somthing like it indicating the record Continues into the next table, but I havn't yet decided how to indicate that a record is a Continuation from a previous table. It might be a good idea to have this type of functionality included with FreeRadius in some way. I am interested in other peoples opinions on this matter and would like to hear your ideas. Guy Jeff Warnica wrote: Not really a FreeRADIUS question, but anyway: For complex queries and/or queries with large result sets MySQL, using MyISAM tables, will produce a temporary table to fulfill the request. To ensure that it gets a consistent (temporary) table, MySQL locks the entire (real) table during this process, preventing writes. Reads can happen, but (I think) any reads that are complex enough to cause a temporary table will also queue up. InnoDB table types do not suffer from this limitation. Even if you can change table types it may be worth while to EXPLAIN some of your SELECT queries, and read the MySQL docs on general query optimizations. With about 5 minutes of effort, I managed to optimize someone else's queries from ~20s to ~5s. Still locks the table, but for significantly less time. On Thu, 2004-03-11 at 16:31, Mike Sturdee wrote: What might be causing this, or what could I do to resolve this? - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: accounting to mysql database
How about this scenario: (say it is March 1, SQL and col names from memory...) The 'live' table keeps up to two months (and a few hours) of data (January, February, March 1) Some time on March 1 you rotate out January CREATE TABLE jan_04 LIKE. INSERT INTO jan_04 SELECT * FROM radacct WHERE acctStartTime = 2004-02-01 UPDATE jan_04 SET acctStopTime = 2004-01-31 23:59:58, acctSessionTime = acctSessionTime - 1 WHERE acctStopTime = 2004-01-31 23:59:59 UPDATE jan_04 SET acctStopTime = 2004-01-31 23:59:59 WHERE acctStopTime 2004-01-31 23:59:59 UPDATE jan_04 SET acctSessionTime = UNIX_TIMESTAMP(TIMEDIFF(acctStopTime, acctStartTime) WHERE acctStopTime = 2004-01-31 23:59:59 DELETE FROM radacct WHERE acctStartTime = 2004-02-01 UPDATE radacct SET acctStarTime = 2004-02-01 00:00:00 WHERE acctStartTime 2004-02-01 00:00:00 UPDATE radacct SET acctSessionTime = UNIX_TIMESTAMP(TIMEDIFF(acctStopTime, acctStartTime) WHERE acctStarTime = 2004-02-01 00:00:00 You don't have to wait a month to do this, but that would pretty much guarantee that all of the to-be-rotated sessions have been closed. Any sessions that you see that end exactly at 23:59:59 on the last day of the month are ones that are have continuation into the next month. And yes, I recognize that this is the same as some y2k issues: 9/9/99, 12/31/99 meaning 'never' or 'infinite'.. But your only out a second. On Fri, 2004-03-12 at 13:37, Guy Fraser wrote: The main issue I come across with tables that span no more than one month, is that if a user is online past the end of the month, their time is accounted in the next month. I need to come up with a way to split the records that overlap the end of a month {or year} that does not damage the integrity of the record. I have been considering a termination code of Split or somthing like it indicating the record Continues into the next table, but I havn't yet decided how to indicate that a record is a Continuation from a previous table. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: accounting to mysql database
One solution may be to change your insert statements in sql.conf from Insert to Insert Delayed. you can read some here: http://www.mysql.com/doc/en/INSERT_DELAYED.html Post back to the list if it works. I'm no guru, so back up your sql.conf before making changes :) -Mike - Original Message - From: Mike Sturdee [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, March 11, 2004 3:31 PM Subject: accounting to mysql database I have radius set up to log accounting to a MySQL database. It currently holds a couple years worth of logging from several thousand users, so it's quite large.. Problem I'm having is if I do a select that will return a couple hundred entries, or anything other than the simplest of queries, radius will freeze until the query is sql complete, resulting in any auth requests essentially to be rejected. FreeRadius 0.9.3 MySQL Server 4.0.17 (MyISAM tables) What might be causing this, or what could I do to resolve this? Thanks, -Mike - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: accounting to mysql database
Not really a FreeRADIUS question, but anyway: For complex queries and/or queries with large result sets MySQL, using MyISAM tables, will produce a temporary table to fulfill the request. To ensure that it gets a consistent (temporary) table, MySQL locks the entire (real) table during this process, preventing writes. Reads can happen, but (I think) any reads that are complex enough to cause a temporary table will also queue up. InnoDB table types do not suffer from this limitation. Even if you can change table types it may be worth while to EXPLAIN some of your SELECT queries, and read the MySQL docs on general query optimizations. With about 5 minutes of effort, I managed to optimize someone else's queries from ~20s to ~5s. Still locks the table, but for significantly less time. On Thu, 2004-03-11 at 16:31, Mike Sturdee wrote: What might be causing this, or what could I do to resolve this? - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: accounting to mysql database
Mike Sturdee [EMAIL PROTECTED] wrote: I have radius set up to log accounting to a MySQL database. It currently holds a couple years worth of logging from several thousand users, so it's quite large.. Problem I'm having is if I do a select that will return a couple hundred entries, or anything other than the simplest of queries, radius will freeze until the query is sql complete, resulting in any auth requests essentially to be rejected. Have two accounting databases: One used by your RADIUS server for daily/weekly/monthly accounting for live sessions, and another for multi-year backups. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html