[receiver/sqlquery] report component status on scrape and collection failures

[vyagh]

Description

sqlqueryreceiver had no way to signal to the collector that a database connection or query was failing, it would just log errors and carry on.
This hooks into the componentstatus API so the healthcheckv2 extension can surface those failures as an unhealthy status, making the receiver's health alertable without digging through logs.

Link to tracking issue

fixes #43837

[github-actions]

Welcome, contributor! Thank you for your contribution to opentelemetry-collector-contrib.

Important reminders:

• Please review our Contributing Guidelines.
• Don't forget to sign the Contributor License Agreement (CLA) if you haven't already.

A maintainer will review your pull request soon. Thank you for helping make OpenTelemetry better!

[dmitryax]

@axw do you think it makes sense to be implemented right in the scraping controller instead?

[axw]

@dmitryax hmm, not sure. That would mean that a single scrape failing implies that the component is generally unhealthy? Not sure how well that generalises.

It's also unclear to me if this PR is the right solution for the linked issue, which specifically talks about connection status. If the connection is healthy but a query fails, then should the component be marked unhealthy?

[vyagh]

@axw so should i only report unhealthy when db connection itself fails, and leave query errors as just logged warnings?

[axw]

@vyagh I'm not sure that's a great solution either, since it's not surfacing all the information. I think the ideal solution may involve multiple levels of status reporting: open-telemetry/opentelemetry-collector#13210

If that sounds good to @dmitryax, then I'd suggest doing it in multiple steps:

• Start by reporting just connection-level failures for the receiver component
• Follow up later with query-specific subcomponent reporting

[vyagh]

got it, i'll wait for dmitryax's opinion :)

[vyagh]

push to resolve merge conflicts

[vyagh]

@dmitryax should i implement Andrew's solution?