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='lcs...@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@open.com.au > http://www.open.com.au/mailman/listinfo/radiator -- Heikki Vatiainen <h...@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. _______________________________________________ radiator mailing list radiator@open.com.au http://www.open.com.au/mailman/listinfo/radiator
