Field Filter Values Not Getting Refreshed

Get Help
1

I am using 0.32.5 with Firefox (latest update), H2 metadata db, and referencing SQL Server 2012 database

I have a dashboard that references a native SQL query. The query does a lookup on one table which has 11 rows. I have a filter field that properly shows the list of values in the drop down (there are 2 values).

I changed the database connection to point to an updated SQL Server database. The schema is identical but the table now has 13 rows. After setting the connection, I hit “Sync database schema now” and watched the console for errors (there weren’t any). I brought up my dashboard and the filter field only shows the original 2 values, the 3rd value that was added in the new rows, would not appear. All 13 rows displayed properly in the results area.

The problem is only related to the filter fields.

These are all the things I checked or tried that did not help:

Checked shared link
Hit F5 in browser
Logged out of Metabase (client side) and cleared Firefox cache
Logged out of Metabase (client side), cleared Firefox cache, and restarted Firefox
Set the Auto-Refresh and waited for it to complete

The only thing that worked was running “Re-scan field values now” then hitting F5 on the dashboard page.

I’ve checked the issues list and it seems most sync issues are closed. There are some that say it will take a while for synched changes to appear. I’m not sure what “a while” means. In my case, I changed to point to a different database but the real use case is that the data is updated in the database and the user wants to see the updated values. So I tested that too - when I come into the dashboard, all new data appears immediately, but the field filter dropdown does not have the new values.

I can’t re-scan the fields and tell the users to hit F5 on their browser every time the data is updated. Before I report this as a bug, I was wondering if anyone else is having problems or know what the cause could be.

By the way, I have “Enable Caching” set to Disabled.

2

There are multiple open issues involving this, but it seems really difficult to reproduce.
If you check your metadata database, then find the actual field in metabase_fieldvalues, does the values look like expected or not?

3

Hi @flamber,

Sorry, that took me a little while, never looked at the H2 db before.

The metabase_fieldvalues table does not have the new value.

4

Okay, like I said in the previous thread, you should really consider migrating to a different backend like Postgres or MariaDB/MySQL. It is not recommended to use H2 in production.
https://www.metabase.com/docs/latest/operations-guide/start.html#migrating-from-using-the-h2-database-to-mysql-or-postgres
When you sync, it logs overall information about table and field changes, so you’re saying it does not say anything about field changes even when there should be changes?
What happens if you delete the row and sync again?

5

I’ve played with this quite a bit now. Unfortunately, I can not check the contents of H2 when Metabase is running because it says the database is in use. So I have to keep shutting down and bringing back up MB which may have an effect on my testing. Yes, I’m sure mysql or Postgres would be better but I’m not in production and I’m not ready to convert yet.

Here’s the main pattern I found. If I add a new data row and re-run the dashboard (go back to Our Analytics and go back into the dashboard), the results show the new row but the filter field dropdown does not. H2’s metabase_fieldvalues does not have the updated value. If I re-scan the fields in MB, H2 has the new value. If I go into the dashboard again, the dropdown will not show the new value. I have to hit F5 in the browser or I have to logout, log back in, and go into the dashboard again to get the new value to appear. Deleting a row has the same effect on the dropdowns - they won’t refresh unless a re-scan is done followed by F5 -or- logging out, logging in, and running dashboard again.

Note that I always wait for the re-scan to complete by watching the console before I try anything else.

6

@sldorman Great breakdown.

So Metabase only does a field scan once a day - you can disable it or change the interval to something higher in Admin Settings > Databases > (database) > “This is a large database…” (click Save, then click the Scheduling tab)
You can force a sync with the API or by clicking the scan-button.

As for the interface not updating until you refresh the browser, there’s several open issues about this. Example, if you create a new database and go to Ask Question > Custom Question, then the new database is not available until you refresh.

7

Thanks for the information regarding scheduling, I never realized it was there. But the most frequent setting for field values is daily. For schema changes, the most frequent is hourly.

My schema won’t change but my data will. The user will run something from another app that will populate the database Metabase runs questions against. The user will then use MB to analyze the results. Users will do this frequently.

There is no other way to automatically re-scan frequently? Ideally, it should re-scan every time the query is run - and only scan those fields that are tied to filters on that question/dashboard to prevent lengthy response times.

8

Do you have any control of the other app? Meaning if it’s your own app, then you can make it send an API request to force a scan, when data is populated.
Another option would be to have a script that checks the app database every minute and will request the API to scan, when data has changed.
API references:
https://github.com/metabase/metabase/wiki/Using-the-REST-API
https://github.com/metabase/metabase/blob/master/docs/api-documentation.md#post-apifieldidrescan_values - specifically update only a single fields values, not the entire table or database.

9

@flamber, thanks for your valuable inputs on various questions. Is it possible to provide option to schedule scan of filter or database values every X minutes along with hourly etc?

10

I cannot find any feature request for that, so you’re welcome to create a new issue, but don’t expect it to get fixed, since your case is the perfect reason to be using API.
Scans of the entire database are very heavy, when the database multiple gigabytes storage and many millions of records, so having it run multiple times a day would not be a good idea.

11

I understand and you are right should be using API if required to refresh quite often