Source: http://www.felix021.com/blog/read.php?2102
Yesterday @zind classmate found me before a blog (has been modified), inside mentioned mysql_ping and mysql_opt_reconnect some things.
The reason I wrote that blog is because some of the code written last year encountered "2006:mysql server has gone away" error. This is because the default value of the Wait_timeout parameter is 28800, that is, if a connection has not been requested for 8 consecutive hours, then the server side will disconnect it. In the test environment One night no request is normal ... So the next morning came up in the morning when the mistake was found.
In fact, I have considered this problem, really ... Because I know that PHP has a function called mysql_ping (), the PHP manual says: "Mysql_ping () Check the connection to the server is normal. If disconnected, the connection is automatically attempted. This function can be used for long-idle scripts to check if the server has closed the connection and reconnect if necessary. ”
In retrospect, it was a foolish and naïve past. According to the mysql_ping () document in the MySQL official C API: "Checks whether the connection to the server is working. If the connection have gone down
And auto-reconnect is enabledAn attempt to reconnect is made. ... Auto-reconnect is disabled by default. To enable it, call Mysql_options () with the Mysql_opt_reconnect option ", that is, it actually relies on mysql_opt_reconnect this configuration, And this configuration by default (since 5.0.3 start) is off!
Although it is very angry and painful to think of, but see Libmysql/client.c:mysql_init () notes in the Calm:
Referenced by default we don ' t reconnect because it could silently corrupt data (after reconnection you potentially lose table lock s, user variables, session variables (transactions but they is specifically dealt with in Mysql_reconnect ()). This is a change: < 5.0.3 Mysql->reconnect were set to 1 by default.
Well, if there's a problem, then look at it. The workaround is to call Mysql_options and set Mysql_opt_reconnect to 1:
CharValue = 1;
Mysql_options (MySQL, Mysql_opt_reconnect, &value);
But!! Before MySQL 5.0.19, mysql->reconnect = 0 was put in Mysql_real_connect ()! In other words, if you cannot do the same as other options, you must set Mysql_opt_reconnect before Mysql_real_connect (), Pit Daddy!
Well, all right, anyway, the problem with the pit is over, and the conclusion is that no matter which version, if you want to enable automatic re-connection, it's best to Mysql_real_connect () after all, it's not wrong anyway.
Then the point of this is (it seems too wordy): Mysql_opt_reconnect's documentation says, this option is used to enable/disable automatic reconnection when the connection is found, so when does MYSQL find the link to be broken?
The problem may be too big, but let's go after it, mysql_ping ().
Download source http://cdn.mysql.com/Downloads/MySQL-5.1/mysql-5.1.67.tar.gz, decompression after ctags-r, and then Vim-t Mysql_ping, immediately positioned, it seems too simple point:intStdCall
Mysql_ping (MYSQL *mysql)
{
intRes
Dbug_enter ("mysql_ping");
res= Simple_command (mysql,com_ping,0,0,0); Try sending a PING packet to the server
if(res = = Cr_server_lost && mysql->reconnect)//If the SERVER is hung, and Mysql->reconnect istrue
res= Simple_command (mysql,com_ping,0,0,0); One more ping??
Dbug_return (RES);
}
Well, it seems the key is the Simple_command. CTRL +], it turns out that:
#DefineSimple_command (mysql, command, arg, length, skip_check) \
(* (MySQL)->methods->advanced_command) (MySQL, command, 0, 0, arg, length, Skip_check,NULL)
All right, first go after MySQL, there is a const struct st_mysql_methods *methods, and then Chase St_mysql_methods ....
typedef structSt_mysql_methods
{
My_bool (*read_query_result) (MYSQL *mysql);
My_bool (*advanced_command) (MYSQL *mysql, enum Enum_server_command command,
Const unsigned Char*header,unsigned LongHeader_length,
Const unsigned Char*arg,unsigned LongArg_length,
My_bool Skip_check, mysql_stmt *stmt);
......
Hang Daddy! This Bird code again! C Language of egg ache! struct has no method but property! There's no way, only violence:
Reference Find-name ' *.c '-exec/bin/grep ' {} '-hne ' Mysql->methods *= '; '
./libmysql_r/client.c:1907:mysql->methods= &client_methods;
./sql-common/client.c:1907:mysql->methods= &client_methods;
./libmysql/client.c:1907:mysql->methods= &client_methods;
./libmysqld/libmysqld.c:120:mysql->methods= &embedded_methods;
./sql/client.c:1907:mysql->methods= &client_methods;
Decisive Chase to Client_methods:StaticMysql_methods client_methods=
{
Cli_read_query_result,/* Read_query_result */
Cli_advanced_command,/* Advanced_command */
...
That is to say, Simple_command finally called the Cli_advanced_command function. The preceding Simple_command (mysql,com_ping,0,0,0) is equivalent to calling the Cli_advanced_command (MySQL, com_ping, 0, 0, 0, 0, 0, NULL).
What does this function do? In fact, it's not complicated:
1. Set the default return value to 1 (return when error Goto is unexpected)
2. Set the Sigpipe handler (to ignore it)
3. If Mysql->net.vio = = 0, then call Mysql_reconnect to re-connect, failure will return 1
4. mysql is not ready to return 1
5. Clear the previous information (error code, buffer, affected_rows), etc.
6. Call Net_write_command to send the command to the server if it fails:
6.1 Check the error message, if it is because the sending packet is too large, goto end
6.2 Call End_server (MySQL) to close the connection
6.3 Call Mysql_reconnect attempt to re-connect if failed goto end
6.4 Call Net_write_command again to send command to server, fail Goto end
7. Set result = 0 (Sent successfully)
8. If the parameter requires checking the return of the server, read a packet to check (fails result=1)
9. (end tag)
10. Restore Sigpipe
11. Return result
As you can see, there are two calls to mysql_reconnect here, but both are conditional: the first time is in the case of Mysql->net.vio = = 0, the second time is the Net_write_command failure and not because the package is too large. Vio related code see confused, really can not find a clue, so decided to violence a bit: Directly modify this function, add a bunch of fprintf (stderr, ...) (Do not say where the specific add, anyway hard to plug it), and then wrote a C code: #include<stdio.h>
#include<stdlib.h>
#include<mysql/mysql.h>
voidDo_err (MYSQL *mysql) {
if(Mysql_errno (MySQL)) {
fprintf (stderr, "%d:%s\n", Mysql_errno (MySQL), mysql_error (MySQL));
Exit (Mysql_errno (MySQL));
}
}
intMain ()
{
MySQL * mysql = mysql_init (NULL);
Do_err (MySQL);
Mysql_real_connect (MySQL, "127.0.0.1", "root", "123456", "Test", 3306,NULL, 0);
Do_err (MySQL);
CharValue = 1;
Mysql_options (MySQL, Mysql_opt_reconnect, &value);
CharCMD[1024] = "SELECT * from T";
while(1) {
mysql_query (MySQL, cmd);
Do_err (MySQL);
Mysql_res *result = Mysql_store_result (mysql);
Mysql_row ROW;
while(row = mysql_fetch_row (result))! =NULL) {
intI, Num_fields = Mysql_num_fields (result);
for(i = 0; i < num_fields; i++)
printf("%s\t", Row[i]? Row[i]: "NULL");
Note that the previous sentence is not binary safe because the row may contain the
printf("\ n");
}
Mysql_free_result (result);
printf("press Enter ..."); GetChar ();
}
Mysql_close (MySQL);
return0;
}
Run output: Reference inside Mysql_real_query
Mysql->net.vio = 0x90e760
Mysql->status = 0
NET Write_command
After Send_query
---
1
2
Press enter...//restart MySQL server before pressing ENTER, the following sentences are manually indented according to the function call hierarchy ...
Inside Mysql_real_query
Mysql->net.vio = 0x90e760//Enter Cli_advanced_command
Mysql->status = 0
Net_write_command
End_server//Description Net_write_command failed.
Inside Mysql_reconnect//it will call Mysql_real_query
Inside Mysql_real_query
Mysql->net.vio = 0x919990//Then back to the Cli_advanced_command
Mysql->status = 0
Net_write_command//This is a success.
After Send_query//This sentence I wrote in Mysql_real_query
Reconnect succeded
After reconnect:mysql->status = 0
After Send_query//So come again.
According to the output of fprintf, it is found that under normal circumstances, the Mysql->net.vio pointer is not equal to 0, so the first mysql_reconnect will not be called. The Net_write_command is also executed correctly, and the second reconnect is not called.
And after executing a query, restart MySQL server and then execute query (mysql_query = Mysql_real_query = Mysql_send_query = Cli_advanced_ command), you will find that Mysql->net.vio is still not equal to 0, but Net_write_command failed, so the first call to End_server () (which will mysql-> Net.vio is set to 0, but does not affect the subsequent process ... ), and then call the second reconnect, this reconnect will call Mysql_init () and Mysql_real_query () to perform some initialization of the command, and then back to the Cli_advanced_command, step by step backtracking ...
In conclusion, if Mysql_opt_reconnect () is set, then mysql_query () can be automatically re-connected. In fact, since Cli_advanced_command will invoke mysql_reconnect if necessary (in fact this function is only called here), all the places where cli_read_query_result are used (or Simple_ command), you can also complete automatic re-connection.
End.
Bastard, this article is purely to make up for January at least one of this goal ah!
--
Turn http://blog.chinaunix.net/uid-22957904-id-3594136.html
Reprint please indicate from
http://www.felix021.com/blog/read.php?2102, the original source is indicated in the reproduced text, thank you:)
Google Reader Subscription
Click here, RSS address:
http://www.felix021.com/blog/feed.php。
Mysql_ping () and mysql_opt_reconnect MySQL server has gone away "error
