mysql issue

[andy]

Ok,

I have found the issue.
Thanks for the answers on persisnt connects also.

The issue:

mysql NDB cluster backend
Freeradius 1.1.1 from FreeBSD ports

MySQL clusters require an auto-incrementing field to be the primary key.
Within the default table definitions for freeradius, which I have used, in the table radius.radius_radacct there is an 
auto-incrementing field that is not defined as a primary key.

In order to port this table into an NDB cluster engine type of table, I changed the field "RadAcctId" from KEY to 
"PRIMARY KEY". 

This appears to generate the following errors:

rlm_sql_mysql: MYSQL check_error: 2006, returning SQL_DOWN
rlm_sql_mysql: MYSQL check_error: 2006, returning SQL_DOWN
rlm_sql_mysql: MYSQL check_error: 2006, returning SQL_DOWN
rlm_sql_mysql: MYSQL check_error: 2006, returning SQL_DOWN


Note that these errors have debug output in between the errors.

running the following I can see a bit more clearly that all these errors relate to RADACCT table:

grep SQL_DOWN radiusdebug.log -B2 | more
rlm_sql (primary): Reserving sql socket id: 93
rlm_sql_mysql: query:  UPDATE RADIUS_RADACCT SET AcctStopTime = '2006-05-25 14:56:11', AcctSessionTime = '28', 
AcctInputOctet
s = '538', AcctOutputOctets = '764', AcctTerminateCause = 'User-Request', AcctStopDelay = '1', ConnectInfo_stop = '' 
WHERE Ac
ctSessionId = '0300FFFF78001DED-4475B75E' AND UserName = '00:0a:e4:56:e2:7c' AND NASIPAddress = '62.231.32.50' AND 
AcctStopTi
me = 0
rlm_sql_mysql: MYSQL check_error: 2006, returning SQL_DOWN
--


Then, after that I call a simple perl script to set an attribute for my redbacks interfaces and the subscriber 
interface that has been assigned to the user:

rlm_sql (primary): Reserving sql socket id: 92
rlm_sql_mysql: query:  SELECT id,UserName,Attribute,Value,op FROM RADIUS_RADCHECK WHERE Username = '0x0000fffc3e23' 
ORDER BY
id
rlm_sql_mysql: MYSQL check_error: 2006, returning SQL_DOWN

This perl script bails as the mysql server has been marked down. Then the socket is closed and radius moves onto the 
next socket for its next request. The next request starts up accounting again like so:

rlm_sql (primary): Reserving sql socket id: 91
rlm_sql_mysql: query:  INSERT into RADIUS_RADACCT (RadAcctId, AcctSessionId, AcctUniqueId, UserName, Realm, 
NASIPAddress, NAS
PortId, NASPortType, AcctStartTime, AcctStopTime, AcctSessionTime, AcctAuthentic, ConnectInfo_start, ConnectInfo_stop, 
AcctIn
putOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, FramedProtocol, 
FramedIPAddr
ess, AcctStartDelay, AcctStopDelay) values('', '0300FFFF78001DEE-4475B785', '699d8ef3462ee740', '00:0a:e4:56:e2:7c', 
'', '62.
231.32.50', '67174400', 'Virtual', '2006-05-25 14:56:21', '0', '0', 'RADIUS', '', '', '0', '0', '', '', '', 
'Dialout-Framed-U
ser', '', '', '', '0')
rlm_sql_mysql: MYSQL check_error: 2006, returning SQL_DOWN
--


and yet again, mysql is marked as down. The clue to solving this led to me seeing exactly double the amount of inserts 
from my little perl script from preauth to the number of selects ran from the same script.

So, the inserts were working before accounting started, but afterwards, the select to select the attribute and pass it 
back to the redback device was bailing. in between was the accounting start and stop queries.

After reading the documented limitations of MySQL Clustering available here:

bug report: http://bugs.mysql.com/bug.php?id=17190
documented limits: http://dev.mysql.com/doc/refman/5.0/en/mysql-cluster-limitations.html

I rememebered about the radacct table field change.
I then disabled radius accounting and the errors is completely gone.

So, Im now wondering if this has been spotted before or if the freeradius developers have enough info from this mail to 
look into the issue. It seems only related to NDB mySQL clustering, which Im sure more and more folk will use as it 
becomes more stable.

thoughts/ideas/workarounds??

Would be happy to hear a suggestion for the table modification that bypasses this mysql cluster limitation or an 
updated accounting query that will work in this environment.

Thanks for your responses.

cheers





On Thu, May 25, 2006 at 10:58:42AM -0400, Dennis Skinner wrote:
> andy wrote:
> > On another note still related to MySQL, is there a way to enforce mysql and freeradius to make persistant connections 
> > to the database store?
> 
> It does.  The radius server will make all of its connections to the
> MySQL server at startup (or remake them on a HUP) and they stay there.
> If you do "show processlist" or run mytop, you will see a bunch of idle
> processes until radiusd starts doing something.
> 
> As to your first issue, are you sure it isn't network or firewall related?
> 
> -- 
> Dennis Skinner
> Systems Administrator
> BlueFrog Internet
> http://www.bluefrog.com
> - 
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
> 

-- 
andy    andy at shady.org
-----------------------------------------------
Never argue with an idiot. They drag you down 
to their level, then beat you with experience.
-----------------------------------------------