MySQL has gone away…

Hello guys,
You must be working from home I guess, well, me too.
So while working on a new module of the project. I came across a situation where we needed to regularly push data from MySQL to Elasticsearch and send emails as well. No wonder we cannot keep user waiting after submitting a form. So I decided to implement Advanced Message Queuing Protocol using RabbitMQ. Since we are using PHP, here the PHP implementation for the protocol.
You can learn more here about the implementation.

Overview

This is what a sample sender file code looks like this

<?php

require_once __DIR__ . '/vendor/autoload.php';
use PhpAmqpLib\Connection\AMQPStreamConnection;
use PhpAmqpLib\Message\AMQPMessage;

$connection = new AMQPStreamConnection('localhost', 5672, 'guest', 'guest');
$channel = $connection->channel();

$channel->queue_declare('hello', false, false, false, false);

$msg = new AMQPMessage('Hello World!');
$channel->basic_publish($msg, '', 'hello');

echo " [x] Sent 'Hello World!'\n";

$channel->close();
$connection->close();
?>

Here is what receiver code file looks like:

<?php

require_once __DIR__ . '/vendor/autoload.php';
use PhpAmqpLib\Connection\AMQPStreamConnection;

$connection = new AMQPStreamConnection('localhost', 5672, 'guest', 'guest');
$channel = $connection->channel();

$channel->queue_declare('hello', false, false, false, false);

echo " [*] Waiting for messages. To exit press CTRL+C\n";

$callback = function ($msg) {
    echo ' [x] Received ', $msg->body, "\n";
    
    // CALL A DATABASE MODEL
    $this->load->model("yourmodel") ;

    // GET SOME RECORDS
    $data = $this->yourmodel->get_data() ;
    if($data){
        // send emails
    }
};

$channel->basic_consume('hello', '', false, true, false, false, $callback);

while ($channel->is_consuming()) {
    $channel->wait();
}

$channel->close();
$connection->close();
?>

My problem was on the receiver. So the code worked fine for few hours and then the process dropped. I checked the console and there it was “MySQL has done away“. After trying different methods for some days, I was able to finally fix the problem.

The problem was…

I am using Codeigniter for the project and the problem was pretty basic in nature. The MySQL connection that was set while executing the code was not automatically closed by Codeigniter. Since the code never exited from the loop..

while ($channel->is_consuming()) {
    $channel->wait();
}

The connection to database was never closed. Now MySQL has default wait_timeout of 8 hours. If the connection is not used MySQL simply drops it. You do not need to change this configuration to solve the error.

The solution is…

The solution was simple. You disconnect from database when your task is done and then reconnect to database when you need something from database. For this solution, your callback should look like this:

$callback = function ($msg) {
    echo ' [x] Received ', $msg->body, "\n";
    

    /* reconnect to database */
    if ($this->db->conn_id === FALSE) {                
         $this->load->database();
         $this->db->reconnect();
    }
    
    // CALL A DATABASE MODEL
    $this->load->model("yourmodel") ; 

    // GET SOME RECORDS
    $data = $this->yourmodel->get_data() ;
    if($data){
        // send emails
        // do other tasks
    }

    // code the database connection
    $this->db->close();

};

Hope this helps.

Cover Photo by Talia Cohen on Unsplash

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.