MySQL Bugs: #40036: Connector "lost" pooled connections.

Bug #40036	Connector "lost" pooled connections.
Submitted:	15 Oct 2008 8:25	Modified:	24 Sep 21:12
Reporter:	Moshe Lampert
Status:	Verified
Category:	Connector/Net	Severity:	S2 (Serious)
Version:	6.0.3	OS:	Any
Assigned to:	Reggie Burnett	Target Version:
Tags:	pooling, NET
Triage:	D2 (Serious)

Description:
I am using mysql last connector with mysql 5.0.20 @ debian. two Windows/IIS servers whith
2 similar websites.

all connections closed with Conn.close / Conn.dispose

when I enabled pooling (pooling=true;Connection Lifetime=10;), I see after a while a
"sleep" connections for long time.

after a half an hour - I recived error:
System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to
obtaining a connection from the pool. This may have occurred because all pooled
connections were in use and max pool size was reached. 

I can kill the sleeped connections but it doesn't help. when I recycled App Pool - all
connections closed and it's leaks connections again (slowly but safely).

When I disabled pooling - all works, but the polling make all website faster.

Windows 2003, IIS6, ASP.net 2

How to repeat:
some heavy website (100 asp.net requests/sec) with pooling=true.

(fix a mistake)

Hello Moshe and thanks for your report. I am puzzled as to what do you expect from me now?
To build-up "heavy" web site and test? If not then there must be a test case but I don't
see it attached.

However, I feel this needs some investigation but don't know where to start...

My testcase is super-simple.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles
Me.Load
	Dim Conn As New MySqlConnection("server=localhost;user id=root; password=...;
database=...; pooling=true;")
		Conn.Open()

(sorry, 'enter' pressed)

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles
Me.Load
	Dim Conn As New MySqlConnection("server=localhost;user id=root; password=...;
database=...; pooling=true;")
	Conn.Open()

	Dim Cmd As New MySqlCommand("select * from homepage", Conn)
	Dim oReader As MySqlDataReader = Cmd.ExecuteReader()
	While oReader.Read
		' do something / do nothing
	End While
	oReader.Close()
	Cmd.Dispose()
	Conn.Close()
	Conn.Dispose()
End Sub

(sorry, 'enter' pressed)

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles
Me.Load
	Dim Conn As New MySqlConnection("server=localhost;user id=root; password=...;
database=...; pooling=true;")
	Conn.Open()

	Dim Cmd As New MySqlCommand("select * from homepage", Conn)
	Dim oReader As MySqlDataReader = Cmd.ExecuteReader()
	While oReader.Read
		' do something / do nothing
	End While
	oReader.Close()
	Cmd.Dispose()
	Conn.Close()
	Conn.Dispose()
End Sub

(sorry, 'enter' pressed)

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles
Me.Load
	Dim Conn As New MySqlConnection("server=localhost;user id=root; password=...;
database=...; pooling=true;")
	Conn.Open()

	Dim Cmd As New MySqlCommand("select * from homepage", Conn)
	Dim oReader As MySqlDataReader = Cmd.ExecuteReader()
	While oReader.Read
		' do something / do nothing
	End While
	oReader.Close()
	Cmd.Dispose()
	Conn.Close()
	Conn.Dispose()
End Sub

sleeped connections

Attachment: Untitled.gif (image/gif, text), 21.71 KiB.

(Sorry, 'enter' pressed - my keyboard need to replaced!)

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles
Me.Load
	Dim Conn As New MySqlConnection("server=localhost;user id=root; password=...;
database=...; pooling=true;")
	Conn.Open()

	Dim Cmd As New MySqlCommand("select * from homepage", Conn)
	Dim oReader As MySqlDataReader = Cmd.ExecuteReader()
	While oReader.Read
		' do something / do nothing
	End While
	oReader.Close()
	Cmd.Dispose()
	Conn.Close()
	Conn.Dispose()
End Sub

I'm used Microsoft's Web Applications Stress Tool to make a 100 requests/sec and after a
while I get this:

after a some weeks of testing (Windows 2003 - IIS6, Windows Vista - IIS7), my conclusion
is that connection get re-used 20-30 times (with 1-2 secs) and after a while "losted".

when I set  "Connection Lifetime" to 1 sec, I see much less losted connections.

show full processlist (lifetime=10) result:
1040, 'a7', 'localhost:57158', 'a7new', 'Sleep', 226, '', ''
1041, 'a7', 'localhost:57164', 'a7new', 'Sleep', 203, '', ''
1042, 'a7', 'localhost:57177', 'a7new', 'Sleep', 183, '', ''
1044, 'a7', 'localhost:57188', 'a7new', 'Sleep', 126, '', ''
1045, 'a7', 'localhost:57220', 'a7new', 'Sleep', 103, '', ''
1046, 'a7', 'localhost:57222', 'a7new', 'Sleep', 83, '', ''
1048, 'a7', 'localhost:57248', 'a7new', 'Sleep', 26, '', ''
1049, 'a7', 'localhost:57258', 'a7new', 'Sleep', 3, '', ''
1050, 'a7', 'localhost:57259', 'a7new', 'Sleep', 3, '', ''
1051, 'root', 'localhost:57260', 'a7new', 'Query', 0, '', 'show full processlist'

Moshe, I think the problem is in IIS pooling. Can you please try and shut it down (again,
not c/NET but IIS pooling) and retest as IIS is partly responsible for pooling mechanisms
and I want to check if it does not release connections from pool properly.
Again, probably stale connections are not released from pool by IIS and/or you are not
checking them before reuse and/or pool can not accommodate such high traffic and/or MySQL
server is configured so that it can't give you requested number of connections (you should
be able to find out which scenario is in effect with netstat and "show processlist") .

Moshe,

If your web server is heavily loaded you should consider increasing the "max pool size"
connection string parameter, which is 100 by default. For the environments with medium
and low database activity the default value is ok, but in your case the pool can exhaust
too quickly.
Thanks.

IIS pooling/webgarden disabled.

Max pool size is not the problem in this server (without pooling, show full processlist
shows 10-12).
I think the problem is in the connector itself, while connections still opened when
"Connection Lifetime" is reached, and the max pool (any pool, 300 for example) will be
reached.

(I checked 5.2.4, 5.2.5 - same problem)

I have a WindowsForms application running using connector for .NET, just sitting MySQL
next to my app on the same machine, and I have the very same problem that Moshe
explains.
You can use the code he provided in his last post, and it will fail on winforms
application too.
I also made a debug on the sources and I could see that the error is because when a new
connection is requested this test fails:

if (!HasIdleConnections)   <--- This condition FAILS
    driver = CreateNewPooledConnection();  <--- So it creates a new connection
else
    driver = CheckoutConnection();  <--- Instead of reusing an existing one

(located in GetPooledConnection function on MySQLPool.cs, line 150)

and it fails because idlePool is empty, but MySQL Administrator shows THERE ARE idle
connections, so due to that fact, connector makes the decision to create a new connection
and therefore the idle connection is wasted and never recovered because the reference to
the connection has been lost.
I couldn't figure out the condition that makes idlePool to be empty when there still are
connections available (understanding all of connector code was out of scope to me)

The error is present in connector 6 too. I tried both versions and fails the same way.

Screenshot showing the error described

Attachment: ScreenShot.JPG (image/jpeg, text), 131.89 KiB.

Antonio, "SLEEP" word has mislead you. Connection to MySQL server that is in "SLEEP" state
actually *waits* for something from server. So please do not mix c/NET or IIS pooled
connection state with MySQL server connection state.

As I am not convinced there is no bug here, it's still in analyzing.

Let's say I'm wrong, due to the fact connections are sleeping and waiting for something to
happen instead of IDLE.
But anyway, connection pooling means reusing connections, the connections shown are
connections that were used, then they were closed/disposed and are supposed to go back to
the pool.
Well that's something that doesn't happen.  If I continue executing the application, a
lot more "sleeping" connections appear until I reach max pool size and then MySQL refuses
further connections with error "Too many connections"

Ok, they are not idle, they're sleeping, but why??
I closed/disposed every connection (I need to use many connections since connector
doesn't support MARS) so why they sleep instead of getting idle?
Why connection pool doesn't reuse them?
You say they are waiting for something in server, please tell me what can possibly be
waiting a connection that was closed? Why connection pooling are not  reusing them?

As you can see, maybe the names of the connection states are not correct, but I think
THERE is a bug, since connection pool is NOT reusing them.

The connection String I'm using is this, hope it helps

"Database=omnivent;Port=3306;Pooling=True;Use Compression=True;Server=localhost;User
Id=XXXXX;Password=YYYYY;Allow Batch=True;Connect Timeout=10"

Thanks in advance

I just want to confirm same problem with WinForms and 5.2.5 connector. Until few  weeks
ago I've used 5.0.6 and there was no problem

Milan

Tonci:
Any news on this?

Hi I also have the same problem... I use Subsonic 2.1 with mysql 5.2.5. (maybe I can
upgrade to mysql 6.0 and it'll magically go away?)
I had same problem with 5.2.3 just so u know.

[MySqlException (0x80004005): error connecting: Timeout expired.  The timeout period
elapsed prior to obtaining a connection from the pool.  This may have occurred because
all pooled connections were in use and max pool size was reached.]
   MySql.Data.MySqlClient.MySqlPool.GetConnection() +184
   MySql.Data.MySqlClient.MySqlConnection.Open() +335
   SubSonic.MySqlDataProvider.CreateConnection(String newConnectionString) +61
   SubSonic.MySqlDataProvider.CreateConnection() +46

Guys, this is very stretched but try this:
  o Increase connection timeout
  o Change the registry parameter responsible for the number of TCP ports on Windows as
described in
http://dev.mysql.com/doc/refman/5.0/en/can-not-connect-to-server.html#can-not-connect-to-s...
  o Retest

Remember that for each different connection string you get separate pool.

too fast...
  o Check max_connections on server and adjust all pool sizes accordingly

Guys, we are working vigorously but with no progress... All we've gathered so far are bits
and pieces.
So here's another one: 
http://msdn.microsoft.com/en-us/library/system.threading.threadpool.setmaxthreads.aspx
Do you errors appear when you're close to or over the MaxWorkerThreads? If so, it's
limitation of framework and OS. This might also be the answer to Antonio's questions
"sleeping but why?".

Tonci:
I already tried your suggestions, but with no success =(
Increasing pool size just delays the error but it happens anyway.
In the other hand, I don't really think that worker threads has something to do, at least
in my particular case, due to the fact that connections go to sleep immediatly after
connection is closed/disposed. And even if I don't reach the connection limit (e.g. I
quit from the application), I have a lot of sleeping connections that never get reused,
but interesting thing is when application is closed all the connections are freed
correctly (because they are in the "InUse" or" ActivePool" -don't remember variable name-
collection, I noticed that when debugged the code).  Nothing is left behind.

I do use worker threads, they are in the worst case 3 worker threads concurrently
working, but the problem reported is easily reproduced even with just the main thread of
the application.

Error regarding connection limit ("Too many connections") is only thrown when I reach the
max pool size (I also tried with a hardcoded version of connection string just to avoid
getting different pools. The problem is just the same)

I hope this info help you to detect the problem.

Thanks

I'm looking forward to hear more in the matter. Do you think things will be solved if I
switch over to mysql 6.0 on server and connector?

There's been new update on this. Support is currently testing workaround for possible
cause of this problem. More to the point, we are to moving Ping() requests out of the
locked section.

Workflow:
  Now:
   (1) lock the pool object
   (2) get pooled connection or create a new one if pool is empty
   (3) ping the server using the connection from step 2
   (4) mark connection as valid if ping finished successfully or create a new connection
   (5) unlock the pool object

Consequently, all requests to the pool have to wait until ping() finishes and the pool is
unlocked.

  Testing:
   (1) lock the pool object
   (2) get pooled connection or create a new one if pool is empty
   (3) unlock the pool object
   (4) ping the server using the connection from step 2
   (5) mark connection as valid if ping finished successfully or create a new connection

We have high hopes this will cure the problem you're seeing or, at least, improve status
a bit. After testing, Reggie will make the final call.

Currently I've set pooling=false which solves it for me, but I'm guessing at a pretty high
loss of speed. I am waiting for this solution eagerly :) How's the testing going?

The change to pooling=false hasn't solved the issue for me! I'm still getting occurencies
of unable to connect to any of the specified mysql hosts. when clearly the mysql host is
up and alive. what suggestions do you have for me?

Nicklas, there have been situations in which new code could produce deadlock so we're
refining it. As for you concrete case, I am really not a magician... Probably you're
hitting some framework or server limitation, don't know...

I did the adjustments you told about [26 Mar 17:09] and site has been running stable
since.. also did some optimizations where I saw I opened so many unecessary connections.
I made stored procs of them and joined the results and gained much nicer performance and
much much less connections opened... thx.

obviously we hit the limit of 5000 connections at once, probably due to sloppy coding

Nicklas, glad things function now... This is just to show how complex connectors matter
are. You never know where the devil is. Especially on windows.

Hi Toncy,
Is there any news on the correction you told us about (March 27th)?

Antonio, unfortunately no news for now. Each patch goes through rigorous testing process
even though it appears to be correct or functioning...

Probably same problem as described in Bug#43349.

Hi.
 I am just migarting an appication I have with .Net C# from SQL Express to MySQL 6.0.
Same problem with connections and timeing out.

Do you have any news on this issue?

By the way, solution from March 26th didn't work for me either; however pooling=false
worked, but very slow; end user is gona kill me if I release my system with this
"solution"

I would appreciate if you have any news on this.

After a deep searching inside code, on our test and production servers, I found solution
for a 50%~  of the connections losted.

If pooling is disabled and something fail:
open > work > error > GC closed connection

If pooling is open, pooler has a some type of array of in-use connections,
when some code fail, GC found  refrence to opened connection in the array and keeps the
connection object, and the object will marked "in-use" forever.

I solve it (not 100% ! the problem is inside from other reasons) with using "With":

Using Conn As New MySqlConnection(ConnString)
	Conn.Open()
End Using

in ASP.NET page (or base-class), option two:

Private Sub Page_Error(ByVal sender As Object, ByVal e As System.EventArgs) Handles
Me.Error
	Conn.Close() : Conn.Dispose()
End Sub

this is not a perefect solution, but reduced losted connections.

Bug#43349 is marked as duplicate of this report.

Hi guys

I'd like to know if this problem can be reproduced with an alternative pooling algorithm,
if you have time try out http://bugs.mysql.com/file.php?id=12463 (from issue #40684) and
see if that helps or makes things worse.

something new?

The connector may need to check if connection losted open, then return to the pool, or
set a "timeout" for connection, and return automatically to the pool to solve this
problem.

Moshe, not that I know of. Will look into status when I return from vacation.
I also made mistake by leaving report in Analyzing instead of Verified...

I had the same issue with the connector. Turns out there is an issue with pooling that
exists in all versions from 5.1.7 up to current version (It's still not fixed).  Download
the complete source and apply the changes in this post and it works perfectly!

http://bugs.mysql.com/bug.php?id=31996