Re: Freeradius-Users Digest, Vol 106, Issue 26
Hello Alan, Arran, I am in post-crisis analysis so I know why my database had a problem and do not need to debug it. However I do not understand why the Stop records was failing to write in the detail files. Start records was well written in detail files because it is not send to the database. I did not change a lot of configuration, do you have any idea on where to search ? I dot not have an error code for rlm_sql in my logs only the followings: Wed Feb 5 13:53:07 2014 : Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0 Wed Feb 5 13:53:37 2014 : Error: WARNING: Unresponsive child for request 4700544, in component accounting module sql Wed Feb 5 13:53:38 2014 : Info: WARNING: Child is hung for request 4700544 in component accounting module sql. There no logs problem about detail files. I am not using redundant section. In site-enabled/default I have: accounting { detail radutmp sql attr_filter.accounting_response } Thanks. Aurélien Lafranchise Network Operations Manager Mob.: +33 (0)6 03 88 36 26 Fax: +33 (0)4 83 33 45 61 eMail: aurelien.lafranchise@mobiquithings.com Web: http://www.mobiquithings.com Le 11 févr. 2014 à 17:05, freeradius-users-request@lists.freeradius.org a écrit :
De: Alan DeKok <aland@deployingradius.com> Objet: Rép : Acounting problem with SQL and detail files Date: 11 février 2014 16:28:19 UTC+1 À: FreeRadius users mailing list <freeradius-users@lists.freeradius.org> Répondre à: FreeRadius users mailing list <freeradius-users@lists.freeradius.org>
Aurélien Lafranchise wrote:
We had a database connection problem (Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0). We were surprised that during this problem the accounting information that cannot be passed to the database was not written in the detail files.
Run the server in debugging mode to see why. You can use "raddebug" on a live server.
Also, if there are no DB handles... check the database. It's likely broken / slow / whatever.
The default configuration logs to the "detail" file. If you configure SQL, the default configuration logs to both SQL and "detail". If one fails, it still logs to the other.
So... what changes did you make to the default configuration?
Alan DeKok.
De: Arran Cudbard-Bell <a.cudbardb@freeradius.org> Objet: Rép : Acounting problem with SQL and detail files Date: 11 février 2014 17:05:20 UTC+1 À: FreeRadius users mailing list <freeradius-users@lists.freeradius.org> Répondre à: FreeRadius users mailing list <freeradius-users@lists.freeradius.org>
On 11 Feb 2014, at 14:47, Aurélien Lafranchise <aurelien.lafranchise@mobiquithings.com> wrote:
Hi All,
We noticed an issue on our FreeRADIUS Version 2.1.12 on a CentOS 6.3 kernel 2.6.32-279.2.1.el6.x86_64.
Our FreeRADIUS is connected to a database for the accounting queries. It is also configured to save the same accounting information in the detail files.
We had a database connection problem (Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0). We were surprised that during this problem the accounting information that cannot be passed to the database was not written in the detail files.
You were using something like
redundant { sql detail }
Right?
What error code was rlm_sql returning when it had no handles left?
You may want to consider using v3.0.x, the connection pool code, and reconnection logic is a significant improvement over the module specific code in v2.x.x.
-Arran
Arran Cudbard-Bell <a.cudbardb@freeradius.org> FreeRADIUS Development Team
FD31 3077 42EC 7FCD 32FE 5EE2 56CF 27F9 30A8 CAA2
With the right subject ! Aurélien Lafranchise Network Operations Manager Mob.: +33 (0)6 03 88 36 26 Fax: +33 (0)4 83 33 45 61 eMail: aurelien.lafranchise@mobiquithings.com Web: http://www.mobiquithings.com Le 11 févr. 2014 à 18:29, Aurélien Lafranchise <aurelien.lafranchise@mobiquithings.com> a écrit :
Hello Alan, Arran,
I am in post-crisis analysis so I know why my database had a problem and do not need to debug it.
However I do not understand why the Stop records was failing to write in the detail files. Start records was well written in detail files because it is not send to the database.
I did not change a lot of configuration, do you have any idea on where to search ?
I dot not have an error code for rlm_sql in my logs only the followings: Wed Feb 5 13:53:07 2014 : Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0 Wed Feb 5 13:53:37 2014 : Error: WARNING: Unresponsive child for request 4700544, in component accounting module sql Wed Feb 5 13:53:38 2014 : Info: WARNING: Child is hung for request 4700544 in component accounting module sql. There no logs problem about detail files.
I am not using redundant section. In site-enabled/default I have: accounting { detail radutmp sql attr_filter.accounting_response }
Thanks.
Aurélien Lafranchise Network Operations Manager Mob.: +33 (0)6 03 88 36 26 Fax: +33 (0)4 83 33 45 61 eMail: aurelien.lafranchise@mobiquithings.com Web: http://www.mobiquithings.com
<MBQT_signature.png>
Le 11 févr. 2014 à 17:05, freeradius-users-request@lists.freeradius.org a écrit :
De: Alan DeKok <aland@deployingradius.com> Objet: Rép : Acounting problem with SQL and detail files Date: 11 février 2014 16:28:19 UTC+1 À: FreeRadius users mailing list <freeradius-users@lists.freeradius.org> Répondre à: FreeRadius users mailing list <freeradius-users@lists.freeradius.org>
Aurélien Lafranchise wrote:
We had a database connection problem (Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0). We were surprised that during this problem the accounting information that cannot be passed to the database was not written in the detail files.
Run the server in debugging mode to see why. You can use "raddebug" on a live server.
Also, if there are no DB handles... check the database. It's likely broken / slow / whatever.
The default configuration logs to the "detail" file. If you configure SQL, the default configuration logs to both SQL and "detail". If one fails, it still logs to the other.
So... what changes did you make to the default configuration?
Alan DeKok.
De: Arran Cudbard-Bell <a.cudbardb@freeradius.org> Objet: Rép : Acounting problem with SQL and detail files Date: 11 février 2014 17:05:20 UTC+1 À: FreeRadius users mailing list <freeradius-users@lists.freeradius.org> Répondre à: FreeRadius users mailing list <freeradius-users@lists.freeradius.org>
On 11 Feb 2014, at 14:47, Aurélien Lafranchise <aurelien.lafranchise@mobiquithings.com> wrote:
Hi All,
We noticed an issue on our FreeRADIUS Version 2.1.12 on a CentOS 6.3 kernel 2.6.32-279.2.1.el6.x86_64.
Our FreeRADIUS is connected to a database for the accounting queries. It is also configured to save the same accounting information in the detail files.
We had a database connection problem (Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0). We were surprised that during this problem the accounting information that cannot be passed to the database was not written in the detail files.
You were using something like
redundant { sql detail }
Right?
What error code was rlm_sql returning when it had no handles left?
You may want to consider using v3.0.x, the connection pool code, and reconnection logic is a significant improvement over the module specific code in v2.x.x.
-Arran
Arran Cudbard-Bell <a.cudbardb@freeradius.org> FreeRADIUS Development Team
FD31 3077 42EC 7FCD 32FE 5EE2 56CF 27F9 30A8 CAA2
Aurélien Lafranchise wrote:
However I do not understand why the Stop records was failing to write in the detail files. Start records was well written in detail files because it is not send to the database.
I'm not sure what that means. The config you posted shows that start and stop messages are handled the same.
I did not change a lot of configuration, do you have any idea on where to search ?
Keep the database up. If the database goes down, the entire server can stop working. Maybe that's what happened here. Alan DeKok.
Hello, Sorry I mixed up some stuff. Start and Stop records are handle the same on my configuration. During the database problem start records went properly in detail files but not stop records. Why? In the previous email, you tell me that there is no impact on detail files if the database goes down and you tell me now that it can be have an impact. I am a bit lost. Thanks Aurélien Lafranchise Network Operations Manager Mob.: +33 (0)6 03 88 36 26 Fax: +33 (0)4 83 33 45 61 eMail: aurelien.lafranchise@mobiquithings.com Web: http://www.mobiquithings.com Le 11 févr. 2014 à 18:57, Alan DeKok <aland@deployingradius.com> a écrit :
Aurélien Lafranchise wrote:
However I do not understand why the Stop records was failing to write in the detail files. Start records was well written in detail files because it is not send to the database.
I'm not sure what that means. The config you posted shows that start and stop messages are handled the same.
I did not change a lot of configuration, do you have any idea on where to search ?
Keep the database up. If the database goes down, the entire server can stop working. Maybe that's what happened here.
Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Aurélien Lafranchise wrote:
Sorry I mixed up some stuff. Start and Stop records are handle the same on my configuration. During the database problem start records went properly in detail files but not stop records. Why?
Perhaps you could try reading my messages. I've been trying to answer this.
In the previous email, you tell me that there is no impact on detail files if the database goes down and you tell me now that it can be have an impact. I am a bit lost.
You're looking for inconsistencies where there is none. You're not reading my messages and taking them at face value. You're interpreting them through your ideas as to how the server works. The modules are independent. If "detail" is listed before "sql", it will ALWAYS log to "detail", even if there's a problem with "sql". However, if the SQL server is *blocked* rather than *down*, it will also block FreeRADIUS. It will block *all* threads in FreeRADIUS, and the server will not be able to process *anything*. My answer to these issues is always the same. If you have problems with SQL, go fix SQL. Stop trying to "fix" FreeRADIUS when there's nothing wrong with it. If the dashboard on your car says "no fuel", you re-fuel it. You don't claim that the dashboard is broken.. and replace the dashboard. The same applies here. SQL is broken. No amount of posting to this list about FreeRADIUS will fix your SQL server. Fix the real problem. Alan DeKok.
Thanks for your answer. I am looking for redundancy, if SQL does not work I can count on detail files. If the SQL problem blocks the whole FreeRADIUS server I think there is a problem of design (might be resolved in later version?). You may answer "work-as-design", but could you confirm? Why there is no protection against blocked SQL server to let FreeRADIUS process queries ? Considering that, would you advise another configuration? Thanks AL Le 12 févr. 2014 à 14:54, Alan DeKok <aland@deployingradius.com> a écrit :
Aurélien Lafranchise wrote:
Sorry I mixed up some stuff. Start and Stop records are handle the same on my configuration. During the database problem start records went properly in detail files but not stop records. Why?
Perhaps you could try reading my messages. I've been trying to answer this.
In the previous email, you tell me that there is no impact on detail files if the database goes down and you tell me now that it can be have an impact. I am a bit lost.
You're looking for inconsistencies where there is none. You're not reading my messages and taking them at face value. You're interpreting them through your ideas as to how the server works.
The modules are independent. If "detail" is listed before "sql", it will ALWAYS log to "detail", even if there's a problem with "sql". However, if the SQL server is *blocked* rather than *down*, it will also block FreeRADIUS. It will block *all* threads in FreeRADIUS, and the server will not be able to process *anything*.
My answer to these issues is always the same. If you have problems with SQL, go fix SQL. Stop trying to "fix" FreeRADIUS when there's nothing wrong with it.
If the dashboard on your car says "no fuel", you re-fuel it. You don't claim that the dashboard is broken.. and replace the dashboard.
The same applies here. SQL is broken. No amount of posting to this list about FreeRADIUS will fix your SQL server. Fix the real problem.
Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Aurélien Lafranchise wrote:
I am looking for redundancy, if SQL does not work I can count on detail files.
The problem is that "not working" is VERY different from "working and doing bad things".
If the SQL problem blocks the whole FreeRADIUS server I think there is a problem of design
Don't be rude. Since you don't understand how the system works, you shouldn't be pointing out deficiencies in the design. How do *you* propose to make the server "work properly" when the SQL server takes forever to respond to a query? You have the following problem: - FreeRADIUS uses SQL to store data - FreeRADIUS receives 1000 accounting packets/s - FreeRADIUS has 100 threads - each SQL INSERT takes 2000 seconds to finish So... the first 100 packets received are inserted into SQL. And 2000 seconds later, the threads are free to process another request. During that time, the NAS sent another 2000000 requests. Maybe you try to solve the problem by adding more threads. Can your system handle 2000000 threads? No? You should start blaming the OS! Stupid Linux people can't even write a real OS... The problem is that there is no SQL API to say "stop after 2 seconds" There is no API to stop a long-running SQL query. If you need to write data to SQL and your SQL server doesn't work, you're *stuck*. There is NOTHING MORE YOU CAN DO.
You may answer "work-as-design", but could you confirm?
You're asking the wrong (and rude) question. We didn't design the server to block. We designed the server to use existing APIs. So... we're bound by the limitations of those APIs.
Why there is no protection against blocked SQL server to let FreeRADIUS process queries ?
Because the SQL libraries we use don't allow that.
Considering that, would you advise another configuration?
Fix your SQL server. I think I mentioned that. Could you explain in little words why you're blaming FreeRADIUS, when I told you very specifically that the SQL server is to blame? I *could* give you a configuration which may work around some of these issues. I did, in fact... see raddb/sites-available/. But that won't solve the problem. It will just hide it for a short while. And 20 minutes later, the system will go down because the detail files filled the disk, and you'll be back here. Blaming FreeRADIUS. I find this attitude *very* annoying. You're unwilling to fix your broken system. Instead, you're insulting me and saying I should do the impossible. Because you don't understand how things work. That's annoying. Alan DeKok.
On 12 Feb 2014, at 14:17, Aurélien Lafranchise <aurelien.lafranchise@mobiquithings.com> wrote:
Thanks for your answer.
I am looking for redundancy, if SQL does not work I can count on detail files. If the SQL problem blocks the whole FreeRADIUS server I think there is a problem of design (might be resolved in later version?).
Honestly the best solution is to run another instance of FreeRADIUS which just writes to detail files, and use a front end load balancer (you can use FreeRADIUS for this also).
You may answer "work-as-design", but could you confirm? Why there is no protection against blocked SQL server to let FreeRADIUS process queries ?
FreeRADIUS doesn't block, the client library blocks, or more the system call the client library made blocks. From the point where the worker calls a function in the client library, there is no way to force that call to return cleanly. Even if you kill the worker thread, the connection structures that were passed into the library may not be in a consistent state, so when you come to use it, or free it, you may get random memory errors which cause the server process to exit.
Considering that, would you advise another configuration?
If you don't want to run multiple instances: Determine the point where running additional parallel queries does not increase overall SQL qp/s. Set the upper bound of the SQL connection pool to be that limit. Set the upper bound of the thread pool to be higher than that limit. If during normal operation you find excessive numbers of packets being written to the detail file, get a faster database. -Arran Arran Cudbard-Bell <a.cudbardb@freeradius.org> FreeRADIUS Development Team FD31 3077 42EC 7FCD 32FE 5EE2 56CF 27F9 30A8 CAA2
Hi,
I am looking for redundancy, if SQL does not work I can count on detail files. If the SQL problem blocks the whole FreeRADIUS server I think there is a problem of design (might be resolved in later version?).
use buffered-sql instead of plain SQL - then it will store to a file and SQL doesnt do anything inline. alan
Hi,
I am looking for redundancy, if SQL does not work I can count on detail files. If the SQL problem blocks the whole FreeRADIUS server I think there is a problem of design (might be resolved in later version?).
use buffered-sql instead of plain SQL - then it will store to a file and SQL doesnt do anything inline.
thanks for your help. the problem is that I need to check counters in nearly real-time in the database. AL
A.L.M.Buxey@lboro.ac.uk wrote:
use buffered-sql instead of plain SQL - then it will store to a file and SQL doesnt do anything inline.
If the SQL server blocks, you'll still need two processes. And then when the detail file writer fills the disk, he'll be back complaining that FreeRADIUS doesn't properly rotate it's logs. Alan DeKok.
Please Mr. DeKok do not be negative, others users are trying to help. I can understand that is your "baby" but don't take it personally! Le 12 févr. 2014 à 16:39, Alan DeKok <aland@deployingradius.com> a écrit :
A.L.M.Buxey@lboro.ac.uk wrote:
use buffered-sql instead of plain SQL - then it will store to a file and SQL doesnt do anything inline.
If the SQL server blocks, you'll still need two processes.
And then when the detail file writer fills the disk, he'll be back complaining that FreeRADIUS doesn't properly rotate it's logs.
Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On 12 Feb 2014, at 16:14, Aurélien Lafranchise <aurelien.lafranchise@mobiquithings.com> wrote:
Please Mr. DeKok do not be negative, others users are trying to help. I can understand that is your "baby" but don't take it personally!
You guys really do have 'condescending asshole' down to a fine art :) I wonder sometimes if it's unintentional, if it's just culture clash, but I really don't think it is. Arran Cudbard-Bell <a.cudbardb@freeradius.org> FreeRADIUS Development Team FD31 3077 42EC 7FCD 32FE 5EE2 56CF 27F9 30A8 CAA2
Hi,
Please Mr. DeKok do not be negative, others users are trying to help. I can understand that is your "baby" but don't take it personally!
You guys really do have 'condescending asshole' down to a fine art :)
I wonder sometimes if it's unintentional, if it's just culture clash, but I really don't think it is.
I confirmed for this one it was not the case for the others email.
Aurélien Lafranchise wrote:
I confirmed for this one it was not the case for the others email.
You will stop complaining, or you will be unsubscribed from the list, and permanently banned. This is your only warning. You asked questions and argued with the answers. That's rude. You blamed FreeRADIUS for problems with the SQL server. That's rude. You implied were were idiots because the design of the server was wrong. That's rude. You then got upset when you were told your behavior was rude. And now you're still complaining. This list is for people who want to solve problems. They ask questions, follow instructions, and generally try to get things done. If you want to complain that we're mean for trying to help you, you will be banned. You're not here to solve problems, so you shouldn't be on this list. Alan DeKok.
On 12 Feb 2014, at 15:22, A.L.M.Buxey@lboro.ac.uk wrote:
Hi,
I am looking for redundancy, if SQL does not work I can count on detail files. If the SQL problem blocks the whole FreeRADIUS server I think there is a problem of design (might be resolved in later version?).
use buffered-sql instead of plain SQL - then it will store to a file and SQL doesnt do anything inline.
Yes, though it's a mite slow, so if you have even moderate numbers of accounting requests it gets overloaded rather quickly. It really needs to be fixed, I took a look at it but after reading up on POSIX locking... Wow that's one truly awfully, completely broken set of functions. There is essentially, no way to do byte range locks on a file, and have the lock states reflected internally within the process. There's a nice article about it here: http://apenwarr.ca/log/?m=201012#13 Arran Cudbard-Bell <a.cudbardb@freeradius.org> FreeRADIUS Development Team FD31 3077 42EC 7FCD 32FE 5EE2 56CF 27F9 30A8 CAA2
Arran Cudbard-Bell wrote:
Yes, though it's a mite slow, so if you have even moderate numbers of accounting requests it gets overloaded rather quickly.
That will be fixed when I get time to re-write the detail file reader. Alan DeKok.
First of all I totally understand all your statements, I do not want to be rude or annoying, the SQL server is fixed since a long time and I am not looking for your help on my database but only to understand how FreeRADIUS works and interact in order to find a workaround. From what you said I understood from you both that in case of a blocked SQL databases the librairies does not allow FreeRADIUS to just consider the server as blocked and apply a timeout. Thank you for the idea of having two separates instances of FreeRADIUS one for detail files and one for SQL queries. FreeRADIUS is a great tool but as all tools that is sometimes not easy to use and users can ask help. It can also have problem: sometimes bugs sometimes not. I consider my problem as "work as design" because of the librairies not because of FreeRADIUS. I have now a better understanding on what happens. AL
Aurélien Lafranchise wrote:
First of all I totally understand all your statements, I do not want to be rude or annoying, the SQL server is fixed since a long time and I am not looking for your help on my database but only to understand how FreeRADIUS works and interact in order to find a workaround.
Then ask questions which indicate that you want to learn. Don't ask questions which insinuate that we're idiots.
From what you said I understood from you both that in case of a blocked SQL databases the librairies does not allow FreeRADIUS to just consider the server as blocked and apply a timeout.
Thank you for the idea of having two separates instances of FreeRADIUS one for detail files and one for SQL queries.
It will help, but only for a very short time. Your database is broken. All of this arguing could have been avoided if you just fixed the database. I've been doing this for about 20 years now. I know a bit about the subject. You weren't attempting to educate yourself or to fix the problem, you were attempting to blame FreeRADIUS for everything, and tell me that I intentionally designed broken software. That's why you got a long response. Alan DeKok.
Ridiculous! Le 12 févr. 2014 à 16:47, Alan DeKok <aland@deployingradius.com> a écrit :
Aurélien Lafranchise wrote:
First of all I totally understand all your statements, I do not want to be rude or annoying, the SQL server is fixed since a long time and I am not looking for your help on my database but only to understand how FreeRADIUS works and interact in order to find a workaround.
Then ask questions which indicate that you want to learn. Don't ask questions which insinuate that we're idiots.
From what you said I understood from you both that in case of a blocked SQL databases the librairies does not allow FreeRADIUS to just consider the server as blocked and apply a timeout.
Thank you for the idea of having two separates instances of FreeRADIUS one for detail files and one for SQL queries.
It will help, but only for a very short time. Your database is broken.
All of this arguing could have been avoided if you just fixed the database. I've been doing this for about 20 years now. I know a bit about the subject. You weren't attempting to educate yourself or to fix the problem, you were attempting to blame FreeRADIUS for everything, and tell me that I intentionally designed broken software.
That's why you got a long response.
Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
participants (4)
-
A.L.M.Buxey＠lboro.ac.uk -
Alan DeKok -
Arran Cudbard-Bell -
Aurélien Lafranchise