• Manually reconnect MySQL in CakePHP 3
  • The error case
  • First attempt
  • Final solution
  • The End

#Manually reconnect MySQL in CakePHP 3

We have encountered a weird issue in our CakePHP 3 application recently. It is a trick solution and we decide to blog about it in case anyone else needs it.

#The error case

We developed a queue system to generate reports for some parts of our application. And the worker is running as a long running process (CakePHP shell). We had weird issues after deploying the queue to live server. Jobs were processed perfectly initially. But after one day, all reporting jobs would fail. If I restart the queue, they will work as normal again. They fail again after one day.

From the error log file, we found some error messages:

Exception: SQLState[HY000]: General error: 2006 MySQL server has gone away in [xxx/vendor/cakephp/cakephp/src/Database/Statement/MysqlStatement.php line 36]

The error above indicates it has something to do with the database part of our system.

#First attempt

As you might have guessed. Our first attempt is of cause to find answers on Google. All links on the 1st page of Google tells us that, we need to increase the values for max_allowed_packet and wait_timeout.

However nothing worked after updating those values.

#Final solution

We spent days trying to find the cause. We are sure it has something to do the database connection. We did not have a clue until we read the source code of CakePHP 3. There is a ticket on CakePHP 3 repository indicating that CakePHP 3 does not re-connect MySQL if it fails (https://github.com/cakephp/cakephp/issues/3423).

We finally realised the cause:

Once a CakePHP 3 shell is started, it uses one single database connection until the process is died. Our worker is a long CakePHP shell running process. Once it is started, it will never be shut down on purpose. If no job is sent to the worker, the MySQL connection will keep open and stay idle. When that idle is greater than wait_timeout. The exception is raised.

We can adjust the wait_timeout setting. But we can not set it to stay forever. We need a better solution.

The solution is to re-connect MySQL if an exception is raised. To do this, we can do this simply by three lines as shown below:

?

$connection = ConnectionManager::get('default');
$connection->disconnect();
$connection->connect();

#The End

Hopefully this simple tutorial helped you with your development. If you like our post, please follow us on Twitter and help spread the word. We need your support to continue. If you have questions or find our mistakes in above tutorial, do leave a comment below to let us know.