Example of http_post communication module using mysql_udf and curl Library

Use mysql_udf and curl library to complete http_post communication module (mysql_udf, multi_curl, http, post)

This module is currently mainly used for data synchronization between the sns of xoyo and the tcsql database independently developed by kingsoft_xoyo. When a feed is inserted into the sns database, the trigger is used to call this module, send synchronous data to the tcsql database. You can also use this module to forward and synchronize data with other databases or programs using socket interfaces.
The http_post module mainly uses the mysql_udf interface and curl library.
Mysql_udf is an interface provided by mysql for C language. Through this interface, you can customize mysql functions. By calling these mysql functions, you can call the corresponding C language module to execute specific functions, implement interaction between mysql Data and external applications. The curl library is a common application-layer network protocol library. It mainly uses the curl_multi asynchronous communication api for network transmission.
First, refer to the udf_example.c file officially provided by mysql to create three main interface functions: initialization function, execution function and destructor.

Copy codeThe Code is as follows:
// Args is a parameter returned by an SQL statement, and message is used to return error information.
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
Void http_post_deinit (UDF_INIT * initid );
// Args is the parameter returned by the SQL statement, and message is the error message returned. It is recommended to use these parameters.
// Initialize the 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 );
// Destructor void http_post_deinit (UDF_INIT * initid );

In the mysql_udf interface, the main function body does not allow the use of new or malloc to dynamically allocate memory, so if you need to apply for memory space, you must use xxxx_init () the function applies and assigns the requested address to initid-> ptr pointer, then uses it in the main function body, and releases it in the xxxx_deinit destructor body. In addition, the call to the mysql_udf interface seems to encounter the double free error when the concurrency exceeds a certain degree. To avoid this error, therefore, in my program, the combination of static space and dynamic application space is used, so that if the data is small and the concurrency is large, there will be no double free error. For static application space, the maximum value is about 160000 ~ About 172.16byte, I use 160000 here. When mysql sends more data than this number, it dynamically applies for memory. The initialization function is as follows:

Copy codeThe Code is 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 & gt; 160000)
{
Int allocLength = 200 + 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;

}

Specifically, http_post_init needs to return the my_bool type. The purpose of this function is to provide users with a way to check whether the data transmitted by mysql parameters is correct. If yes, mysql returns 0, and mysql automatically calls the defined main function, if 1 is returned, mysql prints the message and exits without calling the main function. Therefore, pay attention when setting the return value.

The main function is as follows:

Copy codeThe Code is 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
Timeout. TV _sec = 0;
Timeout. TV _usec = 100000;

Char sendArray [160000] = "\ 0"; // can not move this into the if
If (initid-> ptr = NULL)
{
// Char sendArray [160000] = "\ 0"; // 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 has es 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 (& 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 curl library is used for communication. The curl library is divided into three parts. The easy mode is the synchronous mode, the multi mode is the asynchronous mode, and the share mode is the multi-thread data sharing mode.

After easy sends data, it will block the response waiting for the server. If no response is returned, it will always block. Of course, you can set a timeout, but if this time is set to small, easy transmission of big data will be interrupted, which is too large to affect the time efficiency. In addition, when the acceptor does not send response, the easy library will block waiting even if the data is sent, in some cases, the sender does not need to wait for the respons of the acceptor. After the sending is complete, the easy method is not applicable. So select the multi library.

As shown in the program, first initialize and set the easy handle to post mode. Specify the data to be post, as shown below:

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 );

To use the multi mode, you must also Initialize an easy mode and put the handle of this easy mode into the so-called multi function execution stack:

Curl_multi_add_handle (multi_handle, curl );

Use curl_multi_perform (multi_handle, & still_running) for asynchronous transmission. However, if the returned function is not CURLM_CALL_MULTI_PERFORM, re-execute the function. Until the loop while (CURLM_CALL_MULTI_PERFORM = curl_multi_perform (multi_handle, & still_running); ends. If still_running in the function body is set to 1, the connection is established and data is being sent. The select mechanism must be used for data transmission.

The curl_multi_fdset (multi_handle, & fdread, & fdwrite, & fdexcep, & maxfd) function writes the largest descriptor to maxfd,

Then run select to wait: rc = select (maxfd + 1, & fdread, & fdwrite, & fdexcep, & timeout );

Finally, if the select return value is not-1 (error) 0 (timeout), the asynchronous transmission is performed again, that is, the curl_multi_perform function is executed

Still_running is 0, and the program ends and exits.

The maximum number of executions is set here. If the server encounters a problem and cannot send response, still_running will not change to 0, and the program will be in an endless loop,

Therefore, set the maximum number of loops TRY_TIMES to prevent this situation. However, if this number is set to a small value, the server may exit after the data is not sent. If the setting is too large and the program is sent, the server will execute extra loops without the response. Therefore, this TRY_TIMES must be set based on the data size and network conditions.

The number of data transfers is slightly longer. Here, I set the number of cycles for small data to 25, and the big data cycle to 100.

The last part is the Destructor body:

Copy codeThe Code is as follows:
Void http_post_deinit (UDF_INIT * initid)
{
If (initid! = NULL & initid-> ptr! = NULL)
{
Free (initid-> ptr );
Initid-> ptr = NULL;
}
}

Releases the memory set by the initialization function.

The compilation and execution process is as follows:

Save the program as http_post.c and compile it as follows (Please adjust it according to the mysql path on the machine ):
Copy codeThe Code is as follows:
Gcc-wall-I/usr/local/webserver/mysql/include/mysql/-shared http_post.c-o http_post.so-fPIC
// Use the header file provided by mysql to generate a dynamic link library
Cp-f http_post.so/usr/local/webserver/mysql/lib/mysql/plugin/http_post.so
// Put the generated. so file into the plugin folder of mysql
// Enter mysql to install functions in the dynamic link library
Cd/usr/local/webserver/mysql/bin/mysql
./Mysql
// Enter the following command in the mysql command line:
Mysql> drop function if exists http_post;
// The purpose is to drop functions with the same name if they are installed in the system.
Mysql> create function http_post returns integer soname 'HTTP _ post. so ';
// Generate the http_post function and specify that the call source is http_post.so.
// Call the function to send post data to the specified ip address and port. Open the network debugging assistant on the host with the specified ip address before the call, and listen to the 3888 terminal.
Mysql> select http_post ('testpost. com/index. php', 'sfasaf ');

The following result is displayed in the network assistant: