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 <vegard@svanberg.no> [*Takapa@IRC (EFnet)]
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.
* Alan DeKok <aland@deployingradius.com> [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 <vegard@svanberg.no> [*Takapa@IRC (EFnet)]
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.
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-e... Sent from the FreeRadius - User mailing list archive at Nabble.com.
On Thu, Jul 7, 2011 at 10:09 AM, ahthrift <andyonfire@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
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, ahthrift<andyonfire@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)
participants (6)
-
ahthrift -
Alan DeKok -
Andrew Thrift
-
Fajar A. Nugraha -
Phil Mayers -
Vegard Svanberg