Problem using the jobs config pattern

[nipthisin]

Hi,
I doubt this is a bug so I'm posting it here, but hope someone can help with finding the problem.

Describe the bug
While trying to use the 'jobs' part of the config in order to have multiple DB connections instead of 'target', I receive multiple errors.
When using just one connection with a 'target' config, it works just fine.

To Reproduce
I add new lines to the jobs part one by one. It gives the correct error about missing the next respective part one by one.
Until I get to the last few lines where you define the connections. Described below.

Expected behavior
I get to connect to at least one database without an error.

Configuration
It works until this point and complains with message "exactly one of 'jobs' and 'target' must be defined":

jobs:
- job_name: test_job
  collectors: [test_collector]
  static_configs:
   - targets:

After adding the next line there is an error "Unknown field in static_config: testname":

jobs:
- job_name: test_job
  collectors: [test_collector]
  static_configs:
   - targets:
     testname:

When only using the db string there is the error "did not find expected key":

jobs:
- job_name: test_job
  collectors: [test_collector]
  static_configs:
   - targets:
     : 'test'

This is how it should be written because it is how it works for all the other parts of the config. When having the full key value pair there is still the error "Unknown field in static_config: testname":

jobs:
- job_name: test_job
  collectors: [test_collector]
  static_configs:
   - targets:
     testname: 'test'

Playing with the indentation gives the error "exactly one of 'jobs' and 'target' must be defined":

jobs:
- job_name: test_job
  collectors: [test_collector]
  static_configs:
   - targets:
       testname: 'test'

[burningalchemist]

Hi @nipthisin, could you please wrap your snippets in the code block? It's mainly about the configuration definition, and sadly we're dealing with YAML design imperfections. I'm happy to assist here, though. :)

[burningalchemist]

Have a look at the working example in the README.md:

The jobs/target error ("exactly one of 'jobs' and 'target' must be defined":) implies that you need to remove/comment out the target stanza (single target mode) and only keep jobs, otherwise sql_exporter wouldn't be able to decide which mode to run.

[burningalchemist]

Also, I forgot to ask in the first place - what's your version? and how you deploy it.

[nipthisin]

I'm using 'test' as an example here, but in my actual setup I use the string that works in the single 'target' config.
The whole real config isn't more than I've already shown. The 'general' and 'collectors' sections are fine because they work when using the single 'target' config. Here is the full slightly redacted version:

collectors:
- collector_name: performance
  metrics:
  - help: Count of actual successful performance records per type
    key_labels:
    - type
    metric_name: XXX_performance_successful_count
    query: "SELECT count(*) as count,\n    c.performance_type as type\nFROM performance.performance
      p JOIN performance.code c on p.code_id  = c.id\nGROUP BY 2 \n"
    type: gauge
    value_label: sum
    values:
    - count
global:
  max_connection_lifetime: 5m
  max_connections: 3
  max_idle_connections: 3
  min_interval: 0s
  scrape_error_drop_interval: 0s
  scrape_timeout: 180s
  scrape_timeout_offset: 500ms
jobs:
- job_name: XXX_psql_targets
  collectors: [performance]
  static_configs:
  - targets:
      pg1: 'postgresql://USER:PW@MACHINE:PORT/db_int_XXX?sslmode=disable'

We're on version 0.15.0. I don't have control over it but can request an update.

[burningalchemist]

Hey @nipthisin, I've taken your example, changed the DSN to my test db file, and the query, and it works without any issues:

collectors:
- collector_name: performance
  metrics:
  - metric_name: test
    help: Count of actual successful performance records per type
    type: gauge
    key_labels: [Username]
    values: [Identifier]
    query: |
      SET @@DELIMITER = ";";
      SELECT Username, Identifier FROM `username.csv` ;
global:
  max_connection_lifetime: 5m
  max_connections: 3
  max_idle_connections: 3
  min_interval: 0s
  scrape_error_drop_interval: 0s
  scrape_timeout: 180s
  scrape_timeout_offset: 500ms
jobs:
- job_name: XXX_psql_targets
  collectors: [performance]
  static_configs:
  - targets:
      pg1: 'csvq:/sql_exporter/test.csv'

If you use Kubernetes for instance and ConfigMaps, this might be something related to the mounting and potentially might create a malformed file.

I'd suggest running a standalone app locally, and validate the configuration. Even though you won't be able to access your database instance, you'd at least pass the config validation check.

[nipthisin]

We do use Kubernetes and ConfigMaps yes.

I'll try to run it locally, but I assume I'll get the same (positive) results like you do then.

[burningalchemist]

Well, that'd be a way to identify the place of a malformed configuration. Also, if you have a positive result, it'd also mean that's the issue is beyond the application.

Originally this project was written for Kubernetes as well, and it works on a large scale for a plenty of companies I know, so I don't think Kubernetes is a blocker here.

My suspicion at the moment is that somehow your configuration file is malformed when it's mounted. With ConfigMaps I guess you have to check for newlines and indents and stuff. 🤔

[burningalchemist]

Let me test this config with v0.15 (the latest is v0.17.1) and I'll get back to you. 👍

[burningalchemist]

@nipthisin, I've just checked the version 0.15 for any regressions, but it seems working fine as well. (By the way, if you hit /config endpoint, it returns the parsed configuration, which is not our case so far).

[nipthisin]

@burningalchemist
I'm sorry it took this long. We've swapped over to not using configmaps anymore, but it still does not work. We'll have to find another way.
Thank you for all the effort you put into this.

[burningalchemist]

@nipthisin Would you like us to schedule and hop on a call? I've helped some AWS folks in the past this way. Happy to suggest the same to you. 👍