Metabase database auto sync not adding new tables

Hi Team,

We are connected to snowflake database and enabled the auto schema refresh every hour. But new tables are not added until I press sync manually for schema refresh, after it takes couple of hours to refresh.

So two problems here-

  1. Why new tables not added by auto schedule schema refresh in metabase?
  2. Hard refresh takes lot of time

I am looking for your assistance.

Thanks,
Vijay Raj

Hi @vijayvatsalraj

  1. Post "Diagnostic Info" from Admin > Troubleshooting.
  2. Check the logs, when you do a "Sync database schema now" - Admin > Troubleshooting > Logs.

Diagnostic Info
---------------------
{
"browser-info": {
"language": "en-US",
"platform": "Win32",
"userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/94.0.4606.81 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.11+9",
"java.vendor": "AdoptOpenJDK",
"java.vendor.url": "https://adoptopenjdk.net/",
"java.version": "11.0.11",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.11+9",
"os.name": "Linux",
"os.version": "4.14.181-140.257.amzn2.x86_64",
"user.language": "en",
"user.timezone": "GMT"
},
"metabase-info": {
"databases": [
"athena",
"postgres",
"snowflake",
"h2"
],
"hosting-env": "unknown",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "12.7"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.2.18"
}
},
"run-mode": "prod",
"version": {
"date": "2021-08-03",
"tag": "v0.40.2",
"branch": "release-x.40.x",
"hash": "b884d29"
},
"settings": {
"report-timezone": null
}
}
}

Logs( This is every hour trigger by metabase due to auto scheduling every hour. Here expectation is that it will get us all new tables. But it is not getting through auto schedule of schema refresh in metabase)

[d5650612-49fa-454e-b07f-a8c8709934e2] 2021-10-23T08:00:00-04:00 INFO metabase.task.send-pulses Sending scheduled pulses...
[d5650612-49fa-454e-b07f-a8c8709934e2] 2021-10-23T08:00:01-04:00 ERROR metabase.query-processor.middleware.catch-exceptions Error processing query: null

[56bbb00b-2428-4fc1-bc0a-17653fe16604] 2021-10-23T08:19:48-04:00 INFO metabase.driver.athena Running Athena query : 'DESCRIBE lp_contributionlake_catalog.public_messaging_core_events;'...
[56bbb00b-2428-4fc1-bc0a-17653fe16604] 2021-10-23T08:19:49-04:00 ERROR metabase.driver.athena Failed to execute query: DESCRIBE lp_contributionlake_catalog.public_messaging_core_events; [Simba]AthenaJDBC An error has been thrown from the AWS Athena client. FAILED: SemanticException MetaException(message:Insufficient Lake Formation permission(s) on default (Service: AmazonDataCatalog; Status Code: 400; Error Code: AccessDeniedException; Request ID: d8bdba44-1ccb-4b3c-8060-7a63c8f8ca0f; Proxy: null)) [Execution ID: b7c7be1d-f984-416b-9d78-6564a6cefe36]
[d5650612-49fa-454e-b07f-a8c8709934e2] 2021-10-23T08:45:07-04:00 DEBUG metabase.server.middleware.log GET /api/session/properties 200 5.3 ms (2 DB calls) App DB connections: 0/15 Jetty threads: 3/50 (4 idle, 0 queued) (125 total active threads) Queries in flight: 0 (0 queued)
[56bbb00b-2428-4fc1-bc0a-17653fe16604] 2021-10-23T08:45:07-04:00 DEBUG metabase.server.middleware.log GET /api/user/current 200 9.8 ms (3 DB calls) App DB connections: 0/15 Jetty threads: 3/50 (4 idle, 0 queued) (129 total active threads) Queries in flight: 0 (0 queued)
[56bbb00b-2428-4fc1-bc0a-17653fe16604] 2021-10-23T08:45:07-04:00 DEBUG metabase.server.middleware.log GET /api/database 200 19.0 ms (4 DB calls) App DB connections: 0/15 Jetty threads: 3/50 (4 idle, 0 queued) (130 total active threads) Queries in flight: 0 (0 queued)
[d5650612-49fa-454e-b07f-a8c8709934e2] 2021-10-23T08:45:07-04:00 DEBUG metabase.server.middleware.log GET /api/collection/root/items 200 8.8 ms (4 DB calls) App DB connections: 0/15 Jetty threads: 3/50 (4 idle, 0 queued) (126 total active threads) Queries in flight: 0 (0 queued)
[56bbb00b-2428-4fc1-bc0a-17653fe16604] 2021-10-23T08:45:07-04:00 DEBUG metabase.server.middleware.log GET /api/collection/root 200 3.7 ms (2 DB calls) App DB connections: 0/15 Jetty threads: 3/50 (4 idle, 0 queued) (130 total active threads) Queries in flight: 0 (0 queued)
[d5650612-49fa-454e-b07f-a8c8709934e2] 2021-10-23T08:45:09-04:00 DEBUG metabase.server.middleware.log GET /api/collection/root/items 200 9.5 ms (4 DB calls) App DB connections: 0/15 Jetty threads: 3/50 (4 idle, 0 queued) (126 total active threads) Queries in flight: 0 (0 queued)
[56bbb00b-2428-4fc1-bc0a-17653fe16604] 2021-10-23T08:45:15-04:00 DEBUG metabase.server.middleware.log GET /api/setting 200 562.9 µs (0 DB calls) App DB connections: 0/15 Jetty threads: 3/50 (4 idle, 0 queued) (130 total active threads) Queries in flight: 0 (0 queued)
[d5650612-49fa-454e-b07f-a8c8709934e2] 2021-10-23T08:45:15-04:00 DEBUG metabase.server.middleware.log GET /api/session/properties 200 4.0 ms (2 DB calls) App DB connections: 0/15 Jetty threads: 3/50 (4 idle, 0 queued) (126 total active threads) Queries in flight: 0 (0 queued)
[56bbb00b-2428-4fc1-bc0a-17653fe16604] 2021-10-23T08:45:15-04:00 DEBUG metabase.server.middleware.log GET /api/setup/admin_checklist 200 20.4 ms (11 DB calls) App DB connections: 0/15 Jetty threads: 3/50 (4 idle, 0 queued) (130 total active threads) Queries in flight: 0 (0 queued)
[d5650612-49fa-454e-b07f-a8c8709934e2] 2021-10-23T08:47:48-04:00 DEBUG metabase.server.middleware.log GET /api/util/bug_report_details 200 4.5 ms (1 DB calls) App DB connections: 0/15 Jetty threads: 3/50 (4 idle, 0 queued) (125 total active threads) Queries in flight: 0 (0 queued)
[d5650612-49fa-454e-b07f-a8c8709934e2] 2021-10-23T08:54:45-04:00 INFO metabase.models.field-values Field sbqq__group__c was previously automatically set to show a list widget, but now has 103 values. Switching Field to use a search widget instead.
[d5650612-49fa-454e-b07f-a8c8709934e2] 2021-10-23T09:00:00-04:00 INFO metabase.task.send-pulses Sending scheduled pulses...

[d5650612-49fa-454e-b07f-a8c8709934e2] 2021-10-23T09:02:50-04:00 DEBUG metabase.server.middleware.log GET /api/search 200 116.9 ms (4 DB calls) App DB connections: 0/15 Jetty threads: 3/50 (4 idle, 0 queued) (126 total active threads) Queries in flight: 0 (0 queued)
[56bbb00b-2428-4fc1-bc0a-17653fe16604] 2021-10-23T09:02:50-04:00 DEBUG metabase.server.middleware.log GET /api/database/7/schemas 200 40.8 ms (4 DB calls) App DB connections: 0/15 Jetty threads: 3/50 (4 idle, 0 queued) (130 total active threads) Queries in flight: 0 (0 queued)
[d5650612-49fa-454e-b07f-a8c8709934e2] 2021-10-23T09:02:50-04:00 DEBUG metabase.server.middleware.log GET /api/database/5/schemas 200 33.7 ms (4 DB calls) App DB connections: 0/15 Jetty threads: 3/50 (4 idle, 0 queued) (126 total active threads) Queries in flight: 0 (0 queued)
[d5650612-49fa-454e-b07f-a8c8709934e2] 2021-10-23T09:02:51-04:00 DEBUG metabase.server.middleware.log GET /api/database/2/schemas 200 26.7 ms (4 DB calls) App DB connections: 0/15 Jetty threads: 3/50 (4 idle, 0 queued) (126 total active threads) Queries in flight: 0 (0 queued)
[56bbb00b-2428-4fc1-bc0a-17653fe16604] 2021-10-23T09:02:51-04:00 DEBUG metabase.server.middleware.log GET /api/database/8/schemas 200 37.9 ms (4 DB calls) App DB connections: 0/15 Jetty threads: 3/50 (3 idle, 0 queued) (130 total active threads) Queries in flight: 0 (0 queued)
[56bbb00b-2428-4fc1-bc0a-17653fe16604] 2021-10-23T09:02:59-04:00 DEBUG metabase.server.middleware.log GET /api/collection/root/items 200 8.4 ms (4 DB calls) App DB connections: 0/15 Jetty threads: 3/50 (4 idle, 0 queued) (130 total active threads) Queries in flight: 0 (0 queued)
[d5650612-49fa-454e-b07f-a8c8709934e2] 2021-10-23T09:02:59-04:00 DEBUG metabase.server.middleware.log GET /api/collection/root 200 3.4 ms (2 DB calls) App DB connections: 0/15 Jetty threads: 3/50 (4 idle, 0 queued) (126 total active threads) Queries in flight: 0 (0 queued)
[56bbb00b-2428-4fc1-bc0a-17653fe16604] 2021-10-23T09:03:00-04:00 DEBUG metabase.server.middleware.log GET /api/collection/root/items 200 11.5 ms (4 DB calls) App DB connections: 0/15 Jetty threads: 3/50 (4 idle, 0 queued) (130 total active threads) Queries in flight: 0 (0 queued)

Hi @vijayvatsalraj
You should upgrade to 0.40.5. Latest release is 0.41.1: https://github.com/metabase/metabase/releases
So you're having problem with the third-party Athena driver. It's giving permission errors, so I I guess it's something to do with your Athena permissions: Insufficient Lake Formation permission

Hi, It is not about Athena errors. I am talking about snowflake new tables not get updated in metabase with auto scheduling. I am expecting whenever new table added in snowflake it should come into metabase via auto scheduling. Whenever I press manually on sync schema refresh in admin mode,it did bring new tables

@vijayvatsalraj Well, upgrade to at least 0.40.5, and post the relevant logs, since you posted log errors on Athena.
There's no difference between manual and automatic sync - they both do the same. It's difficult to say what the problem is without logs.