On Jun 9, 2020, at 9:53 AM, Jeff Crowe <jeff@wtccommunications.ca> wrote:
I have a situation where one of my radius accounting servers was offline while users connected, now the interim accounting updates result in errors because it cannot find a matching accounting session (obviously). Is there a way to have freeradius invoke the alternate query in the queries.conf file to create the missing record?
The server automatically fails over from one query to another.
From: /etc/freeradius/mods-config/sql/main/mysql/queries.conf
Yes, we know that. There is no need to post configuration files to the list. We've seen them before. We have access to them.
(0) sql: EXPAND %{tolower:type.%{Acct-Status-Type}.query} (0) sql: --> type.interim-update.query (0) sql: Using query template 'query' (0) sql: EXPAND %{%{Stripped-User-Name}:-%{%{User-Name}:-DEFAULT}} (0) sql: --> cxusername (0) sql: SQL-User-Name set to 'cxusername' (0) sql: EXPAND UPDATE radacct SET acctupdatetime = (@acctupdatetime_old:=acctupdatetime), acctupdatetime = FROM_UNIXTIME(%{integer:Event-Timestamp}), acctinterval = %{integer:Event-Timestamp} - UNIX_TIMESTAMP(@acctupdatetime_old), framedipaddress = '%{Framed-IP-Address}', acctsessiontime = %{%{Acct-Session-Time}:-NULL}, acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}', acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}' WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}' (0) sql: --> UPDATE radacct SET acctupdatetime = (@acctupdatetime_old:=acctupdatetime), acctupdatetime = FROM_UNIXTIME(1591709700), acctinterval = 1591709700 - UNIX_TIMESTAMP(@acctupdatetime_old), framedipaddress = '1.2.3.4', acctsessiontime = 5751579, acctinputoctets = '7' << 32 | '1232081388', acctoutputoctets = '100' << 32 | '862013286' WHERE AcctUniqueId = 'f4e2b974a457942478b811ae16067bc6' (0) sql: Executing query: UPDATE radacct SET acctupdatetime = (@acctupdatetime_old:=acctupdatetime), acctupdatetime = FROM_UNIXTIME(1591709700), acctinterval = 1591709700 - UNIX_TIMESTAMP(@acctupdatetime_old), framedipaddress = '1.2.3.4', acctsessiontime = 5751579, acctinputoctets = '7' << 32 | '1232081388', acctoutputoctets = '100' << 32 | '862013286' WHERE AcctUniqueId = 'f4e2b974a457942478b811ae16067bc6' (0) sql: ERROR: rlm_sql_mysql: ERROR 1054 (Unknown column 'acct_session_id' in 'where clause'): 42S22 (0) sql: SQL query returned: server error
That's why it's not failing over. Because there's no such column "acct_session_id". it's not clear where that's coming from, as the default configuration does NOT use "acct_session_id". You can check that by searching the debug output. So... what did you change from the defaults? Did you edit the schemas? Edit the table definitions? Add stored procedures with a typo of "acct_session_id" ? i.e. the default schema works. The default queries work. If they don't work, something changed on your system. Alan DeKok.