Re: FreeTDS affected_rows_count Problem, Virtual Servers - nas_table Server Column Problem

classic Classic list List threaded Threaded
3 messages Options
| Threaded
Open this post in threaded view
|

Re: FreeTDS affected_rows_count Problem, Virtual Servers - nas_table Server Column Problem

Users mailing list
Hello,

Today i will post 2 problems together that i have already sent by 2 different emails but still i cannot resolve without taking side ways.
I will give earlier email links but both of the problems will be stated completely here again not to make reader to consult other sources of the problem statement:


Note: Though i overcome the problems here i thought you may need to know about them to make already the best radius server on the globe better.

1- FreeTDS affected_rows_count Problem earlier email:
    http://freeradius.1045715.n5.nabble.com/mssql-freetds-interim-update-problem-td5757217.html

2- Virtual Servers - nas_table Server Column Problem earlier email
    http://freeradius.1045715.n5.nabble.com/Virtual-Servers-amp-nas-table-Server-Column-Problem-td5758312.html


Problem Statement

1-  FreeTDS affected_rows_count Problem
    Since rlm_sql_freetds does not get the true affected rows count it continues to insert new records in radacct table for the same session in interim updates.
I attached debug output (debug_freetds.txt).
Some lines from debug that will summarize the problem are as follows:

(3) WISPSQL: Executing query: UPDATE radacct SET AcctInterval = DATEDIFF(second, AcctUpdateTime, GETDATE()), AcctUpdateTime  = GETDATE(), AcctStopTime = NULL, AcctSessionTime = 121, FramedIPAddress = '192.168.10.255', AcctInputOctets = convert(bigint, '0' * POWER(2.0, 32)) | '95438', AcctOutputOctets = convert(bigint, '0' * POWER(2.0, 32)) | '45977' WHERE AcctUniqueId = '69913b8866eac834270ab589229ef6ab'
(3) WISPSQL: SQL query returned: success
(3) WISPSQL: -1 record(s) updated
(3) WISPSQL: Trying next query...
(3) WISPSQL: EXPAND IF NOT EXISTS(SELECT 1 FROM radacct WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}') INSERT INTO radacct (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctUpdateTime, AcctStopTime, AcctSessionTime, AcctAuthentic, ConnectInfoStart, ConnectInfoStop, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress) VALUES('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{%{Packet-Src-IP-Address}:-%{NAS-IP-Address}}', '%{%{NAS-Port-Id}:-%{NAS-Port}}', '%{NAS-Port-Type}', DATEADD(second, -1 * (%{%{Acct-Session-Time}:-0}), GETDATE()), GETDATE(), NULL, %{%{Acct-Session-Time}:-NULL}, '%{Acct-Authentic}', '%{Connect-Info}', NULL, convert(bigint, '%{%{Acct-Input-Gigawords}:-0}' * POWER(2.0, 32)) | '%{%{Acct-Input-Octets}:-0}', convert(bigint, '%{%{Acct-Output-Gigawords}:-0}' * POWER(2.0, 32)) | '%{%{Acct-Output-Octets}:-0}', '%{Called-Station-Id}', '%{Calling-Station-Id}', NULL, '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}')
(3) WISPSQL:    --> IF NOT EXISTS(SELECT 1 FROM radacct WHERE AcctUniqueId = '69913b8866eac834270ab589229ef6ab') INSERT INTO radacct (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctUpdateTime, AcctStopTime, AcctSessionTime, AcctAuthentic, ConnectInfoStart, ConnectInfoStop, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress) VALUES('81600002', '69913b8866eac834270ab589229ef6ab', 'test@orbit', '', '192.168.1.200', 'LAN', 'Ethernet', DATEADD(second, -1 * (121), GETDATE()), GETDATE(), NULL, 121, 'RADIUS', '', NULL, convert(bigint, '0' * POWER(2.0, 32)) | '95438', convert(bigint, '0' * POWER(2.0, 32)) | '45977', 'PPPoE', 'D8:C4:97:15:C1:F8', NULL, 'Framed-User', 'PPP', '192.168.10.255')
(3) WISPSQL: Executing query: IF NOT EXISTS(SELECT 1 FROM radacct WHERE AcctUniqueId = '69913b8866eac834270ab589229ef6ab') INSERT INTO radacct (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctUpdateTime, AcctStopTime, AcctSessionTime, AcctAuthentic, ConnectInfoStart, ConnectInfoStop, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress) VALUES('81600002', '69913b8866eac834270ab589229ef6ab', 'test@orbit', '', '192.168.1.200', 'LAN', 'Ethernet', DATEADD(second, -1 * (121), GETDATE()), GETDATE(), NULL, 121, 'RADIUS', '', NULL, convert(bigint, '0' * POWER(2.0, 32)) | '95438', convert(bigint, '0' * POWER(2.0, 32)) | '45977', 'PPPoE', 'D8:C4:97:15:C1:F8', NULL, 'Framed-User', 'PPP', '192.168.10.255')
(3) WISPSQL: SQL query returned: success
(3) WISPSQL: -1 record(s) updated
(3) WISPSQL: No additional queries configured
rlm_sql (WISPSQL): Released connection (8)
(3)     [WISPSQL] = noop
(3)     update control {
(3)       EXPAND %{User-Name}
(3)          --> test@orbit
(3)       SQL-User-Name set to 'test@orbit'
rlm_sql (WISPSQL): Reserved connection (7)

Here update runs and returns success
    (3) WISPSQL: SQL query returned: success
    (3) WISPSQL: -1 record(s) updated

but since it does not return true updated rows count (-1 record(s) updated) so it tries to insert new record
    (3) WISPSQL: Executing query: IF NOT EXISTS(SELECT 1 FROM radacct WHERE AcctUniqueId = '69913b8866eac834270ab589229ef6ab') INSERT INTO radacct (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctUpdateTime, AcctStopTime, AcctSessionTime, AcctAuthentic, ConnectInfoStart, ConnectInfoStop, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress) VALUES('81600002', '69913b8866eac834270ab589229ef6ab', 'test@orbit', '', '192.168.1.200', 'LAN', 'Ethernet', DATEADD(second, -1 * (121), GETDATE()), GETDATE(), NULL, 121, 'RADIUS', '', NULL, convert(bigint, '0' * POWER(2.0, 32)) | '95438', convert(bigint, '0' * POWER(2.0, 32)) | '45977', 'PPPoE', 'D8:C4:97:15:C1:F8', NULL, 'Framed-User', 'PPP', '192.168.10.255')
    (3) WISPSQL: SQL query returned: success
    (3) WISPSQL: -1 record(s) updated

To disable insert i check if there is already a record for the same session
    IF NOT EXISTS(SELECT 1 FROM radacct WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}')

To check true affected rows count i added an unlang statement in accounting section, it does not do anything but just to check if sql server returns true count:
    update control {
        Tmp-Integer-0 = "%{WISPSQL:SELECT @@ROWCOUNT}"
    }

    (3)       EXPAND %{WISPSQL:SELECT @@ROWCOUNT}
    (3)          --> 1
    (3)       Tmp-Integer-0 = 1
as seen it returns count as 1.

I also checked affected rows count by freetds utility tsql and saw that it returns true values with select @@ROWCOUNT statement.

I downloaded freeradius source code from gitHub to see if i can find anything about the way it gets affected rows count:

the 2 methods in rlm_sql_freetds.c file related are:

static int sql_num_rows(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
{
    rlm_sql_freetds_conn_t *conn = handle->conn;
    CS_INT    num;

    if (ct_res_info(conn->command, CS_ROW_COUNT, &num, CS_UNUSED, NULL) != CS_SUCCEED) {
        ERROR("rlm_sql_freetds: error retrieving row count");


        return RLM_SQL_ERROR;
    }

    return num;
}

static int sql_affected_rows(rlm_sql_handle_t *handle, rlm_sql_config_t *config)
{
    return sql_num_rows(handle, config);
}

the second being a recall of the first.

Here ct_res_info method (cannot find its definition) returns affected_rows count, may be its a freetds library method or calling a method from library, and seems that that freetds library method does not do its job.
Can't it be possible to use a direct call to sql server to get @@ROWSCOUNT by a select statment to get affected rows count?


2- Virtual Servers - nas_table Server Column Problem
    I want to use vertiaul servers and add specific clients to specific servers. And to do that i set Server columns of nas_table records with virtual server name for wich i want to attach.
I encountered 2 problems with this method:
    1 - Multiple clients cannot be added to the same virtual server, if tried get error like:
        rlm_sql (sql): Processing generate_sql_clients
        rlm_sql (sql): Executing select query: SELECT Id, IPAddress, ShortName, 'other', Secret, ISNULL(Server, '') FROM nas WHERE IsActive = 1
        rlm_sql (sql): Adding client 10.5.50.1 (home) to default clients list
        rlm_sql (10.5.50.1): Client "home" (sql) added
        rlm_sql (sql): Adding client 192.168.1.210 (citylight) to default clients list
        Failed to associate clients with virtual server default
        Failed to add client, possible duplicate?
    
        Although there is no other client with same Name (IPAddress) it cannot add second client.

    2- If it gets an Access-Request it errors with "from unknown client" even if the client is added and same client works if it is made global, some lines from debug:
        rlm_sql (WISPSQL): Adding client 192.168.1.200 (orbitalanya) to WISPServer clients list
        rlm_sql (192.168.1.200): Client "orbitalanya" (WISPSQL) added

        Error with Access-Request:
        Ignoring request to auth address * port 2812 bound to server WISPServer from unknown client 192.168.1.200 port 49390 proto udp

I attached debug output as file debug_VirtualServers.txt

I got an answer from Mr. Alan DeKok on Wed, Jul 29 at 2:59 PM for my previous email for the same problem.
    >>  I've pushed a fix.  You can download the latest version of v3.0.x from gthub. Alan DeKok
I have downloaded source https://github.com/FreeRADIUS/freeradius-server/archive/v3.0.x.zip and installed 3.0.22 but still get same error. Is it wrong version or do i do something wrong with configuration?

Thanks.
    
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

debug_freetds.txt (127K) Download Attachment
debug_VirtualServers.txt (71K) Download Attachment
| Threaded
Open this post in threaded view
|

Re: FreeTDS affected_rows_count Problem, Virtual Servers - nas_table Server Column Problem

Alan DeKok-2
On Aug 6, 2020, at 6:55 AM, Ali Arslan via Freeradius-Users <[hidden email]> wrote:
>
> Today i will post 2 problems together that i have already sent by 2 different emails but still i cannot resolve without taking side ways.
> I will give earlier email links but both of the problems will be stated completely here again not to make reader to consult other sources of the problem statement:
...
> 1-  FreeTDS affected_rows_count Problem
>     Since rlm_sql_freetds does not get the true affected rows count it continues to insert new records in radacct table for the same session in interim updates.
> I attached debug output (debug_freetds.txt).
> Some lines from debug that will summarize the problem are as follows:

  The short summary is to file a bug upstream.  i.e. with the people who wrote the FreeTDS client library.

  There's not a lot we can do to work around bugs in libraries that we use.  And unless the library is OpenSSL, we generally *don't* work around bugs like this.  Doing that would turn FreeRADIUS into a horrible collection of patches and work-arounds.

> To check true affected rows count i added an unlang statement in accounting section, it does not do anything but just to check if sql server returns true count:
>     update control {
>         Tmp-Integer-0 = "%{WISPSQL:SELECT @@ROWCOUNT}"
>     }
>
>     (3)       EXPAND %{WISPSQL:SELECT @@ROWCOUNT}
>     (3)          --> 1
>     (3)       Tmp-Integer-0 = 1
> as seen it returns count as 1.
>
> I also checked affected rows count by freetds utility tsql and saw that it returns true values with select @@ROWCOUNT statement.
>
> I downloaded freeradius source code from gitHub to see if i can find anything about the way it gets affected rows count:
>
> the 2 methods in rlm_sql_freetds.c file related are:
>
> static int sql_num_rows(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)

  You can modify this function in your source free to run the "SELECT @@ROWCOUNT" query, and return that value.

  I'm inclined to *not* make that change in FreeRADIUS.  Because it's a bug in someone else's library.

> 2- Virtual Servers - nas_table Server Column Problem
>     I want to use vertiaul servers and add specific clients to specific servers. And to do that i set Server columns of nas_table records with virtual server name for wich i want to attach.
> I encountered 2 problems with this method:
>     1 - Multiple clients cannot be added to the same virtual server, if tried get error like:
>         rlm_sql (sql): Processing generate_sql_clients
>         rlm_sql (sql): Executing select query: SELECT Id, IPAddress, ShortName, 'other', Secret, ISNULL(Server, '') FROM nas WHERE IsActive = 1
>         rlm_sql (sql): Adding client 10.5.50.1 (home) to default clients list
>         rlm_sql (10.5.50.1): Client "home" (sql) added
>         rlm_sql (sql): Adding client 192.168.1.210 (citylight) to default clients list
>         Failed to associate clients with virtual server default
>         Failed to add client, possible duplicate?
>    
>         Although there is no other client with same Name (IPAddress) it cannot add second client.

  Hmm... that should work.  I'll see if I can do some tests.

>     2- If it gets an Access-Request it errors with "from unknown client" even if the client is added and same client works if it is made global, some lines from debug:
>         rlm_sql (WISPSQL): Adding client 192.168.1.200 (orbitalanya) to WISPServer clients list
>         rlm_sql (192.168.1.200): Client "orbitalanya" (WISPSQL) added
>
>         Error with Access-Request:
>         Ignoring request to auth address * port 2812 bound to server WISPServer from unknown client 192.168.1.200 port 49390 proto udp

  That should work, too.

> I attached debug output as file debug_VirtualServers.txt
>
> I got an answer from Mr. Alan DeKok on Wed, Jul 29 at 2:59 PM for my previous email for the same problem.
>     >>  I've pushed a fix.  You can download the latest version of v3.0.x from gthub. Alan DeKok
> I have downloaded source https://github.com/FreeRADIUS/freeradius-server/archive/v3.0.x.zip and installed 3.0.22 but still get same error. Is it wrong version or do i do something wrong with configuration?

  I'll see if I can reproduce it here.

  Alan DeKok.


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
| Threaded
Open this post in threaded view
|

Re: FreeTDS affected_rows_count Problem, Virtual Servers - nas_table Server Column Problem

Alan DeKok-2
On Aug 6, 2020, at 8:16 AM, Alan DeKok <[hidden email]> wrote:

>>    1 - Multiple clients cannot be added to the same virtual server, if tried get error like:
>>        rlm_sql (sql): Processing generate_sql_clients
>>        rlm_sql (sql): Executing select query: SELECT Id, IPAddress, ShortName, 'other', Secret, ISNULL(Server, '') FROM nas WHERE IsActive = 1
>>        rlm_sql (sql): Adding client 10.5.50.1 (home) to default clients list
>>        rlm_sql (10.5.50.1): Client "home" (sql) added
>>        rlm_sql (sql): Adding client 192.168.1.210 (citylight) to default clients list
>>        Failed to associate clients with virtual server default
>>        Failed to add client, possible duplicate?
>>
>>        Although there is no other client with same Name (IPAddress) it cannot add second client.
>
>  Hmm... that should work.  I'll see if I can do some tests.

  I've checked, and it works with the latest version from v3.0.x.

>>    2- If it gets an Access-Request it errors with "from unknown client" even if the client is added and same client works if it is made global, some lines from debug:
>>        rlm_sql (WISPSQL): Adding client 192.168.1.200 (orbitalanya) to WISPServer clients list
>>        rlm_sql (192.168.1.200): Client "orbitalanya" (WISPSQL) added
>>
>>        Error with Access-Request:
>>        Ignoring request to auth address * port 2812 bound to server WISPServer from unknown client 192.168.1.200 port 49390 proto udp
>
>  That should work, too.

  It works with the latest code from v3.0.x.

  Alan DeKok.


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html