buffered-sql detail
The type detail server appears to be broken again in stable git. The server logs fine I put the sql module in the main accounting block of the virtual server. if I move the sql block back inside the buffered-sql detail server it moves the detail file to -> .work but then never does anything with it, even worst, it deletes the .work file without sending anything. I had this exact same config with a previous version of freeradius and it worked just fine. Unfortunately I don't remember what version that was. bsddev# radiusd -v radiusd: FreeRADIUS Version 2.1.6, for host amd64-portbld-freebsd7.2, built on Aug 4 2009 at 15:32:18 server buffered-sql { listen { type = detail filename = ${radacctdir}/sqlrelay load_factor = 10 } preacct { files } accounting { sql } } [sqlrelay] Processing sql_log_postauth [sqlrelay-teksavvy] expand: INSERT into radius.dbo.radpostauth (username, pass, reply, authdate) values ('%{User-Name}', '%{User- Password:-Chap-Password}' [sqlrelay] expand: /var/log/radacct/sqlrelay -> /var/log/radacct/ sqlrelay ++[sqlrelay-teksavvy] returns ok } # server teksavvy Sending Access-Accept of id 231 to 127.0.0.1 port 57073 Finished request 0. Going to the next request Waking up in 0.2 seconds. Polling for detail file /var/log/radacct/sqlrelay detail_recv: Renaming /var/log/radacct/sqlrelay -> /var/log/radacct/ sqlrelay.work --> Here I'm expecting the sql module to wake up and send the log, but it never does. Waking up in 1.1 seconds. Polling for detail file /var/log/radacct/sqlrelay
Gabriel Blanchard wrote:
The type detail server appears to be broken again in stable git. The server logs fine I put the sql module in the main accounting block of the virtual server.
if I move the sql block back inside the buffered-sql detail server it moves the detail file to -> .work but then never does anything with it, even worst, it deletes the .work file without sending anything.
I had this exact same config with a previous version of freeradius and it worked just fine. Unfortunately I don't remember what version that was.
bsddev# radiusd -v radiusd: FreeRADIUS Version 2.1.6, for host amd64-portbld-freebsd7.2, built on Aug 4 2009 at 15:32:18
That's not the current "stable" in git. It should be 2.1.6, as the version string was changed in May. This problem has come up before, and I'm not sure why it's happening. But I believe it's fixed in 2.1.7. See http://git.freeradius.org/pre/ for a pre-release tar file of 2.1.7. Alan DeKok.
On 5-Aug-09, at 4:24 AM, Alan DeKok wrote:
Gabriel Blanchard wrote:
bsddev# radiusd -v radiusd: FreeRADIUS Version 2.1.6, for host amd64-portbld-
freebsd7.2,
built on Aug 4 2009 at 15:32:18
That's not the current "stable" in git. It should be 2.1.6, as the version string was changed in May.
?? It says --->Version 2.1.6<---
This problem has come up before, and I'm not sure why it's happening. But I believe it's fixed in 2.1.7.
See http://git.freeradius.org/pre/ for a pre-release tar file of 2.1.7.
Alright, I'll try and let you know if that worked. -Gabe
On 5-Aug-09, at 8:10 AM, Gabriel Blanchard wrote:
On 5-Aug-09, at 4:24 AM, Alan DeKok wrote:
This problem has come up before, and I'm not sure why it's happening. But I believe it's fixed in 2.1.7.
See http://git.freeradius.org/pre/ for a pre-release tar file of 2.1.7.
Alright, I'll try and let you know if that worked.
Just tried it. bsddev# radiusd -v radiusd: FreeRADIUS Version 2.1.7, for host x86_64-unknown-freebsd7.2, built on Aug 5 2009 at 08:40:59 And getting the exact same result.
On 5-Aug-09, at 9:37 AM, Alan DeKok wrote:
Hm... that's weird. And I can't seem to reproduce the problem here.
Ugh.
I'm just looking through the detail.c file and it seems to be having a problem here /* * We're done reading the file, but we didn't read * anything. Clean up, and don't return anything. */ if (!data->vps) { data->state = STATE_HEADER; if (feof(data->fp)) goto cleanup; return 0; } is the detail (sql relay) file expected to be in a key value pair format? Right now my detail file is simply a bunch of SQL statements from the sql_log module. So it obviously doesn't have any vps so it just jumps right ahead to cleanup without sending anything to my sql module. I know this used to work in a previous 2.x version of Freeradius.
On 5-Aug-09, at 9:48 AM, Gabriel Blanchard wrote:
is the detail (sql relay) file expected to be in a key value pair format? Right now my detail file is simply a bunch of SQL statements from the sql_log module. So it obviously doesn't have any vps so it just jumps right ahead to cleanup without sending anything to my sql module. I know this used to work in a previous 2.x version of Freeradius.
I think I may have answered my own question. I need to use the detail module right? not the sql_log module... -Gabriel
Gabriel Blanchard wrote:
is the detail (sql relay) file expected to be in a key value pair format?
Uh... you have completely confused two different topics. The "detail" file is NOT an "sql relay" file.
Right now my detail file is simply a bunch of SQL statements from the sql_log module.
So... run scripts/radsqlrelay. That's what it's for. This is documented at the top of the "sql_log" module configuration.
So it obviously doesn't have any vps so it just jumps right ahead to cleanup without sending anything to my sql module. I know this used to work in a previous 2.x version of Freeradius.
No, it didn't. Feeding SQL statements to the "detail" file reader NEVER worked. Alan DeKok.
On 5-Aug-09, at 10:05 AM, Alan DeKok wrote:
So it obviously doesn't have any vps so it just jumps right ahead to cleanup without sending anything to my sql module. I know this used to work in a previous 2.x version of Freeradius.
No, it didn't. Feeding SQL statements to the "detail" file reader NEVER worked.
Alright well it appears to be working now. I did need to use 2.17 as you pointed out. 2.1.6 did not log with the exact same config.
On 5-Aug-09, at 1:18 PM, Gabriel Blanchard wrote:
Alright well it appears to be working now. I did need to use 2.17 as you pointed out. 2.1.6 did not log with the exact same config.
I may have found a problem with the detail server. While I was rebooting the SQL server that Freeradius connects to, it stopped responding to any new radius query. I thought that the point of the detail/buffered-sql server was to decouple the accounting logging from everything else. My auth config does not use SQL (local users file) so it should not lock. Thu Aug 6 10:42:24 2009 : Error: rlm_sql_unixodbc: SQL down 08S01 [unixODBC][FreeTDS][SQL Server]Write to SQL Server failed. Thu Aug 6 10:42:24 2009 : Info: rlm_sql_unixodbc: rlm_sql will attempt to reconnect Thu Aug 6 10:42:24 2009 : Debug: rlm_sql (tsisql02): Attempting to connect rlm_sql_unixodbc #12 <--- 1 minute delay, during that time radius was not responding. Thu Aug 6 10:43:41 2009 : Error: rlm_sql_unixodbc: SQL down 08S01 [unixODBC][FreeTDS][SQL Server]Server is unavailable or does not exist. Thu Aug 6 10:43:41 2009 : Error: rlm_sql_unixodbc: Connection failed Thu Aug 6 10:43:41 2009 : Error: rlm_sql (tsisql02): Failed to connect DB handle #12 Thu Aug 6 10:43:41 2009 : Error: rlm_sql (tsisql02): reconnect failed, database down? Thu Aug 6 10:43:41 2009 : Error: [tsisql02] Couldn't update SQL accounting ALIVE record - Thu Aug 6 10:43:41 2009 : Debug: rlm_sql (tsisql02): Released sql socket id: 12 Thu Aug 6 10:43:41 2009 : Info: ++[tsisql02] returns fail I'm assuming that sql_init_socket may need to have some kind of timeout. Or at least initializing a socket shouldn't lock up the entire server.
So am I doing something completely stupid and nobody is telling me or something? Every time the SQL server is down or slower than the usual, even though I'm using the detail server configured like below, the entire freeradius daemon blocks. It stops responding to any new query until the SQL server comes back up. server buffered-sql { listen { type = detail filename = ${radacctdir}/sqlrelay load_factor = 40 poll_interval = 5 } preacct { preprocess acct_unique files } accounting { tsisql02 } } On 6-Aug-09, at 10:59 AM, Gabriel Blanchard wrote:
On 5-Aug-09, at 1:18 PM, Gabriel Blanchard wrote:
Alright well it appears to be working now. I did need to use 2.17 as you pointed out. 2.1.6 did not log with the exact same config.
I may have found a problem with the detail server. While I was rebooting the SQL server that Freeradius connects to, it stopped responding to any new radius query. I thought that the point of the detail/buffered-sql server was to decouple the accounting logging from everything else. My auth config does not use SQL (local users file) so it should not lock.
Thu Aug 6 10:42:24 2009 : Error: rlm_sql_unixodbc: SQL down 08S01 [unixODBC][FreeTDS][SQL Server]Write to SQL Server failed. Thu Aug 6 10:42:24 2009 : Info: rlm_sql_unixodbc: rlm_sql will attempt to reconnect Thu Aug 6 10:42:24 2009 : Debug: rlm_sql (tsisql02): Attempting to connect rlm_sql_unixodbc #12 <--- 1 minute delay, during that time radius was not responding. Thu Aug 6 10:43:41 2009 : Error: rlm_sql_unixodbc: SQL down 08S01 [unixODBC][FreeTDS][SQL Server]Server is unavailable or does not exist. Thu Aug 6 10:43:41 2009 : Error: rlm_sql_unixodbc: Connection failed Thu Aug 6 10:43:41 2009 : Error: rlm_sql (tsisql02): Failed to connect DB handle #12 Thu Aug 6 10:43:41 2009 : Error: rlm_sql (tsisql02): reconnect failed, database down? Thu Aug 6 10:43:41 2009 : Error: [tsisql02] Couldn't update SQL accounting ALIVE record - Thu Aug 6 10:43:41 2009 : Debug: rlm_sql (tsisql02): Released sql socket id: 12 Thu Aug 6 10:43:41 2009 : Info: ++[tsisql02] returns fail
I'm assuming that sql_init_socket may need to have some kind of timeout. Or at least initializing a socket shouldn't lock up the entire server.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/devel.html
Hi,
So am I doing something completely stupid and nobody is telling me or something? Every time the SQL server is down or slower than the usual, even though I'm using the detail server configured like below, the entire freeradius daemon blocks. It stops responding to any new query until the SQL server comes back up.
which version of FreeRADIUS and are you sure you arent using SQL in any of the other virtual servers (eg for logging, NAS table etc?) alan
On 10-Aug-09, at 12:36 PM, Alan Buxey wrote:
Hi,
So am I doing something completely stupid and nobody is telling me or something? Every time the SQL server is down or slower than the usual, even though I'm using the detail server configured like below, the entire freeradius daemon blocks. It stops responding to any new query until the SQL server comes back up.
which version of FreeRADIUS
radiusd: FreeRADIUS Version 2.1.7, for host x86_64-unknown-freebsd7.2, built on Aug 5 2009 at 12:56:04
and are you sure you arent using SQL in any of the other virtual servers (eg for logging, NAS table etc?)
Yes I'm sure. I can also tell since if I remove my buffered-sql server from sites-enabled everything is fine (besides not logging). On another note, I'm using the unixodbc driver. Not sure it that has any impact. -Gabe
On 10-Aug-09, at 1:08 PM, Alan DeKok wrote:
impact.
If the Unixodbc driver blocks, then the server blocks.
Blame the Unixodbc driver. It doesn't happen for anything else.
So my SQL back end is running MS SQL (yes I know it's horrible). So I just tried using sybase but it will only compile on a i386 arch and the freetds implementation was removed a little while ago as recommended by the freetds developers themselves. So from the looks of it I'm SOL. What was the problem with the rlm_sql_freetds module anyway? Maybe I can spend the time to fix it. -Gabe
Gabriel Blanchard wrote:
So my SQL back end is running MS SQL (yes I know it's horrible). So I just tried using sybase but it will only compile on a i386 arch and the freetds implementation was removed a little while ago as recommended by the freetds developers themselves. So from the looks of it I'm SOL.
It's not even the FreeRADIUS driver. It's the Unixodbc driver, maintained by the Unixodbc people. We say "connect please" They say "wait forever..." And we're stuck.
What was the problem with the rlm_sql_freetds module anyway? Maybe I can spend the time to fix it.
The FreeTDS people said "don't use that API". Alan DeKok.
On 10-Aug-09, at 2:37 PM, Alan DeKok wrote:
It's not even the FreeRADIUS driver. It's the Unixodbc driver, maintained by the Unixodbc people.
We say "connect please"
They say "wait forever..."
And we're stuck.
I understand. I'm not blaming anyone, just trying to get to the bottom of it. Does iodbc have the same problem?
The FreeTDS people said "don't use that API".
I'm just curious as to why they said not to use it. Many other projects use the API, php for example uses it. -Gabe
On 10-Aug-09, at 2:43 PM, Gabriel Blanchard wrote:
I understand. I'm not blaming anyone, just trying to get to the bottom of it. Does iodbc have the same problem?
The FreeTDS people said "don't use that API".
I'm just curious as to why they said not to use it. Many other projects use the API, php for example uses it.
mmm never mind, I just looked up the old code for rlm_sql_freetds.c and does appear to be using an old API. So I'm assuming that if I code it against the new API and submit a patch it would be welcome? -Gabe
Hi,
mmm never mind, I just looked up the old code for rlm_sql_freetds.c and does appear to be using an old API. So I'm assuming that if I code it against the new API and submit a patch it would be welcome?
i'd say that sounds about right. the issue was the API used was deprecated alan
On 11-Aug-09, at 3:46 AM, Alan DeKok wrote:
Gabriel Blanchard wrote:
mmm never mind, I just looked up the old code for rlm_sql_freetds.c and does appear to be using an old API. So I'm assuming that if I code it against the new API and submit a patch it would be welcome?
Yes.
Well I just spent the entire day coding this module and I wasted about 90% of that time on FreeTDS getting stuck for ever it the query times out (I purposely made them timeout to test it). After looking at the source code of FreeTDS it turns out that the dbsettime() function that is supposed to set a timeout value for the dbsqlexec() function doesn't even work! So I figured out a work around, by using the dbsetinterrupt function to set a call back. It calls whatever pointer of a function defined by us about once a second. This function can then be used to cancel the query after a certain amount of time. So it turns out that the problem isn't really with unixODBC but rather FreeTDS itself...in a sense anyway. I also found that a lot of other projects that use FreeTDS have this exact same problem. All and all, the module I just coded works and is able to INSERT or UPDATE the database. I haven't implemented SELECTs yet. -Gabe
Attached is the rlm_sql_freetds module I coded like I said yesterday. It's a diff from stable git. It still does NOT support SELECT queries. I may add them later on, but I have no use for them as I use the module solely for inserting data. It appears to be stable (so far...) I've been using it on one of my 3 radius servers for about 24h and it's been stable. -Gabe
Gabriel Blanchard wrote:
Attached is the rlm_sql_freetds module I coded like I said yesterday. It's a diff from stable git. It still does NOT support SELECT queries. I may add them later on, but I have no use for them as I use the module solely for inserting data.
It appears to be stable (so far...) I've been using it on one of my 3 radius servers for about 24h and it's been stable.
Is there a Makefile for it? Alan DeKok.
Alan DeKok wrote:
Gabriel Blanchard wrote:
On another note, I'm using the unixodbc driver. Not sure it that has any impact.
If the Unixodbc driver blocks, then the server blocks.
Blame the Unixodbc driver. It doesn't happen for anything else.
Running the server with -X (debug) forces the server to run in single threaded mode. I'd expect most database drivers to exhibit the same behaviour in single threaded mode. When running in threaded mode, only a single thread should block if the database driver blocks. So please verify that you see the entire server blocking when running in threaded. If you need detailed output, you can increase the debug level using radmin. Arran
On 10-Aug-09, at 4:19 PM, Arran Cudbard-Bell wrote:
Running the server with -X (debug) forces the server to run in single threaded mode. I'd expect most database drivers to exhibit the same behaviour in single threaded mode.
Well I didn't think about that...
When running in threaded mode, only a single thread should block if the database driver blocks. So please verify that you see the entire server blocking when running in threaded. If you need detailed output, you can increase the debug level using radmin.
Even so, when something happens to the SQL server all threads will eventually lock. Causing the same problem. Maybe a low timeout would be a "sort of horrible work around" -Gabe
When running in threaded mode, only a single thread should block if the database driver blocks. So please verify that you see the entire server blocking when running in threaded. If you need detailed output, you can increase the debug level using radmin.
Even so, when something happens to the SQL server all threads will eventually lock. Nope. Detail file reader only allows one packet to be in flight at any one time.
If you want parallel insertions you'd have to run multiple queues, and figure out a way of distributing accounting requests between them... I wonder if the expressions module supports the modulo operator. -Arran
Hi,
Even so, when something happens to the SQL server all threads will eventually lock.
should do, yes, as each thread deal with its own things
Causing the same problem. Maybe a low timeout would be a "sort of horrible work around"
run lots of threads and have a low timeout - then the stuck ones will get unstuck after some time but there would be enough living threads to deal with other things. an alternative would be to run a completely seperate instance of FreeRADIUS just for the buffered-sql functions so the main authen/author server runs completely non interupted. alan
participants (4)
-
Alan Buxey -
Alan DeKok -
Arran Cudbard-Bell -
Gabriel Blanchard