Sudden "MySQL server has gone away" error in PHP site
Solution 1
As I said in my update, I concluded that the problem with MySQL arises when the link to Facebook takes longer than the maximum connection time with the DB. None of the suggestions could beat this limitation, so I decided to work around it and reconnect every time I presumed the link maybe gone.
So after each call to Facebook, I used to following code:
$this->load->database(); $this->db->reconnect();
This is the particular solution when using CodeIgniter, and AFAIK the db->reconnect() function is only available since version 1.7.2 so I updated it in order to work.
Thanks everyone for your answers!
Solution 2
It's probably a connection time out affecting your persistent connections in PHP. I used to see them all of the time. The timeout parameter is within MySQL itself.
Your options include: - not using persistent connections - turning off idle timeout on the MySQL server - trapping the error
I always wrap reconnection into my own PDO class, so I can't even remember if PHP reconnects or not. In any case, it's an easy fix. On query, catch & reconnect.
I have "generated" this error in the past with InnoDB. If you're using that engine, what's the output of SHOW ENGINE INNODB STATUS
after a failure?
Related videos on Youtube
lima
Updated on April 15, 2022Comments
-
lima about 2 years
Today one of my websites started showing
Error Number: 2006 MySQL server has gone away
It's a low-traffic client site running under Apache 2.2.9 (Debian), PHP 5.2.6-1+lenny3 (using CodeIgniter 1.7.1 framework) and MySQL 5.0.51a. I obviously reasearched about the error but all the possible solutions imply that there are big queries going on that may time out and reset the connection, or hit the packet limits. However, this is not the case, it's a small database processed with the simplest queries. To be sure about this I made up a few queries to return one row, still the same error.
Database credentials are fine, I can even login directly into mysql, run some of the site's queries and get the right data instantly. There are several other sites on the same server and connections to the database, much larger sites, and they all have no problems.
I tried:
- Restarting MySQL
- Restarting the whole server
- Looking for errors in the logs (both Apache and MySQL, none)
- Checking db user permissions
- Changing
mysql.connect_timeout
anddefault_socket_timeout
in PHP - Changing
max_allowed_packet
in MySQL - Reading the official docs, forum and everything in SO that says "MySQL server has gone away"
New:
- Disabling persistent connections in PHP
- Changing
wait_timeout
andconnect_timeout
in MySQL
Update:
It seems to be related to the execution time of my script: it retrieves some info using the Facebook PHP client and this call seems to be failing randomly today, so I either have no data from Facebook or the MySQL error. But to my surprise, none of the given solutions seems to deal with the timeout.
Any ideas? thank you for your time!
-
Eric J. over 14 yearsAre Apache and MySQL on the same box? If you create a simple PHP page that runs a simple query (e.g. SELECT VERSION();) does that work?
-
lima over 14 yearsNo, I don't have any hooks in CodeIgniter or the DB
-
lima over 14 yearsDisabling persistent connections didn't make any difference, but turning off idle timeout (zeroing MySQL's wait_timeout and connect_timeout, is that right?) seems to solve the issue. However, I'm afraid this might have negative repercussions, like never-ending MySQL processes, am I correct? Also, what do you mean by "trapping the error"? I haven't done anything to the code, neither did the DB data changed, this is an overnight issue and that's the the weird thing. Thank you!
-
lima over 14 yearsOkay, the issue is still there, but I found a pattern. I'll update the question.
-
pestilence669 over 14 yearsZeroing mysql's timeouts can have negative repercussions on glitchy networks. Um, trapping the error would involve a bit of custom down in the DB handlers for your framework... or a giant catch (), connect and retry. Strange that disabling persistent connections didn't do the trick.
-
pestilence669 over 14 yearsActually... are you using replication on this MySQL box?
-
lima over 14 yearsNo, I did once just to migrate the server. I've updated the question, the culprit seems to be clearer now.
-
lima over 14 yearsThank you for your input Lucas, but there was a misunderstanding, I meant that the problem was generated overnight, meaning it was sudden with no apparent cause. The task itself (the data retrieval and db stuff) is made in almost every page-load due to Facebook TOS.
-
Lucas over 14 yearsDo you get the error instantly - ie as soon as you do a pageview? or are the cases where it fails after the script has been running a certain amount of time?
-
lima over 14 yearsIt may fail instantly or in the middle of the script, depending on the cause of the error. It's detailed in my answer now :)
-
ianace about 12 yearshi i have encountered this kind of problem and the solution is a change in the architecture of your website, you could never guarantee that facebook will reply in a definite time also the values/data taken from facebook will never be up to date. what we did is do a seperate chron job for fetching data from facebook
-
Imnotapotato about 8 yearsI have the same problem, and this doesn't work, did anything change since 2009 ?
-
Ryan over 5 yearsI have a 500-point bounty here and am wondering if you think your experience is related and could help: stackoverflow.com/q/53469793/470749