Wednesday, May 14, 2014

CakePHP database connection gone while executing a Cron job [solved]

I was working on a CakePHP website that had a script constantly running in background. This script is launched every 10 minutes using Cron and it has a loop that keeps going infinitely until the next launch. It attempts to read some IDs from a Redis key-value store, and then fetches the associated data from the MySQL database.

The problem I got into was that sometimes the script wasn't able to fetch the records from the database, even though they were there. I was confused, because the script worked fine when run using the command line, but not as a Cron task.

Long story short, the database kept going away due to the connection timeout. It didn't matter whether the script was started automatically or manually. It worked when database was used right after starting the script, however, it would disconnect after 5 minutes (300 seconds) of 'empty' looping and would not reconnect automatically if new data came in later.

I found a couple similar problems on Stack Overflow and the suggested solution was to add the following code to the script:

$this->ModelName->getDatasource()->reconnect();

However, the reconnect() method didn't work without parameters, therefore I had to tweak the code even further. Here's the code with some context:

<?php
class MailerShell extends Shell {
  function mail() {
    // some code...

    $db =& ConnectionManager::getDataSource('default');

    while(time() < $start_time + $time_limit) {
      // some code...
      $email = $this->Email->find('all', 'conditions' => array('Email.id' => $job_id));
      if($email === false) { // database connection error
        $error = $db->lastError();
        if($error == "2006: MySQL server has gone away") {

          $db->reconnect($db->config);

        }
      }
      // some code...
    }
  }
}

Basically, I just point the $db variable to the database object and later call its reconnect() method, providing it with its own config array. The code that actually solves the problem in the above excerpt is the following:

$db =& ConnectionManager::getDataSource('default');
$db->reconnect($db->config);

Happy coding :)

P.S. The CakePHP version for this project is 1.3.

No comments:

Post a Comment