MySQL connection problems

When using PHP to process databases, we may encounter various problems, such as having no permissions. You only need to correct these problems according to the error prompt; however, when using background scripts to process the business logic, the processing time is long because the data volume to be processed is large, at this time, the importance of the database connection time is reflected.

The database connection parameters are mainly timeout configurations in MySQL, such as wait_timeout, interval_timeout, and connnect_timeout. Detailed descriptions of these parameters can be obtained from the Internet, so I will not copy them directly. We usually modify these values when optimizing MySQL to reduce sleep connections. For example, if it is set to 10 s, of course, this will not have any impact on the front-end PHP processing, because you cannot let the user wait for more than 10 seconds for processing a page. However, these long-time connections to the backend will produce some unexpected things.

 

Here I will list some of my problems here:

1. it is mainly to use a unique connection in the entire program, and the connection needs to be maintained for at least half an hour to one hour. In this process, the connection time is insufficient and the connection is automatically disconnected, at this time, the program failed to get the connection, or the previous connection exists, but it is already an invalid connection resource, so the program crashes in this place.

2. The following is also a problem: directly write the error message:

PHP warning: mysql_connect (): Lost connection to MySQL server at 'reading initial communication packet ', system error: 111

 

Analyze the problem. Here we will just summarize the problem. The errors may vary according to your program's writing method. For example, nested recursion exists in the program for more than one hundred times, this is also because each connection cannot be connected.

In this case, the solution is to set the timeout time a little longer. In addition, you can disable the invalid connection and re-request to generate a new connection.

 

Here, we also need to note that when using memcache, it is also because you use an instance for a long time (You encapsulate the memcahe operation), which may easily lead to operation failure, in this case, we can generate several more or execute them three times in a loop.

If you encounter similar problems, please contact me.