Metabase database auto sync not adding new tables

vijayvatsalraj · October 21, 2021, 7:28pm

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-

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

I am looking for your assistance.

Thanks,
Vijay Raj

flamber · October 21, 2021, 7:35pm

Hi @vijayvatsalraj

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

vijayvatsalraj · October 23, 2021, 1:22pm

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)

flamber · October 24, 2021, 8:47am

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

vijayvatsalraj · October 24, 2021, 1:23pm

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

flamber · October 25, 2021, 8:02am

@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.

gonchik · October 19, 2023, 1:05pm

we meet with that on daily basis

Luiggi · October 20, 2023, 1:15am

check what's erroring, or increase the size of your snowflake cluster

Srikanta · December 16, 2023, 7:53am

Similar problem here, PostGre schema 'appointments' has new tables which are not getting picked up.

[00bb68fe-34a9-42a5-9f34-24bfea773756] 2023-12-16T13:18:19+05:30 DEBUG metabase.server.middleware.log GET /api/setting 200 1.0 ms (0 DB calls) App DB connections: 2/15 Jetty threads: 5/50 (1 idle, 0 queued) (109 total active threads) Queries in flight: 0 (0 queued)
[00bb68fe-34a9-42a5-9f34-24bfea773756] 2023-12-16T13:18:19+05:30 DEBUG metabase.server.middleware.log GET /api/session/properties 200 7.8 ms (2 DB calls) App DB connections: 2/15 Jetty threads: 5/50 (1 idle, 0 queued) (110 total active threads) Queries in flight: 0 (0 queued)
[00bb68fe-34a9-42a5-9f34-24bfea773756] 2023-12-16T13:18:19+05:30 DEBUG metabase.server.middleware.log GET /api/setup/admin_checklist 200 27.9 ms (11 DB calls) App DB connections: 0/15 Jetty threads: 3/50 (3 idle, 0 queued) (112 total active threads) Queries in flight: 0 (0 queued)
[00bb68fe-34a9-42a5-9f34-24bfea773756] 2023-12-16T13:18:21+05:30 DEBUG metabase.server.middleware.log GET /api/util/bug_report_details 200 3.7 ms (1 DB calls) App DB connections: 0/15 Jetty threads: 3/50 (4 idle, 0 queued) (112 total active threads) Queries in flight: 0 (0 queued)
[00bb68fe-34a9-42a5-9f34-24bfea773756] 2023-12-16T13:18:44+05:30 DEBUG metabase.server.middleware.log GET /api/util/bug_report_details 200 3.6 ms (1 DB calls) App DB connections: 0/15 Jetty threads: 4/50 (3 idle, 0 queued) (112 total active threads) Queries in flight: 0 (0 queued)
[00bb68fe-34a9-42a5-9f34-24bfea773756] 2023-12-16T13:19:51+05:30 DEBUG metabase.server.middleware.log GET /api/database/3 200 11.9 ms (3 DB calls) App DB connections: 0/15 Jetty threads: 3/50 (4 idle, 0 queued) (112 total active threads) Queries in flight: 0 (0 queued)
[00bb68fe-34a9-42a5-9f34-24bfea773756] 2023-12-16T13:19:57+05:30 INFO metabase.sync.util STARTING: Sync metadata for postgres Database 3 'Appointment Production'
[00bb68fe-34a9-42a5-9f34-24bfea773756] 2023-12-16T13:19:57+05:30 INFO metabase.sync.util STARTING: step 'sync-timezone' for postgres Database 3 'Appointment Production'
[00bb68fe-34a9-42a5-9f34-24bfea773756] 2023-12-16T13:19:57+05:30 DEBUG metabase.server.middleware.log POST /api/database/3/sync_schema 200 2.2 ms (1 DB calls) App DB connections: 0/15 Jetty threads: 3/50 (4 idle, 0 queued) (112 total active threads) Queries in flight: 0 (0 queued)
[00bb68fe-34a9-42a5-9f34-24bfea773756] 2023-12-16T13:19:57+05:30 INFO metabase.sync.util FINISHED: step 'sync-timezone' for postgres Database 3 'Appointment Production' (11.3 ms)
[00bb68fe-34a9-42a5-9f34-24bfea773756] 2023-12-16T13:19:57+05:30 INFO metabase.sync.util STARTING: step 'sync-tables' for postgres Database 3 'Appointment Production'
[00bb68fe-34a9-42a5-9f34-24bfea773756] 2023-12-16T13:19:57+05:30 INFO metabase.sync.util FINISHED: step 'sync-tables' for postgres Database 3 'Appointment Production' (39.0 ms)
[00bb68fe-34a9-42a5-9f34-24bfea773756] 2023-12-16T13:19:57+05:30 INFO metabase.sync.util STARTING: step 'sync-fields' for postgres Database 3 'Appointment Production'
[00bb68fe-34a9-42a5-9f34-24bfea773756] 2023-12-16T13:19:57+05:30 INFO metabase.sync.util FINISHED: step 'sync-fields' for postgres Database 3 'Appointment Production' (288.1 ms)
[00bb68fe-34a9-42a5-9f34-24bfea773756] 2023-12-16T13:19:57+05:30 INFO metabase.sync.util STARTING: step 'sync-fks' for postgres Database 3 'Appointment Production'
[00bb68fe-34a9-42a5-9f34-24bfea773756] 2023-12-16T13:19:57+05:30 INFO metabase.sync.util FINISHED: step 'sync-fks' for postgres Database 3 'Appointment Production' (150.1 ms)
[00bb68fe-34a9-42a5-9f34-24bfea773756] 2023-12-16T13:19:57+05:30 INFO metabase.sync.util STARTING: step 'sync-metabase-metadata' for postgres Database 3 'Appointment Production'
[00bb68fe-34a9-42a5-9f34-24bfea773756] 2023-12-16T13:19:57+05:30 INFO metabase.sync.util FINISHED: step 'sync-metabase-metadata' for postgres Database 3 'Appointment Production' (32.2 ms)
[00bb68fe-34a9-42a5-9f34-24bfea773756] 2023-12-16T13:19:58+05:30 INFO metabase.sync.util FINISHED: Sync metadata for postgres Database 3 'Appointment Production' (548.9 ms)
[00bb68fe-34a9-42a5-9f34-24bfea773756] 2023-12-16T13:19:58+05:30 INFO metabase.sync.util STARTING: Analyze data for postgres Database 3 'Appointment Production'
[00bb68fe-34a9-42a5-9f34-24bfea773756] 2023-12-16T13:19:58+05:30 INFO metabase.sync.util STARTING: step 'fingerprint-fields' for postgres Database 3 'Appointment Production'
[00bb68fe-34a9-42a5-9f34-24bfea773756] 2023-12-16T13:19:58+05:30 INFO metabase.sync.analyze fingerprint-fields Analyzed [·············································] 12% Table 161 'public.appointment_ratings'
[00bb68fe-34a9-42a5-9f34-24bfea773756] 2023-12-16T13:19:58+05:30 INFO metabase.sync.analyze fingerprint-fields Analyzed [·······································] 24% Table 168 'public.drug_reminder_notifications'
[00bb68fe-34a9-42a5-9f34-24bfea773756] 2023-12-16T13:19:58+05:30 INFO metabase.sync.util FINISHED: step 'fingerprint-fields' for postgres Database 3 'Appointment Production' (36.9 ms)
[00bb68fe-34a9-42a5-9f34-24bfea773756] 2023-12-16T13:19:58+05:30 INFO metabase.sync.util STARTING: step 'classify-fields' for postgres Database 3 'Appointment Production'
[00bb68fe-34a9-42a5-9f34-24bfea773756] 2023-12-16T13:19:58+05:30 INFO metabase.sync.analyze classify-fields Analyzed [·································] 35% Table 162 'public.user_address_mapping'
[00bb68fe-34a9-42a5-9f34-24bfea773756] 2023-12-16T13:19:58+05:30 INFO metabase.sync.analyze classify-fields Analyzed [···························] 47% Table 165 'public.drug_reminders'
[00bb68fe-34a9-42a5-9f34-24bfea773756] 2023-12-16T13:19:58+05:30 INFO metabase.sync.analyze classify-fields Analyzed [·····················] 59% Table 169 'public.appointments'
[00bb68fe-34a9-42a5-9f34-24bfea773756] 2023-12-16T13:19:58+05:30 INFO metabase.sync.util FINISHED: step 'classify-fields' for postgres Database 3 'Appointment Production' (25.3 ms)
[00bb68fe-34a9-42a5-9f34-24bfea773756] 2023-12-16T13:19:58+05:30 INFO metabase.sync.util STARTING: step 'classify-tables' for postgres Database 3 'Appointment Production'
[00bb68fe-34a9-42a5-9f34-24bfea773756] 2023-12-16T13:19:58+05:30 INFO metabase.sync.analyze classify-tables Analyzed [···············] 71% Table 163 'public.user_time_slots'
[00bb68fe-34a9-42a5-9f34-24bfea773756] 2023-12-16T13:19:58+05:30 INFO metabase.sync.analyze classify-tables Analyzed [·········] 82% Table 166 'public.appointment_transaction'
[00bb68fe-34a9-42a5-9f34-24bfea773756] 2023-12-16T13:19:58+05:30 INFO metabase.sync.analyze classify-tables Analyzed [************************···] 94% Table 170 'public.schema_version'
[00bb68fe-34a9-42a5-9f34-24bfea773756] 2023-12-16T13:19:58+05:30 INFO metabase.sync.util FINISHED: step 'classify-tables' for postgres Database 3 'Appointment Production' (16.9 ms)
[00bb68fe-34a9-42a5-9f34-24bfea773756] 2023-12-16T13:19:58+05:30 INFO metabase.sync.util FINISHED: Analyze data for postgres Database 3 'Appointment Production' (95.8 ms)
[00bb68fe-34a9-42a5-9f34-24bfea773756] 2023-12-16T13:20:05+05:30 DEBUG metabase.server.middleware.log GET /api/util/bug_report_details 200 5.1 ms (1 DB calls) App DB connections: 0/15 Jetty threads: 3/50 (4 idle, 0 queued) (112 total active threads) Queries in flight: 0 (0 queued)

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/120.0.0.0 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.232-176.381.amzn2.x86_64",
"user.language": "en",
"user.timezone": "GMT"
},
"metabase-info": {
"databases": [
"mysql",
"postgres"
],
"hosting-env": "elastic-beanstalk",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "13.10"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.2.18"
}
},
"run-mode": "prod",
"version": {
"date": "2021-05-27",
"tag": "v0.39.3",
"branch": "release-x.39.x",
"hash": "598a112"
},
"settings": {
"report-timezone": "Asia/Calcutta"
}
}
}

Luiggi · December 17, 2023, 2:19am

check if you can query those tables via Metabase SQL questions