RE: FR with MySQL - Stored Procedures
That is great news! Alan DeKok wrote: >Thomas Martens wrote: >> I added your hack to my version too. >> I also don't get any errors till now. It seems to work with SP, and >> also normal SQL-querys. > > Sounds good to me. > >> Here is the diff...so please, a FR developer take a look at it;) > > Nicolas is looking into it. It should be in 1.1.7 && in 2.0. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: FR with MySQL - Stored Procedures
Thomas Martens wrote: > I added your hack to my version too. > I also don't get any errors till now. It seems to work with SP, and also > normal SQL-querys. Sounds good to me. > Here is the diff...so please, a FR developer take a look at it;) Nicolas is looking into it. It should be in 1.1.7 && in 2.0. Alan DeKok. -- http://deployingradius.com - The web site of the book http://deployingradius.com/blog/ - The blog - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: FR with MySQL - Stored Procedures
I added your hack to my version too. I also don't get any errors till now. It seems to work with SP, and also normal SQL-querys. I've modified your patch with some comments and also added a mysql_version check, so that the patch will only apply to MySQL version > 5. Here is the diff...so please, a FR developer take a look at it;) Thanks, Thomas --- sql_mysql.c 2007-05-08 15:55:47.0 +0200 +++ sql_mysql.c 2007-05-10 10:56:33.0 +0200 @@ -75,6 +75,7 @@ mysql_init(&(mysql_sock->conn)); mysql_options(&(mysql_sock->conn), MYSQL_READ_DEFAULT_GROUP, "freeradius"); + if (!(mysql_sock->sock = mysql_real_connect(&(mysql_sock->conn), config->sql_server, config->sql_login, @@ -82,7 +83,16 @@ config->sql_db, atoi(config->sql_port), NULL, - CLIENT_FOUND_ROWS))) { + CLIENT_FOUND_ROWS + #if MYSQL_VERSION_ID >= 5 + /* +* the CLIENT_MULTI_STATEMENTS flag also include the +* CLIENT_MULTI_RESULT flag, these are necessary for +* Stored Procedures (MySQL 5.x) +*/ + | CLIENT_MULTI_STATEMENTS + #endif + ))) { radlog(L_ERR, "rlm_sql_mysql: Couldn't connect socket to MySQL server [EMAIL PROTECTED]:%s", config->sql_login, config->sql_server, config->sql_db); radlog(L_ERR, "rlm_sql_mysql: Mysql error '%s'", mysql_error(&mysql_sock->conn)); mysql_sock->sock = NULL; @@ -289,6 +299,18 @@ if (sqlsocket->row == NULL) { return sql_check_error(mysql_errno(mysql_sock->sock)); } + + #if MYSQL_VERSION_ID >= 5 + /* +* Stored Procedures return two results (the result and affected rows), +* so FR fails with a mysql errorcode 2014 (CR_COMMANDS_OUT_OF_SYNC), +* when a second mysql-query is executed +* +* so we drop the second result (assume FR expect just one result) +*/ + mysql_next_result(mysql_sock->sock); + #endif + return 0; } - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: FR with MySQL - Stored Procedures
Did some further research on the MySQL - FR Stored Procedure (SP) problem. When calling the SP, MySQL always returns two results. One is the actual result and the other is the number of affected rows, which is different to a normal e.g. SELECT query. SP: mysql> call CheckIt('myString'); ++ | result | ++ | 10 | (result is correct) ++ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) <-- Result plus the number of affected rows! Normal Query: mysql> select 25 AS result; ++ | result | ++ | 25 | ++ 1 row in set (0.00 sec) <--- Normal query with one result MYSQL 5.0 Ref manual If you write C programs that use the CALL SQL statement to execute stored procedures that produce result sets, you must set the CLIENT_MULTI_RESULTS flag, either explicitly, or implicitly by setting CLIENT_MULTI_STATEMENTS when you call mysql_real_connect(). This is because each such stored procedure produces multiple results: the result sets returned by statements executed within the procedure, as well as a result to indicate the call status. To process the result of a CALL statement, use a loop that calls mysql_next_result() to determine whether there are more results. The following procedure outlines a suggested strategy for handling multiple statements: 1. Pass CLIENT_MULTI_STATEMENTS to mysql_real_connect(), to fully enable multiple-statement execution and multiple-result processing. 2. After calling mysql_query() or mysql_real_query() and verifying that it succeeds, enter a loop within which you process statement results. 3. For each iteration of the loop, handle the current statement result, retrieving either a result set or an affected-rows count. If an error occurs, exit the loop. 4. At the end of the loop, call mysql_next_result() to check whether another result exists and initiate retrieval for it if so. If no more results are available, exit the loop. -- Just for a test, I added a very quick and dirty 'mysql_next_result' into the sql_free_result function of "sql_mysql.c" in row 292 of FR 1.1.6, the same location Thomas used the . if (sqlsocket->row == NULL) { return sql_check_error(mysql_errno(mysql_sock->sock)); } mysql_next_result(mysql_sock->sock); /* eat the number of affected rows result */ return 0; } . As a result I do not get the 2014 error anymore and everything seems to be working fine. Since I do not really know the implications of just adding this command, maybe one of the experts could help out here. In an ealier posting 3 days ago I said that the problem is not really stored procedure related ... but it is! Once the SP is called at least once other queries will have errors too. Gunther FR 1.1.6 - MySQL 5.0.41 - CentOS 4.4 - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: FR with MySQL - Stored Procedures
I added this patch to sql_mysql.c and started testing. 1. When I do not call any SPs, it works fine 2. When I call a SP for the first time, it works fine 3. When I call a SP for the second and more time, the 2014 error shows again It seems that these errors happen whenever any SP is called a second time, and there are still some results not freed. Gunther -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] g] On Behalf Of cky Sent: Tuesday, 08 May 2007 10:18 AM To: freeradius-users@lists.freeradius.org Subject: Re: FR with MySQL - Stored Procedures so...after a while I found a workaround for the SP problem..well, is quick and dirty and probably not the best solution. I've added to the function "sql_fetch_row" in sql_mysql.c a simple "sql_free_result(sqlsocket, config);"...see the diff below. Now my Stored Procedure works correct and as expected (my SP is at the end of this message). Perhaps someone can verify this, and perhaps this can added to the freeradius sourcecode from a developer (well, perhaps not so dirty ;) ) --- sql_mysql.c_org 2007-05-08 15:55:47.0 +0200 +++ sql_mysql.c 2007-05-08 15:57:35.0 +0200 @@ -50,6 +50,8 @@ SQL_ROW row; } rlm_sql_mysql_sock; +static int sql_free_result(SQLSOCK*, SQL_CONFIG*); + /* * * Function: sql_create_socket @@ -82,7 +84,7 @@ config->sql_db, atoi(config->sql_port), NULL, - CLIENT_FOUND_ROWS))) { + CLIENT_FOUND_ROWS|CLIENT_MULTI_STATEMENTS))) { radlog(L_ERR, "rlm_sql_mysql: Couldn't connect socket to MySQL server [EMAIL PROTECTED]:%s", config->sql_login, config->sql_server, config->sql_db); radlog(L_ERR, "rlm_sql_mysql: Mysql error '%s'", mysql_error(&mysql_sock->conn)); mysql_sock->sock = NULL; @@ -289,6 +291,9 @@ if (sqlsocket->row == NULL) { return sql_check_error(mysql_errno(mysql_sock->sock)); } + + sql_free_result(sqlsocket, config); + return 0; } My SP: CREATE PROCEDURE ssg_auth (IN `CallingStationID` VARCHAR(14),IN `ClientIP` VARCHAR(15)) SQL SECURITY INVOKER BEGIN DECLARE rows INT (1); DECLARE v_id BIGINT (20); DECLARE v_UserName VARCHAR(10); DECLARE v_Attribute,v_Value VARCHAR (30); DECLARE v_op VARCHAR (4); DECLARE v_IP VARCHAR (15); DECLARE user CURSOR FOR SELECT `id`,`UserName`,`Attribute`,`Value`,`op`,`IP` FROM `ssg_check` WHERE `Calling-Station-Id` = `CallingStationID`; SELECT COUNT(*) INTO rows FROM `ssg_check` WHERE `Calling-Station-Id` = `CallingStationID`; IF rows = 0 THEN INSERT INTO `ssg_check` (`Calling-Station-Id`,`IP`) VALUES (`CallingStationID`,`ClientIP`); SELECT '0','Guest','Auth-Type','REJECT',':='; END IF; IF rows = 1 THEN OPEN user; FETCH user INTO v_id,v_UserName,v_Attribute,v_Value,v_op,v_IP; CLOSE user; IF v_Value = 'REJECT' and v_ip != `ClientIP` THEN UPDATE `ssg_check` SET `IP` = `ClientIP` WHERE `Calling-Station-Id` = `CallingStationID`; END IF; SELECT v_id,v_UserName,v_Attribute,v_Value,v_op; END IF; END; // Thomas -- View this message in context: http://www.nabble.com/FR-with-MySQL---Stored-Procedures-tf3701829.html#a1037 6727 Sent from the FreeRadius - User mailing list archive at Nabble.com. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: FR with MySQL - Stored Procedures
so...after a while I found a workaround for the SP problem..well, is quick and dirty and probably not the best solution. I've added to the function "sql_fetch_row" in sql_mysql.c a simple "sql_free_result(sqlsocket, config);"...see the diff below. Now my Stored Procedure works correct and as expected (my SP is at the end of this message). Perhaps someone can verify this, and perhaps this can added to the freeradius sourcecode from a developer (well, perhaps not so dirty ;) ) --- sql_mysql.c_org 2007-05-08 15:55:47.0 +0200 +++ sql_mysql.c 2007-05-08 15:57:35.0 +0200 @@ -50,6 +50,8 @@ SQL_ROW row; } rlm_sql_mysql_sock; +static int sql_free_result(SQLSOCK*, SQL_CONFIG*); + /* * * Function: sql_create_socket @@ -82,7 +84,7 @@ config->sql_db, atoi(config->sql_port), NULL, - CLIENT_FOUND_ROWS))) { + CLIENT_FOUND_ROWS|CLIENT_MULTI_STATEMENTS))) { radlog(L_ERR, "rlm_sql_mysql: Couldn't connect socket to MySQL server [EMAIL PROTECTED]:%s", config->sql_login, config->sql_server, config->sql_db); radlog(L_ERR, "rlm_sql_mysql: Mysql error '%s'", mysql_error(&mysql_sock->conn)); mysql_sock->sock = NULL; @@ -289,6 +291,9 @@ if (sqlsocket->row == NULL) { return sql_check_error(mysql_errno(mysql_sock->sock)); } + + sql_free_result(sqlsocket, config); + return 0; } My SP: CREATE PROCEDURE ssg_auth (IN `CallingStationID` VARCHAR(14),IN `ClientIP` VARCHAR(15)) SQL SECURITY INVOKER BEGIN DECLARE rows INT (1); DECLARE v_id BIGINT (20); DECLARE v_UserName VARCHAR(10); DECLARE v_Attribute,v_Value VARCHAR (30); DECLARE v_op VARCHAR (4); DECLARE v_IP VARCHAR (15); DECLARE user CURSOR FOR SELECT `id`,`UserName`,`Attribute`,`Value`,`op`,`IP` FROM `ssg_check` WHERE `Calling-Station-Id` = `CallingStationID`; SELECT COUNT(*) INTO rows FROM `ssg_check` WHERE `Calling-Station-Id` = `CallingStationID`; IF rows = 0 THEN INSERT INTO `ssg_check` (`Calling-Station-Id`,`IP`) VALUES (`CallingStationID`,`ClientIP`); SELECT '0','Guest','Auth-Type','REJECT',':='; END IF; IF rows = 1 THEN OPEN user; FETCH user INTO v_id,v_UserName,v_Attribute,v_Value,v_op,v_IP; CLOSE user; IF v_Value = 'REJECT' and v_ip != `ClientIP` THEN UPDATE `ssg_check` SET `IP` = `ClientIP` WHERE `Calling-Station-Id` = `CallingStationID`; END IF; SELECT v_id,v_UserName,v_Attribute,v_Value,v_op; END IF; END; // Thomas -- View this message in context: http://www.nabble.com/FR-with-MySQL---Stored-Procedures-tf3701829.html#a10376727 Sent from the FreeRadius - User mailing list archive at Nabble.com. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: FR with MySQL - Stored Procedures
Hello Gunther, I've the same problems here. I use also a simple SP (just for testing) and I run into the same problems, like you. I've also patched the "sql_mysql.c" file and added the CLIENT_MULTI_STATEMENTS flag. After that, I get also the error 2014. After that, I added to every function, a radlog(L_DBG,"THOMAS: Init (sql_init_socket)"); (with the functionsnames), to see the order of the calls. This is the result: radius_xlat: 'CALL test2 ('00e0.18e0.c19d')' rlm_sql (sql): Reserving sql socket id: 1 THOMAS: Select_Query (sql_select_query) THOMAS: Query (sql_query) rlm_sql_mysql: query: CALL test2 ('00e0.18e0.c19d') THOMAS: Check_error (sql_check_error) THOMAS: Store (sql_store_result) THOMAS: Num_fields (sql_num_fields) THOMAS: Fetch (sql_fetch_row) THOMAS: Fetch (sql_fetch_row) THOMAS: Check_error (sql_check_error) THOMAS: Finish_Select (sql_finish_select_query) THOMAS: Free (sql_free_results) radius_xlat: '' radius_xlat: 'SELECT id,UserName,Attribute,Value,op FROM ssg_reply WHERE `id` = '1' ORDER BY id' THOMAS: Select_Query (sql_select_query) THOMAS: Query (sql_query) rlm_sql_mysql: query: SELECT id,UserName,Attribute,Value,op FROM ssg_reply WHERE `id` = '1' ORDER BY id THOMAS: Check_error (sql_check_error) rlm_sql_mysql: MYSQL check_error: 2014 received rlm_sql_getvpdata: database query error I think, the problem is the double call of "sql_fetch_row", but I'm not sure. If I replace the SP with a normal SELECT-query, everything works fine (and the sql_fetch_row function is also called twice). The problem is, I need the SP, so I need a solution,fix,workaround, whatever:) My system: debian etch freeradius 1.1.3 (from deb), and also freeradius 1.1.6 from source mysql 5.0.32 Best regards, Thomas -- View this message in context: http://www.nabble.com/FR-with-MySQL---Stored-Procedures-tf3701829.html#a10373403 Sent from the FreeRadius - User mailing list archive at Nabble.com. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: FR with MySQL - Stored Procedures
Alan DeKok wrote >> I forgot to mention that the problem I reported previously is actually >> not 'Stored Procedure' related, but related to the MySQL client_flag >> 'CLIENT_MULTI_STATEMENTS'. This flag is required to support Stored >> Procedures and is causing the problem (at least with my operating >> system (CentOS 4.4)). So no need to create any stored procedures ... >> just compile FR with CLIENT_MULTI_STATEMENTS in the >> rlm_sql/drivers/rlm_sql_mysql/sql_mysql.c module. > > OK. > >> Looking forward using Stored Procedures (no more 253 byte limit for my >> SQL statements!!!). > > Huh? From the changelog in 1.1.5: > > * Increase buffer size for dynamic expansion, which allows > longer SQL qeuries. (close: #405) > > 1.1.5 and 1.1.6 allow SQL queries up to 4k in length. The only limit of 253 bytes is the data that has to go into a RADIUS packet. > > Alan DeKok. Thanks Alan! OK, seems I am not really up-to-date ;-) Good to know that that limit has been lifted. When things work ... you hardly like to touch them ;-) I thought this was a RADIUS definition issue and cannot be changed (rfc) as I learned some time ago ... Anyhow, the stored procedures is the way I like to proceed and it would be great if I can use them within FreeRadius like other people apparently do. Gunther - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: FR with MySQL - Stored Procedures
Gunther wrote: > I forgot to mention that the problem I reported previously is actually not > 'Stored Procedure' related, but related to the MySQL client_flag > 'CLIENT_MULTI_STATEMENTS'. This flag is required to support Stored > Procedures and is causing the problem (at least with my operating system > (CentOS 4.4)). So no need to create any stored procedures ... just compile > FR with CLIENT_MULTI_STATEMENTS in the > rlm_sql/drivers/rlm_sql_mysql/sql_mysql.c module. OK. > Looking forward using Stored Procedures (no more 253 byte limit for my SQL > statements!!!). Huh? From the changelog in 1.1.5: * Increase buffer size for dynamic expansion, which allows longer SQL qeuries. (close: #405) 1.1.5 and 1.1.6 allow SQL queries up to 4k in length. The only limit of 253 bytes is the data that has to go into a RADIUS packet. Alan DeKok. -- http://deployingradius.com - The web site of the book http://deployingradius.com/blog/ - The blog - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: FR with MySQL - Stored Procedures
I forgot to mention that the problem I reported previously is actually not 'Stored Procedure' related, but related to the MySQL client_flag 'CLIENT_MULTI_STATEMENTS'. This flag is required to support Stored Procedures and is causing the problem (at least with my operating system (CentOS 4.4)). So no need to create any stored procedures ... just compile FR with CLIENT_MULTI_STATEMENTS in the rlm_sql/drivers/rlm_sql_mysql/sql_mysql.c module. Looking forward using Stored Procedures (no more 253 byte limit for my SQL statements!!!). Gunther - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html