CODEWITHSUNDEEP
phpmysqllinuxdatabase-connection
Continuation
Continuation

Reputation: 13040

How to tell if any MySQL connections has been dropped or timed out?

A client is using PHP to connect to MySQL. The PHP scripts and the MySQL database are located on 2 different Linux servers. He complained that database connections were being dropped or timed out and asked me to take a look.

Is there any place in MySQL that can show me what and how many connections have been dropped or timed out? I looked into slow query log and didn't see anything.

Any suggestions on how to diagnose this dropped/timed out database connection problem?

Thanks

Upvotes: 2

Views: 3710

Answers (2)

MarkR
MarkR

Reputation: 63558

You can certainly monitor the 

Aborted_clients

Status variable (i.e. with SHOW GLOBAL STATUS) and see connection timeouts.

However, the best place to detect this is on the client-side. Assuming you have proper error handling and logging on the client side, you'll generally see the famous "Server has gone away" error.

As your application is written in PHP, there is a likelihood that it doesn't have proper error handling / logging. You should fix that.

A common cause of this is the abuse of persistent connections from an Apache prefork server. Because Apache prefork servers generally keep processes around for a long time doing no work, but persistent connections can remain open from each process, the connections can end up being idle for too long, creating the "Morning bug".

The fix for this is simple - just turn OFF persistent connections. They are an inappropriate pessimisation in almost every case.

Upvotes: 5

RobertPitt
RobertPitt

Reputation: 57268

In regards to the server logs you can visit http://dev.mysql.com/doc/refman/5.0/en/server-logs.html for more information

Within PHP:

  • The link is a resource that can be passed into [mysql_ping], this would return true / false depending on the network status.

For example:

if(mysql_ping($link))
{
    //Still Connected.
}

Upvotes: 3

Related Questions