Bug #28745 UDF call from Stored Proc. doesn't work when calling it with result set values
Submitted: 29 May 2007 15:01 Modified: 26 Jul 2007 3:30
Reporter: Peter R. Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S2 (Serious)
Version:5.1.18-beta, 5.0 OS:Linux (Suse 10)
Assigned to: Paul DuBois CPU Architecture:Any
Tags: socket, stored procedure, udf, udp

[29 May 2007 15:01] Peter R.
Description:
I have written a small UDF that sends UDP packets to ipaddresses. The ipaddress and message are passed as arguments to the function. If I execute the function from the commandline "Select sendUdpMsg("hello world!", "192.168.0.10")" everything works great but if I want to send the message to IP addresses retrieved from a select in a stored procedure the function can't call the c function sendto() in the UDF. It's strange that the calls only fail after calling the UDF with certain (but valid) IPs.
Like recommended in the comments of Bug #17261 I also tried to call the function with substring(ip, 1) but that didn't help either. 

Thank you.
Peter

How to repeat:
1. Create sendUdpMsg UDF 
2. Run Stored Procedure 1. UDF should return three times 0 to indicate that everything worked fine. 
3. Run Stored Procedure 2. Returns 0, 5, 5. Exitcode 5 indicates that sendto() failed.

*********************
***** UDF ***********
*********************

#include <stdio.h>
#ifdef _WIN32
#include <windows.h>
#include <WinSock.h>
#define DLL_EXPORT __declspec(dllexport)
#else
#define DLL_EXPORT
#include <unistd.h>
#include <stdlib.h>
#include <sys/socket.h>
#include <netinet/in.h>
#include <arpa/inet.h>
#endif
#include <string.h>
#include <time.h>
#include <sys/types.h>
#include <mysql.h>

// Ensure that names aren't mangled when compiling with c++
#ifdef __cplusplus
extern "C" {
#endif

// Function prototypes.
my_bool sendUdpMsg_init(UDF_INIT *, UDF_ARGS *, char *);
long long sendUdpMsg(UDF_INIT *, UDF_ARGS *, char *, char *);
void sendUdpMsg_deinit(UDF_INIT *initid);

my_bool sendUdpMsg_init(UDF_INIT *initid, UDF_ARGS *args, char *message) {
if(args->arg_count != 3) {
strcpy(message, "sendUdpMsg needs three arguments: message, recipient address and a boolean value");
return 1;
}
if(args->arg_type[0] != STRING_RESULT ){
strcpy(message, "sendUdpMsg accepts only string arguments as messages");
return 1;
}
if(args->arg_type[1] != STRING_RESULT ){
strcpy(message, "Recipient host address must be string");
return 1;
}
if(args->arg_type[2] != STRING_RESULT ){
strcpy(message, "Third argument needs to be a string value. Set to true if you want to wait and receive a response");
return 1;
}

initid->maybe_null = 0;
initid->const_item = 1;
return 0;
} // End of sendUdpMsg_init()

void sendUdpMsg_deinit(UDF_INIT *initid)
{
if (initid->ptr)
free(initid->ptr);
}

long long sendUdpMsg(UDF_INIT *initid, UDF_ARGS *args, char *is_null,
char *error)
{
my_socket s; // Socket for UDP communication.
struct sockaddr_in server;
struct sockaddr_in client;

// Create and bind the socket.
if((s = socket(AF_INET, SOCK_DGRAM, 0)) == -1)
return 3;
server.sin_family = AF_INET;
server.sin_port = htons(6999);
server.sin_addr.s_addr = htonl(INADDR_ANY);
if(bind(s, (struct sockaddr *) &server, sizeof(server)) == -1)
{
#ifdef _WIN32
closesocket(s);
#else
close(s);
#endif
fprintf(stderr, "Could not bind.\n");
return 4;
}

client.sin_family = AF_INET;
client.sin_port = htons(3001);
client.sin_addr.s_addr = inet_addr(args->args[1]);

// send message
if(sendto(s, args->args[0], strlen(args->args[0])+1, 0, (struct sockaddr *) &client, sizeof(client))
== -1)
{
#ifdef _WIN32
closesocket(s);
s = NULL;
#else
close(s);
#endif
fprintf(stderr, "Could not send.\n");
return 5;
}

#ifdef _WIN32
closesocket(s);
#else
close(s);
#endif
fprintf(stderr, "packet sent.\n");
return 0;
} // End of sendUdpMsg().
#ifdef __cplusplus
}
#endif 

**********************
* STORED PROCEDURE 1 *
**********************

CREATE PROCEDURE test()
BEGIN
DECLARE exitcode INTEGER DEFAULT 1;
DECLARE i INTEGER DEFAULT 0;
DECLARE MSG VARCHAR (15) DEFAULT '$$alive?';
DECLARE ip VARCHAR (15);

pingnodes:REPEAT
SET i = i + 1;

IF(i = 1) THEN
SELECT sendUdpMsg(MSG, "192.168.0.165", "true") INTO exitcode;
ELSEIF(i = 2) THEN
SELECT sendUdpMsg(MSG, "192.168.0.4", "true") INTO exitcode;
ELSEIF(i = 3) THEN
SELECT sendUdpMsg(MSG, "192.168.0.74", "true") INTO exitcode;
END IF;

SELECT ip, exitcode;

UNTIL i = 3
END REPEAT pingnodes;
END$$ 

**********************
* STORED PROCEDURE 2 *
**********************

CREATE PROCEDURE test()
BEGIN
DECLARE exitcode INTEGER DEFAULT 1;
DECLARE i INTEGER DEFAULT 0;
DECLARE MSG VARCHAR (15) DEFAULT 'hello world';
DECLARE ip VARCHAR (15);

pingnodes:REPEAT
SET i = i + 1;

IF(i = 1) THEN
SET ip = "192.168.0.165";
ELSEIF(i = 2) THEN
SET ip = "192.168.0.4";
ELSEIF(i = 3) THEN
SET ip = "192.168.0.74";
END IF;

SELECT sendUdpMsg(MSG, ip, "true") INTO exitcode;

SELECT ip, exitcode;

UNTIL i = 3
END REPEAT pingnodes;
END$$
[29 May 2007 19:47] Sveta Smirnova
Thank you for the report.

But version 5.0.16 is quite old.

Please try with current 5.0.41 version and say us result.
[29 May 2007 20:01] Peter R.
Sorry version is 

mysql  Ver 14.13 Distrib 5.1.16-beta, for pc-linux-gnu (i686) using readline 5.0
[29 May 2007 20:02] Peter R.
version corrected in bug report
[30 May 2007 21:13] Peter R.
I'm really not proficient in C but I assume that the problem has something to do how parameters are passed from the Stored Procedure to the UDF. I wonder if it is possible to get the value of the ip and create a completely new and independent string in memory with the charachters of the argument. The same like if I would write 

char* ip = "192.168.0.74";

and then pass this to sendto()

I made a test and hardcoded the ips in the UDF 

char* ip;
if(strcmp(args->args[1],"test") == 0){  
   ip = "192.168.0.165";
} else if (strcmp(args->args[1],"test2") == 0){  
   ip = "192.168.0.74";
} else if (strcmp(args->args[1],"test3") == 0){  
   ip = "192.168.0.4";
}
client.sin_addr.s_addr = inet_addr(ip);

This worked perfectly. Is there a way to make those new strings with the values of the passed arguments? 
This would be a workaround that would help me really a lot. For someone who knows C this question should be very easy to answer. 

Thank you in advance.
Peter
[31 May 2007 8:53] Sveta Smirnova
Thank you for the feedback.

I can not repeat described behaviour in my environment.

But your guesswork about parameters passing can be right.

Please upgrade to last 5.1.18 and if you can repeat issue with this version, add call fprintf(stderr, "\n%s, %d\n", args->args[1], args->lengths[1]); into definition of function sendUdpMsg in the C file, turn on error log, call procedure 1, then procedure 2 and send us error output.
[31 May 2007 16:40] Peter R.
Updated to version 5.1.18 still not working. See provided Errorlog. 

I'm not suprised that it works in your environment because it only happens with certain ips. I have really no idea what causes it. Is there a way to get a stack trace from what happens inside the sendto function, because the arguments seem to be passed right?

I still wanted to ask if there is a workaround as I suggested in my previous post? This is definitely a critical bug for me.

Thank you.
Peter
[31 May 2007 19:58] Sveta Smirnova
Thank you for the feedback.

There is record about broken queries in the error log:

/***************
 * call Proc 2 *
****************/
192.168.0.165, 13
packet sent.

192.168.0.465, 11
Could not send.

192.168.0.745, 12
Could not send.

But 192.168.0.465 and 192.168.0.745 are invalid IP addresses. Could you repeat the issue with valid IP addresses? I.e. like template [0-255].[0-255].[0-255].[0-255]?
[31 May 2007 20:33] Peter R.
Good Sveta, I would say we found the bug. Is it possible that a character buffer is not cleared after the call?

The function was called with exactly the same three ips as in the first stored procedure. This is the output from the mysql console 
from the "SELECT ip, exitcode;":

 CALL test2();
+---------------+----------+
| ip            | exitcode |
+---------------+----------+
| 192.168.0.165 |        0 |
+---------------+----------+
1 row in set (0.00 sec)

+-------------+----------+
| ip          | exitcode |
+-------------+----------+
| 192.168.0.4 |        5 |
+-------------+----------+
1 row in set (0.00 sec)

+--------------+----------+
| ip           | exitcode |
+--------------+----------+
| 192.168.0.74 |        5 |
+--------------+----------+
1 row in set (0.00 sec)

If you look carefully at the error log you see that the length of the string and the string itself don't match.

192.168.0.165, 13 (is right)
192.168.0.465, 11 (is wrong should be length 13 as well)

also watch this:

192.168.0.165, 13
           ^^
           ||
192.168.0.465, 11 (was called with 192.168.0.4)
            ^
            |
192.168.0.745, 12 (was called with 192.168.0.74)

Thanks
Peter
[31 May 2007 22:25] Peter R.
Thank you for pointing me to the problem. I found now a workaround that seems to work for me. I create a new character array and fill it with the characters of the argument until it reaches the length of the argument. That way I get rid of the characters that are to much.

Code:
-----

char newip [15];
long i = 0;
while (i != args->lengths[1]){
	newip[i] = args->args[1][i];
	i = i + 1;
}
newip[args->lengths[1]] = '\0';
client.sin_addr.s_addr = inet_addr(newip);

Is it my task to clear the argument string after each call? 

Thanks again. If you need more feedback please reply to this thread.

Peter
[1 Jun 2007 8:40] Sveta Smirnova
Thank you for the feedback.

Verified as described in last comment.
[1 Jun 2007 8:50] Sveta Smirnova
Real problem is not sendto error, but how MySQL passes variables to UDF function.

Simplified test.

bug28745.c:
#ifdef STANDARD
/* STANDARD is defined, don't use any mysql functions */
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#ifdef __WIN__
typedef unsigned __int64 ulonglong;	/* Microsofts 64 bit types */
typedef __int64 longlong;
#else
typedef unsigned long long ulonglong;
typedef long long longlong;
#endif /*__WIN__*/
#else
#include <my_global.h>
#include <my_sys.h>
#if defined(MYSQL_SERVER)
#include <m_string.h>		/* To get strmov() */
#else
/* when compiled as standalone */
#define strmov(a,b) strcpy(a,b)
#define bzero(a,b) memset(a,0,b)
#define memcpy_fixed(a,b,c) memcpy(a,b,c)
#endif
#endif
#include <mysql.h>
#include <ctype.h>

static pthread_mutex_t LOCK_hostname;

/* These must be right or mysqld will not find the symbol! */

my_bool bug28745_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
void bug28745_deinit(UDF_INIT *initid);
char *bug28745(UDF_INIT *initid, UDF_ARGS *args, char *result,
	       unsigned long *length, char *is_null, char *error);

my_bool bug28745_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
{
  if (args->arg_count != 1 || args->arg_type[0] != STRING_RESULT)
  {
    strcpy(message,"Wrong arguments to bug28745;  Use the source");
    return 1;
  }
  initid->max_length=MAXMETAPH;
  return 0;
}

void bug28745_deinit(UDF_INIT *initid __attribute__((unused)))
{
}

char *bug28745(UDF_INIT *initid __attribute__((unused)),
               UDF_ARGS *args, char *result, unsigned long *length,
               char *is_null, char *error __attribute__((unused)))
{
  char *org_result;
  org_result=result;

  result = args->args[0];
  
  fprintf(stderr, "\n%s, %d\n", args->args[0], args->lengths[0]);
  
  *length= (unsigned long) args->lengths[0];
  return result;
}

STORED PROCEDURE bug28745():

CREATE PROCEDURE bug28745()
BEGIN
DECLARE exitcode INTEGER DEFAULT 1;
DECLARE i INTEGER DEFAULT 0;
DECLARE MSG VARCHAR (15) DEFAULT 'hello world';
DECLARE ip VARCHAR (15);
pingnodes:REPEAT
SET i = i + 1;
IF(i = 1) THEN SET ip = "192.168.0.165";
ELSEIF(i = 2) THEN SET ip = "192.168.0.4";
ELSEIF(i = 3) THEN SET ip = "192.168.0.74";
END IF;
SELECT bug28745(ip) INTO exitcode;
SELECT ip, exitcode;
UNTIL i = 3
END REPEAT pingnodes;
END|

Then:

CALL bug28745(); and examine error log.
[1 Jun 2007 8:57] Sveta Smirnova
Version 5.0 is affected too.
[25 Jul 2007 22:07] Damien Katz
It looks like there is no bug here. In all the example code provided, the code assumes that the arg->arg[n] buffers are null terminated. This is the source of the problem, the buffers are not *necessarily* null terminated.

When running the example provided by Sveta, I see the old garbage characters at the end of strings when outputting using fprintf, but that is because fprintf it assuming the string is null terminated. However, the strings true lengths (args->lengths[n]) are correctly passed in to the UDF, they just aren't being used correctly in any of the example code provided.
[25 Jul 2007 22:17] Paul DuBois
You could set this to be a Documentation bug and assign it to me, and I'll check the manual to see whether this point about null-termination (the lack thereof) is clear and update the manual as necessary.
[25 Jul 2007 23:18] Damien Katz
Changing bug to Paul as a possible documentation issue per his recommendation.
[26 Jul 2007 3:30] Paul DuBois
I checked the manual and it says this:

"An argument of type STRING_RESULT is given as a string pointer plus a length, to allow handling of binary data or data of arbitrary length. The string contents are available as args->args[i] and the string length is args->lengths[i]. You should not assume that strings are null-terminated."

So it's clear that for strings, it's necessary to use the length to determine the argument length, and that you cannot assume the string is null-terminated. I think that covers the case discussed in this bug report, so I am closing the report.

Reference: http://dev.mysql.com/doc/refman/5.0/en/udf-arguments.html (see discussion of char **args)