Jump to content

Question regarding "2006 MySQL server has gone away"


monollonom
 Share

Recommended Posts

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 by monollonom
Added link to the github issue
Link to comment
Share on other sites

@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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 !)

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...