Error running agent: Error loading config file Error parsing sql array

pavannayakanti · October 26, 2021, 5:56pm

Hello Community,

my telegraf service which was working till yesterday suddenly stopped working with below error.
[root@h0012345 naypavk]# telegraf -config /app/ztoscity/src/telegraf/declinedpullrequests1.conf
2021-10-26T17:55:08Z I! Starting Telegraf 1.16.3
2021-10-26T17:55:08Z E! [telegraf] Error running agent: Error loading config file /app/ztoscity/src/telegraf/declinedpullrequests1.conf: Error parsing sql array, Undefined but requested output: sql

here is my content of telegraf config file:

# Configuration for telegraf agent
[agent]
  ## Default data collection interval for all inputs
  interval = "30s"
  ## Rounds collection interval to 'interval'
  ## ie, if interval="10s" then always collect on :00, :10, :20, etc.
  round_interval = true

  ## Telegraf will send metrics to outputs in batches of at most
  metric_buffer_limit = 10000

  collection_jitter = "0s"

  flush_interval = "20s"
  flush_jitter = "0s"

  precision = ""

  hostname = ""
  omit_hostname = false

###############################################################################
#                            INPUT PLUGINS                                    #
###############################################################################


# Read formatted metrics from one or more HTTP endpoints
[[inputs.http]]
  ## One or more URLs from which to read formatted metrics
  urls = [
        "https://bitbucket.domainsys.local/rest/api/1.0/projects/TOS/repos/tos/pull-requests?state=DECLINED&start=1&limit=100",
        "https://bitbucket.domainsys.local/rest/api/1.0/projects/TOS/repos/tos/pull-requests?state=DECLINED&start=101&limit=100",
        "https://bitbucket.domainsys.local/rest/api/1.0/projects/TOS/repos/tos/pull-requests?state=DECLINED&start=201&limit=100"]

  ## HTTP method
   method = "GET"

  ## Optional HTTP headers
  # headers = {"X-Special-Header" = "Special-Value"}
    headers = {"authorization" = "Basic xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"}

  ## Amount of time allowed to complete the HTTP request
   timeout = "5s"

  ## List of success status codes
   success_status_codes = [200]

     data_format = "json"

        json_query = "values"
        json_string_fields = ["state","createdDate","updatedDate","author_status","author_user_emailAddress","author_user_name","author_user_displayName","fromRef_id","fromRef_displayId","fromRef_latestCommit","fromRef_repository_name","fromRef_repository_project_key","toRef_id","toRef_displayId","toRef_repository_displayId","reviewers_0_user_name","reviewers_0_user_emailAddress","reviewers_0_user_displayName","reviewers_0_status","reviewers_1_user_name","reviewers_1_user_emailAddress","reviewers_1_user_displayName","reviewers_1_status","reviewers_2_user_name","reviewers_2_user_emailAddress","reviewers_2_user_displayName","reviewers_2_status","reviewers_3_user_name","reviewers_3_user_emailAddress","reviewers_3_user_displayName","reviewers_3_status","reviewers_4_user_name","reviewers_4_user_emailAddress","reviewers_4_user_displayName","reviewers_4_status","reviewers_5_user_name","reviewers_5_user_emailAddress","reviewers_5_user_displayName","reviewers_5_status","reviewers_6_user_name","reviewers_6_user_emailAddress","reviewers_6_user_displayName","reviewers_6_status"]
        name_override = "declined_pull_requests"


[[outputs.file]]
        files = ["xmlparser.out"]
        influx_sort_fields = true

###############################################################################
#                            OUTPUT PLUGINS                                   #
###############################################################################

# # Send metrics to SQL Database
[[outputs.sql]]
        driver = "pgx"
#
       data_source_name = "postgresql://myuser:mypass@h0012345.domainsys.local:5432/bitbucketdb"
       timestamp_column = "timestamp"

jpowers · October 26, 2021, 6:06pm

Hi!

To start I tried taking your config, however, s it is above, it is not valid TOML. In the section, [[inputs.http]] the urls array is missing a comma between the last and second to last URL.

Try fixing that section first, and if you still have issues try a TOML linter to ensure your configuration file is still valid.

Thanks!

pavannayakanti · October 27, 2021, 11:57am

Hello Jpowers, that was a typo i made while i making it the code shorter for reference. I have corrected that, and it can be ignored now.
please note that error is happening while puhsing json data to database.
Please see if you can advise further. Thanks.

jpowers · October 27, 2021, 1:14pm

The error would indicate that something is wrong with your configuration file. I would try to debug this by using a simple configuration file with just a single input and output and verify that telegraf can load with that.

I did try to use your output.sql config stanza and telegraf loaded and immediately tried to connect to the fake hostname.

pavannayakanti · October 27, 2021, 3:19pm

i could not understand the concept of debugging the config file with a single input and output. How does it looks like?

On another note, this is not limited to just one telegraf file. I have similar other telegraf configs and each telegraf started failing with the same error.

jpowers · October 27, 2021, 3:47pm

Hi,

I would do the following:

Create a simple configuration file with just one input and one output. Based on your output above, I would try something like this:

[[inputs mem]]
[[outputs.sql]]
  driver = "pgx"
  data_source_name = "postgresql://myuser:mypass@h0012345.domainsys.local:5432/bitbucketdb"
  timestamp_column = "timestamp"

Next I would run Telegraf in test mode, which will not try to connect to the outputs, but it is useful to find config errors.

telegraf --test --debug --config config.toml

Does that command successfully run? Or does it print any errors about your configuration file?

If it has issues with the configuration file, then you need to further debug that till it works. If that does not have issues, you could try this with every configuration file you are using.

Telegraf would not stop working randomly with that error about the configuration file without something changing. Therefore, going through each configuration file you are using and trying them out to figure out what is causing the issue would be a good next step.

pavannayakanti · October 27, 2021, 6:05pm

I tried your suggestion:
Commenting the below code shows the outcome(json data) with out any issues, proving there is no issue in fetching data from API. But just enabling [[outputs.sql]] in the config, it is ending up with below:

[root@h0012345 naypavk]# telegraf --config /app/xxxxxx/src/telegraf/declinedpullrequests.conf --test --debug
2021-10-27T17:56:12Z I! Starting Telegraf 1.16.3
2021-10-27T17:56:12Z E! [telegraf] Error running agent: Error loading config file /app/xxxxx/src/telegraf/declinedpullrequests.conf: Error parsing sql array, Undefined but requested output: sql

jpowers · October 27, 2021, 9:21pm

What does that config file look like? can you simplify it?

pavannayakanti · October 28, 2021, 11:33am

# Configuration for telegraf agent
[agent]
  interval = "30s"
  round_interval = true
  metric_batch_size = 1000
  metric_buffer_limit = 10000
  collection_jitter = "0s"
  flush_interval = "20s"
  flush_jitter = "0s"
  precision = ""
  hostname = ""
  omit_hostname = false
  
###############################################################################
#                            INPUT PLUGINS                                    #
###############################################################################


# Read formatted metrics from one or more HTTP endpoints
[[inputs.http]]
  ## One or more URLs from which to read formatted metrics
  urls = [ 
	"https://bitbucket.domainsys.local/rest/api/1.0/projects/TOS/repos/tos/pull-requests?state=DECLINED&start=1&limit=100",
	"https://bitbucket.domainsys.local/rest/api/1.0/projects/TOS/repos/tos/pull-requests?state=DECLINED&start=101&limit=100",
	"https://bitbucket.domainsys.local/rest/api/1.0/projects/TOS/repos/tos/pull-requests?state=DECLINED&start=201&limit=100"
  ]

  ## HTTP method
   method = "GET"

  ## Optional HTTP headers
  # headers = {"X-Special-Header" = "Special-Value"}
    headers = {"authorization" = "Basic xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"}


   timeout = "5s"
   success_status_codes = [200]

     data_format = "json"

	json_query = "values"	
        json_string_fields = ["state","createdDate","updatedDate","author_status","author_user_emailAddress","author_user_name","author_user_displayName","fromRef_id","fromRef_displayId","fromRef_latestCommit","fromRef_repository_name","fromRef_repository_project_key","toRef_id","toRef_displayId","toRef_repository_displayId","reviewers_0_user_name","reviewers_0_user_emailAddress","reviewers_0_user_displayName","reviewers_0_status","reviewers_1_user_name","reviewers_1_user_emailAddress","reviewers_1_user_displayName","reviewers_1_status","reviewers_2_user_name","reviewers_2_user_emailAddress","reviewers_2_user_displayName","reviewers_2_status","reviewers_3_user_name","reviewers_3_user_emailAddress","reviewers_3_user_displayName","reviewers_3_status","reviewers_4_user_name","reviewers_4_user_emailAddress","reviewers_4_user_displayName","reviewers_4_status","reviewers_5_user_name","reviewers_5_user_emailAddress","reviewers_5_user_displayName","reviewers_5_status","reviewers_6_user_name","reviewers_6_user_emailAddress","reviewers_6_user_displayName","reviewers_6_status"]

	name_override = "declined_pull_requests"
	
[[outputs.file]]
	files = ["xmlparser.out"]
	influx_sort_fields = true
  
###############################################################################
#                            OUTPUT PLUGINS                                   #
###############################################################################

# # Send metrics to SQL Database
#[[outputs.sql]]
#       driver = "pgx"
#       data_source_name = "postgresql://myuser:mypass@h0012345.associatesys.local:5432/bitbucketdb"
#       timestamp_column = "timestamp"

jpowers · October 28, 2021, 2:16pm

I took that config file and ran it:

./telegraf --config config.toml --debug
2021-10-28T14:12:54Z I! Starting Telegraf 
2021-10-28T14:12:54Z I! Loaded inputs: http
2021-10-28T14:12:54Z I! Loaded aggregators: 
2021-10-28T14:12:54Z I! Loaded processors: 
2021-10-28T14:12:54Z I! Loaded outputs: file
2021-10-28T14:12:54Z I! Tags enabled: host=ryzen
2021-10-28T14:12:54Z I! [agent] Config: Interval:30s, Quiet:false, Hostname:"ryzen", Flush Interval:20s
2021-10-28T14:12:54Z D! [agent] Initializing plugins
2021-10-28T14:12:54Z D! [agent] Connecting outputs
2021-10-28T14:12:54Z D! [agent] Attempting connection to [outputs.file]
2021-10-28T14:12:54Z D! [agent] Successfully connected to outputs.file
2021-10-28T14:12:54Z D! [agent] Starting service inputs
2021-10-28T14:13:00Z E! [inputs.http] Error in plugin: [url=https://bitbucket.domainsys.local/rest/api/1.0/projects/TOS/repos/tos/pull-requests?state=DECLINED&start=201&limit=100]: Get "https://bitbucket.domainsys.local/rest/api/1.0/projects/TOS/repos/tos/pull-requests?state=DECLINED&start=201&limit=100": dial tcp: lookup bitbucket.domainsys.local: no such host
2021-10-28T14:13:00Z E! [inputs.http] Error in plugin: [url=https://bitbucket.domainsys.local/rest/api/1.0/projects/TOS/repos/tos/pull-requests?state=DECLINED&start=101&limit=100]: Get "https://bitbucket.domainsys.local/rest/api/1.0/projects/TOS/repos/tos/pull-requests?state=DECLINED&start=101&limit=100": dial tcp: lookup bitbucket.domainsys.local: no such host
2021-10-28T14:13:00Z E! [inputs.http] Error in plugin: [url=https://bitbucket.domainsys.local/rest/api/1.0/projects/TOS/repos/tos/pull-requests?state=DECLINED&start=1&limit=100]: Get "https://bitbucket.domainsys.local/rest/api/1.0/projects/TOS/repos/tos/pull-requests?state=DECLINED&start=1&limit=100": dial tcp: lookup bitbucket.domainsys.local: no such host
^C2021-10-28T14:13:03Z D! [agent] Stopping service inputs
2021-10-28T14:13:03Z D! [agent] Input channel closed
2021-10-28T14:13:03Z I! [agent] Hang on, flushing any cached metrics before shutdown
2021-10-28T14:13:03Z D! [outputs.file] Buffer fullness: 0 / 10000 metrics
2021-10-28T14:13:03Z I! [agent] Stopping running outputs
2021-10-28T14:13:03Z D! [agent] Stopped Successfully

No issues with the config file getting loaded. I also tried uncommenting the SQL section and also was able to get it to load successfully with no errors about any config setting.

pavannayakanti · October 28, 2021, 2:27pm

Okay, in that case am left with no other option recreating the entire telegraf & postgres environment :

pavannayakanti · October 29, 2021, 12:30pm

Hello @jpowers , one quick update.
i created another environment with telegraf and same config file started working with out any issues.
So, something must have got messed up with telegraf setup in previous environment.
Anyways, thank you very much for your prompt inputs in these regards. No need to mention, it was a great help.

jpowers · October 29, 2021, 1:01pm

Glad to hear you got it working!

Topic		Replies	Views
Error parsing Agent, Undefined but requested input: Agent Telegraf	1	1373	November 16, 2021
Telegraf - Error running agent: Error loading config file InfluxDB 2 telegraf	2	2441	February 18, 2021
Error running agent: Error: no inputs found, did you provide a valid config file Telegraf telegraf	6	9876	May 26, 2022
Just instaled telegraf and im having some trouble with the config file Telegraf telegraf , docker	10	761	November 14, 2022
Telegraf precision parameter parsing in agent configuration Telegraf telegraf	10	1119	February 24, 2022

Error running agent: Error loading config file Error parsing sql array

Related Topics