can query errors cause loss of dynamic tables?

[judell]

In Slack (https://steampipe.slack.com/archives/C03A0M2MW0N/p1649885060931159?thread_ts=1649833418.465559&cid=C03A0M2MW0N), @johnsmyth reports:

1. I created some views:

> create view view_dynamic as select * from "Employees"
> create view view_static_same_plugin as select * from googlesheets_sheet
> create view view_static_different_plugin as select * from aws_ec2_instance
> select table_schema as schema_name,
       table_name as view_name
from information_schema.views
where table_schema not in ('information_schema', 'pg_catalog')
order by schema_name,
         view_name;
+-------------+------------------------------+
| schema_name | view_name                    |
+-------------+------------------------------+
| public      | view_dynamic                 |
| public      | view_static_different_plugin |
| public      | view_static_same_plugin      |
+-------------+------------------------------+

2. I ran steampipe many times in rapid succession:

for i in $(seq 1 20); do steampipe query "select * from view_dynamic"; done

3 it ran successfully several times, then failed with:

Warning: executeQueries: query 1 of 1 failed: ERROR: rpc error: code = Unknown desc = googleapi: Error 429: Quota exceeded for quota metric 'Read requests' and limit 'Read requests per minute per user' of service 'sheets.googleapis.com' for consumer 'project_number:558253193561'.
Details:
[
  {
    "@type": "type.googleapis.com/google.rpc.ErrorInfo",
    "domain": "googleapis.com",
    "metadata": {
      "consumer": "projects/558253193561",
      "quota_limit": "ReadRequestsPerMinutePerUser",
      "quota_metric": "sheets.googleapis.com/read_requests",
      "service": "sheets.googleapis.com"
    },
    "reason": "RATE_LIMIT_EXCEEDED"
  }
]
, rateLimitExceeded (SQLSTATE HV000)

4 I ran steampipe query, and the googlesheets views were gone:

select table_schema as schema_name, table_name as view_name from information_schema.views where table_schema not in ('information_schema', 'pg_catalog') order by schema_name, view_name; +-------------+------------------------------+ | schema_name | view_name | +-------------+------------------------------+ | public | view_static_different_plugin | +-------------+------------------------------+

My assumption is that because it couldn't create the table (because of the throttling error), steampipe dropped and recreated the schema, and the dependent views were also deleted.

[tnelson-doghouse]

As an additional datum, when I added a second Google Sheet, the dashboard completely failed to start. The error message was:

Error: dashboard server startup timed out

If the dashboard were a little more resilient, and able to maybe wait a few seconds and try again, that'd be great.
Not sure if this problem is related or not.

[tnelson-doghouse]

Further note; it may actually have been a third Google sheet that I was adding; I had one configured in that I don't think I was using much.

[github-actions[bot]]

This issue is stale because it has been open 60 days with no activity. Remove stale label or comment or this will be closed in 30 days.

[tnelson-doghouse]

Has this been fixed, or is it still a problem?

[binaek]

Hi @tnelson-doghouse ...

Thank you for bringing this our notice. We are looking if this is still an issue.

[binaek]

I could not reproduce this in my local (v0.20.10).

However, looking back at the implementation of schema fetching in the googlesheets plugin here is what I believe could be happening:

1. API calls in the plugin hits rate limits
2. Next time server restarts, schema fetching errors because of the rate limit hit by the plugin (the googlesheets plugin makes API calls during schema creation)
3. Steampipe drops the old schema - because the schema fetch has errored

Steampipe fetches schema from plugins on every restart (and when config files change while it's running)

When working with dynamic plugins like googlesheets, I think it's better to just have the service running with steampipe service start. Other steampipe commands will discover the running steampipe service.

dynamic plugins are ones which do not have a static set of tables and columns. They generate their table/column set on the fly when they are started up.

[binaek]

Closing this issue for now! Please reopen if required.