FR with MySQL - Stored Procedures

[Gunther]

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: freeradius-users-bounces+freeradius=caribsms.com at lists.freeradius.org
[mailto:freeradius-users-bounces+freeradius=caribsms.com at lists.freeradius.or
g] On Behalf Of cky
Sent: Tuesday, 08 May 2007 10:18 AM
To: freeradius-users at 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.000000000 +0200
+++ sql_mysql.c 2007-05-08 15:57:35.000000000 +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 %s@%s:%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