MySQL running on Windows 7 64 bit.
I'm trying to track down a leak of connections, I'm pretty sure the only ASP.NET app using the server is at fault here.
We received the "Too many connections" error and checking the server we saw many connections with status Sleep.
I know that, fiddling with my.cf, MySQL can be forced to close sleeping connection after a shorter time, but I would rather fix the actual problem instead of the symptoms.
So I was wondering if there's a way to retrieve the SQL statement that was last executed on a given connection. I think if I could do that on older sleeping connections, I might have an easier time tracking down this leak.
Of course, if anyone can think of an alternative solution for this scenario, any suggestion is very welcome.
1 Answer 1
You can enable "general query log", which will log each executed query, along with its session/connection ID, so if you grep the connection ID, you will have all the queries on one session.
HOWEVER, 'sleep'ing connections are not the cause of max connection error, but a result of it. They are not harmful to stay there. In fact, they are established connections that can be re-used by future sessions.
What would be more useful in this case is to search the slow query log, and check the innodb status to get information about locks and dead locks that have happened:
SHOW ENGINE INNODB STATUS;