invoking the interim-update alternate query when accounting session not found

[Alan DeKok]

On Jun 9, 2020, at 9:53 AM, Jeff Crowe <jeff at 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.