Get Free Trial

Cannot load data from database executing query “SELECT * FROM Environment”. Please check query or connection string.

Re-Open
Brian asked on July 6, 2022

Hi,
I am trying to set up Data Server. I am using the following flexmonster-config.json:

{
"DataSources": [
{
"Type": "database",
"DatabaseType": "mysql",
"ConnectionString": "Server=[REDACTED];Port=3306;User=[REDACTED];Password=[REDACTED]";Database=[REDACTED]",
"Indexes": {
"environment": {
"Query": "SELECT * FROM Environment"
}
}
}
],
"Security": {
"Authorization": {
"Enabled": false
},
"CORS": {
"AllowOrigin": "*"
}
},
"DataStorageOptions": {
"DataRefreshTime": "60"
},
"Port": "9500"
}

I am receiving the following error:

2022-07-06 18:02:54.5551|ERROR|Flexmonster.DataServer.Core.IPrepopulatingService|Index environment cannot be loaded. Details: Cannot load data from database executing query "SELECT * FROM Environment". Please check query or connection string. 
at Flexmonster.DataServer.Core.DataLoaders.DataLoader.Load(String index)
at Flexmonster.DataServer.Core.DataStorages.DataStorage.GetOrAddAsync(String cacheKey)
at Flexmonster.DataServer.FlexmonsterPrepopulatingCacheService.TryLoadIndex(IServiceScope scope, String indexName)

I verified that the server can connect to MySQL and that the IP and credentials are correct by using the MySQL command line tool on the server.
I searched the knowledgebase and tried ignorePrepared, the timeout suggestion, and tried using backticks. It did not work in any of those cases.
The only thing I can think of is that the password has an ” in it that I had to escape in the JSON (\” in JSON). I will work with my ops team to change that and update here, but is there anything else I can try?

5 answers

Public
Brian July 6, 2022

Please ignore the extra ” in the connectionString after password. That is a typo and it is not letting me edit it.

Public
Brian July 6, 2022

We fixed it by simplifying the password. It looks like some characters in the password might be getting interpreted and this should probably be fixed.
Here are the special characters that were in the old password: ] – ; } $ “

Public
Maksym Diachenko Maksym Diachenko Flexmonster July 7, 2022

Hello, Brian!

Thank you for writing to us.

We are glad to hear that you found the solution.
Our team will look closer at the issue with special characters while connecting FDS to the SQL data source, and provide a fix, ETA Jul 26.

Feel free to contact us if more questions arise.

Best Regards,
Maksym

Public
Maksym Diachenko Maksym Diachenko Flexmonster July 26, 2022

Hello, Brian,

Our team researched this issue and is ready to share the results.

The reason for this error is using a semicolon(;) character inside a password. It is used as a delimiter between the ConnectionString parameters and can cause errors when is in unexpected places. We suggest wrapping the password into single quotation marks(') as a workaround.

Password='password123;[{$-?}]'

Please let us know if this workaround works for you.

Best regards,
Maksym

Public
Brian July 26, 2022

Thank you!

Please login or Register to Submit Answer