How to turn model caching on?

Get Help
1

Hello,

I have installed a local instance with a connection to my MySQL database.

When I want to activate the data model cache, I get the message "Lack permission to create table in schema metabase_cache_5e4bf_2".

When I look at the logs, I get this message:

INFO metabase.driver.mysql.ddl Step :persist.check/create-schema was successful for db WordPress - Beautysane.com
WARN metabase.driver.mysql.ddl 
java.sql.SQLTransientConnectionException: (conn=1382660) Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting.

How can I solve this problem ?

At the moment, my experience with Metabase is very slow without this option.

Thank you very much,

2

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html

Metabase does not create tables with a primary key on its cache so you might consider disabling that global variable on MySQL

3

Thank you for your answer.
How can I do this?

I tried to :

  1. I create the database from the interface
  2. I connect to this database from the command line
  3. I display the variables: sql_require_primary_key=ON
  4. I run SET sql_require_primary_key = OFF;
  5. Display variables: sql_require_primary_key=OFF
  6. I click on "Turn model caching ON".
  7. My database is deleted

In any case, my action SET sql_require_primary_key = OFF; is not permanent, because if I log out and then back in, my variable is set to ON again.

If not, isn't there another way?
It's unusual not to have a primary key.

4

My host doesn't allow me to deactivate sql_require_primary_key globally, what can I do ?

Metabase is able to access the database since it deleted it after the error...

5

just added Persisted models in MySQL won't work if sql_require_primary_key=true · Issue #32802 · metabase/metabase · GitHub, there's no workaround currently other than changing the host

6

Okay, thanks, I hope the team finds a solution soon, it's a real shame.

Otherwise, do you know if it's possible to manually create the tables Metabase needs?
Even if it means adding a useless primary key in AUTO INCREMENT to keep Mysql happy?

7

Nope ? No another idea ?

8

Build “manually” a materialized view. As MySQL doesn’t have such concept you’ll need to build these manually with scripts (code) with a task runner

9

Thanks for your answer.
I have no problem building the tables manually via third-party scripts.

Unfortunately I haven't found any documentation on what form this MySQL table should take for the Metabase cache. Do you have any information on this?

10

No special form whatsoever

11

I'm sorry, I don't understand your answer.
What can I do?

12
  1. install Apache Airflow
  2. build a task with python that does the following:
    2.1) drop the current table (materialized view) you created before
    2.1) selects the table you want to materialize and save it with a special name
  3. tells Metabase to go and fetch this new table with the Notify