Swoole Project Thinking Transformation-MySQL server gone away

MySQL as the golden Partner of PHP and the most widely used database on the Internet, inevitably deal with it every day, many friends in the familiar with the use of Swoole, also a lot of pits, ready to combat, and finally on-line, is a pleasant experience swoole brought great improvements, Suddenly the database operation Bug, a large number of MySQL server gone away, so swooler in the heart of the grass and the horse Pentium, scold, swoole mistakenly i ~ ~ ~

Wait!!! This is really not a swoole problem!!!! It's not Swoole's problem!!! It's not Swoole's problem!!! (important thing to say three times)

Reason

It's not Swoole's problem, so what's his reason?

This is from the MySQL mechanism, MySQL itself is a multi-threaded program, each connection, will open a thread to deal with the relevant query, so MySQL to avoid taking up a hole in the wool, will periodically recover a long time without any query connection (time period by wait_ Timeout configuration effect), so in Swoole, because is a long memory-based service, we have established a MySQL connection, do not actively shut down or in the way of Pconnect, then this MySQL connection will be kept, and then long time without interaction with the database, On the initiative by the MySQL server shut down, and then continue to use this connection, reported MySQL server gone away.

Solution Solutions

know the cause of the problem, it can be the right remedy.

Scenario 1: Modify the MySQL wait_timeout value to a very large value.

This method is not advisable, may produce a large number of sleep connections, resulting in a maximum MySQL connection, is not recommended to use.

Scenario 2: Active connection detection before each query

If using mysqli, the built-in mysqli_ping can be used

Example:

if (! $mysqli->ping ()) {

Mysqli->connect (); reconnects

}

If it is PDO, you can detect the server information of MySQL server to determine:

try {

$pdo->getattribute (\pdo::attr_server_info);

} catch (\exception $e) {

if ($e->getcode () = = ' HY000 ') {

$pdo = new PDO (XXX); reconnects

} else {

Throw $e;

}

}

But this solution has one drawback: one more request, so the improved method: Use a global variable to hold the last query time, the next time the query is compared with the current times, more than waite_timeout re-connected. Or you can also use Swoole_tick timing detection.

Scenario 3: Passive detection, each query with a try catch packet, if there is a MySQL gone away exception, then reconnect, and then execute the current SQL.
Example:

try {

Query ($sql);

} catch (\exception $e) {

if ($e->getcode () = = ' HY000 ') {

Reconnect (); reconnects

Query ($sql)

} else {

Throw $e;

}

}

Scenario 4: With a short connection, be sure to manually close after each operation

Additional question: Why does MySQL need a connection pool?

Swoole Project Thinking Transformation-MySQL server gone away