Question regarding "2006 MySQL server has gone away"

[monollonom]

Hi again,

The reason I went for creating a hook generating image variations when uploading was to avoid the "reload the front-end page until you have all your variations generated", because each time I was getting this error "2006 MySQL server has gone away". But it kept happening, even with the splitted ajax calls.

I do generate a lot of variations (3 sizes + webp variants for 10+ images per page), so I understand it takes a lot time for Imagick (and memory, both of which I increased) but I'm assuming it shouldn't impact MySQL, especially since the connection is supposed to be closed / recreated up to three times if you get this error. However, reading the code, I can see the connection is correctly closed but is it actually recreated on the next try ?

It's obviously getting too technical to me, but my question would be: if a PDOStatement is executed and the connection is closed, does it try to reconnect by itself ? Or I am right assuming there is someting missing here ? Like a new call to the prepare() function, which then recreate the pdo ? It's mostly a question for @ryan but maybe someone else could tell me ?

Many thanks in advance !

Edit: Github issue created

Edited January 10, 2021 by monollonom
Added link to the github issue

[netcarver]

@monollonom If you are using ProcessWire's database (ie $this->database, wire('database') or wire()->database depending on context) then yes, it should connect again on the next query.

What might be more interesting is trying to find out why the MySQL server is going away. Do you have any processes (like your API stuff) that are forking by any chance? If this is the case, the code in the forked process gets a copy of the parent state - including the pdo driver state - and when the child process uses that state to access MySQL, the DB server can close the connection, leading to "2006 MySQL server has gone away" for both the child and the parent processes.

I discovered this running PW-bootstrapped code from forked PHP processes in some of my own installations where I have background workers processing various jobs.

I do not know for sure why MySQL closes the connection, but my working hypothesis is that it closes it when it sees it being accessed from a different process ID. The cure, however, is fairly easy in these cases as you just make sure the first thing your forked code/background worker does is close the db connection - forcing the child process to create a new connection on the next database call which it then uses for the remainder of its operation, and leaving the parent process' connection intact and operational as well. Taking this action in my forked code totally cleared up these "2006" errors for me.

[monollonom]

Hi @netcarver,

Could you point me out how to check if my code is forking a process ? You're mentioning IDs, from which object could I print it to Tracy ?

To me, I am just doing a single thing (create variations) so I don't really see why it would fork.

Trying further, and to reproduce the problem using in a simpler case, I managed to trigger a "2006 MySQL server has gone away" using this in the Tracy console :

$page->of(false);
$title = $page->title;
$page->title = "Test";
$page->save();
sleep(60);
$page->title = $title;
$page->save();
return "ok";

I'm making several tests in the WireDatabasePDO file to see if I can get something out of it.

[monollonom]

Since I'm able to reproduce the error after exactly 60 seconds, this clearly indicates that the wait_timeout MySQL settings of my server is set to 60. A fix could be to increase it, but it doesn't change the fact that the execute function from WireDatabasePDO does not work as advertised. I mean, it does retry to execute the query, but since it's tied to the previously failed PDO connection, it doesn't work as expected.

What could be done is to re-prepare the query after the creation of a new PDO connection. However, since you can't access the binded values from the PDOStatement, an idea could be to create a custom PDOStatement (like the one used for debugging), that would keep a track of binded values so that if you recreate the query, you can re-bind the values and thus re-execute the complete query.

(at least from my understanding based on the informations I gathered, I might be completely wrong !)

[netcarver]

You'd know if your processes were forking as you'd be doing it yourself, or running software like php-resque that does it for you, so what I described above probably isn't the cause in your case.

[netcarver]

If you think you've found a bug in WireDatabasePDO, please do open an issue for it in the processwire-issues repository.

[monollonom]

I just wanted to be sure that it wasn't me just trying too hard to find a bug for my problem, but I will go ahead and fill one on Github.

Thanks again.

Edit: we'll see how it goes

Edited January 10, 2021 by monollonom
Added link to the github issue