"Connection Must Be Opened Error" When Already open

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
hmuscroft
Posts: 23
Joined: Tue 28 Nov 2006 10:07

Post by hmuscroft » Fri 03 Apr 2009 14:53

Thanks for your reply Shalex.
Maybe connection is closed before it is used somewhere in your code. Please review your code.
No, the connection is NEVER closed. It is opened at startup and disposed at on termination of the application. To be specific, I have a DATA class which encapsulates all DB functions. This class has a public DbConnection property (called Connection). The Connection property 'getter' is as follows :-

Code: Select all

get
{
  if (m_connection == null)
  {
     m_connection = new PgSqlConnection(m_constr);
     m_connection.Open();
  }
  return m_connection;
}
That is the ONLY place my application can get the connection object and 'm_connection' is a private PgSqlConnection object. On program shutdown, the DATA class calls m_connection.Close() and m_connection.Close(). Nowhere in my code is there a single call to .Close apart from in the destructor for the DATA class, which is a PUBLIC STATIC class and is not destroyed until the application terminates.

HOWEVER...

I have now changed my code back so that the ConnectionString contains "Pooling=true" (it was false with the previous code) and the getter code now reads as follows :-

Code: Select all

get
{
  PgSqlConnection con = new PgSqlConnection(m_constr);
  con.Open();
  return con;
}
Each routine which references data.Connection now gets its own connection object and it also disposes of the connection as soon as it's finished with it.

The result? NO PROBLEMS. Clearly there is an issue when applications hold on to a single connection object and keep it open throughout the lifetime of the application.
>> We cannot reproduce the "Connection must be opened" issue.
However, I am sure that you accept there clearly is a problem? I am not your only customer with this issue. leeottaway, AndreyR, irimawi and phoebe007 have all also reported the exact same problem (and I would bet there are others watching this forum with the same problem who haven't weighed in).

Please also bear in mind I myself can't reproduce the problem on my network (apart from by disconnecting the cable), but about 50% of my clients are experiencing this issue and I have observed the error on their systems when it occurs.

Perhaps it might be wise to revise the advice given in this post :-
http://devart.com/forums/viewtopic.php?t=14287
... and to suggest that developers use "Pooling=true" and that we create/dispose of PgSqlConnection objects explicitly as and when they are needed?

Kind Regards,

Hedley

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Mon 06 Apr 2009 12:26

Code: Select all

get
{
  if (m_connection == null)
  {
     m_connection = new PgSqlConnection(m_constr);
     m_connection.Open();
  }
  return m_connection;
}
This getter code checks whether the m_connection variable is set to an instance of an object or not. But it doesn't check the state of the connection object. Even if you include the (m_connection == null || m_connection.State != ConnectionState.Open) check in your getter code, but there were some connection problems - the m_connection will be considered as valid, and its state - as open. So it is recommended to execute some test query to the database (e.g., "select 1") to ensure the connection object is valid actually.

The connection pooling feature is intended to increase the performance (the connection is not closed when the Close() or Dispose() method is called). If it helps you to fix the problem, please use it as a workaround.
For more information, please refer to our online documentation: http://www.devart.com/dotconnect/postgr ... /?FAQ.html , the Connection pooling section.

hmuscroft
Posts: 23
Joined: Tue 28 Nov 2006 10:07

Post by hmuscroft » Mon 06 Apr 2009 20:28

This getter code checks whether the m_connection variable is set to an instance of an object or not. But it doesn't check the state of the connection object
Correct but as I stated earlier, nowhere in my code do I *ever* call .Close() on the connection object.

In fact, the connection object iteself is only accessible internally to the DATA class. The rest of my app simply call utliity functions on the DATA class e.g. public DataTable GetQuery(string SQL) in order to retrieve data. The connection object itself is not exposed so it's not hard to clearly see that I never call .Close();

Are you suggesting that the PgSqlConnection object could be closed by some other process? Perhaps by your implementation of one of the other ADO data classes like PgSqlDataAdapter for example?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Tue 07 Apr 2009 09:09

Among the possible reasons of this error can be the following:
1) the ExecuteReader(CommandBehavior) method of the PgSqlCommand object is called with the CommandBehavior.CloseConnection parameter (for more information, please refer to our online documentation);
2) the network problems (see my post above);
3) some exception in your program that causes the connection close. E.g., you may handle this program exception and skip the moment of closing connection.

asj
Posts: 8
Joined: Fri 16 Sep 2011 08:05

Could large amounts of data be the cause?

Post by asj » Wed 01 Feb 2012 09:19

Hi all,

Apologies for posting in an old topic, but I am receiving a similar error on 5.50.228.0 and may be able to provide some additional info.

Did the reasons mentioned by shalex work for you?
Specifically i have issues inserting a binary file (209 MB) into postgres with the Devart dotConnect EF4 provider. While it certainly could be argued binary files (especially of that size) should not be stored in the DB, it is out of my hands in this case.
I also have a case where inserting ~70.000 records into a table using just the classic DataAdapter yields the same "Connection must be opened" error. One of these columns contain a lot of data as well.

Is there some kind of limit to how much data may be transferred (per transaction/table/row/column) at once using dotConnect or even in PostgreSQL?
Is there perhaps a configuration value which may be increased?

My EF4 stacktrace looks like this. It always works with small files and the connection is certainly open before calling SaveChanges().
System.InvalidOperationException: Connection must be opened.
at Devart.Common.Utils.CheckConnectionOpen(IDbConnection connection)
at Devart.Data.PostgreSql.PgSqlConnection.Rollback()
at Devart.Data.PostgreSql.PgSqlTransaction.Dispose(Boolean disposing)
at System.Data.EntityClient.EntityTransaction.Dispose(Boolean disposing)
at System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options)

Best regards,
Anders

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Tue 07 Feb 2012 14:41


asj
Posts: 8
Joined: Fri 16 Sep 2011 08:05

Post by asj » Tue 07 Feb 2012 14:49

Thank you.

Post Reply