[RADIATOR] issues with mysql and deadlocks

[Heikki Vatiainen]

On 06/08/2011 01:28 PM, Alex Sharaz wrote:

> I'm running a hardware load balanced Radiator setup with multiple
> Radiator back end servers. With the exception of a handful of
> variables,each back end server runs an identical config file. Accounting
> records are processed through the use of 3 <Handlers>, one for start
> records, one for interim records and one for stop records.
> 
> The problem is that as any back end server can process an accounting
> record, I'm getting loads of deadlock messages appearing in my Radiator
> logs (see below). The example below is where I'm updating a mysql record
> using info  from a RADIUS interim packet.

Ok, so I guess the log entry is from AuthBy SQL where accounting is handled?

I found the following discussion:
http://stackoverflow.com/questions/2332768/how-to-avoid-mysql-deadlock-found-when-trying-to-get-lock-try-restarting-transa

The advice seems to be to check the statements so that queries are done
in such order that any locks that are needed are aquired in the same
order by all queries.

Also it was mentioned that client should retry the operation. Radiator
already retries SQLRetries times (see ref.pdf for more). The error is
logged every time it is noticed, so even if "do failed" happens the
operation is tried again unless SQLRetries is reached. In other words,
"do failed" is not the outcome of all retries, but it is logged for each
try that fails.

Please let us know if you find a way to rearrange the queries. It would
be interesting to hear from the results.

> The SessionDatabase config is
>
> <SessionDatabase SQL>
>    Identifier Hull_Wired_Dot1x_Alive
>    DBSource        dbi:mysql:radSupport:%{GlobalVar:accountingDb}:3306
>    DBUsername      XXXXX
>    DBAuth          YYYYYY
>    SQLRecoveryFile %L/wired-sqlfailures-%Y-%m-%d
> ##   Timeout 0
> ##   FailureBackoffTime 10
>    ReplaceQuery update RADONLINE set TIME_STAMP_STR=current_timestamp,
> servicetype='Dot1x-Alive',ACCTSESSTIME=%{Acct-Session-Time},ACCTINOCTETS=%{Acct-Input-Octets},ACCTOUTOCTETS=%{Acct-Output-Octets}
> \
>    where IDENTIFIER='%{NAS-IP-Address}' and
> ACCTSESSIONID='%{Acct-Session-Id}'
>    DeleteQuery
>    ClearNasQuery
> </SessionDatabase>
> 
> Wed Jun  8 10:23:38 2011 922998: ERR: do failed for 'update RADONLINE 
> set
> nasidentifier='Trapeze',identifier='150.237.252.3',calling_station_id='58-1F-AA-C2-EF-54',
> nasport='AP103/1',nasporttype='Wireless',servicetype='Trapeze-Stop',username='lcskad at hull.ac.uk',
> acctinoctets=4181,acctoutoctets=18311,authstatus='',time_stamp=1307525018,time_stamp_str=current_timestamp,
> ACCTSESSIONID='SESS-24877-30e39b-524912-773' where
> NASIDENTIFIER='Trapeze' and
> CALLING_STATION_ID=LOWER('58-1F-AA-C2-EF-54') and
> ACCTSESSIONID='SESS-24877-30e39b-524912-773'': Deadlock found when
> trying to get lock; try restarting transaction
> 
> I'm guessing I need to set auto commit somewhere for each of my session
> database definitions... .
> 
> Is this doable?
> 
> Rgds
> Alex
> 
> 
> 
> 
> Checked by  Hu-fw-yhman
> 
> 
> 
> _______________________________________________
> radiator mailing list
> radiator at open.com.au
> http://www.open.com.au/mailman/listinfo/radiator


-- 
Heikki Vatiainen <hvn at open.com.au>

Radiator: the most portable, flexible and configurable RADIUS server
anywhere. SQL, proxy, DBM, files, LDAP, NIS+, password, NT, Emerald,
Platypus, Freeside, TACACS+, PAM, external, Active Directory, EAP, TLS,
TTLS, PEAP, TNC, WiMAX, RSA, Vasco, Yubikey, MOTP, HOTP, TOTP,
DIAMETER etc. Full source on Unix, Windows, MacOSX, Solaris, VMS,
NetWare etc.