Error on opening DbConnection

[brb24]

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]

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]

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]

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]

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]

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]

Is this forum the official support system?

[Shalex]

Is this forum the official support system?

Yes, it is: https://www.devart.com/dotconnect/postg ... pport.html.

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]

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]

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]

Any news?

[Shalex]

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

[Shalex]

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]

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]

New build of dotConnect for PostgreSQL 7.10.1152 is available for download now: viewtopic.php?f=3&t=37213.

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).