Technology sharing | MySQL C API parameter MySQL_ OPT_ READ_ Some behavior analysis of timeout


Author: Dai Yuebing

MYSQL_ OPT_ READ_ Timeout is a parameter used to set the read timeout in MySQL C API client. In the official MySQL document, this parameter is described as follows:

  • MYSQL_OPT_READ_TIMEOUT (argument type: unsigned int *)

    The timeout in seconds for each attempt to read from the server. There are retries if necessary, so the total effective timeout value is three times the option value. You can set the value so that a lost connection can be detected earlier than the TCP/IPClose_Wait_Timeout value of 10 minutes.

That is to say, when needed, the actual timeout will be three times the set value. But after the actual test, it is found that the actual timeout is consistent with the set timeout. However, when the triple timeout occurred was not found in the document. So the source code of MySQL 5.7.20 is analyzed. Use GDB debugging code to find the code that actually communicates with MySQL server, as follows:

Technology sharing | MySQL C API parameter MySQL_ OPT_ READ_ Some behavior analysis of timeout

Among them, Vio_ In the read() function, recv and poll are used to read messages and time out. net_ should_ The retry() function returns true only if Eintr occurs. From this code, it is consistent with the test results, and the read is not retried three times. It is only retried if the read operation is interrupted by a system interrupt, but there is no limit on the number of retries. From the analysis of the above code, we can see that the logic of the code does not match the description of the document. So after a search, I found a MySQL bug (bug ා 31163). This bug reports that in MySQL 5.0, the actual timeout time of MySQL C API reading is three times of the set value, which is consistent with the description of existing documents. So the code of MySQL 5.0.96 is analyzed. Also use GDB to find the communication part of the code. This time, we found the code to try again three times, as follows:

Technology sharing | MySQL C API parameter MySQL_ OPT_ READ_ Some behavior analysis of timeout

The read / write timeout of this version of the MySQL API is set directly using the setsockopt. In the first loop, the first timeout occurs at point a (although the annotation is non blocking, the client’s connection is always in blocking mode). Then set the socket to blocking mode at point B, and reset the number of retries at point C. Since the alarm is set, the loop after the second time will directly enter the branch of point D and determine the number of cycles. As a client, net > retry_ The count is always 1, so it is retried twice. After three vioreads, the function exits from point E. From the above analysis, it can be seen that the description of this parameter in MySQL document is out of date_ OPT_ READ_ Timeout does not have a triple timeout problem. The processing result in bug ා 31163 also updates the description of this parameter in the document to the actual read timeout time which is three times of the set time. Maybe the maintainers of MySQL forget to update the document when they update the later version.

Recommended Today

Oracle scheduled tasks

Timing task query To query Oracle scheduled tasks, you can use: –Scheduled tasks for all users SELECT * FROM dba_jobs; –Timing task of the user select * from user_jobs; In the query results, the what field generally stores the name of the stored procedure (or the specific stored procedure content). Broken = n indicates that […]