Context Deadline Exceeded

[barywhyte]

Hello @burningalchemist, thank you for the fast response!

I saw in release note for v0.12.2 that the context deadline issue was addressed.

For this is what I get whenever I attempt to collect metrics from localhost:9399/metrics.

No metrics gathered, [from Gatherer #1] context deadline exceeded

My datasource line looks like:

data_source_name: 'sqlserver://prom_usr:#!$deasgfhf@10.0.41.101:1433'

My database is MS SQL

I have changed scrape_timeout to about 60s and the error became complicated:

No metrics gathered, [from Gatherer #1] lookup prom_usr on 10.0.0.71:53: read udp 10.0.41.101:55098->10.0.0.71:53: i/o timeout

Both sql_exporter and MS SQL are running on the same windows host server 2016. I do not know what I am doing wrong here.

I am currently using v0.12.3 of sql_exporter

[burningalchemist]

So, as I previously replied with:

Context deadline exceeded, especially in your case, implies that the exporter can't
successfully connect to the database. You need to check if the node is accessible from
the network you're running the exporter on.

0.12.2-3 improved the error propagation.

I would start with that. It looks to me either as an authentication issue or a networking issue.

Also, please note:
If your DSN contains special characters in any part of your connection string (including passwords), you might need to apply URL encoding (percent-encoding) to them.

For example, p@$$w0rd#abc then becomes p%40%24%24w0rd%23abc.

[barywhyte]

@burningalchemist thank you! I check this out in the morning and revert. Also both exporter and MS SQL are running on the same node. In fact, my grafana which is running on a different node (all nodes are within the same network, so they can ping each other at least) is a able to connect to the MS SQL instance via Grafana plugin.

For the password encoding, I will try that once I am back in the datacenter in the morning and revert. Thank you!

[burningalchemist]

@barywhyte if networking is not an issue, I bet it's the authentication details (url encoded stuff). Give it a check. 👍

[barywhyte]

@burningalchemist Thank you. And yes this is not networking issue. Two servers can ping each other and I am able to access wmi exporter via my prometheus server as seen in the diagram here.

However, after encoding special characters, I still encountered the same context deadline exceeded error. Here is my connection string:
sqlserver://prom_ur:%23%21%24Prom@10.0.41.101:1433/MSSQLINSTANCE'. With or without MSSQLINSTANCE, the same error persist. On my remote prometheus server, server returned **HTTP status 500 Internal Server Error** error showed up which speaks to **context deadline exceeded** error on the DB side

[burningalchemist]

Hi @barywhyte,

As I mentioned before, context deadline exceeded is a generic error, which implies that the result cannot be returned in assigned amount of time (10s by default). This can be anything - authentication details, authentication query parameters, an incorrect or incomplete connection string.

Also, DSN has a strict format, so it should be correct. Please check the spec here: https://github.com/xo/dburl?tab=readme-ov-file#example-urls. To me, MSSQLINSTANCE string seems incorrect as I don't think it's the database name, is it?

Also, your query might be expensive enough, so the timeout happens, you might need to increase the timeout significantly.

I'd suggest you to pick usql tool, and try to connect with it first, this might help, if none of the above does. sql_exporter is based upon the same library, so it should help identify the correct parameters to connect properly.

[barywhyte]

@burningalchemist MSSQLINSTANCE is not database name. It's an instance name, meaning there are at least 1 database in it. I got the connection string idea from https://github.com/microsoft/go-mssqldb#connection-parameters-and-dsn and also https://github.com/free/sql_exporter/#data-source-names when I first tried to use free/sql_exporter.

Yea I agree with you. This is an authentication issue with the DB itself. I just setup the same configuration on my local workstation and I don't have this issue at all. In fact, I didn't have to encode special characters and everything just check out. I am engaging the database team to help me figure out the issue with the DB user. I do not have control over the DB server.

Thanks for usql too. Found it useful. Sorry I am not a database engineer, just user of database.

Thanks

[burningalchemist]

@barywhyte No worries at all, hope the issue gets solved for you soon. usql is a decent tool to prove things work correct. Once you confirmed the connection is stable and you can execute queries, the rest should work as well. 👍

With the connection string itself, it's a little bit tricky, since sql_exporter goal is to support as many databases as possible, we use dburl dependency, which format at times might be different from particular drivers. It's consistent among all the supported drivers, though.

[barywhyte]

@burningalchemist You are right all along! Error was due to permissions given to SQL server user. Took a couple of hours to read about MS SQL installation, Studio manager, creating instances and adding databases and playing around with permissions over instances and individual databases. Then went to database engineers telling them exactly what needs to be done. All I needed was View any database permission. Damn it! Spend days working with people who are not knowledgeable enough about the service they own.

Thank you! This can be closed

[burningalchemist]

Hey @barywhyte thanks for following up on this. I'm glad you got it working. 👍🚀