[RESOLVED] PDO Transactions Life Span

dougal85

Do transactions automatically commit or rollback after a specific period of time? does mySQL close its connection after a specific time? or maybe PHP closes the connection?

I've got quite a semi-complicated script that takes about 85 seconds to execute usually. This is due to it accessing remote files etc as that just adds time.

I keep getting errors like;

SQLSTATE[HY000]: General error: 2013 Lost connection to MySQL server during query

SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

What did I do to make mySQL go away does it just not love me anymore?

MarkR

dougal85 wrote:
Do transactions automatically commit or rollback after a specific period of time?

No, they don't.

does mySQL close its connection after a specific time?

Yes, but only if it's not doing anything (i.e. idle) for a specified period as configured in its settings. In this case, if that connection was in the middle of a transaction it would be rolled back.

or maybe PHP closes the connection?

Only at the end of the page (including if an error occurs)

I've got quite a semi-complicated script that takes about 85 seconds to execute usually. This is due to it accessing remote files etc as that just adds time.

I keep getting errors like;
SQLSTATE[HY000]: General error: 2013 Lost connection to MySQL server during query
OR
SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
What did I do to make mySQL go away does it just not love me anymore?

That generally means either the thread was killed or timed out. Neither of these should normally happen in 85 seconds.

Consult your DBA to find out what the current timeout is (hint: There are some configuration variables in "SHOW VARIABLES"). 85 seconds sounds too short - maybe you can get them to increase it.

Does this problem occur on your development server or production?

Mark

MarkR

It's possible that you may have a script on the server which kills queries which take too long - look at what's running on your server and ask your colleagues and/or your DBA to find out if such a thing exists.

MySQL does not kill running (i.e. non-idle) connections normally.

Mark

dougal85

Unfortunately, I'm working in-office on this one and the testing server here is PHP 5.0.8. ewww. So I'm annoyingly working on the live server (passworded sub-domain).

When you say mySQL will close the connection if it is idle. I think this could be possible as for the 85 seconds its not constantly executing queries and the full thing is wrapped in a transaction so I guess there could be maybe up to 30 seconds between queries. Maybe even more, I'm going to add in simpe time outputting to try and figure out whats going on.

At the moment I'm just testing the script by running it manually, ie. goign to the URL. However, once its set up as a cron job will it have different permissions? I didn't think of this before...

I'm emailing the DBA at the moment and hopefully that will help

dougal85

I just done "SHOW VARIABLES" - its quite long but here are the most interesting looking values

...
connect_timeout  	5
...
delayed_insert_timeout  	300
...
net_read_timeout  	30
net_write_timeout 	60
...
wait_timeout  	45

dougal85

Ok, I've been working on it this morning and each time its been executing in about 35-40 seconds now and its going fine. No errors. I've no idea why, maybe I'm just lucky today.

This timing makes me think its the 45 wait_timeout that will kill it, so that means if it takes abother 5 or 10 seconds it will have the same problems.

MarkR

wait_timeout will kill the connection if the script goes away for 45 seconds and does not issue any queries for that time.

This would happen, for instance, if the page was very slow and did loads of work (e.g. in memory, with a file, or writing stuff to the user's browser) which took 45 seconds.

45 seconds wait_timeout is unbelievably quick, ours is normally at least 900. The default is (I think) 8 hours or something.

Mark

dougal85

Great, thanks. I'll see if I can get them to change it... or think about restructuring my code somehow.