I've created a view in a dedicated MySQL schema ("analytics") that contains data from plenty of other schemas. Since my intended master view is a little complex and sub-queries in views are not possible, I needed to create multiple other views and join them into my master view.
When I now try to connect this "analytics" schema to Metabase, all the views are showing up except the master view that is building up on other views.
I also tried to create a dummy view, synced it to Metabase successfully and then changed the view's query, but then Metabase threw out a sync error when trying to access the data. Removing and re-adding didn't help either.
How can I properly sync this master view?
Hi @Matthias
MySQL views has two types of security - either "definer" or "invoker".
Try running this query from within Metabase select * from your_master_view limit 1
You'll need to post the error during sync, since I'm guessing that it is a privilege problem.
hi @flamber, great, this was basically the hint for my fix.
- I've followed your advice to run the query in Metabase
- Got this error message:
Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation '>=' . This was because there's a "WHERE" clause that mixes different collations.
- I've added
collate "utf8mb4_unicode_ci" behind the WHERE clause and ran it again
- Boom: Everything properly visible in Metabase
Maybe this helps someone else in the future, too 