Re: rlm_sql: NUMERIC VALUE OUT OF RANGE error
I too am experiencing this issue with FreeRadius 2.1.10 and Postgres.
/rlm_sql_postgresql: query affected rows = 0
[sql] expand: %{NAS-Port} - 2153775120
[sql] expand: %{Acct-Delay-Time} - 0
[sql] expand: %{Acct-Session-Time} - 301
[sql] expand: %{Acct-Input-Gigawords} - 0
[sql] expand: %{Acct-Input-Octets} - 14189
[sql] expand: %{Acct-Output-Gigawords} - 0
[sql] expand: %{Acct-Output-Octets} - 31036
[sql] expand: INSERT INTO radacct (AcctSessionId, AcctUniqueId,
UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime,
AcctSessionTime, AcctAuthentic, AcctInputOctets, AcctOutputOctets,
CalledStationId, CallingStationId, ServiceType, FramedProtocol,
FramedIPAddress, XAscendSessionSvrKey) VALUES('%{Acct-Session-Id}',
'%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', NULLIF('%{Realm}', ''),
'%{NAS-IP-Address}', %{%{NAS-Port}:-NULL}::integer, '%{NAS-Port-Type}',
('%S'::timestamp - '%{%{Acct-Delay-Time}:-0}'::interval -
'%{%{Acct-Session-Time}:-0}'::interval), '%{Acct-Session-Time}',
'%{Acct-Authentic}', (('%{%{Acct-Input-Gigawords}:-0}'::bigint 32) +
'%{%{Acct-Input-Octets}:-0}'::bigint),
(('%{%{Acct-Output-Gigawords}:-0}'::bigint 32) +
'%{%{Acct-Output-Octets}:-0}'::bigint), '%{Called-Station-Id}',
'%{Calling-Station-Id}', '%{Service-Type}', '%{Framed-Protocol}',
NULLIF('%{Framed-IP-Address}', '')::inet, '%{X-Ascend-Session-Svr-Key}') -
INSERT INTO radacct
rlm_sql_postgresql: Status: PGRES_FATAL_ERROR
rlm_sql_postgresql: Error integer out of range
rlm_sql_postgresql: Postgresql Fatal Error: [22003: NUMERIC VALUE OUT OF
RANGE] Occurred!!
[sql] Couldn't insert SQL accounting ALIVE record - ERROR: integer out of
range
rlm_sql (sql): Released sql socket id: 2
/
I cannot figure out how to modify the query that is executed for this, I
modified /etc/freeradius/sql.conf with addtional section:
/ accounting_update_query_alt = INSERT into ${acct_table1} \
(AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress,
NASPortId, NASPortType, AcctStartTime, \
AcctSessionTime, AcctAuthentic, AcctInputOctets,
AcctOutputOctets, CalledStationId, CallingStationId, \
ServiceType, FramedProtocol, FramedIPAddress,
XAscendSessionSvrKey) \
values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}',
'%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', \
('%{NAS-Port}'::bigint, '%{NAS-Port-Type}'),
('%S'::timestamp - '%{Acct-Delay-Time:-0}'::interval -
'%{Acct-Session-Time:-0}'::interval), \
'%{Acct-Session-Time}', '%{Acct-Authentic}', \
(('%{Acct-Input-Gigawords:-0}'::bigint 32) +
'%{Acct-Input-Octets:-0}'::bigint), \
(('%{Acct-Output-Gigawords:-0}'::bigint 32) +
'%{Acct-Output-Octets:-0}'::bigint), '%{Called-Station-Id}', \
'%{Calling-Station-Id}', '%{Service-Type}',
'%{Framed-Protocol}', \
NULLIF('%{Framed-IP-Address}', '')::inet,
'%{X-Ascend-Session-Svr-Key}')
/
as well as start/stop sections but it had no effect on the query run by
FreeRadius. How am I able to fix this issue ?
Thanks,
Andrew
--
View this message in context:
http://freeradius.1045715.n5.nabble.com/rlm-sql-NUMERIC-VALUE-OUT-OF-RANGE-error-tp2776043p4559678.html
Sent from the FreeRadius - User mailing list archive at Nabble.com.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: rlm_sql: NUMERIC VALUE OUT OF RANGE error
On Thu, Jul 7, 2011 at 10:09 AM, ahthrift andyonf...@gmail.com wrote:
I too am experiencing this issue with FreeRadius 2.1.10 and Postgres.
I cannot figure out how to modify the query that is executed for this, I
Did you read the file sql.conf?
##
#
# Configuration for the SQL module
#
# The database schemas and queries are located in subdirectories:
#
# sql/DB/schema.sql Schema
# sql/DB/dialup.conf Basic dialup (including policy) queries
# sql/DB/counter.conf counter
# sql/DB/ippool.conf IP Pools in SQL
# sql/DB/ippool.sql schema for IP pools.
#
# Where DB is mysql, mssql, oracle, or postgresql.
#
so if you're using the default setup it should be in sql/postgresql/dialup.conf
modified /etc/freeradius/sql.conf with addtional section:
/ accounting_update_query_alt = INSERT into ${acct_table1} \
Look at dialup.conf, and change the part that matches the offending
query (it should be accounting_start_query)
--
Fajar
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: rlm_sql: NUMERIC VALUE OUT OF RANGE error
Thanks Fajar,
I actually figured this out 10 minutes after posting to the list, blue
text on black terminal window :(
Thanks for your response.
Regards,
Andrew
On 7/07/2011 3:52 p.m., Fajar A. Nugraha wrote:
On Thu, Jul 7, 2011 at 10:09 AM, ahthriftandyonf...@gmail.com wrote:
I too am experiencing this issue with FreeRadius 2.1.10 and Postgres.
I cannot figure out how to modify the query that is executed for this, I
Did you read the file sql.conf?
##
#
# Configuration for the SQL module
#
# The database schemas and queries are located in subdirectories:
#
# sql/DB/schema.sql Schema
# sql/DB/dialup.conf Basic dialup (including policy) queries
# sql/DB/counter.conf counter
# sql/DB/ippool.conf IP Pools in SQL
# sql/DB/ippool.sql schema for IP pools.
#
# Where DB is mysql, mssql, oracle, or postgresql.
#
so if you're using the default setup it should be in sql/postgresql/dialup.conf
modified /etc/freeradius/sql.conf with addtional section:
/ accounting_update_query_alt = INSERT into ${acct_table1} \
Look at dialup.conf, and change the part that matches the offending
query (it should be accounting_start_query)
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
rlm_sql: NUMERIC VALUE OUT OF RANGE error
Freeradius 2.1.0.
I have a NAS which sends a NAS-Port-Id attribute in the range
2147483648..2164260863. PostgreSQL doesn't like the query Freeradius
performs. It's choking when trying to insert for instance
'2163214239::integer' into the radacct table.
$ select 2163214239::integer;
ERROR: integer out of range
Example:
INSERT INTO radacct (AcctSessionId, AcctUniqueId, UserName, Realm,
NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctStopTime,
AcctSessionTime, AcctAuthentic, ConnectInfo_stop, AcctInputOctets,
AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause,
ServiceType, FramedProtocol, FramedIPAddress, AcctStopDelay)
values('80f0079f', '5c9f0b7076dcc9c0', 'username', NULLIF('', ''),
'1.2.3.4', 2163214239::integer, 'Wireless-802.11', ('2008-09-26
09:52:52'::timestamp - '1'::interval - '3382'::interval), ('2008-09-26
09:52:52'::timestamp - '1'::interval), NULLIF('3382', '')::bigint, '', '',
(('0'::bigint 32) + '57743'::bigint), (('0'::bigint 32) +
'294709'::bigint), 'hotspot', 'XX:XX:XX:XX:XX:XX', 'Lost-Service', '',
'', NULLIF('192.168.12.94', '')::inet, 0)
rlm_sql_postgresql: Status: PGRES_FATAL_ERROR
rlm_sql_postgresql: Error integer out of range
rlm_sql_postgresql: Postgresql Fatal Error: [22003: NUMERIC VALUE OUT OF RANGE]
Occurred!!
[sql] Couldn't insert SQL accounting STOP record - ERROR: integer out of range
rlm_sql (sql): Released sql socket id: 3
++[sql] returns fail
Quick fixes...?
--
Vegard Svanberg [EMAIL PROTECTED] [EMAIL PROTECTED] (EFnet)]
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: rlm_sql: NUMERIC VALUE OUT OF RANGE error
Vegard Svanberg wrote: I have a NAS which sends a NAS-Port-Id attribute in the range 2147483648..2164260863. PostgreSQL doesn't like the query Freeradius performs. It's choking when trying to insert for instance '2163214239::integer' into the radacct table. $ select 2163214239::integer; ERROR: integer out of range It's treating the number as a signed 32-bit integer, and the number is greater than 2^31. And the NASPortId field in the default schema is VARCHAR, not integer. Hmm... the default queries add a ::integer to the NAS-Port-Id. Why? Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: rlm_sql: NUMERIC VALUE OUT OF RANGE error
* Alan DeKok [EMAIL PROTECTED] [2008-09-26 11:07]: $ select 2163214239::integer; ERROR: integer out of range It's treating the number as a signed 32-bit integer, and the number is greater than 2^31. And the NASPortId field in the default schema is VARCHAR, not integer. Hmm... the default queries add a ::integer to the NAS-Port-Id. Why? I didn't realize I could change the queries until now, so I just removed the integer cast. Works now. Thanks. -- Vegard Svanberg [EMAIL PROTECTED] [EMAIL PROTECTED] (EFnet)] - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: rlm_sql: NUMERIC VALUE OUT OF RANGE error
Alan DeKok wrote:
Vegard Svanberg wrote:
I have a NAS which sends a NAS-Port-Id attribute in the range
2147483648..2164260863. PostgreSQL doesn't like the query Freeradius
performs. It's choking when trying to insert for instance
'2163214239::integer' into the radacct table.
$ select 2163214239::integer;
ERROR: integer out of range
It's treating the number as a signed 32-bit integer, and the number is
greater than 2^31.
And the NASPortId field in the default schema is VARCHAR, not integer.
On that note - at least in recent versions of postgres, there is no
performance benefit to using varchar(n) over text. I would argue the
default postgres schema should probably use text over varchar, as
I've been bitten by some packets with fields being larger than their
schema types (but still legal) e.g.
NAS-Port-Id = GigabitEthernet1/0/1 i.e. 20 bytes
...with NASPortID being a varchar(15)
Hmm... the default queries add a ::integer to the NAS-Port-Id. Why?
Confusingly, the default postgres accounting queries define a column
called NASPortID then put the value of the NAS-Port (not
NAS-Port-ID) into it. I guess this is why it uses the integer cast.
I tend to think it would be more helpful to store both i.e.
insert into radacct (.. nasportid, nasport, ...) values (
..
%{%{NAS-Port}:-NULL}::integer,
NULLIF('%{NAS-Port-Id}',''),
..
)
To be honest, I don't know why a lot of the typecasts are there;
postgres will enforce types as necessary. Some of them e.g. the ones to
::interval are needed of course.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
