MySQL server has gone away — ErrorException
So I did a bunch of research and 99% of the users on the forum say you can use the mysql_ping command to check for a connection, so I put this in place:
if(!mysql_ping($this->sDBLink)) Connect(true); Now I get the same error, just in reference to the mysql_ping function instead of the mysql_query function:
Fatal error: Uncaught exception 'ErrorException' with message 'mysql_ping(): MySQL server has gone away' How do I reliably check that a connection still exists? mysql_ping throws an exception.
From what I understand, it means the connection has timed out. I create my connection early on in the program. When I try use the same connection later on it gives this error.
Is there a reason you’re not using a PDO or mysqli object instead? They’ve deprecated this extension and I’ve never seen this error using either of the other two.
You need to trap an error on the connection, ping only works if its established a connection to the server. If you trap the error on the connection then you can abort application loading and flag a nice connection error instead.
2 Answers 2
Why does a connection time out? Because it has been unused for a period of time longer than a particular threshold. This kind of thing is necessary for all kinds of network applications to make them resilient. What if a user abruptly switches off a client machine while it is connected to a MySQL server? The server needs to be able to drop the client connection after a while.
This kind of thing is inherent in network programming. It’s a feature, not a bug.
What can you do about this?
You can switch to a more modern mysql connection management library that handles this stuff.
Especially if your client software gets used infrequently, you can reorganize your software to connect to MySQL, use the connection, and disconnect. That way you won’t need a persistent connection. But that’s impractical if your client server gets used a lot; there’s a lot of overhead to establishing a connection.
You can use the connection regularly. mysql_ping uses the connection without actually doing any server work. So would a query that said something like SELECT 1 . If you ping the connection every minute or so, then a two minute timeout won’t cause your MySQL server to conclude that your client has gone away and disconnect it.
You can handle the ErrorException you’re getting correctly, by trying to re-establish the connection instead of just blowing out your program with an error message. Use PHP code something like this:
try < some operation on the mysql connection. >catch (ErrorException $ex)
Sudden «MySQL server has gone away» error in PHP site
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!
Are 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?
@Eric J: Yes, and surprisingly for me, yes to the 2nd one too, it seems to be a problem carried by the framework then..
Any chance you could post the generated query output? I assume this is going through CodeIgniter. Are you using InnoDB or an exotic code page, by any chance?
@Pestilence Yeah I could but it’s just some rows with not much ado. I’m using MyISAM and UTF-8 on all dbs/tables.
4 Answers 4
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!
hi 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
I have a 500-point bounty here and am wondering if you think your experience is related and could help: stackoverflow.com/q/53469793/470749
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?
Disabling 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!
Zeroing 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.
No, I did once just to migrate the server. I’ve updated the question, the culprit seems to be clearer now.
Since the other sites work, we can assume that it is related to your site (and not the server). The official documents mention that this might happen if you try to execute queries after the server connection is closed. Do you have any hooks in CodeIgniter that does something towards the end of a request where the database connection might be closed?
You mention that this is an overnight task and that you’re getting FaceBook data etc. Is the process running for a long time?
I’ve had a recent script that is moving data from one cluster to another and transforming formats etc. This script runs 24/7 moving this data across. I’ve found that with or without persistent connections, the MySQL libraries will still drop out after a few minutes (sometimes 5 minutes, sometimes longer).
The only way I’ve found to get around it for my case was to put a time check in my wrapper and to check how long it’s been since the last time I reconnected before executing the query. I’ve set it to check if it’s been greater than 2 minutes and if so, to reestablish the connection, ensuring to set the «new_link» flag (4th param) on mysql_connect is set to false.
Since changing this, I’ve never had the same error again.
Executing mysqli_stmt->execute() causes «MySQL server has gone away» error
However, I have set the timeout and the maximum packet size variable to their maximum values, and the query is simply selecting from an empty table. There’s no reason why either of those should be a problem. I also verified from Python— the server can be connected to and queries should be able to be executed. It even works fine from phpMyAdmin.
On the PHP documentation for mysqli_stmt::prepare , it says this regarding the error:
- I’m using mysqlnd on Linux, and it’s not supposed to give this error when the statement is longer than max_allowed_packet
- I have already mentioned how I have set max_allowed_packet to the variable’s maximum value.
If you would like for me to give more information such as my SQL server or PHP configuration, let me know what you need.
One article I read said to use mysqli->ping() to check how the connection’s doing, and it appears to be fine until I call mysqli_stmt->execute() .
I’m fairly certain it’s a problem with my implementation— I tried reinstalling the web server and the MySQL server, switching PHP versions, and I even tried switching hosts. But despite my attempts to fix this, I continue to get the error.
host = $chost; $this->name = $cname; $this->user = $cuser; $this->pass = $cpass; mysqli_report(MYSQLI_REPORT_ALL); $this->mysqli = new mysqli($this->getHost(), $this->getUsername(), $this->getPassword(), $this->getName()); > /* closes the connection to the database */ function close() < return $this->getMySQLi()->close(); > /* returns a query object for the given parameters */ function query($query, $type='', . $params) < $statement = $this->getMySQLi()->prepare($query); if(strlen($type) != 0) < // bind parameters to query $statement->bind_param($type, . $params); > /* * stackoverflow readers: this the debug code * I mentioned to check the connection * */ if ($this->getMySQLi()->ping()) < printf ("Our connection is ok!\n"); >else < printf ("Error: %s\n", $this->getMySQLi()->error); > return new Query($statement); > /* getter functions */ function getMySQLi() < return $this->mysqli; > function getHost() < return $this->host; > function getName() < return $this->name; > function getUsername() < return $this->user; > function getPassword() < return $this->pass; > > /* define the query class */ class Query < private $statement; private $result; /* constructor, sets variables and stuff */ function __construct($statement) < $this->statement = $statement; > /* executes the statement */ function execute() < $status = $this->getStatement()->execute(); $this->result = $this->getStatement()->get_result(); return $status; > /* closes the statement */ function close() < return $this->getStatement()->close(); > /* returns the number of results */ function countRows() < return $this->getResult()->num_rows; > /* getter functions */ /* returns the statement object */ function getStatement() < return $this->statement; > /* returns the result object */ function getResult() < return $this->result; > function getRow() < return $this->getResult()->fetch_assoc(); > /* returns the result in an array */ function getRows() < $rows = array(); while($row = $this->getRow()) < $rows[] = $row; >return $rows; > > ?> So. My question is, is there a problem with my implementation? How can the problem be mitigated? Is it a problem with the SQL server or PHP?
Edit: Here’s how I’m using the Database class ( getConnection() simply returns a new Database instance)
function getUsers() < $query = getConnection()->query('SELECT * FROM `users`'); $query->execute(); return $query->getRows(); > Solving «MySQL server has gone away» errors
I have written some code in PHP that returns the html content from .edu domains. A brief introduction is given here: Errors regarding Web Crawler in PHP The crawler works fine when the number of links to crawl are small (something around 40 URLS) but I am getting «MySQL server has gone away» error after this number. I am storing html content as longtext in MySQL tables and I am not getting why the error arrives after at least 40-50 insertions. Any help in this regard is highly appreciated. Please note that I have already altered the wait_timeout and max_allowed_packet to accomodate my queries and the php code and now I don’t know what to do. Please help me in this regard.
5 Answers 5
You might be inclined to handle this problem by «pinging» the mysql server before a query. This is a bad idea. For more on why, check this SO post: Should I ping mysql server before each query?
The best way to handle the issue is by wrapping queries inside try/catch blocks and catching any database exceptions so that you can handle them appropriately. This is especially important in long running and/or daemon type scripts. So, here’s a very basic example using a «connection manager» to control access to DB connections:
class DbPool < private $connections = array(); function addConnection($id, $dsn) < $this->connections[$id] = array( 'dsn' => $dsn, 'conn' => null ); > function getConnection($id) < if (!isset($this->connections[$id])) < throw new Exception('Invalid DB connection requested'); >elseif (isset($this->connections[$id]['conn'])) < return $this->connections[$id]['conn']; > else < try < // for mysql you need to supply user/pass as well $conn = new PDO($dsn); // Tell PDO to throw an exception on error // (like "MySQL server has gone away") $conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION ); $this->connections[$id]['conn'] = $conn; return $conn; > catch (PDOException $e) < return false; >> > function close($id) < if (!isset($this->connections[$id])) < throw new Exception('Invalid DB connection requested'); >$this->connections[$id]['conn'] = null; > > class Crawler < private $dbPool; function __construct(DbPool $dbPool) < $this->dbPool = $dbPool; > function crawl() < // craw and store data in $crawledData variable $this->save($crawledData); > function saveData($crawledData) < if (!$conn = $this->dbPool->getConnection('write_conn') < // doh! couldn't retrieve DB connection . handle it >else < try < // perform query on the $conn database connection >catch (Exception $e) < $msg = $e->getMessage(); if (strstr($msg, 'MySQL server has gone away') < $this->dbPool->close('write_conn'); $this->saveData($val); > else < // some other error occurred >> > > >