Error on opening DbConnection

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
brb24
Posts: 9
Joined: Fri 27 Apr 2018 08:07

Error on opening DbConnection

Post by brb24 » Fri 27 Apr 2018 08:51

Hello,
I have an exception occurring only after some time.

I have a thread excecuted every 300 milliseconds (only for test purpose) that read from database as:

Code: Select all

TestDataContext context = new TestDataContext (PgConnectionString);
List<Command> commands = (from o in context.Commands
                                      where
                                       ..                               
                                      select o
                                     ).OrderBy(o=>o.Id).Take(10).ToList();
                                     
...                                     
In various configuration, after 1 or 5 days, it give this exception:

Code: Select all

2018-04-27 11:24:35,951 [18] ERROR .. Message: Error on opening DbConnection.
StackTrace:    at Devart.Data.Linq.LinqCommandExecutionException.CanThrowLinqCommandExecutionException(String message, Exception e)
   at Devart.Data.Linq.Engine.cg.g()
   at Devart.Data.Linq.Engine.cg.b(IConnectionUser A_0)
   at Devart.Data.Linq.DataProvider.a(br A_0, Object[] A_1)
   at Devart.Data.Linq.Engine.DataQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
...
Source: Devart.Data.Linq
TargetSite: Boolean CanThrowLinqCommandExecutionException(System.String, System.Exception)
I think it can be a misconfiguration of session pool, so in connection string i have tried:
  • "User Id=postgres;Password=xxx;Host=127.0.0.1;Database=xxx;Unicode=True;Port=5432;Pooling = True; Min Pool Size = 0; Max Pool Size = 100; Connection Lifetime = 0;" -> with this configuration in 4-5 days it stop work.
  • "User Id=postgres;Password=xxx;Host=127.0.0.1;Database=xxx;Unicode=True;Port=5432;Pooling = True; Min Pool Size = 0; Max Pool Size = 25; Connection Lifetime = 300;Validate Connection=True;" -> with this configuration in a day it stop work.
  • "User Id=postgres;Password=xxx;Host=127.0.0.1;Database=xxx;Unicode=True;Port=5432;Pooling = False; Min Pool Size = 0; Max Pool Size = 25; Connection Lifetime = 300;Validate Connection=True;" -> with Pooling=False.
In case of exception thrown i also tried to use solution in other topic as "PgSqlConnection.ClearAllPools(true);" or "Validate Connection=True;" but with no success.

How can i resolve it?

Thanks for your reply.

brb24
Posts: 9
Joined: Fri 27 Apr 2018 08:07

Re: Error on opening DbConnection

Post by brb24 » Mon 30 Apr 2018 13:58

In the same code, after some other test, i have this exception on select query:

Code: Select all

Message: A privilege that the service requires to function properly does not exist in the service account configuration. You may use the Services Microsoft Management Console (MMC) snap-in (services.msc) and the Local Security Settings MMC snap-in (secpol.msc) to view the service configuration and the account configuration. (Exception from HRESULT: 0x80070511)
StackTrace:    at Devart.Data.Linq.Engine.i.a(InvocationExpression A_0)
   at Devart.Data.Linq.Engine.i.c(Expression A_0, Type A_1)
   at Devart.Data.Linq.Engine.i.a(BinaryExpression A_0)
   at Devart.Data.Linq.Engine.i.a(BinaryExpression A_0)
   at Devart.Data.Linq.Engine.i.a(Expression A_0, LambdaExpression A_1)
   at Devart.Data.Linq.Engine.i.b(MethodCallExpression A_0)
   at Devart.Data.Linq.Engine.i.b(Expression A_0, LambdaExpression A_1, a1 A_2)
   at Devart.Data.Linq.Engine.i.b(MethodCallExpression A_0)
   at Devart.Data.Linq.Engine.i.b(Expression A_0, Expression A_1)
   at Devart.Data.Linq.Engine.i.b(MethodCallExpression A_0)
   at Devart.Data.Linq.Engine.i.i(Expression A_0)
   at Devart.Data.Linq.Engine.dk.d(Expression A_0)
   at Devart.Data.Linq.Engine.dk.f(Expression A_0)
   at Devart.Data.Linq.Engine.DataQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at ...
Source: Devart.Data.Linq
TargetSite: Devart.Data.Linq.Engine.SqlNode a(System.Linq.Expressions.InvocationExpression)

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

Re: Error on opening DbConnection

Post by Shalex » Wed 02 May 2018 10:26

2018-04-27 11:24:35,951 [18] ERROR .. Message: Error on opening DbConnection.
StackTrace: at Devart.Data.Linq.LinqCommandExecutionException.CanThrowLinqCommandExecutionException(String message, Exception e)
[...]
1. There should be the inner exception(s). Please specify all inner exceptions.
Message: A privilege that the service requires to function properly does not exist in the service account configuration. You may use the Services Microsoft Management Console (MMC) snap-in (services.msc) and the Local Security Settings MMC snap-in (secpol.msc) to view the service configuration and the account configuration. (Exception from HRESULT: 0x80070511)
StackTrace: at Devart.Data.Linq.Engine.i.a(InvocationExpression A_0)
[...]
2.1. Your project type is Windows Service (.NET Framework), isn't it?
2.2. Specify the inner exception.
2.3. The error says that account used for running your service is missing some privileges. Please compare privileges of an account, that does the same job successfully, with a "limited" account to find out missing privileges.

brb24
Posts: 9
Joined: Fri 27 Apr 2018 08:07

Re: Error on opening DbConnection

Post by brb24 » Mon 07 May 2018 16:39

I cannot reproduce this problem in a developer machine while debugging, but with a VS snapshot i see 4GB allocated after 3 days (https://drive.google.com/open?id=1VCTwD ... _1Ihj8V_1k ).
In a virtual machine with limited resources I have logged in file this exception, with no inner exception.

2.1 yes, the project is a windows service, a default RemObject DataAbstract Server (in this test it is not installed as service, but executed as program, with administrative user).
2.2 No inner exception.
2.3 This is executed already by admin, it give this exception after some hours/days.

The problem here is very similar: https://social.msdn.microsoft.com/Forum ... formssetup

In "TestDataContext context = new TestDataContext (PgConnectionString);", are there some data to dispose mannually? Is some data appended in a static content to clear?

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

Re: Error on opening DbConnection

Post by Shalex » Tue 08 May 2018 19:29

We cannot identify the reason of the issue at the moment.

Please consider using short-living context for each separate unit of work. Also turn off caching for the context instances that do not need it (i.e. will not save changes). Refer to viewtopic.php?t=20325.

brb24
Posts: 9
Joined: Fri 27 Apr 2018 08:07

Re: Error on opening DbConnection

Post by brb24 » Wed 09 May 2018 13:23

I think i am doing correctly, but as it is difficult to expose the problem, I prepared an example by strictly copying the interested thread: https://drive.google.com/open?id=1qYGxW ... 36njJBkYCx

It is a simple windows form that start a thread repeated after n milliseconds (in this example it is set to 1ms after finish work for speed up).
In zip there is also a backup of db with only an example table (the real project has more tables and it give exception faster).
I also commented compiledQueryCache as described in another post viewtopic.php?t=22490

The exception is thrown after a day of activity, but you can look at memory growing.

brb24
Posts: 9
Joined: Fri 27 Apr 2018 08:07

Re: Error on opening DbConnection

Post by brb24 » Wed 16 May 2018 07:54

Is this forum the official support system?

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

Re: Error on opening DbConnection

Post by Shalex » Wed 16 May 2018 12:32

brb24 wrote:
Wed 16 May 2018 07:54
Is this forum the official support system?
Yes, it is: https://www.devart.com/dotconnect/postg ... pport.html.
brb24 wrote:
Wed 09 May 2018 13:23
I prepared an example by strictly copying the interested thread: https://drive.google.com/open?id=1qYGxW ... 36njJBkYCx
[...]
The exception is thrown after a day of activity, but you can look at memory growing.
Thank you for the test project. After 6 hours of running in the debug mode, the heap size remains less than 1 MB. The memory consumed is checked via Debug > Windows > Show Diagnostic Tools > Memory Usage (press Take Snapshot from time to time). Does this way of memory checking show the memory growth in your environment? Tell us your results after 1 hour, 3 hours, etc of running.

brb24
Posts: 9
Joined: Fri 27 Apr 2018 08:07

Re: Error on opening DbConnection

Post by brb24 » Wed 16 May 2018 13:25

Thanks for reply, I will test and report it in as soon as possible.

In the meantime, as screenshot that I posted in a previous post, in diagnostic tool -> process memory graph, "private Byte" section is growing (after 1700sec is 60MB, starting from 28MB).

As i see debugging slow down the process, please, publish the project as you see it in a pc and let it go for a day or less, it will stop thread when it crashes, reporting error in textarea.

brb24
Posts: 9
Joined: Fri 27 Apr 2018 08:07

Re: Error on opening DbConnection

Post by brb24 » Wed 16 May 2018 16:46

During test in debug mode, after few hours i was not abled to create a snapshot for memory exception: https://drive.google.com/open?id=1dV5U0 ... baHK-BCFhb

Pubblishing in a pc, after few hours it give the exception: https://drive.google.com/open?id=128jYw ... B_Xq1yg4kt

brb24
Posts: 9
Joined: Fri 27 Apr 2018 08:07

Re: Error on opening DbConnection

Post by brb24 » Mon 21 May 2018 08:02

Any news?

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

Re: Error on opening DbConnection

Post by Shalex » Tue 22 May 2018 07:15

Thank you for the additional information. We are investigating the issue.

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

Re: Error on opening DbConnection

Post by Shalex » Wed 23 May 2018 14:50

A destructor for DataContext is added. We will notify you when the new build of dotConnect for PostgreSQL is available for download.

As a current solution, please dispose your DataContext instance explicitly via context.Dispose() or via USING.

brb24
Posts: 9
Joined: Fri 27 Apr 2018 08:07

Re: Error on opening DbConnection

Post by brb24 » Wed 23 May 2018 16:27

Thanks, I have tryed context.Dispose() in this example project and it seems working, for now i deployed it in a testing machine. In debug there is no memory growing.

In the main project i have a strange issues: the project is composed by a "Custom Data Abstract Service".
The final executable can run as windows service and as desktop application.

With the only thread active, with the same executable, i see in task manager:
- runned as desktop application no memory growing
- runned as windows service fast memory growing

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

Re: Error on opening DbConnection

Post by Shalex » Fri 25 May 2018 18:24

New build of dotConnect for PostgreSQL 7.10.1152 is available for download now: viewtopic.php?f=3&t=37213.
brb24 wrote:
Wed 23 May 2018 16:27
With the only thread active, with the same executable, i see in task manager:
- runned as desktop application no memory growing
- runned as windows service fast memory growing
We cannot reproduce the issue in our environment.
We suppose that the issue could be caused by different garbage collectors used in your two scenarios (or the same garbage collector but with different behavior in case of Windows Service).

Post Reply