CakePHP 2.10 - MySQL reconnection after server stopped

Need Help
1

Hi everyone.

I use a cli launched cakephp (2.10) shell to create an infinite loop, delayed each second, that check in db for new rows (notification) and do an action, based on row data.

I’m faced with a problem that sometimes, mysql server is not available (can’t work on this part). I’m simulated this part with a service mysql stop / start.

So i want to handle the problem and reconnect if it happen.

Here is the code :

App::uses('NotificationsController', 'Controller');
App::uses('AppController', 'Controller');
App::uses('ConnectionManager', 'Model');

class NotificationDispatcherShell extends AppShell {
    private $n_h=null;
    private $app_h=null;
    private $time_step=1;//(1s)

    public function start(){
        $this->n_h = NotificationsController::instance();
        $this->app_h = new AppController();
        $this->app_h->_log('debug', __CLASS__.'->'.__FUNCTION__, 'process');

        while(true){
            sleep($this->time_step);
            $this->get_new();
        }
    }

    private function get_new(){
        try{
            // get all new notifications
            $data = $this->n_h->_getAll([
                    'order'=>['timestamp ASC'],
                    'conditions'=>['is_new'=>1]
                ]
            );

            // if there is data
            if (!empty($data)){
                foreach($data as $line){
                    //do things
                }
            }
        }
        catch(PDOException $err){
            $this->app_h->_log('debug', 'error with mysql connection', 'process');
            $this->app_h->_log('debug', $err->getMessage(), 'process');
        }
    }
}

Without doing anything in the catch part, i got that infinite log without script crash, event if i do back
service mysql start :

2019-03-13 10:21:15 Debug: NotificationDispatcherShell->start
2019-03-13 10:21:19 Debug: error with mysql connection
2019-03-13 10:21:19 Debug: SQLSTATE[08S01]: Communication link failure: 1053 Server shutdown in progress
Warning Error: Error while sending QUERY packet. PID=7267 in [[...]/lib/Cake/Model/Datasource/DboSource.php, line 472]

2019-03-13 10:21:21 Warning: Error while sending QUERY packet. PID=7267 in [[...]/lib/Cake/Model/Datasource/DboSource.php, line 472]
2019-03-13 10:21:21 Debug: error with mysql connection
2019-03-13 10:21:21 Debug: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
2019-03-13 10:21:23 Debug: error with mysql connection
2019-03-13 10:21:23 Debug: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

I tried to add a reconnect in the catch part :

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

I got another message and a script crash :

2019-03-13 10:27:53 Debug: NotificationDispatcherShell->start
2019-03-13 10:27:57 Debug: error with mysql connection
2019-03-13 10:27:57 Debug: SQLSTATE[08S01]: Communication link failure: 1053 Server shutdown in progress
Error: Database connection "Mysql" is missing, or could not be created.
#0 [...]/lib/Cake/Model/Datasource/DboSource.php(286): Mysql->connect()
#1 [...]/app/Console/Command/NotificationDispatcherShell.php(78): DboSource->reconnect()
#2 [...]/app/Console/Command/NotificationDispatcherShell.php(46): NotificationDispatcherShell->get_new()
#3 [...]/lib/Cake/Console/Shell.php(459): NotificationDispatcherShell->start()
#4 [...]/lib/Cake/Console/ShellDispatcher.php(219): Shell->runCommand('start', Array)
#5 [...]/lib/Cake/Console/ShellDispatcher.php(66): ShellDispatcher->dispatch()
#6 [...]/app/Console/cake.php(47): ShellDispatcher::run(Array)
#7 {main}

What is the right way to handle it ?

2

Depending on the system your application is being run, you can e.g. let your process die, build a bash-script that checks wether your application is running and simply start it if not.
You can then e.g. executed this bash-script every minute using a cronjob.

3

It’s a linux based host system.
A cronjob based solution is one i thought about.
But a job that check a job instead of handling by framework tools, if exist, isn’t relevant in my mind (even if is a technical solution). I prefer try to understand what happened in framework.
That’s why i asked ;).

Thanks dmuenstermann for tips.

5

$db = ConnectionManager::getDataSource('default'); $db->reconnect();
and
service mysql restart

Both are different. One reconnects to the database and other reboots the database management system.

If your MySQL dies intermittently then you may want to prevent it by checking dmesg and / or mysql.err. If it is OOM - Out of Memory, you may want to add a swap space.

if it is PDO that drops the connection then you need better memory management with php.ini.

6

Thanks @karmicdice.
I will check if i find something with your tips.