Solution of MySQL server has gone away caused by worker resident in the background in gearman

Time:2021-6-2

This paper describes the solution of MySQL server has gone away caused by worker resident in the background in gearman. The details are as follows:

The main reasons are as follows:

1. MySQL service down
2. No operation for a long time, more than wait_ When setting timeout, MySQL will automatically disconnect
3. MySQL request link is actively killed
4. If the sent request or returned result is too large, Max can be set_ allowed_ The value of packet
5. In the program, you operate the database through a single instance. If the two operations take longer than wait_ timeout。

For the convenience of demonstration, set wait_ The timeout time is 10 seconds


> set global wait_timeout=10;
> show global variables like 'wait_timeout';

The code of worker.php is as follows:

<?php
//Create a worker
$worker = new GearmanWorker();
//Add a job service
$worker->addServer('127.0.0.1', 4730);
//Register a callback function for business processing
$worker->addFunction('longTime', function($job) {
  //Workload () gets the serialized data sent by the client
  $data = json_decode($job->workload(), true);

  $db = new mysqli('192.168.1.100', 'root', '', 'test');
  if($db->connect_error) {
    die('connect error');
  }

  //Wait 15 seconds, we set MySQL wait_ The timeout is 10 seconds
  sleep(15);

  //When the execution reaches this point, MySQL will automatically disconnect the connection because of the timeout
  $ret = $db->query("INSERT INTO test VALUES(NULL, {$data['title']}, {$data['content']});");
  if($ret) {
    Return "insert successfully";
  }
});

//Dead cycle
//Tasks waiting for job submission
while($worker->work());

The client.php code is as follows:

<?php
//Create a client
$client = new GearmanClient();
//Add a job service
$client->addServer('127.0.0.1', 4730);

//Do normal synchronization
$ret = $client->doNormal('longTime', json_encode(array(
  'title '= >' I am the title ',
  'content' = > 'I am content',
)));

echo $ret;

The result of execution is shown in the figure below. MySQL server has gone away appears.

General solution:

1. Turn up wait_ Value of timeout (not recommended)
2. Each time you operate the database, Ping () once, and if you disconnect, connect again.
3. On wait_ Within the timeout interval, we query from time to time (‘select 1 + 1 ‘); Let MySQL know that the connection is still alive.

Modify the worker.php code as follows:

<?php
//Create a worker
$worker = new GearmanWorker();
//Add a job service
$worker->addServer('127.0.0.1', 4730);
//Register a callback function for business processing
$worker->addFunction('longTime', function($job) {
  //Workload () gets the serialized data sent by the client
  $data = json_decode($job->workload(), true);
  
  $db = new mysqli('192.168.1.100', 'root', '', 'test');
  if($db->connect_error) {
    die('connect error');
  }
  
  //Wait 15 seconds, we set MySQL wait_ The timeout is 10 seconds
  sleep(15);

  //ini_set('mysqli.reconnect', 1);
  //According to the PHP manual, Ping will try to re connect, but the reality is that it won't
  //$db->ping();

  if(!$db->ping()) {
    $db->close();
    //Reconnecting to the database
    $db = new mysqli('192.168.1.100', 'root', '', 'test');
  }

  $ret = $db->query("INSERT INTO test VALUES(NULL, '{$data['title']}', '{$data['content']}');");
  if($ret) {
    Return "insert successfully";
  }
});

//Dead cycle
//Tasks waiting for job submission
while($worker->work());

The results are as follows

For more information about PHP, readers interested in this site can see the following topics: summary of PHP process and thread operation skills, summary of PHP network programming skills, introductory course of PHP basic syntax, complete collection of PHP array operation skills, summary of PHP string usage “PHP + MySQL database operation tutorial” and “PHP common database operation skills summary”

I hope this article is helpful for PHP programming.