Mysql_ping () and MYSQL_OPT_RECONNECT MySQL server has gone away "error, mysqlgoneaway

Mysql_ping () and MYSQL_OPT_RECONNECT MySQL server has gone away "error, mysqlgoneaway
Source: http://www.felix021.com/blog/read.php? 2102
Yesterday @ Zind found my previous blog (modified), which mentioned mysql_ping and MYSQL_OPT_RECONNECT.

The reason for writing this blog is that some code written last year encountered the "2006: MySQL server has gone away" error. This problem occurs because the default value of the wait_timeout parameter is 28800. That is to say, if a connection has no request for eight consecutive hours, the Server will disconnect it. In the test environment, it is normal to have no requests one night ...... So the next morning, I found this error.

Actually, I have considered this issue ...... Because I know that there is a function in php called mysql_ping (), the PHP manual says: "mysql_ping () checks whether the connection to the server is normal. If it is disconnected, the system automatically tries to connect. This function can be used for idle for a long time to check whether the server has closed the connection. If necessary, connect again ."

In retrospect, it was silly and naive. According to the MySQL official c api mysql_ping () document: "Checks whether the connection to the server is working. If the connection has gone downAnd 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 depends on the configuration MYSQL_OPT_RECONNECT, and this configuration is disabled by default (starting from 5.0.3!

Although I was angry and angry, the comments in libmysql/client. c: mysql_init () were relaxed:
Reference By default we don't reconnect because it cocould silently encrypt upt data (after reconnection you potentially lose table locks, user variables, session variables (transactions but they are specifically dealt with in mysql_reconnect ()). this is a change: <5.0.3 mysql-> reconnect was set to 1 by default.

Well, if there is a problem, face it. The solution is to call mysql_options and set MYSQL_OPT_RECONNECT to 1:
Char value = 1;
Mysql_options (mysql, MYSQL_OPT_RECONNECT, & value );

But !! Before mysql 5.0.19, mysql-> reconnect = 0 was placed in mysql_real_connect! That is to say, if you cannot process other options, you must set MYSQL_OPT_RECONNECT before mysql_real_connect!

Okay. In short, the question about pitfall has come to an end. The conclusion is that, in whatever version, if you want to enable automatic reconnection, it is best that it will not be wrong after mysql_real_connect.

Then the focus of this article is (it seems to be a little too long): MYSQL_OPT_RECONNECT documentation said that this option is used to enable/disable (when the connection is found to be disconnected) Automatic reconnection, so when will MYSQL find the link disconnected?

This problem may be too big, but you may want to catch up with what mysql_ping () has done.

Download the source code http://cdn.mysql.com/Downloads/MySQL-5.1/mysql-5.1.67.tar.gz, decompress the ctags-R, and then vim-t mysql_ping, immediately located, it seems too simple point: int STDCALL
Mysql_ping (MYSQL * mysql)
{
Int res;
DBUG_ENTER ("mysql_ping ");
Res = simple_command (mysql, COM_PING, 0, 0); // try to send a ping packet to the server.
If (res = CR_SERVER_LOST & mysql-> reconnect) // if the server is down, mysql-> reconnect is true.
Res = simple_command (mysql, COM_PING, 0, 0); // ping again ??
DBUG_RETURN (res );
}

Well, the key is simple_command. Ctrl +:
# Define simple_command (mysql, command, arg, length, skip_check )\
(* (Mysql)-> methods-> advanced_command) (mysql, command, 0, 0, arg, length, skip_check, NULL)

Okay, let's catch up with MYSQL first. There is a const struct st_mysql_methods * methods in it, and catch up with st_mysql_methods ....
Typedef struct st_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 long header_length,
Const unsigned char * arg, unsigned long arg_length,
My_bool skip_check, MYSQL_STMT * stmt );
......
Ah! This is another bird code! Cool C language! Struct has only attributes. No method is available! No way. It can only be violent:
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. c: 120: mysql-> methods = & embedded_methods;
./SQL/client. c: 1907: mysql-> methods = & client_methods;

Decisively catch client_methods: static MYSQL_METHODS client_methods =
{
Cli_read_query_result,/* read_query_result */
Cli_advanced_command,/* advanced_command */
...

That is to say, simple_command finally calls the cli_advanced_command function. Previously simple_command (mysql, COM_PING, 0) is equivalent to calling cli_advanced_command (mysql, COM_PING, 0, 0, 0, 0, 0, 0, NULL ).

What has this function done... In fact, it is not complicated:
1. Set the default return value to 1 (returned when a goto error occurs)
2. Set handler of sigpipe (to ignore it)
3. If mysql-> net. vio = 0, call mysql_reconnect to reconnect. If the connection fails, 1 is returned.
4. mysql is not ready. 1 is returned.
5. Clear the previous information (error code, buffer, affected_rows) and so on.
6. Call net_write_command to send the command to the server. If the command fails:
6.1 check the error message. If the sending package is too large, go to end
6.2 call end_server (mysql) to close the connection
6.3 call mysql_reconnect to try again. If the goto end fails
6.4 call net_write_command again to send the command to the server. If the command fails, go to end.
7. Set result = 0 (sent successfully)
8. If the parameter needs to check the server's return, read a packet and check it (if it fails, result = 1)
9. (end label)
10. Restore sigpipe
11. Return result

We can see that mysql_reconnect is called twice, but it is conditional: The first time is in mysql-> net. when vio = 0, net_write_command fails for the second time, not because the package is too large. Vio-related code seems confused, so I cannot find a clue, so I decided to be a little violent: directly modify this function and add a bunch of fprintf (stderr ,...) (I will not talk about where it is added, but it will work hard anyway.) Then I wrote a C code: # include <stdio. h>
# Include <stdlib. h>
# Include <mysql/mysql. h>

Void do_err (MYSQL * mysql ){
If (mysql_errno (mysql )){
Fprintf (stderr, "% d: % s \ n", mysql_errno (mysql), mysql_error (mysql ));
Exit (mysql_errno (mysql ));
}
}

Int main ()
{
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 );

Char value = 1;
Mysql_options (mysql, MYSQL_OPT_RECONNECT, & value );

Char cmd [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 ){
Int I, num_fields = mysql_num_fields (result );
For (I = 0; I <num_fields; I ++)
Printf ("% s \ t", row [I]? Row [I]: "NULL ");
// Check whether the previous sentence is binary secure, because the row may contain \ 0 or \ 0 at the end.
Printf ("\ n ");
}

Mysql_free_result (result );
Printf ("press enter..."); getchar ();
}
Mysql_close (mysql );
Return 0;
}

Running 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 statements are manually indented according to the function call level ......
Inside mysql_real_query
Mysql-> net. vio = 0x90e760 // enter cli_advanced_command
Mysql-> status = 0
Net_write_command
End_server // indicates that net_write_command has failed.
Inside mysql_reconnect // It will call mysql_real_query
Inside mysql_real_query
Mysql-> net. vio = 0x919990 // then return to cli_advanced_command
Mysql-> status = 0
Net_write_command // succeeded this time
After send_query // I wrote this sentence in mysql_real_query.
Reconnect succeded
After reconnect: mysql-> status = 0
After send_query ..

According to the fprintf output, it is found that under normal circumstances, the pointer mysql-> net. vio is not equal to 0, so the first mysql_reconnect will not be called. Net_write_command is also correctly executed, and the second reconnect is not called.

After a query is executed, restart the mysql server and run the query (mysql_query => mysql_real_query => mysql_send_query => cli_advanced_command). mysql-> net. vio is still not equal to 0, but net_write_command fails. Therefore, end_server () is called first (mysql-> net. vio is set to 0, but does not affect the subsequent process ...), then, the second reconnect is called. This reconnect will call mysql_init () and mysql_real_query () to execute some initialization commands, so it will return to cli_advanced_command and trace back one step...

In conclusion, if MYSQL_OPT_RECONNECT () is set, mysql_query () can be automatically reconnected. In fact, cli_advanced_command will call mysql_reconnect if necessary (in fact, this function is only called here). Therefore, all the places where cli_read_query_result is used (or simple_command ), you can also perform automatic reconnection.

End.

// Asshole, this article is purely for at least one goal in January!

--

Turn http://blog.chinaunix.net/uid-22957904-id-3594136.html

Reprinted please indicate from Http://www.felix021.com/blog/read.php? 2102If the source is reprinted, the original source is indicated. Thank you :)
Google Reader subscription Click here, RSS address: Http://www.felix021.com/blog/feed.php.
Why does MySQL generate the MySQL server has gone away error when I delete the table?

Have you checked the online information? I think some methods are feasible.
Www.jb51.net/article/23791.htm
 
Insert 20 thousand data records into a mysql table at the same time. Is the error "MySQL server has gone away" displayed? What is the situation?

Interactive_timeout is the timeout for handshaking between the client and the server. If you perform a large operation, the handshaking takes a little longer. Too long is not good, sometimes there will be an endless loop operation, the handshake will continue to hold until the system resources are exhausted. Default Value of the parameter: 28800 seconds (8 hours)

Wait_timeout: When the handshake is complete and the wait time exceeds the set value, the connection process is automatically closed and the resources occupied are released. Default Value of the parameter: 28800 seconds (8 hours)

Show variables like '% timeout % ';

You can run the preceding command to check the current settings of the two values.
You can also modify it in my. cnf or my. ini.
Interactive_timeout = 120
Wait_timeout = 5

Not too long