Hi list, Fairly new to freeradius, just about to deploy a solution involving RedBack with data storage on the backend using MySQL. I am using NDBD MySQL clustering on the backend and I see the following error from FreeRadius: rlm_sql_mysql: MYSQL check_error: 2006, returning SQL_DOWN This seems to relate to a timeout issue but googling doesnt provide any answers for that specific error code. (too like the date 2006 I expect). I have fairly tweaked the mysql backend to be fairly resilant in a heavy load situation, and none of the other applications running against this cluster seem to have issues. I was wondering if anyone could enlighten me as to how to fix this error, or what threshholds would be recomended for running in this environment? It seems that when this error occurs, for the next few auth attempts, certain attributes are not passed back to the redback. This specifically being IP-Interface. any clues on this issue would be greatly appreciated. thanks -- andy andy@shady.org ----------------------------------------------- Never argue with an idiot. They drag you down to their level, then beat you with experience. -----------------------------------------------
On another note still related to MySQL, is there a way to enforce mysql and freeradius to make persistant connections to the database store? On Wed, May 24, 2006 at 11:10:16AM +0100, andy wrote:
Hi list,
Fairly new to freeradius, just about to deploy a solution involving RedBack with data storage on the backend using MySQL.
I am using NDBD MySQL clustering on the backend and I see the following error from FreeRadius:
rlm_sql_mysql: MYSQL check_error: 2006, returning SQL_DOWN
This seems to relate to a timeout issue but googling doesnt provide any answers for that specific error code. (too like the date 2006 I expect).
I have fairly tweaked the mysql backend to be fairly resilant in a heavy load situation, and none of the other applications running against this cluster seem to have issues.
I was wondering if anyone could enlighten me as to how to fix this error, or what threshholds would be recomended for running in this environment? It seems that when this error occurs, for the next few auth attempts, certain attributes are not passed back to the redback. This specifically being IP-Interface.
any clues on this issue would be greatly appreciated.
thanks
-- andy andy@shady.org ----------------------------------------------- Never argue with an idiot. They drag you down to their level, then beat you with experience. ----------------------------------------------- - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-- andy andy@shady.org ----------------------------------------------- Never argue with an idiot. They drag you down to their level, then beat you with experience. -----------------------------------------------
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
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@shady.org ----------------------------------------------- Never argue with an idiot. They drag you down to their level, then beat you with experience. -----------------------------------------------
andy <andy@shady.org> wrote:
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 1.1.1 the RadAcctID field is auto-increment, and PRIMARY KEY. See doc/examples/mysql.sql. Which version are you looking at? Alan DeKok.
On Thu, May 25, 2006 at 05:20:03PM -0400, Alan DeKok wrote:
andy <andy@shady.org> wrote:
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 1.1.1 the RadAcctID field is auto-increment, and PRIMARY KEY. See doc/examples/mysql.sql.
Which version are you looking at?
Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
The version im looking at is freeradius from ports: dhcp1# radiusd -v radiusd: FreeRADIUS Version 1.1.1, for host , built on May 18 2006 at 18:06:18 I have re-imported the radacct table from this source and the error is slightly different but is still happening: rlm_sql (primary): Reserving sql socket id: 89 rlm_sql_mysql: query: INSERT into RADIUS_RADACCT (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctStopTime, AcctSessionTime, AcctAuthentic, ConnectInfo_start, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay, AcctStopDelay) values('0300FFFF78001E5D-4476C727', '52213596cf7e22f9', '00:0a:e4:56:e2:7c', '', '62.231.32.50', '67174400', 'Virtual', '2006-05-26 10:15:19', '0', '0', 'RADIUS', '', '', '0', '0', '', '', '', 'Dialout-Framed-User', '', '', '', '0') rlm_sql_mysql: MYSQL check_error: 2006, returning SQL_DOWN rlm_sql (primary): Attempting to connect rlm_sql_mysql #89 rlm_sql_mysql: Starting connect to MySQL server for #89 rlm_sql (primary): Connected new DB handle, #89 rlm_sql_mysql: query: INSERT into RADIUS_RADACCT (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctStopTime, AcctSessionTime, AcctAuthentic, ConnectInfo_start, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay, AcctStopDelay) values('0300FFFF78001E5D-4476C727', '52213596cf7e22f9', '00:0a:e4:56:e2:7c', '', '62.231.32.50', '67174400', 'Virtual', '2006-05-26 10:15:19', '0', '0', 'RADIUS', '', '', '0', '0', '', '', '', 'Dialout-Framed-User', '', '', '', '0') rlm_sql (primary): Released sql socket id: 89 It now doesnt move onto the next socket, but retries the current connection. I have also updated the default queries from sql.conf.example so everything is default. cheers -- andy andy@shady.org ----------------------------------------------- Never argue with an idiot. They drag you down to their level, then beat you with experience. -----------------------------------------------
Freeradius keeps a configurable number of open connections, called sockets in the config, which it uses to query the DB at all times. I believe it keeps these open persistently. In sql.conf... # number of sql connections to make to server num_sql_socks = 5 Chris Carver Network Engineer 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?
On Wed, May 24, 2006 at 11:10:16AM +0100, andy wrote:
Hi list,
Fairly new to freeradius, just about to deploy a solution involving RedBack with data storage on the backend using MySQL.
I am using NDBD MySQL clustering on the backend and I see the following error from FreeRadius:
rlm_sql_mysql: MYSQL check_error: 2006, returning SQL_DOWN
This seems to relate to a timeout issue but googling doesnt provide any answers for that specific error code. (too like the date 2006 I expect).
I have fairly tweaked the mysql backend to be fairly resilant in a heavy load situation, and none of the other applications running against this cluster seem to have issues.
I was wondering if anyone could enlighten me as to how to fix this error, or what threshholds would be recomended for running in this environment? It seems that when this error occurs, for the next few auth attempts, certain attributes are not passed back to the redback. This specifically being IP-Interface.
any clues on this issue would be greatly appreciated.
thanks
-- andy andy@shady.org ----------------------------------------------- Never argue with an idiot. They drag you down to their level, then beat you with experience. ----------------------------------------------- - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
participants (4)
-
Alan DeKok -
andy -
Chris Carver -
Dennis Skinner