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.
⋅ 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
⋅ 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
⋅ 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
⋅ June 5, 2020
Thank you for the update.
I will be patient about it.
⋅ 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
⋅ June 10, 2020
Thank you Vera,
This work perfect for us, with huge data and few data.
Thanks again.
⋅ 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