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
FR with MySQL - Stored Procedures
Despite that several people reported that their FreeRadius 1.x installation
is working fine with MySQL Stored Procedures, I run into quite some
problems.
Here my environment:
- CentOS 4.4 on Xen Server 3.1
- FreeRadius 1.1.6
- MySQL 5.0.37 Community Edition with INNODB Tables
I used a very simple stored procedure to track down the problem:
---
DELIMITER //
DROP PROCEDURE IF EXISTS CheckIt //
CREATE PROCEDURE CheckIt ()
BEGIN
SELECT 12345;
END//
---
This routine will always return the value 12345.
Anyhow, when I called this procedure from FreeRadius I always go an error:
"PROCEDURE myDB.CheckIt can't return a result set in the given context"
-
Sun May 6 07:23:10 2007 : Debug: rlm_sql_mysql: query: CALL CheckIt()
Sun May 6 07:23:10 2007 : Debug: rlm_sql_mysql: MYSQL check_error: 1312
received
Sun May 6 07:23:10 2007 : Error: rlm_sql (sql): database query error, CALL
CheckIt(): PROCEDURE myDB.CheckIt can't return a result set in the given
context
-
I then tried to call the same function via a PHP script (w/o FreeRadius
involved) and run into the same problem.
Then I found the information that it is required for MySQL Stored Procedures
to function
the client_flag 'CLIENT_MULTI_STATEMENTS' (refer to mysql.h) has to be added
to the mysql_real_connect call.
After adding it to the call within PHP all worked fine.
Then I added the flag to
freeradius-1.1.6/src/modules/rlm_sql/drivers/rlm_sql_mysql/sql_mysql.c :
-
if (!(mysql_sock->sock = mysql_real_connect(&(mysql_sock->conn),
config->sql_server,
config->sql_login,
config->sql_password,
config->sql_db,
atoi(config->sql_port),
NULL,
CLIENT_FOUND_ROWS|CLIENT_MULTI_STATEMENTS))) {
-
./configure; make; make install
Note: CLIENT_MULTI_STATEMENTS automatically also sets CLIENT_MULTI_RESULTS
within MySQL
Started FreeRadius and procedure calls were accepted and results were
correct.
-
Sun May 6 21:29:08 2007 : Debug: rlm_sql_mysql: query: CALL CheckIt()
Sun May 6 21:29:08 2007 : Debug: rlm_sql (sql): - sql_xlat finished
Sun May 6 21:29:08 2007 : Debug: rlm_sql (sql): Released sql socket id: 0
Sun May 6 21:29:08 2007 : Debug: radius_xlat: '12345'
-
A user can now login and things seem to work fine. But then consecutive
MySQL queries started
showing new error results:
-
Sun May 6 21:41:42 2007 : Debug: rlm_sql_mysql: MYSQL check_error: 2014
received
Sun May 6 21:41:42 2007 : Error: rlm_sql (sql): database query error,
: Commands out of sync; you can't run this command
now
-
MySQL seems to track the state of each call and when the order of this state
is incorrect,
MySQL responds with 'CR_COMMANDS_OUT_OF_SYNC' = 'Commands out of sync; you
can't run this command now'.
>From what I found on the net ... "When the result of a statement isn't freed
MySQL gives an error when
trying to process a new query"
Could it be that there is somewhere a 'mysql_free_result' missing?
This is what the MySQL documentation is saying:
B.1.2.13. Commands out of sync
If you get Commands out of sync; you can't run this command now in your
client code, you are calling client functions in the wrong order.
This can happen, for example, if you are using mysql_use_result() and try to
execute a new query before you have called mysql_free_result(). It can also
happen if you try to execute two queries that return data without calling
mysql_use_result() or mysql_store_result() in between.
2.4.16. Upgrading MySQL
If, after an upgrade, you experience problems with recompiled client
programs, such as Commands out of sync or unexpected core dumps, you
probably have used old header or library files when compiling your programs.
In this case, you should check the date for your mysql.h file and
libmysqlclient.a library to verify that they are from the new MySQL
distribution. If not, recompile your programs with the new headers and
libraries.
... Did that ...
When I leave some time between a login/logout/login it works ... Looks like
the MySQL status information
times out after a short while.
Any hints on getting this up and running without changing O/S ? Thanks!
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
