Level 9
Oh, and does this mean Laravel only auto-reconnects database connections in English locales? Is everyone outside US/UK just hacking a workaround of some sort?
Apr 18, 2018
8
Level 9
How do I add a message to the list detecting a closed database connection?
So, I'm using long-running queue workers and an Azure SQL Server database. On the whole it works great, except for when the workers have not processed a job in some time.
What happens is the database connection drops after some time unused, as you would expect, but Laravel does not detect this. Instead it blindly keeps trying to use the dead database connection for every new job that is dispatched.
Azure SQL Server PDO returns this error when the connection times out:
ERROR: SQLSTATE[08S02]: [Microsoft][ODBC Driver 13 for SQL Server]SMux Provider: Physical connection is not usable [xFFFFFFFF].
It seems that Laravel detects a database connection has been lost by comparing this message with a list of messages (all in English!) that it holds here in Illuminate\Database\DetectsLostConnections:
protected function causedByLostConnection(Throwable $e)
{
$message = $e->getMessage();
return Str::contains($message, [
'server has gone away',
'no connection to the server',
'Lost connection',
'is dead or not enabled',
'Error while sending',
'decryption failed or bad record mac',
'server closed the connection unexpectedly',
'SSL connection has been closed unexpectedly',
'Error writing data to the connection',
'Resource deadlock avoided',
'Transaction() on null',
'child connection forced to terminate due to client_idle_limit',
'query_wait_timeout',
'reset by peer',
]);
}
So here is my quandary: how do I fix this? If I add the Azure message "Physical connection is not usable" to this list, then my long-running jobs have a happy time. I can submit this as a PR, so other people's long-running jobs can be in the happy place too. But what do I do in the meantime? What do I need to configure/inject/extend/whatever to get more messages into this array? Overriding that one script in the autoloader is one way, but it's not very update-safe.
Thanks.
Level 67
I haven't tried this with a trait, but you could try creating a new trait (copy the old trait and add your message to it), and binding that to the ioc container as a singleton so it will call your trait instead of the default.
In the AppServiceProvider's register() method:
$this->app->singleton(
'Illuminate\Database\DetectsLostConnections', //original
'App\Traits\DetectsLostConnections' //yours
);
It works with normal classes. I've never tried it with a trait, but you could give it a shot.
1 like
Level 9
I've never dived into the ioc container like that before. The Illuminate\Database\Connections class is very specific about needing this trait, and I would have thought the composer autoloader would just load the original every time. Or does the ioc extend the autoloader so that scripts can be overridden?
Level 67
Yes, it basically tells it "when something requests this thing, give it this other thing instead". It overrides composer autoloading.
I've done it for quite a few things, but I haven't tried it with a trait. Since a Trait isn't a class, I'm not sure it would work but it's what I thought of.
Level 67
I don't think this is possible in the ioc container since a trait isn't a class. You're most likely going to have to add the string to the actual trait until laravel merges your pull request (which I hope you do a PR)
Level 9
Yeah, I tried it, but it's using the core trait and not mine. Issue raised on the framework here, which I'll link to the PR:
https://github.com/laravel/framework/issues/23925
Thanks for the tips. maybe I can override the Connections class there and add my own trait or method to replace the trait method.
Or I could just put an autoload entry in composer.json to override that one trait. It's still a bit hacky, and I have no idea how I could extend the original trait to include anything extra that a Laravel update may bring.
Level 67
Yeah, the trait is being used in 3 core classes that I can see. So you'd have to do it in multiple places. It would be easier to just edit the trait directly. You'd just have to make sure it was still there when you upgrade laravel versions.
I suppose another option is to just restart the queue workers every x minutes/hours in a scheduled job?
Level 9
I've added the two "connection lost" messages I've seen to a PR, so hopefully that will go through at some point (hopefully to laravel 5.6).
I also found some strange connection issues with the Azure queue, and that does not have an auto-reconnect facility built in. When the connection to that drops (which happens apparently randomly) it sends an XML message to the Azure Queue library, which then passes it on to the queue worker as though it were a queued laravel job message (doh!). This starts a job with that data (doh!) which promptly dies as it attempts to JSON decode (with some deserialization thrown in) the XML+text message (doh!) and Laravel does not know it has died since it does not do so cleanly (doh!). The artisan queue worker then just hangs around for ten minutes, blocking doing nothing but waiting for the child process to finish (a child process that is already dead), before issuing an error telling me the job took too long (when it never really started, and wasn't a real job anyway, but a piece of puke that the Azure Queue threw at the worker), and then tries to read the next message from the queue - which is a DEAD LINK that has no auto-reconnect facility. DOH!
The result is, I'm telling the queue worker to restart every ten minutes (artisan queue:restart). Hopefully that will keep things up and running.
So, all around - bugs in Laravel, bugs in the MS queue handling composer package, bugs in the Azure network that is up and down like a yoyo. What a fun week :-(
Please or to participate in this conversation.