Devart Forums

LarsOberg

It has been a while now, but if I remember correctly, this turned out to be a problem with a stored procedure that I was calling, which caused an exception to be thrown (and silently eaten by the app), which in turn caused the connection not to be closed properly. So it was nothing wrong with Devart's component.

LarsOberg

I have uploaded the log file as requested. Please let me know what you find.

LarsOberg

Bump?

LarsOberg

Pinturiccio wrote: If you open connections when a connection with the same connection string is already open, there will be one connection in the pool, not 20. It is likely, that a connection with the same connection string is opened in another thread, while the first connection has not been closed yet. In this case there are 2 connections in your pool. Your connections can open non-simultaneously, but when other connections have not been closed yet.

I probably was unclear, so let me clarify this: The application does use more than one connection string (it connects to several databases on a couple of different MySQL servers, so there will of course be several connections in the pool - but it does not use over 100 different connections (or anywhere near that many), so the pool should not run out. What I did was to export the DB Monitor log to a file, from which I just filtered out all the lines that contained "Open connection" (since these are the lines that contain the connection strings). Then I simply extracted the connection string portion from these lines + sorted and removed duplicates from these, which gave me all unique connection strings. The total count of unique connection strings is 6 (not 20 as I said earlier, sorry).

The app is driven by an external scheduler that creates flag files that tell the app what to do (like GetOrders, SendItemFeed, SendQtyFeed, etc.). So there are a handful of actions that the app perform over and over throughout the day.

There is only one thread, but more than one connection can be opened at the same time (for example: one connection to the source database and one to the destination database).

As I mentioned in my original post, I cannot reproduce this issue in a small test app (it even takes 2-3 days of running 24x7 in the real app before it happens). Maybe a debug-build of your DLLs that gives you more data when it happens would help?

Please advise.

LarsOberg

Pinturiccio wrote:This exception occurs when the number of open connections is equal to the MaxPoolSize property value. Herewith, there are no unused connections in the pool, othewise, when opening a connection, it would have been taken from the pool and the exception would not have occurred.

I am using the default MaxPoolSize (100) and I do not have anywhere near that many connections open at any one time. As far as I understand it, you identify a unique connection by the exact connection string, so when a connection is opened and there already is a connection with an identical connection string in the pool, the connection should be taken from the pool. Please confirm - is this correct?

I ran DBMonitor to check out the pool usage, and the unique connections peaked at around 20 (and these were not open at the same time - just in the pool). I did this by "Save Log to text file" and counting the number of unique connection strings in the "Open Connection" lines. I also did a count of total number of "Open Connections" lines as well as total number of "Close connection Complete" lines, and I got 1472 for both (which confirms that I am not leaving connections open).

Also, this app is very "repetitive" - it just grabs orders from a database and sends some inventory feeds, etc., so the number of connections it opens is fairly constant - it opens, uses and closes the same connections over and over (so the connections in the pool should stay around 20). And remember that the "pool exception" comes after the app has been running for 2-3 days; if it were a matter of the app opening up more than 100 connections, the exception would have come within the first hour (since everything after that is just a repetition). So there is probably some kind of leak somewhere that makes it eventually get to this state.

Please advise.

LarsOberg

We are closing and disposing every connection, I have already verified that.

Also, I have tried from the VS debugger to manually call ClearAllPools() when this exception occurs, and then try to execute Open connection again, but still get the same exception.

If it is a matter of max pool size being reached, why would ClearAllPools() not handle this??

Lars

LarsOberg

Hello,

There seems to be an issue with dotConnect for MySQL v6.80.332.0. We have an application that runs in an unattended mode 24x7, with frequent and heavy MySQL interactions against a couple of MySQL servers. It runs just fine for 2-3 days, after which I get this exception:

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.
at Devart.Common.DbConnectionFactory.b(DbConnectionBase A_0)
at Devart.Common.DbConnectionClosed.Open(DbConnectionBase outerConnection)
at Devart.Common.DbConnectionBase.Open()
at Devart.Data.MySql.MySqlConnection.Open()

I have tried calling MySqlConnection.ClearAllPools(true) every 10 minutes, but that did not help. When the exception happens, there seems to be no way to call MySqlConnection.Open() without getting this exception (again, ClearAllPools does not help), other than calling it with "pooling=false" in the connection string.

I cannot reproduce this at will, but please look into what could be causing this issue and try to address it. Or maybe you could email me a debug version of the DLLs that would provide you with more data when it happens. I am now running with pooling turned off, but that is very slow and I really need to be able to use pooling.

Thanks,
Lars

LarsOberg

Thanks!

LarsOberg

Bump - Devart, any word on this?

LarsOberg

Hello,

When using SSH (with the proper ssh user name and password), but the incorrect MySQL password, it takes a very long time before you get the MySQL Access denied exception when calling Open(). If you do not use SSH, it comes back instantaneously.

Sampel to reproduce:

Code: Select all

using (var conn = new MySqlConnection("User Id=samtst;Password=TheIncorrectMySqlPassword;Host=192.168.168.1;Port=3306;Database=Global;Protocol=Ssh;Connection Timeout=60;Pooling=True;SSH Host=192.168.168.1;SSH User=mysqlssh;SSH Password=TheCorrectSshPassword;Connection LifeTime=600;"))
{
    conn.Open(); // With SSH, takes 60 seconds, without SSH takes 1 second.

    conn.Close();
}

DevArt MySQL Version: 6.60.283.0
VS 2008, .NET 3.5

Please advise.

Thanks,
Lars

LarsOberg

Bump! Anyone?

LarsOberg

This is with v 5.x of the driver. I just installed the latest driver and did another test and it is working fine!

Sorry for the false alarm.

Lars

LarsOberg

Great.

Also, I Oracle's MySQL Connector/Net has the same issue, so I filed a bug report with them as well:

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

They have verified the bug.

Lars

LarsOberg

Bump. Anyone??

LarsOberg

Hello,

Is there any way you could add a property or similar to specify the prefix character used for command parameters? This would be very helpful for provider factories, etc., where the same app can connect to multiple providers (SQL Server, MySQL, etc.).

Currently, DevArt uses ":" and both Oracle & SQL Server use "@". So porting an app from SQL to Oracle MySQL is easy from this perspective.

Adding support for specifying the prefix character would make it very easy to switch from Oracle to DevArt's MySQL component without having to touch the SQL statements.

Is this something you could implement?

Thanks.

Devart Forums

Search found 41 matches

Re: Pool resource leak / issue?

Re: Pool resource leak / issue?

Re: Pool resource leak / issue?

Re: Pool resource leak / issue?

Re: Pool resource leak / issue?

Re: Pool resource leak / issue?

Pool resource leak / issue?

Opening a connection takes long time if wrong password

Add option to specify command parameter prefix