Use the mysql_udf and curl libraries to complete the Http_post Communication module Example _mysql

Complete the Http_post Communication module (mysql_udf,multi_curl,http,post) using MYSQL_UDF and curl libraries

This module is currently mainly used for the xoyo of SNS and Kingsoft_xoyo independent research and development of the TCSQL database to do data synchronization, when there is a feed into the SNS database, using triggers to call the module, to the Tcsql database to send synchronized data. You can also use this module to forward and synchronize with other databases or programs that use the socket interface.
The Http_post module mainly uses the Mysql_udf interface, and the Curl Library two parts technology.
MYSQL_UDF is a MySQL for C language to provide an interface, through this interface, users can customize the MySQL function, by invoking these MySQL functions, call the corresponding C language module to perform specific functions, the implementation of MySQL data and external application interaction. Curl Library is a more common Application Layer network Protocol library, which mainly uses the Curl_multi asynchronous communication API, which is used for network transmission.
First, refer to the udf_example.c file provided by MySQL, establish 3 main interface functions, namely initialization function, execute function and destructor function.

Copy Code code as follows:

Args is a parameter that is returned by the SQL statement, and the message is to return error messages using these are all prescribed.
My_bool Http_post_init (Udf_init *initid, Udf_args *args, Char *message);
Main function Body
Longlong http_post (Udf_init *initid, Udf_args *args, char *is_null,char *error);
destructor function body
void Http_post_deinit (Udf_init *initid);
Args is the parameter returned by the SQL statement, message is to return error information, use these are all good.
Initialize function body My_bool http_post_init (udf_init *initid, Udf_args *args, Char *message);
Main function Body Longlong http_post (udf_init *initid, Udf_args *args, char *is_null,char *error);
The destructor function body void Http_post_deinit (Udf_init *initid);

In the MYSQL_UDF interface, the main function body is not allowed to use new or malloc dynamic allocation of memory, so if you need to apply for memory space, you must use the Xxxx_init () function to request and assign the address of the request to the Initid->ptr pointer, and then use in the main function body, And is released in the Xxxx_deinit destructor body. In addition, the call to the MYSQL_UDF interface seems to be more than a certain amount of concurrency, if the use of dynamic allocation of memory, there will be double free error, in order to avoid this error, so in my program using static space and dynamic application space combined way, so if the data is small, The concurrency is large and no double free error occurs. For static application space, the most about around 160000~170000byte, I use here 160000, when the MySQL transfer data is greater than this number, only dynamic application of memory. The body of the initialization function is as follows:

Copy Code code as follows:

My_bool Http_post_init (Udf_init *initid, Udf_args *args, Char *message)
{
if (Args->arg_count!= 2)
{
strcpy (Message, "wrong arguments to http_post; ");
return 1;
}

if (Args->arg_count = = 2 && args->args[1]!=null)
{
int flexiblelength = strlen (args->args[1]);

if (Flexiblelength > 160000)
{
int alloclength = + flexiblelength;
if (!) ( Initid->ptr= (char*) malloc (alloclength))
{
strcpy (Message, "couldn ' t allocate memory in Http_post_init");
return 1;
}
return 0;
}
Else
{
initid->ptr=null;
}

}
return 0;

}

Where Http_post_init needs to return the My_bool type. The purpose of this function is to give the user a way to verify that the data passed in by the MySQL parameter is correct, if the correct return 0, then MySQL will automatically call the defined main function, if returned 1, then the MySQL print message information exit, will not call the main function. So be sure to pay attention when setting the return value.

The main functions are as follows:

Copy Code code as follows:

Longlong http_post (Udf_init *initid, Udf_args *args,
Char *is_null __attribute__ ((unused)),
Char *error __attribute__ ((unused))
{
char* Sendbuffer=null;
CURL *curl;
CURLM *multi_handle;
int still_running;
int times=0;//try times If select False
int try_times=25;
struct Timeval Timeout;//set A suitable timeout to play around with
timeout.tv_sec = 0;
Timeout.tv_usec = 100000;

Char sendarray[160000] = "//can";
if (initid->ptr = NULL)
{
Char sendarray[160000] = "n";//error
Sendbuffer=sendarray;
}
Else
{
Sendbuffer = initid->ptr;
try_times=100;
}

strcpy (Sendbuffer,args->args[1]);
Curl = Curl_easy_init ();
Multi_handle = Curl_multi_init ();
if (Curl && multi_handle)
{
/* What URL that receives this POST * *
Curl_easy_setopt (Curl, curlopt_url,args->args[0]);
Curl_easy_setopt (Curl, curlopt_httppost, 1);
Curl_easy_setopt (Curl,curlopt_postfields,sendbuffer);
Curl_multi_add_handle (Multi_handle, curl);
while (Curlm_call_multi_perform = = Curl_multi_perform (multi_handle,\ &still_running));
while (still_running && times< try_times)
{
int RC; Select () Return code
int maxfd;
Fd_set Fdread;
Fd_set Fdwrite;
Fd_set Fdexcep;
Fd_zero (&fdread);
Fd_zero (&fdwrite);
Fd_zero (&AMP;FDEXCEP); Get file descriptors from the transfers
Curl_multi_fdset (Multi_handle, &fdread, &fdwrite, &fdexcep,\ &maxfd);
rc = Select (Maxfd+1, &fdread, &fdwrite, &fdexcep, &timeout);
Switch (RC)
{
Case-1://select Error
Break
Case 0:
Default://Timeout
while (Curlm_call_multi_perform!== curl_multi_perform (Multi_handle, &still_running));
Break
}
times++;
}//end while
Curl_multi_remove_handle (Multi_handle,curl);
Curl_multi_cleanup (multi_handle);//always cleanup
Curl_easy_cleanup (curl);
if (times>=try_times)
{
return 1;
}
return 0;
}//end if
return 1;
}

In the main function, the main use of Curl Library for communication, Curl library into 3 parts, easy is synchronous mode, multi is asynchronous mode, share mode is the mode of multithreading sharing data.

Easy to send out the data, will block waiting for the server response, if not returned, it will always block, of course, you can set a timeout, but if this time is small, easy to send large data will be interrupted, set too much impact time efficiency, in addition, when the receiving end Do not send response, easy library even after sending the data, will block the waiting, sometimes for the sender does not need to wait for the receiver side of the respons, when the send finished can be completed, this time easy is not applicable. So finally choose Multi Library.

As the program shows, first initialize and set the easy handle to post mode, specifying the data that needs to be post, as follows:

Curl = Curl_easy_init ();

Multi_handle = Curl_multi_init ();

Curl_easy_setopt (Curl, curlopt_url,args->args[0]);

Curl_easy_setopt (Curl, curlopt_httppost, 1);

Curl_easy_setopt (Curl,curlopt_postfields,sendbuffer);

Because you want to use the multi mode, you must also initialize an easy mode and put the handle of this very easy mode into the so-called multi function execution stack:

Curl_multi_add_handle (Multi_handle, curl);

The asynchronous transfer is performed using Curl_multi_perform (Multi_handle, &still_running), but if the function does not return a curlm_call_multi_perform, it needs to be rerun. Until the loop while (Curlm_call_multi_perform = = Curl_multi_perform (Multi_handle, &still_running)); At this point, if the still_running in the function body is set to 1, indicating that the connection was established, the data is being sent. The select mechanism needs to be sent with the data.

function Curl_multi_fdset (Multi_handle, &fdread, &fdwrite, &fdexcep, &maxfd); writes the largest descriptor to the MAXFD,

Then wait with select: rc = Select (Maxfd+1, &fdread, &fdwrite, &fdexcep, &timeout);

Finally, if the select return value is not 1 (error) 0 (timeout), the asynchronous transfer is performed again, that is, the Curl_multi_perform function is executed until

The still_running is 0, and the program ends the exit.

This set a maximum number of executions limit, if the server has a problem, can not send response, then still_running will not change to 0, the program will die Loop,

So, set a maximum cyclic number of try_times to prevent this from happening. But this number set small, the data may not be sent out, then quit, such as the setting is too large, the program sent out, the server without response will be more than the implementation of redundant loops. So this try_times needs to be set based on the size of the data and network conditions, more than normal

The number of transmitted data is slightly longer. Here I am small data cycle set 25 times, large data loop set to 100.

Finally, the destructor body:

Copy Code code as follows:

void Http_post_deinit (Udf_init *initid)
{
if (initid!=null && initid->ptr!=null)
{
Free (INITID->PTR);
Initid->ptr = NULL;
}
}

Frees the memory set by the initialization function.

The compile execution process is as follows:

Save the program as HTTP_POST.C compiled as follows (please adjust according to the MySQL path on the machine):

Copy Code code as follows:

gcc-wall-i/usr/local/webserver/mysql/include/mysql/-shared Http_post.c-o Http_post.so-fpic
Generate a dynamic link library using the header file provided by MySQL
Cp-f http_post.so/usr/local/webserver/mysql/lib/mysql/plugin/http_post.so
Put the generated. So file in the MySQL plugin folder
Go to MySQL to install functions in a dynamic-link library
Cd/usr/local/webserver/mysql/bin/mysql
./mysql
Under the MySQL command line, enter the following command:
mysql> DROP FUNCTION IF EXISTS http_post;
The purpose is to install the advanced drop of the same name function in the system.
mysql> CREATE FUNCTION http_post RETURNS INTEGER soname ' http_post.so ';
Generates the Http_post function and indicates that the calling source is http_post.so.
The last Call function is to send post data to the specified IP and port. Before calling, open the Network debugging assistant on the specified IP host and listen on the 3888-terminal.
Mysql> Select Http_post (' testpost.com/index.php ', ' SFASFA ');

You can see the following results in your network assistant: