[Devel] [ openser-Bugs-1618322 ] avp_db_query cannot call mysql stored procedures

SourceForge.net noreply at sourceforge.net
Thu May 24 14:26:00 CEST 2007


Bugs item #1618322, was opened at 2006-12-18 20:17
Message generated for change (Comment added) made by mellen
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=743020&aid=1618322&group_id=139143

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: modules
Group: ver devel
Status: Open
Resolution: None
Priority: 5
Private: No
Submitted By: vabdulla (vabdulla)
Assigned to: Daniel-Constantin Mierla (miconda)
Summary: avp_db_query cannot call mysql stored procedures

Initial Comment:
Hi,

When calling a mysql5 stored procedure which returns a result set like this:
avp_db_query("CALL db.sp_name('$avp(s:param1)', "param2",'$oU')", "$avp(s:rs1);$avp(s:rs2));

I get the following error:

0(1245) submit_query: PROCEDURE db.sp_name can't return a result set in the given context
0(1245) db_raw_query: Error while submitting query
0(1245) avpops:db_query_avp: error - cannot do the query

After some research, I have found that this error come from the client flags passed to mysql_real_connect() function.

The patch attached correct this bug.

-- 
Regards,
-vma
.



----------------------------------------------------------------------

Comment By: Tais M. Hansen (mellen)
Date: 2007-05-24 14:26

Message:
Logged In: YES 
user_id=382592
Originator: NO

I have a new patch for this issue based on the original patch but checking
to make sure it's only used on mysql-4.1 or newer. I can't seem to attach
it to this bug report though?


----------------------------------------------------------------------

Comment By: Tais M. Hansen (mellen)
Date: 2007-05-24 13:26

Message:
Logged In: YES 
user_id=382592
Originator: NO

Is there a problem with this patch since it hasn't made it into
1.1.x/1.2.x?

----------------------------------------------------------------------

Comment By: vabdulla (vabdulla)
Date: 2006-12-21 11:44

Message:
Logged In: YES 
user_id=1580175
Originator: YES

Hello,

According to the mysql documentation, these flags exist since version 4.1
(http://dev.mysql.com/doc/refman/4.1/en/mysql-real-connect.html). 

Otherwise, I don't see a simple way to check the mysql version directly as
you need to be connected first before calling mysql_get_server_version().

-- 
Regards,
-vma
.


----------------------------------------------------------------------

Comment By: Daniel-Constantin Mierla (miconda)
Date: 2006-12-20 22:57

Message:
Logged In: YES 
user_id=1246013
Originator: NO

If I am not wrong, these new flags are available with MySQL 5.0 or
greater. Could you adapt the patch so it detects mysql version and sets the
flags only when the version is appropriate? Otherwise, I guess, will give
compilation errors for older versions of MySQL.

----------------------------------------------------------------------

Comment By: Kobi Eshun (ekobi)
Date: 2006-12-18 21:06

Message:
Logged In: YES 
user_id=1039134
Originator: NO

Hi,

I believe that there is more to this problem than simply setting the
additional client flags. The MySQL manual also recommends disposing of all
result sets by iterating over them with mysql_next_result().

I've appended a patch that does this, and it works fine for some use
cases. However, I don't believe it's trivial to retrieve more than one
result set into the OpenSER context using the current API.

Anyone else have any thougts on this? Cheers,
--
kobi


Index: modules/mysql/dbase.c
===================================================================
RCS file: /cvsroot/openser/sip-server/modules/mysql/dbase.c,v
retrieving revision 1.5
diff -u -r1.5 dbase.c
--- modules/mysql/dbase.c	14 Jul 2006 14:12:59 -0000	1.5
+++ modules/mysql/dbase.c	5 Dec 2006 00:31:03 -0000
@@ -327,6 +327,7 @@
 	}
 
 	CON_RESULT(_h) = mysql_store_result(CON_CONNECTION(_h));
+
 	if (!CON_RESULT(_h)) {
 		if (mysql_field_count(CON_CONNECTION(_h)) == 0) {
 			(*_r)->col.n = 0;
@@ -352,7 +353,18 @@
 		return -4;
 	}
 	
+	/* */
+	/* XXX: flush list of result sets -- ke */
+	LOG(L_DBG,"store_result: emptying mysql_store_result list...\n ");
+	while ((!mysql_next_result (CON_CONNECTION(_h)))) {
+		MYSQL_RES *foo=mysql_store_result (CON_CONNECTION(_h)); 
+		LOG(L_DBG,"store_result: freeing extra result set..\n");
+		mysql_free_result(foo);
+	}
+	LOG(L_DBG,"store_result: done emptying mysql_store_result list.\n ");
+
 	return 0;
+
 }
 
 
Index: modules/mysql/my_con.c
===================================================================
RCS file: /cvsroot/openser/sip-server/modules/mysql/my_con.c,v
retrieving revision 1.4
diff -u -r1.4 my_con.c
--- modules/mysql/my_con.c	14 Jul 2006 14:12:59 -0000	1.4
+++ modules/mysql/my_con.c	5 Dec 2006 00:31:03 -0000
@@ -76,7 +76,7 @@
 		    );
 	}
 
-	if (!mysql_real_connect(ptr->con, id->host, id->username, id->password,
id->database, id->port, 0, 0)) {
+	if (!mysql_real_connect(ptr->con, id->host, id->username, id->password,
id->database, id->port, 0, CLIENT_MULTI_RESULTS|CLIENT_MULTI_STATEMENTS))
{
 		LOG(L_ERR, "new_connection: %s\n", mysql_error(ptr->con));
 		mysql_close(ptr->con);
 		goto err;


----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=743020&aid=1618322&group_id=139143



More information about the Devel mailing list