Connections stay open indefinitely with 5.50

LarsOberg · Post by **LarsOberg** » Wed 20 Jan 2010 19:21

Hello,

I just installed the latest DevArt MySQL 5.50 and it seems to have an issue that keeps all connections open forever. I first noticed when our MySQL server started giving "Too many open connections."

So I did a quick test between the new 5.50 version and our old 5.40 version (from July 2009). With both version, an application opens up about 20 connections initially, but after a few minutes, they go down to about 5 connections with 5.40, but they stay at 20 with 5.50. The same exact app - just compiled with different DevArt versions for the test

Please correct this.

Thanks,
Lars

Ps. Connection Pooling is on. The MySQL server is 5.1.40.

StanislavK · Post by **StanislavK** » Thu 21 Jan 2010 15:10

Please specify your connection string and the value of the wait_timeout server variable.

Also, a small test project would be very helpful.

We cannot reproduce the issue at the moment.

LarsOberg · Post by **LarsOberg** » Thu 21 Jan 2010 17:35

Connection string: "server=...; port=3306; user id=...; password=...; database=inventory; pooling=true;protocol=SSH;SSH Host=...;SSH User=...;SSH Password=...;"

wait_timeout=86400. This setting has been in place for about 3 years, to handle "Lost connection to MySQL server during query."

The problem with dotConnect for MySQL version 5.50 seems to be that connections remain open on the server, even after they have been closed and disposed by the app (AFAIK, wait_timeout is for *idle* connections, not closed ones).

When I compile the same exact program with dotConnect for MySQL version 5.40, the closed & disposed connections get closed on the server as they should be.

Thanks,
Lars

Ps. I have reverted back to 5.40, since 5.50 was disrupting our MySQL server (out of connections), so I cannot easily create a sample for you right now.

StanislavK · Post by **StanislavK** » Fri 22 Jan 2010 11:24

I tried a simple test: opened and closed 20 connections in the application. With both 5.40.49 and 5.50.79 versions of dotConnect for MySQL the behaviour was the same: before application terminated, all connections "sleeped" in the pool. Please point what has to be changed in the test or send us a sample application reproducing the issue.

We are unable to reproduce the problem at the moment.

LarsOberg · Post by **LarsOberg** » Fri 22 Jan 2010 16:48

Sorry if I wasn't clear on one point: The connections will close when the app terminates. The problem I had with 5.50 was that the connections remained open on the server *for as long as the app was running*, even though the connections were closed. This happened with all WinForm apps (I did not try ASP.NET) that I compiled with the new version (only three apps, then the server started to get overwhelmed by the number of open connections and I had to revert to 5.40, whereby all problems disappeared).

Did you use with SSH in your test?

If you still cannot reproduce it, could you send me the sample you used and I can see if I can reproduce the problem with it?

Lars

StanislavK · Post by **StanislavK** » Mon 25 Jan 2010 13:24

We have reproduced the issue with both 5.40.49 and 5.50.79 versions of dotConnect for MySQL. This seems to be the expected behaviour, as different instances of the same application do not share pooled connections. To avoid possible problems, you can use the ClearPool or ClearAllPools methods of the MySqlConnection static interface after unnecessary connections are closed.

Please see the corresponding topics in our help:
ClearPool
ClearAllPools

LarsOberg · Post by **LarsOberg** » Mon 25 Jan 2010 15:45

Thanks for the info, but the issue I am describing is withing the same instance of the same application (not different instances). With 5.40 it works, but with 5.50, it does not.

Lars

StanislavK · Post by **StanislavK** » Mon 25 Jan 2010 17:22

I send a test project to the mail you provided in your forum profile. Please check that the letter is not blocked by your mail filter.

The sample opens 20 connections, closes them and opens again. On our server, it behaves as follows:
1. 20 connections open;
2. 20 connections become "sleeping" in the pool;
3. 20 connections are taken from the pool and reused.

The "too many open connections" problem is reproduced in the following way:
1. start the first instance of the sample;
2. wait till the moment when all connections are closed in the application and rest in the pool;
3. pause the first instance;
4. start another instance of the sample;
5. wait till the moment when 20 connections of the second instance are opened.

As I set the "max_connections" server variable to 39, the second instance crashes with the "too many open connections" error.

Please specify if you are encountering the problem in some other scenario.

Also, do the ClearPool and ClearAllPools methods help?

LarsOberg · Post by **LarsOberg** » Tue 26 Jan 2010 18:30

Hello,

I could not reproduce the issue with your sample application - there seems to be something more than just opening and closing connection that triggers the issue. I don't have time to investigate deeper at the moment, but below is a quick test I just performed to show you that the problem does exist.

I simply started a lone app compiled with 5.40.37 and let it sit idle while monitoring the connections via mysqladmin. Then I did the exact same test with the exact same app compiled with 5.50.79. As you can see, with 5.40 the connections only remain open for a few minutes, while with 5.50 they remain open (only 3 of the 29 initial connections got closed).

***********************************************************
*** Application compiled with DevArt.Data.MySql 5.40.37 ***
***********************************************************
Note: Only one MySQL app running from workstation 192.168.1.12.

--- Right after application start ---
[root@hs-co2 ~]# mysqladmin processlist | grep "192.168.1.12" | wc -l
29

[root@hs-co2 ~]# mysqladmin processlist | grep "192.168.1.12"
| 506361 | guest | 192.168.1.12:56293 | DataControl | Sleep | 25 |
| 506363 | guest | 192.168.1.12:56294 | WebPick | Sleep | 50 |
| 506365 | guest | 192.168.1.12:56295 | Global | Sleep | 50 |
| 506367 | guest | 192.168.1.12:56296 | WebPick | Sleep | 49 |
| 506369 | guest | 192.168.1.12:56297 | Global | Sleep | 49 |
| 506370 | guest | 192.168.1.12:56298 | WebPick | Sleep | 48 |
| 506371 | guest | 192.168.1.12:56299 | Global | Sleep | 48 |
| 506372 | guest | 192.168.1.12:56300 | WebPick | Sleep | 47 |
| 506375 | guest | 192.168.1.12:56301 | WebPick | Sleep | 47 |
| 506376 | guest | 192.168.1.12:56302 | Global | Sleep | 46 |
| 506377 | guest | 192.168.1.12:56303 | WebPick | Sleep | 46 |
| 506378 | guest | 192.168.1.12:56304 | Global | Sleep | 45 |
| 506379 | guest | 192.168.1.12:56305 | WebPick | Sleep | 45 |
| 506380 | guest | 192.168.1.12:56306 | Global | Sleep | 44 |
| 506381 | guest | 192.168.1.12:56307 | WebPick | Sleep | 44 |
| 506382 | guest | 192.168.1.12:56308 | Global | Sleep | 43 |
| 506383 | guest | 192.168.1.12:56309 | WebPick | Sleep | 43 |
| 506384 | guest | 192.168.1.12:56310 | Global | Sleep | 42 |
| 506385 | guest | 192.168.1.12:56311 | WebPick | Sleep | 42 |
| 506386 | guest | 192.168.1.12:56312 | Global | Sleep | 41 |
| 506387 | guest | 192.168.1.12:56313 | WebPick | Sleep | 41 |
| 506388 | guest | 192.168.1.12:56314 | Global | Sleep | 40 |
| 506389 | guest | 192.168.1.12:56315 | WebPick | Sleep | 40 |
| 506390 | guest | 192.168.1.12:56316 | Global | Sleep | 39 |
| 506391 | guest | 192.168.1.12:56317 | WebPick | Sleep | 39 |
| 506392 | guest | 192.168.1.12:56318 | Global | Sleep | 38 |
| 506394 | guest | 192.168.1.12:56319 | WebPick | Sleep | 34 |
| 506395 | guest | 192.168.1.12:56320 | WebPick | Sleep | 33 |
| 506401 | guest | 192.168.1.12:56324 | Global | Sleep | 25 |

--- 2 or 3 minutes later, with application sitting idle (=no activity to the DB) ---
[root@hs-co2 ~]# mysqladmin processlist | grep "192.168.1.12" | wc -l
10
[root@hs-co2 ~]# mysqladmin processlist | grep "192.168.1.12"
| 506390 | guest | 192.168.1.12:56316 | Global | Sleep | 222 |
| 506391 | guest | 192.168.1.12:56317 | WebPick | Sleep | 222 |
| 506392 | guest | 192.168.1.12:56318 | Global | Sleep | 222 |
| 506394 | guest | 192.168.1.12:56319 | WebPick | Sleep | 222 |
| 506395 | guest | 192.168.1.12:56320 | WebPick | Sleep | 222 |

--- another minute later, with application sitting idle (=no activity to the DB) ---
[root@hs-co2 ~]# mysqladmin processlist | grep "192.168.1.12" | wc -l
2
[root@hs-co2 ~]# mysqladmin processlist | grep "192.168.1.12"
| 506392 | guest | 192.168.1.12:56318 | Global | Sleep | 294 |
| 506395 | guest | 192.168.1.12:56320 | WebPick | Sleep | 294 |

***********************************************************
*** Application compiled with DevArt.Data.MySql 5.50.79 ***
***********************************************************
Note: Only one MySQL app running from workstation 192.168.1.12.

--- Right after application start ---
[root@hs-co2 ~]# mysqladmin processlist | grep "192.168.1.12" | wc -l
29
[root@hs-co2 ~]# mysqladmin processlist | grep "192.168.1.12"
| 506506 | guest | 192.168.1.12:56332 | DataControl | Sleep | 14
| 506507 | guest | 192.168.1.12:56333 | WebPick | Sleep | 24
| 506508 | guest | 192.168.1.12:56334 | Global | Sleep | 24
| 506509 | guest | 192.168.1.12:56335 | WebPick | Sleep | 24
| 506510 | guest | 192.168.1.12:56336 | Global | Sleep | 24
| 506511 | guest | 192.168.1.12:56337 | WebPick | Sleep | 23
| 506512 | guest | 192.168.1.12:56338 | Global | Sleep | 23
| 506513 | guest | 192.168.1.12:56339 | WebPick | Sleep | 23
| 506514 | guest | 192.168.1.12:56340 | WebPick | Sleep | 23
| 506515 | guest | 192.168.1.12:56341 | Global | Sleep | 23
| 506516 | guest | 192.168.1.12:56342 | WebPick | Sleep | 23
| 506517 | guest | 192.168.1.12:56343 | Global | Sleep | 23
| 506518 | guest | 192.168.1.12:56344 | WebPick | Sleep | 23
| 506519 | guest | 192.168.1.12:56345 | Global | Sleep | 23
| 506520 | guest | 192.168.1.12:56346 | WebPick | Sleep | 23
| 506521 | guest | 192.168.1.12:56347 | Global | Sleep | 23
| 506522 | guest | 192.168.1.12:56348 | WebPick | Sleep | 23
| 506523 | guest | 192.168.1.12:56349 | Global | Sleep | 23
| 506524 | guest | 192.168.1.12:56350 | WebPick | Sleep | 23
| 506525 | guest | 192.168.1.12:56351 | Global | Sleep | 23
| 506526 | guest | 192.168.1.12:56352 | WebPick | Sleep | 23
| 506527 | guest | 192.168.1.12:56353 | Global | Sleep | 23
| 506528 | guest | 192.168.1.12:56354 | WebPick | Sleep | 23
| 506529 | guest | 192.168.1.12:56355 | Global | Sleep | 23
| 506530 | guest | 192.168.1.12:56356 | WebPick | Sleep | 23
| 506531 | guest | 192.168.1.12:56357 | Global | Sleep | 23
| 506533 | guest | 192.168.1.12:56358 | WebPick | Sleep | 20
| 506534 | guest | 192.168.1.12:56359 | WebPick | Sleep | 20
| 506537 | guest | 192.168.1.12:56362 | Global | Sleep | 14

--- a few minutes later, with application sitting idle (=no activity to the DB) ---
[root@hs-co2 ~]# mysqladmin processlist | grep "192.168.1.12" | wc -l
26

[root@hs-co2 ~]# mysqladmin processlist | grep "192.168.1.12"
| 506508 | guest | 192.168.1.12:56334 | Global | Sleep | 437 |
| 506509 | guest | 192.168.1.12:56335 | WebPick | Sleep | 437 |
| 506510 | guest | 192.168.1.12:56336 | Global | Sleep | 437 |
| 506511 | guest | 192.168.1.12:56337 | WebPick | Sleep | 436 |
| 506512 | guest | 192.168.1.12:56338 | Global | Sleep | 436 |
| 506513 | guest | 192.168.1.12:56339 | WebPick | Sleep | 436 |
| 506514 | guest | 192.168.1.12:56340 | WebPick | Sleep | 436 |
| 506515 | guest | 192.168.1.12:56341 | Global | Sleep | 436 |
| 506516 | guest | 192.168.1.12:56342 | WebPick | Sleep | 436 |
| 506517 | guest | 192.168.1.12:56343 | Global | Sleep | 436 |
| 506518 | guest | 192.168.1.12:56344 | WebPick | Sleep | 436 |
| 506519 | guest | 192.168.1.12:56345 | Global | Sleep | 436 |
| 506520 | guest | 192.168.1.12:56346 | WebPick | Sleep | 436 |
| 506521 | guest | 192.168.1.12:56347 | Global | Sleep | 436 |
| 506522 | guest | 192.168.1.12:56348 | WebPick | Sleep | 436 |
| 506523 | guest | 192.168.1.12:56349 | Global | Sleep | 436 |
| 506524 | guest | 192.168.1.12:56350 | WebPick | Sleep | 436 |
| 506525 | guest | 192.168.1.12:56351 | Global | Sleep | 436 |
| 506526 | guest | 192.168.1.12:56352 | WebPick | Sleep | 436 |
| 506527 | guest | 192.168.1.12:56353 | Global | Sleep | 436 |
| 506528 | guest | 192.168.1.12:56354 | WebPick | Sleep | 436 |
| 506529 | guest | 192.168.1.12:56355 | Global | Sleep | 436 |
| 506530 | guest | 192.168.1.12:56356 | WebPick | Sleep | 436 |
| 506531 | guest | 192.168.1.12:56357 | Global | Sleep | 436 |
| 506533 | guest | 192.168.1.12:56358 | WebPick | Sleep | 433 |
| 506534 | guest | 192.168.1.12:56359 | WebPick | Sleep | 433 |

--- 50 minutes later, with application sitting idle (=no activity to the DB) ---
[root@hs-co2 ~]# mysqladmin processlist | grep "192.168.1.12" | wc -l
26
[root@hs-co2 ~]# mysqladmin processlist | grep "192.168.1.12"
| 506508 | guest | 192.168.1.12:56334 | Global | Sleep | 3011 |
| 506509 | guest | 192.168.1.12:56335 | WebPick | Sleep | 3011 |
| 506510 | guest | 192.168.1.12:56336 | Global | Sleep | 3011 |
| 506511 | guest | 192.168.1.12:56337 | WebPick | Sleep | 3010 |
| 506512 | guest | 192.168.1.12:56338 | Global | Sleep | 3010 |
| 506513 | guest | 192.168.1.12:56339 | WebPick | Sleep | 3010 |
| 506514 | guest | 192.168.1.12:56340 | WebPick | Sleep | 3010 |
| 506515 | guest | 192.168.1.12:56341 | Global | Sleep | 3010 |
| 506516 | guest | 192.168.1.12:56342 | WebPick | Sleep | 3010 |
| 506517 | guest | 192.168.1.12:56343 | Global | Sleep | 3010 |
| 506518 | guest | 192.168.1.12:56344 | WebPick | Sleep | 3010 |
| 506519 | guest | 192.168.1.12:56345 | Global | Sleep | 3010 |
| 506520 | guest | 192.168.1.12:56346 | WebPick | Sleep | 3010 |
| 506521 | guest | 192.168.1.12:56347 | Global | Sleep | 3010 |
| 506522 | guest | 192.168.1.12:56348 | WebPick | Sleep | 3010 |
| 506523 | guest | 192.168.1.12:56349 | Global | Sleep | 3010 |
| 506524 | guest | 192.168.1.12:56350 | WebPick | Sleep | 3010 |
| 506525 | guest | 192.168.1.12:56351 | Global | Sleep | 3010 |
| 506526 | guest | 192.168.1.12:56352 | WebPick | Sleep | 3010 |
| 506527 | guest | 192.168.1.12:56353 | Global | Sleep | 3010 |
| 506528 | guest | 192.168.1.12:56354 | WebPick | Sleep | 3010 |
| 506529 | guest | 192.168.1.12:56355 | Global | Sleep | 3010 |
| 506530 | guest | 192.168.1.12:56356 | WebPick | Sleep | 3010 |
| 506531 | guest | 192.168.1.12:56357 | Global | Sleep | 3010 |
| 506533 | guest | 192.168.1.12:56358 | WebPick | Sleep | 3007 |
| 506534 | guest | 192.168.1.12:56359 | WebPick | Sleep | 3007 |

StanislavK · Post by **StanislavK** » Wed 27 Jan 2010 12:45

Apparently, the new version demonstrates the proper behaviour: the connections closed in the application are returned to the pool and retain on the server.

Please try setting the Connection Lifetime property of the connection string. If the time span between opening and closing the connection in the application is greater than Connection Lifetime, the connection will be closed on the server instead of being returned to the pool.