Data server Cannot load data from database executing query

posted on June 3rd, 2020

Closed

Ingenieria Especializada asked on June 3, 2020

Hello Support Team,

I'm trying to connect to my database in postgresql using the Flexmonster Data Server. When I configure a SQL Query from a table with few row it works fine but when it throws more rows it doesn't work. I don't know is there a limit to set up in the configuration file.

Example:

"Indexes": {
"index_contabilidad_detallada": {
"Query": "SELECT * FROM sgp.tiempos limit 10"
}
}

The data server starts normally:

[root@juanes dataserver]# ./flexmonster-data-server
2020-06-02 21:22:09.7152|INFO|Flexmonster.DataServer.Core.PrepopulatingCacheService|Prepopulation service start working
2020-06-02 21:22:09.7771|INFO|Flexmonster.DataServer.Core.DataStorages.DataStorage|Start loading index index_contabilidad_detallada
2020-06-02 21:22:10.0561|INFO|Flexmonster.DataServer.Core.PrepopulatingCacheService|Index index_contabilidad_detallada was loaded in 0,2819937 seconds
2020-06-02 21:22:10.0564|INFO|Flexmonster.DataServer.Core.PrepopulatingCacheService|Prepopulating finished. Total time spent: 0,2819937 seconds
2020-06-02 21:22:10.1137|INFO|Microsoft.Hosting.Lifetime|Now listening on: http://[::]:9500
2020-06-02 21:22:10.1169|INFO|Microsoft.Hosting.Lifetime|Application started. Press Ctrl+C to shut down.
2020-06-02 21:22:10.1169|INFO|Microsoft.Hosting.Lifetime|Hosting environment: Production
2020-06-02 21:22:10.1169|INFO|Microsoft.Hosting.Lifetime|Content root path: /var/www/html/tiempo/flexmonster/2.8.8/dataserver

But all data (970675 rows) it seems to wait a minute and shows:

[root@juanes dataserver]# ./flexmonster-data-server
2020-06-02 21:24:21.8481|INFO|Flexmonster.DataServer.Core.PrepopulatingCacheService|Prepopulation service start working
2020-06-02 21:24:21.9089|INFO|Flexmonster.DataServer.Core.DataStorages.DataStorage|Start loading index index_contabilidad_detallada
2020-06-02 21:24:23.6720|ERROR|Flexmonster.DataServer.Core.PrepopulatingCacheService|Cannot load data from database executing query "/var/www/html/tiempo/flexmonster/2.8.8/dataserver/SELECT * FROM sgp.tiempos". Please check query.
2020-06-02 21:24:23.6873|FATAL|Microsoft.AspNetCore.Hosting.Diagnostics|Application startup exception
2020-06-02 21:24:23.7130|FATAL|Flexmonster.DataServer.Program|Data prepopulating failed

As you can see the only change is set the limit clause. By the way, the table only have 6 columns.

7 answers

Public

Vera Didenko ⋅ Flexmonster ⋅ June 3, 2020

Hello,

Thank you for writing to us.

It seems the reason could be that the command doesn't have enough time to complete.
Our team kindly suggests specifying the CommandTimeout parameter in the connection string, for example:

Server=127.0.0.1;Port=5432;Database=myDataBase;User Id=myUsername;Password=myPassword;CommandTimeout=20;

This parameter is measured in seconds and it controls how long to wait for a command to finish before throwing an error.

Please let us know if this helps.
Looking forward to your response.

Kind regards,
Vera

Public

Ingenieria Especializada ⋅ June 3, 2020

Hi, Thank you for your quick response.

But sadly, that parameter didn't had any effect. Even I put a CommandTimeout=120 (2 minutes) but when I start de server I can see that it don't take the time parametered but it shows in less than 2 seconds the same log.

[root@juanes dataserver]# ./flexmonster-data-server
2020-06-02 21:24:21.8481|INFO|Flexmonster.DataServer.Core.PrepopulatingCacheService|Prepopulation service start working
2020-06-02 21:24:21.9089|INFO|Flexmonster.DataServer.Core.DataStorages.DataStorage|Start loading index index_contabilidad_detallada
2020-06-02 21:24:23.6720|ERROR|Flexmonster.DataServer.Core.PrepopulatingCacheService|Cannot load data from database executing query “/var/www/html/tiempo/flexmonster/2.8.8/dataserver/SELECT * FROM sgp.tiempos”. Please check query.
2020-06-02 21:24:23.6873|FATAL|Microsoft.AspNetCore.Hosting.Diagnostics|Application startup exception
2020-06-02 21:24:23.7130|FATAL|Flexmonster.DataServer.Program|Data prepopulating failed

Public

Ian Sadovy ⋅ Flexmonster ⋅ June 5, 2020

Hello,

Thank you for the detailed answer.
It seems that our team needs more time to set up a large Postgres database (~1M rows) and check how Flexmonster Data Server performs with it. We will get back to you with results and more details next week.

I hope it works for you.

Regards,
Ian

Public

Ingenieria Especializada ⋅ June 5, 2020

Thank you for the update.

I will be patient about it.

Public

Vera Didenko ⋅ Flexmonster ⋅ June 10, 2020

Hello,

Thank you for giving us some time.

Our team has managed to reproduce the issue on our end as well.
We have prepared a hotfix package that can be downloaded by the following link: https://flexmonster.s3.amazonaws.com/ieb/FLEXMONSTER-2020-IEB-JUN10.zip

Please let us know if the fix works for you.
Waiting for your feedback.

Kind regards,
Vera

Public

Ingenieria Especializada ⋅ June 10, 2020

Thank you Vera,

This work perfect for us, with huge data and few data.

Thanks again.

Public

Mykhailo Halaida ⋅ Flexmonster ⋅ June 16, 2020

Hi!

We are glad to announce that the issue with loading big datasets from databases was fixed and is now officially available in the 2.8.9 version of Flexmonster: https://www.flexmonster.com/release-notes/

You are welcome to update the component. Here is our updating to the latest version guide for assistance: https://www.flexmonster.com/doc/updating-to-the-latest-version/

Please let us know if you have any further questions we can help you with.

Best regards,
Mykhailo

This question is now closed

Changes to Flexmonster Software License Agreement

Data server Cannot load data from database executing query

7 answers