Framework: DB::reconnect() doesnt close old connection causing eventual too many DB connections errors

[ctadlock]

In my system the jobs are run via the new daemon functionality. I have one job that creates many sub jobs (hundreds), so I can easily end up with a lot of jobs to process in the queue. When I switched to using the new daemon feature I ran into an issue where the MySql connection would timeout "MySQL server has gone away". Per the docs I added code to reconnect to the database before the job runs. Now I have a different issue. I quickly max out the number of database connections on the server. Looking at the DB::reconnect() code it tries to disconnect from the database before creating a new connection. However you can see from the MySql monitoring that these connections are never actually released.

Perhaps the PDO object is still being referenced somewhere preventing the connection from actually being close?

[taylorotwell]

Would you mind running a test and changing the DatabaseManager@disconnect function set $this->connections[$name] = null before the unset occurs and see if that fixes the issue?

[ctadlock]

That did not fix the issue.

[ctadlock]

Would be nice if there was a way to force the disconnection. Trusting that some code doesn't have a reference isn't a good idea.
https://bugs.php.net/bug.php?id=62065

From reading the comments here, it looks like there might be an issue with needing to explicitly set the statement object to null well when done. The example given is with PostgreSQL. Where is that code at?
http://php.net/manual/en/pdo.connections.php

[ctadlock]

Looks like the Connection class is holding onto a reference to the PDO. That might need to be null'd somewhere.

Also, if the statement really needs to be set to null there are several places in the Connection class that would need to be modified.

[taylorotwell]

When you tested it did you have a bootstrap/compiled.php file in place that might have been hiding your changes?

On August 3, 2014 at 4:21:01 PM, Craig Tadlock ([email protected]) wrote:

Looks like the Connection class is holding onto a reference to the PDO. That might need to be null'd somewhere.

Also, if the statement really needs to be set to null there are several places in the Connection class that would need to be modified.

—
Reply to this email directly or view it on GitHub.

[ctadlock]

I made the change in the bootstrap/compiled.php file.

[ctadlock]

Drupal had this exact issue...
https://www.drupal.org/node/1791602

[taylorotwell]

So are you using persistent connections in PDO?

On August 3, 2014 at 6:06:26 PM, Craig Tadlock ([email protected]) wrote:

Drupal had this exact issue...
https://www.drupal.org/node/1791602

—
Reply to this email directly or view it on GitHub.

[ctadlock]

No. I meant they also had an issue hunting down all the references to the PDO connection in order to let it disconnect.

[taylorotwell]

OK I'm with you now and have it recreated on my machine.

Ideally I think this shouldn't even be a user concern. We need to figure out a way to just make the detection of a lost connection and a reconnection transparent, etc.

[taylorotwell]

This issue is fixed. No special reconnect code is needed anymore. The connection will detect that error, reconnect, and try the query again automatically.

[taylorotwell]

Can confirm fix by using "dev" stability of 4.2 if you want.

[crynobone]

Had an issue on a production code with PDO::prepare(): MySQL server has gone away recently (increased on disk IO etc) and for some reason the it cause "zombie process" with load of php artisan queue:work. This seem to have solve the problem.

[ctadlock]

I've deployed this and watched it for a couple days. I can confirm its fixed.

[mpskovvang]

I'm still having this issue in Lumen 5.3.

After digging into the code I found the reason might be that reconnect functionality is only handled within the _run_ method. In my case the issue happens as I call $db->beginTransaction();.

A solution might be calling the _tryAgainIfCausedByLostConnection_ method inside the _beginTransaction_ method?

[lukevear]

I've encountered this issue with laravel 5.3 as well (using Forge to run the database queue worker).

The trace as per rollbar:

PDOException: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
    "/home/forge/my-project/vendor/laravel/framework/src/Illuminate/Database/Connection.php" line 608 in beginTransaction
    "/home/forge/my-project/vendor/laravel/framework/src/Illuminate/Database/Connection.php" line 608 in beginTransaction
    "/home/forge/my-project/vendor/laravel/framework/src/Illuminate/Queue/DatabaseQueue.php" line 175 in pop
    "/home/forge/my-project/vendor/laravel/framework/src/Illuminate/Queue/Worker.php" line 173 in getNextJob
    "/home/forge/my-project/vendor/laravel/framework/src/Illuminate/Queue/Worker.php" line 143 in runNextJob
    "/home/forge/my-project/vendor/laravel/framework/src/Illuminate/Queue/Worker.php" line 76 in daemon
    "/home/forge/my-project/vendor/laravel/framework/src/Illuminate/Queue/Console/WorkCommand.php" line 100 in runWorker
    "/home/forge/my-project/vendor/laravel/framework/src/Illuminate/Queue/Console/WorkCommand.php" line 83 in fire
    "<internal>" line 0 in call_user_func_array
    "/home/forge/my-project/bootstrap/cache/compiled.php" line 1272 in call
    "/home/forge/my-project/vendor/laravel/framework/src/Illuminate/Console/Command.php" line 169 in execute
    "/home/forge/my-project/vendor/symfony/console/Command/Command.php" line 256 in run
    "/home/forge/my-project/vendor/laravel/framework/src/Illuminate/Console/Command.php" line 155 in run
    "/home/forge/my-project/vendor/symfony/console/Application.php" line 820 in doRunCommand
    "/home/forge/my-project/vendor/symfony/console/Application.php" line 187 in doRun
    "/home/forge/my-project/vendor/symfony/console/Application.php" line 118 in run
    "/home/forge/my-project/vendor/laravel/framework/src/Illuminate/Foundation/Console/Kernel.php" line 122 in handle
    "/home/forge/my-project/artisan" line 35 in <main>

[slowkow]

I'm also running into this issue with laravel 5.2.

I have systemd waiting 3 seconds after a queue worker fails, then restarting the worker. Sometimes, I get this error showing up in my log every 3 seconds, and it keeps printing this error for several hours.

tail storage/logs/laravel.log -n100 | grep '^\['
[2016-11-30 07:30:48] production.ERROR: PDOException: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away in /var/www/html/mysite.com/vendor/laravel/framework/src/Illuminate/Database/Connection.php:576
[2016-11-30 07:30:50] production.ERROR: PDOException: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away in /var/www/html/mysite.com/vendor/laravel/framework/src/Illuminate/Database/Connection.php:584
[2016-11-30 07:30:51] production.ERROR: PDOException: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away in /var/www/html/mysite.com/vendor/laravel/framework/src/Illuminate/Database/Connection.php:576
[2016-11-30 07:30:51] production.ERROR: PDOException: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away in /var/www/html/mysite.com/vendor/laravel/framework/src/Illuminate/Database/Connection.php:576

After reading stackoverflow posts, I have a hunch that this happens because the workers don't talk to the mysql server for more than 8 hours. (Probably no one is using my site at night.)

I set the timeout to be 24 hours instead of 8 hours, and I hope that these errors will stop.

# /etc/mysql/my.cnf
interactive_timeout = 86400
wait_timeout = 86400

mysql> set wait_timeout = 86400;
Query OK, 0 rows affected (0.00 sec)

mysql> set interactive_timeout = 86400;
Query OK, 0 rows affected (0.00 sec)

[iget-esoares]

@slowkow Did you solved this? I'm getting like 500 or more errors per day on my queue daemon...

[slowkow]

@iget-esoares Yes, adjusting the timeout to 24 hours instead of 8 hours has solved the problem. I don't see the error anymore.