MySQL view using other views unable to sync

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.

  1. I've followed your advice to run the query in Metabase
  2. 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.
  3. I've added collate "utf8mb4_unicode_ci" behind the WHERE clause and ran it again
  4. Boom: Everything properly visible in Metabase

Maybe this helps someone else in the future, too :slight_smile: