Execution issues for workflow with resources

Queries and Resources
, , ,
1

When running an SQL query or API call in workflow from within the self-hosted Retool setup, we get the following error:

"Internal Error running a block: Error: An internal server error occurred"

We checked our logs and the first error type we had was that you support only absolute URLs.

When we changed our DB connection string to use an absolute URL we still had an internal server error:

{“error”:{“code”:“ENOTFOUND”,“errno”:-3008,“hostname”:“our_db_host_name”,“message”:“getaddrinfo ENOTFOUND”,“name”:“Error”,“stack”:“Error: getaddrinfo ENOTFOUND our_db_connection_string\n at GetAddrInfoReqWrap.onlookup [as oncomplete] (node:dns:108:26)\n at GetAddrInfoReqWrap.callbackTrampoline (node:internal/async_hooks:130:17)“,”syscall”:“getaddrinfo”},“level”:“error”,“message”:“[postgres] error in getSchema/connect”,“pid”:105,“source”:“postgres”}

Our JS runs properly from within the workflows, and the only issue we have is with the resources (DB queries / API calls).

Please advise us on how we proceed and fix the usage of our resources in the workflows.

2

Hi @kmx,

Thanks for reaching out! Sorry to hear you're running into this issue :disappointed: Could you share some screenshots of the SQL resource set up? What happens when you click test connection on the resource?

For the API, what error are you seeing? And what is the API?

Also, can you share the Retool version # that you're using? Were any of these resources working previously?

3

Hey @Tess

The SQL resource works fine in other parts of our Retool app except for the workflows. We tested the connection again and it showed the success message (see screenshot below) :point_down:

sql-connection
sql-connection1380×114 6.79 KB

Same for the API, everything works fine in our Retool apps, however, in the workflows we receive the following error:

api-error
api-error1088×452 12.2 KB

The Retol version we use is:

  • Backend: tryretool/backend:3.26.4@sha256:c9eda39aa1e1d8a461da300de680b16695427f5839df8265a4f5265672afb014

  • Frontend: tryretool/https-portal:latest@sha256:13847ab77d70463f5eaf9f37d27708bc4e568408c96302cff83e47342909d680

This is our first time setting up workflows, so we are not aware if they worked before (on some other versions, etc.)

4

Hi @kmx,

Could you please share screenshots/details of how your team is deployed & the steps you've taken to set up Workflows? Our supported deployment types all have slightly different set up instructions for Workflows.

We'll also want to check your deployment's logs to see if there are additional related errors.

Thanks!

5

Hey @Tess,

Thank you for getting back to us!

This is our current setup, we use Nomad as an orchestrator, we believe temporal setup is not the issue because we can see system temporal durable tasks created by Retool already.

job "admin-retool" {
  region      = var.cluster_region
  datacenters = [var.data_center]
  type        = "service"
  namespace   = var.namespace

  meta {
    run_uuid = "${uuidv4()}"
  }

  constraint {
    attribute = attr.unique.hostname
    operator  = "regexp"
    value     = var.hostname
  }

  group "admin-retool" {
    count = 1

    volume "keys" {
      type      = "host"
      source    = "keys"
      read_only = false
    }

    volume "retool" {
      type      = "host"
      source    = "retool"
      read_only = false
    }

    network {

      port "api" {
        to = 3000
      }

      port "workflows" {
        to = 3008
      }

      port "workflows-worker" {
        to = 3005
      }
    }

    restart {
      attempts = 2
      interval = "5m"
      delay    = "15s"
      mode     = "delay"
    }

    task "admin-retool-api" {

      volume_mount {
        volume      = "keys"
        destination = "/root/.ssh"
        read_only   = false
      }

      volume_mount {
        volume      = "retool"
        destination = "/usr/local/retool-git-repo"
        read_only   = false
      }

      driver = "docker"

      config {
        image = var.image_backend

        ports        = ["api"]

        image_pull_timeout = "3m"
        auth_soft_fail = true

        auth {
          username = DOCKER_USERNAME
          password = DOCKER_TOKEN
        }

        args = ["bash", "-c", "./docker_scripts/start_api.sh"]
      }

      resources {
        cpu    = 2048
        memory = 4096
      }

      env {
        ENVIRONMENT = var.environment
        
        SERVICE_TYPE = "MAIN_BACKEND,DB_CONNECTOR,DB_SSH_CONNECTOR"
        COOKIE_INSECURE  = false
        DISABLE_INTERCOM = true
        DISABLE_MEMORY_AND_CPU_USAGE_LOGGING = true
        DISABLE_PUBLIC_PAGES = false
        LOG_AUDIT_EVENTS = true
        LOG_LEVEL = "info"
        DISABLE_GIT_SYNCING = true

        DBCONNECTOR_POSTGRES_POOL_MAX_SIZE = 100
        DBCONNECTOR_QUERY_TIMEOUT_MS = 120000

        WORKFLOW_BACKEND_HOST = NOMAD_HOST_ADDR_admin-retool-workflows
        WORKFLOW_TEMPORAL_CLUSTER_NAMESPACE = "admin"
        WORKFLOW_TEMPORAL_TLS_ENABLED = false

        USE_SHORT_SESSIONS = true

        HIDE_ALL_HEADERS_IN_AUDIT_LOG_EVENTS = true

        BASE_DOMAIN = "https://${var.domain_name}"

        WORKFLOW_TEMPORAL_TLS_CRT = *REDACTED*
        WORKFLOW_TEMPORAL_TLS_KEY = *REDACTED*
      }

      service {
        name = "admin-retool-api"

        port = "api"

        check {
          name     = "admin portal alive"
          type     = "http"
          path     = "/-/ready"
          interval = "10s"
          timeout  = "30s"
        }
      }

      template {
        destination = "secrets/config.env"
        env         = true
        change_mode   = "restart"
        data = <<EOH
{{ range service "temporal" }}
WORKFLOW_TEMPORAL_CLUSTER_FRONTEND_PORT={{ .Port }}
WORKFLOW_TEMPORAL_CLUSTER_FRONTEND_HOST={{ .Address }}
{{ end }}
EOH
      }

      template {
        destination = "secrets/env.vars"
        env         = true
        change_mode   = "restart"
        data        = <<EOF
{{- with nomadVar "path/to/retool-vars" -}}
{{- range $k, $v := . }}
{{ $k }}={{ $v }}
{{- end }}
{{- end }}
EOF
      }

    }


    task "admin-retool-jobs-runner" {

      volume_mount {
        volume      = "keys"
        destination = "/root/.ssh"
        read_only   = false
      }

      driver = "docker"

      config {
        image = var.image_backend

        image_pull_timeout = "10m"
        auth_soft_fail = true

        auth {
          username = DOCKER_USERNAME
          password = DOCKER_TOKEN
        }

        args = ["bash", "-c", "./docker_scripts/start_api.sh"]
      }

      resources {
        cpu    = 1024
        memory = 2048
      }

      env {
        ENVIRONMENT = var.environment
        SERVICE_TYPE = "JOBS_RUNNER"
        LOG_LEVEL = "info"
      }

      service {
        name = "admin-retool-jobs-runner"
      }

      template {
        destination = "secrets/env.vars"
        env         = true
        change_mode   = "restart"
        data        = <<EOF
{{- with nomadVar "path/to/retool-vars" -}}
{{- range $k, $v := . }}
{{ $k }}={{ $v }}
{{- end }}
{{- end }}
EOF
      }

    }

    task "admin-retool-workflows" {

      driver = "docker"

      config {
        image = var.image_backend

        ports        = ["workflows"]

        image_pull_timeout = "3m"
        auth_soft_fail = true

        auth {
          username = DOCKER_USERNAME
          password = DOCKER_TOKEN
        }

        args = ["bash", "-c", "./docker_scripts/start_api.sh"]
      }

      resources {
        cpu    = 1024
        memory = 3072
      }

      env {
        ENVIRONMENT = var.environment
        
        SERVICE_TYPE = "WORKFLOW_BACKEND,DB_CONNECTOR,DB_SSH_CONNECTOR"
        COOKIE_INSECURE  = false

        LOG_LEVEL = "info"

        DISABLE_DATABASE_MIGRATIONS = true
      }

      service {
        name = "admin-retool-workflows"
      }

      template {
        destination = "secrets/env.vars"
        env         = true
        change_mode   = "restart"
        data        = <<EOF
{{- with nomadVar "path/to/retool-vars" -}}
{{- range $k, $v := . }}
{{ $k }}={{ $v }}
{{- end }}
{{- end }}
EOF
      }
    
    }


    task "admin-retool-workflows-worker" {

      driver = "docker"

      config {
        image = var.image_backend

        ports        = ["workflows-worker"]

        image_pull_timeout = "3m"
        auth_soft_fail = true

        auth {
          username = DOCKER_USERNAME
          password = DOCKER_TOKEN
        }

        args = ["bash", "-c", "./docker_scripts/start_api.sh"]
      }

      service {
        name = "admin-retool-workflows-worker"
        
        port = "workflows-worker"

        check {
          name     = "workflows worker alive"
          type     = "http"
          path     = "/api/checkHealth"
          interval = "10s"
          timeout  = "30s"
        }
      }

      resources {
        cpu    = 1500
        memory = 3072
      }

      env {
        ENVIRONMENT = var.environment
        
        SERVICE_TYPE = "WORKFLOW_TEMPORAL_WORKER"
        COOKIE_INSECURE  = false

        LOG_LEVEL = "info"

        DISABLE_DATABASE_MIGRATIONS = true

        NODE_OPTIONS = "--max_old_space_size=1024"

        DBCONNECTOR_POSTGRES_POOL_MAX_SIZE = 100
        DBCONNECTOR_QUERY_TIMEOUT_MS = 120000

        WORKFLOW_BACKEND_HOST = NOMAD_HOST_ADDR_admin-retool-workflows
        WORKFLOW_TEMPORAL_CLUSTER_NAMESPACE = "admin"
        WORKFLOW_TEMPORAL_TLS_ENABLED = false
        
        WORKFLOW_TEMPORAL_TLS_CRT = *REDACTED*
        WORKFLOW_TEMPORAL_TLS_KEY = *REDACTED*
      }

      template {
        destination = "secrets/config.env"
        env         = true
        change_mode   = "restart"
        data = <<EOH
{{ range service "temporal" }}
WORKFLOW_TEMPORAL_CLUSTER_FRONTEND_PORT={{ .Port }}
WORKFLOW_TEMPORAL_CLUSTER_FRONTEND_HOST={{ .Address }}
{{ end }}
EOH
      }

      template {
        destination = "secrets/env.vars"
        env         = true
        change_mode   = "restart"
        data        = <<EOF
{{- with nomadVar "path/to/retool-vars" -}}
{{- range $k, $v := . }}
{{ $k }}={{ $v }}
{{- end }}
{{- end }}
EOF
      }
    
    }

  }
}

Aggregated Logs from admin-retool-api, admin-retool-jobs-runner, admin-retool-workflows, admin-retool-workflows-worker

admin-retool-workflows
2024-03-08T10:22:28+01:00	{"jobId":"6bea4e85-a978-4ff9-8e44-2b851699e7aa","level":"info","message":"Run block response [run_block_error]","organization":{"id":1,"name":"user2@ourdomain.com"},"pid":71,"requestId":"430aaa7f-5e0d-40c6-b87a-cc0e9e4e0d78","status":"run_block_error","timestamp":"2024-03-08T09:22:28.634Z","user":{"email":"user1@ourdomain.com","sid":"user_0683d50370354779b014228411220ac5"}}

admin-retool-jobs-runner
2024-03-08T10:22:28+01:00	{"jobId":"6bea4e85-a978-4ff9-8e44-2b851699e7aa","level":"info","message":"Successfully ran the block","timestamp":"2024-03-08T09:22:28.632Z"}

admin-retool-jobs-runner
2024-03-08T10:22:28+01:00	{"level":"warn","msg":"Internal Server Error","organization":{"id":1,"name":"user2@ourdomain.com"},"pid":71,"requestId":"430aaa7f-5e0d-40c6-b87a-cc0e9e4e0d78","stack":"Error: Internal Server Error\n    at /retool_backend/bundle/main.js:2527:42551","timestamp":"2024-03-08T09:22:28.635Z","type":"CAUGHT_ERROR","user":{"email":"user1@ourdomain.com","sid":"user_0683d50370354779b014228411220ac5"}}

admin-retool-jobs-runner
2024-03-08T10:22:28+01:00	{"error":{"message":"TypeError: Only absolute URLs are supported","stacktrace":"TypeError: Only absolute URLs are supported\n    at getNodeRequestOptions (/node_modules/node-fetch/lib/index.js:1327:9)\n    at /node_modules/node-fetch/lib/index.js:1450:19\n    at new Promise (<anonymous>)\n    at fetch (/node_modules/node-fetch/lib/index.js:1447:9)\n    at wrappedFetch (/node_modules/fetch-retry/index.js:69:9)\n    at Timeout._onTimeout (/node_modules/fetch-retry/index.js:125:11)\n    at listOnTimeout (node:internal/timers:569:17)\n    at process.processTimers (node:internal/timers:512:7)","type":"TypeError"},"jobId":"6bea4e85-a978-4ff9-8e44-2b851699e7aa","level":"error","message":"Error executing block:","orgId":1,"organization":{"id":1,"name":"user2@ourdomain.com"},"pid":71,"requestId":"430aaa7f-5e0d-40c6-b87a-cc0e9e4e0d78","timestamp":"2024-03-08T09:22:28.634Z","user":{"email":"user1@ourdomain.com","sid":"user_0683d50370354779b014228411220ac5"}}

admin-retool-jobs-runner
2024-03-08T10:22:29+01:00	{"level":"info","message":{"http":{"method":"POST","request":{"time":72.91628408432007},"status_code":200,"url_base":"https://ourdomain.com","url_path":"/api/workflow/c229d4a4-5979-4ec1-b0bf-7309ae1bfd8f"},"type":"REQUEST_FINISH"},"organization":{"id":1,"name":"user2@ourdomain.com"},"pid":103,"requestId":"b3d3393c-9f47-44ca-8cd6-0c1141d5666c","timestamp":"2024-03-08T09:22:29.832Z","user":{"email":"user1@ourdomain.com","sid":"user_0683d50370354779b014228411220ac5"}}

admin-retool-jobs-runner
2024-03-08T10:22:29+01:00	{"level":"info","message":{"http":{"method":"POST","url_base":"https://ourdomain.com","url_path":"/api/workflow/c229d4a4-5979-4ec1-b0bf-7309ae1bfd8f"},"type":"REQUEST_BEGIN"},"organization":{"id":1,"name":"user2@ourdomain.com"},"pid":103,"requestId":"b3d3393c-9f47-44ca-8cd6-0c1141d5666c","timestamp":"2024-03-08T09:22:29.759Z","user":{"email":"user1@ourdomain.com","sid":"user_0683d50370354779b014228411220ac5"}}

admin-retool-jobs-runner
2024-03-08T10:22:29+01:00	{"level":"info","message":{"CPU Usage % for process":"0.06","Memory Stats for container":{"freeSystemMemoryMb":11323.06432,"percentageFreeMemory":68.25,"totalSystemMemoryMb":16591.70816},"Memory Stats for process":{"heapTotalMb":352.44032,"heapUsedMb":329.161792,"percentageHeapUsed":93.4,"rssMb":520.003584},"Request handler process":true,"namespace":"memoryUsage","pid":73},"timestamp":"2024-03-08T09:22:29.558Z"}

admin-retool-workflows
2024-03-08T10:22:28+01:00	{"level":"info","message":{"http":{"method":"POST","request":{"time":3636.687691092491},"status_code":500,"url_base":"https://ourdomain.com","url_path":"/api/workflow/c229d4a4-5979-4ec1-b0bf-7309ae1bfd8f/runBlock?origin=frontend"},"type":"REQUEST_FINISH"},"organization":{"id":1,"name":"user2@ourdomain.com"},"pid":71,"requestId":"430aaa7f-5e0d-40c6-b87a-cc0e9e4e0d78","timestamp":"2024-03-08T09:22:28.640Z","user":{"email":"user1@ourdomain.com","sid":"user_0683d50370354779b014228411220ac5"}}

admin-retool-api
2024-03-08T10:22:28+01:00	{"level":"info","message":{"event":{"actionType":"RUN_WORKFLOW_BLOCK","geoLocation":null,"ipAddress":"10.80.89.99","metadata":{"query":{"actionType":"","allowedGroupIds":[],"allowedGroups":[],"bulkUpdatePrimaryKey":"","cacheKeyTtl":"","changeset":"","changesetIsObject":false,"changesetObject":"","confirmationMessage":null,"data":null,"dataArray":[],"databaseHostOverride":"","databaseNameOverride":"","databasePasswordOverride":"","databaseRoleOverride":"","databaseUsernameOverride":"","databaseWarehouseOverride":"","doNotThrowOnNoOp":false,"editorMode":"sql","enableBulkUpdates":false,"enableCaching":false,"enableErrorTransformer":false,"enableTransformer":false,"error":null,"errorTransformer":"// The variable 'data' allows you to reference the request's data in the transformer. \n// example: return data.find(element => element.isError)\nreturn data.error","evalType":"script","events":[],"filterBy":"","finished":null,"importedQueryDefaults":{},"importedQueryInputs":{},"isFetching":false,"isImported":false,"lastReceivedFromResourceAt":null,"metadata":null,"notificationDuration":"","offlineQueryType":"None","offlineUserQueryInputs":"","overrideOrgCacheForUserCache":false,"playgroundQueryId":null,"playgroundQuerySaveId":"latest","playgroundQueryUuid":"","privateParams":[],"query":"SELECT ... *REDACTED*","queryDisabled":"","queryDisabledMessage":"","queryFailureConditions":"","queryRefreshTime":"","queryRunTime":null,"queryThrottleTime":"750","queryTimeout":"120000","queryTriggerDelay":"0","rawData":null,"recordId":"","records":"","requestSentTimestamp":null,"requireConfirmation":false,"resourceNameOverride":"","resourceTypeOverride":null,"runWhenModelUpdates":false,"runWhenPageLoads":false,"runWhenPageLoadsDelay":"","servedFromCache":false,"shouldEnableBatchQuerying":false,"shouldUseLegacySql":false,"showFailureToaster":true,"showLatestVersionUpdatedWarning":false,"showSuccessToaster":true,"showUpdateSetValueDynamicallyToggle":true,"streamResponse":false,"successMessage":"","tableName":"","timestamp":0,"transformer":"// Query results are available as the `data` variable\nreturn data","updateSetValueDynamically":false,"warningCodes":[],"watchedParams":[],"workflowActionType":null,"workflowBlockUuid":null,"workflowId":null,"workflowParams":null,"workflowRunBodyType":"raw","workflowRunId":null},"triggerType":"manual","workflowId":"c229d4a4-5979-4ec1-b0bf-7309ae1bfd8f","workflowName":"Users Risk Scores Hourly Notifier"},"organizationId":1,"resourceName":"2c983da1-287d-4500-af72-9549daf82d1e","responseTimeMs":3639,"userAgent":null,"userId":2},"type":"AUDIT_LOG_EVENT"},"organization":{"id":1,"name":"user2@ourdomain.com"},"pid":71,"requestId":"430aaa7f-5e0d-40c6-b87a-cc0e9e4e0d78","timestamp":"2024-03-08T09:22:28.637Z","user":{"email":"user1@ourdomain.com","sid":"user_0683d50370354779b014228411220ac5"}}

admin-retool-api
2024-03-08T10:22:28+01:00	{"jobId":"6bea4e85-a978-4ff9-8e44-2b851699e7aa","level":"info","message":"Run block response [run_block_error]","organization":{"id":1,"name":"user2@ourdomain.com"},"pid":71,"requestId":"430aaa7f-5e0d-40c6-b87a-cc0e9e4e0d78","status":"run_block_error","timestamp":"2024-03-08T09:22:28.634Z","user":{"email":"user1@ourdomain.com","sid":"user_0683d50370354779b014228411220ac5"}}

admin-retool-workflows-worker
2024-03-08T10:22:28+01:00	{"jobId":"6bea4e85-a978-4ff9-8e44-2b851699e7aa","level":"info","message":"Successfully ran the block","timestamp":"2024-03-08T09:22:28.632Z"}

admin-retool-workflows-worker
2024-03-08T10:22:27+01:00	{"level":"info","message":{"CPU Usage % for process":"0.05","Memory Stats for container":{"freeSystemMemoryMb":11292.868608,"percentageFreeMemory":68.06,"totalSystemMemoryMb":16591.70816},"Memory Stats for process":{"heapTotalMb":237.817856,"heapUsedMb":208.110296,"percentageHeapUsed":87.51,"rssMb":384.688128},"Request handler process":false,"namespace":"memoryUsage","pid":11},"timestamp":"2024-03-08T09:22:27.167Z"}

admin-retool-workflows-worker
2024-03-08T10:22:28+01:00	{"level":"warn","msg":"Internal Server Error","organization":{"id":1,"name":"user2@ourdomain.com"},"pid":71,"requestId":"430aaa7f-5e0d-40c6-b87a-cc0e9e4e0d78","stack":"Error: Internal Server Error\n    at /retool_backend/bundle/main.js:2527:42551","timestamp":"2024-03-08T09:22:28.635Z","type":"CAUGHT_ERROR","user":{"email":"user1@ourdomain.com","sid":"user_0683d50370354779b014228411220ac5"}}

In case you need something else from our side, please let us know!

6

Hi @kmx Thank you!

Can you try to curl $WORKFLOW_BACKEND_HOST/api/checkHealth from the workflows-worker?

If you're not getting a successful response, it's likely an issue with WORKFLOW_BACKEND_HOST = NOMAD_HOST_ADDR_admin-retool-workflows - it likely needs to include a port (:3000).

1 Like