V0.45.1 model caching on MySQL is still not working

Arvydas · December 16, 2022, 8:07am

Hello everyone,

Do not know if I am the only one, but still not able to turn on model caching on MySQL databases even running on newest V0.45.1 version.

Do I need to do anything in addition, to be able to turn that feature on?

All the possible permissions are granted so there is no questioning that.

Thank you in advance.

flamber · December 16, 2022, 9:00am

Hi @Arvydas
Post MySQL version.
Post "Diagnostic Info" from Admin > Troubleshooting.
Post the full stacktraces from Admin > Troubleshooting > Logs.
Of course correct privileges can be questioned.

Arvydas · December 16, 2022, 9:11am

I'm using MySQL 8.0,

Diagnostic info:

{
  "browser-info": {
    "language": "en-GB",
    "platform": "MacIntel",
    "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/108.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.17+8",
    "java.vendor": "Eclipse Adoptium",
    "java.vendor.url": "https://adoptium.net/",
    "java.version": "11.0.17",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.17+8",
    "os.name": "Linux",
    "os.version": "4.4.0-210-generic",
    "user.language": "en",
    "user.timezone": "GMT"
  },
  "metabase-info": {
    "databases": [
      "mysql",
      "h2"
    ],
    "hosting-env": "unknown",
    "application-database": "h2",
    "application-database-details": {
      "database": {
        "name": "H2",
        "version": "1.4.197 (2018-03-18)"
      },
      "jdbc-driver": {
        "name": "H2 JDBC Driver",
        "version": "1.4.197 (2018-03-18)"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2022-12-07",
      "tag": "v0.45.1",
      "branch": "release-x.45.x",
      "hash": "019d31c"
    },
    "settings": {
      "report-timezone": null
    }
  }
}

Full stacktraces:

[fd251130-1482-4262-afe3-1a110bd97ee9] 2022-12-16T11:01:07+02:00 WARN metabase.driver.mysql.ddl Undoing step `:persist.check/create-table` for db DWH - Cubes
[fd251130-1482-4262-afe3-1a110bd97ee9] 2022-12-16T11:01:07+02:00 WARN metabase.driver.mysql.ddl Undoing step `:persist.check/create-schema` for db DWH - Cubes
[fd251130-1482-4262-afe3-1a110bd97ee9] 2022-12-16T11:01:07+02:00 ERROR metabase.server.middleware.log POST /api/database/2/persist 500 88.9 ms (3 DB calls) 
{:via
 [{:type clojure.lang.ExceptionInfo,
   :message "Lack permission to read table in schema metabase_cache_a5484_2",
   :data {:error :persist.check/read-table, :database "DWH - Cubes"},
   :at [metabase.api.database$fn__78654 invokeStatic "database.clj" 759]}],
 :trace
 [[metabase.api.database$fn__78654 invokeStatic "database.clj" 759]
  [metabase.api.database$fn__78654 invoke "database.clj" 737]
  [compojure.core$wrap_response$fn__29287 invoke "core.clj" 160]
  [compojure.core$wrap_route_middleware$fn__29271 invoke "core.clj" 132]
  [compojure.core$wrap_route_info$fn__29276 invoke "core.clj" 139]
  [compojure.core$wrap_route_matches$fn__29280 invoke "core.clj" 151]
  [compojure.core$routes$fn__29299$f__29300 invoke "core.clj" 198]
  [compojure.core$routes$fn__29299$f__29300$respond_SINGLEQUOTE___29301 invoke "core.clj" 197]
  [compojure.core$wrap_route_matches$fn__29280 invoke "core.clj" 153]
  [compojure.core$routes$fn__29299$f__29300 invoke "core.clj" 198]
  [compojure.core$routes$fn__29299$f__29300$respond_SINGLEQUOTE___29301 invoke "core.clj" 197]
  [compojure.core$wrap_route_matches$fn__29280 invoke "core.clj" 153]
  [compojure.core$routes$fn__29299$f__29300 invoke "core.clj" 198]
  [compojure.core$routes$fn__29299$f__29300$respond_SINGLEQUOTE___29301 invoke "core.clj" 197]
  [compojure.core$wrap_route_matches$fn__29280 invoke "core.clj" 153]
  [compojure.core$routes$fn__29299$f__29300 invoke "core.clj" 198]
  [compojure.core$routes$fn__29299$f__29300$respond_SINGLEQUOTE___29301 invoke "core.clj" 197]
  [compojure.core$wrap_route_matches$fn__29280 invoke "core.clj" 152]
  [compojure.core$routes$fn__29299$f__29300 invoke "core.clj" 198]
  [compojure.core$routes$fn__29299$f__29300$respond_SINGLEQUOTE___29301 invoke "core.clj" 197]
  [compojure.core$wrap_route_matches$fn__29280 invoke "core.clj" 153]
  [compojure.core$routes$fn__29299$f__29300 invoke "core.clj" 198]
  [compojure.core$routes$fn__29299$f__29300$respond_SINGLEQUOTE___29301 invoke "core.clj" 197]
  [compojure.core$wrap_route_matches$fn__29280 invoke "core.clj" 152]
  [compojure.core$routes$fn__29299$f__29300 invoke "core.clj" 198]
  [compojure.core$routes$fn__29299$f__29300$respond_SINGLEQUOTE___29301 invoke "core.clj" 197]
  [compojure.core$wrap_route_matches$fn__29280 invoke "core.clj" 153]
  [compojure.core$routes$fn__29299$f__29300 invoke "core.clj" 198]
  [compojure.core$routes$fn__29299$f__29300$respond_SINGLEQUOTE___29301 invoke "core.clj" 197]
  [compojure.core$wrap_route_matches$fn__29280 invoke "core.clj" 152]
  [compojure.core$routes$fn__29299$f__29300 invoke "core.clj" 198]
  [compojure.core$routes$fn__29299$f__29300$respond_SINGLEQUOTE___29301 invoke "core.clj" 197]
  [compojure.core$wrap_route_matches$fn__29280 invoke "core.clj" 152]
  [compojure.core$routes$fn__29299$f__29300 invoke "core.clj" 198]
  [compojure.core$routes$fn__29299$f__29300$respond_SINGLEQUOTE___29301 invoke "core.clj" 197]
  [compojure.core$wrap_route_matches$fn__29280 invoke "core.clj" 152]
  [compojure.core$routes$fn__29299$f__29300 invoke "core.clj" 198]
  [compojure.core$routes$fn__29299$f__29300$respond_SINGLEQUOTE___29301 invoke "core.clj" 197]
  [compojure.core$wrap_route_matches$fn__29280 invoke "core.clj" 153]
  [compojure.core$routes$fn__29299$f__29300 invoke "core.clj" 198]
  [compojure.core$routes$fn__29299$f__29300$respond_SINGLEQUOTE___29301 invoke "core.clj" 197]
  [compojure.core$wrap_route_matches$fn__29280 invoke "core.clj" 153]
  [compojure.core$routes$fn__29299$f__29300 invoke "core.clj" 198]
  [compojure.core$routes$fn__29299$f__29300$respond_SINGLEQUOTE___29301 invoke "core.clj" 197]
  [compojure.core$wrap_route_matches$fn__29280 invoke "core.clj" 152]
  [compojure.core$routes$fn__29299$f__29300 invoke "core.clj" 198]
  [compojure.core$routes$fn__29299$f__29300$respond_SINGLEQUOTE___29301 invoke "core.clj" 197]
  [compojure.core$wrap_route_matches$fn__29280 invoke "core.clj" 152]
  [compojure.core$routes$fn__29299$f__29300 invoke "core.clj" 198]
  [compojure.core$routes$fn__29299 invoke "core.clj" 200]
  [metabase.server.middleware.auth$enforce_authentication$fn__70471 invoke "auth.clj" 14]
  [compojure.core$routes$fn__29299$f__29300 invoke "core.clj" 198]
  [compojure.core$routes$fn__29299 invoke "core.clj" 200]
  [compojure.core$make_context$handler__29327 invoke "core.clj" 289]
  [compojure.core$make_context$fn__29331 invoke "core.clj" 299]
  [compojure.core$routes$fn__29299$f__29300 invoke "core.clj" 198]
  [compojure.core$routes$fn__29299$f__29300$respond_SINGLEQUOTE___29301 invoke "core.clj" 197]
  [compojure.core$make_context$fn__29331 invoke "core.clj" 300]
  [compojure.core$routes$fn__29299$f__29300 invoke "core.clj" 198]
  [compojure.core$routes$fn__29299$f__29300$respond_SINGLEQUOTE___29301 invoke "core.clj" 197]
  [compojure.core$make_context$fn__29331 invoke "core.clj" 300]
  [compojure.core$routes$fn__29299$f__29300 invoke "core.clj" 198]
  [compojure.core$routes$fn__29299$f__29300$respond_SINGLEQUOTE___29301 invoke "core.clj" 197]
  [compojure.core$make_context$fn__29331 invoke "core.clj" 300]
  [compojure.core$routes$fn__29299$f__29300 invoke "core.clj" 198]
  [compojure.core$routes$fn__29299$f__29300$respond_SINGLEQUOTE___29301 invoke "core.clj" 197]
  [compojure.core$make_context$fn__29331 invoke "core.clj" 300]
  [compojure.core$routes$fn__29299$f__29300 invoke "core.clj" 198]
  [compojure.core$routes$fn__29299$f__29300$respond_SINGLEQUOTE___29301 invoke "core.clj" 197]
  [compojure.core$make_context$fn__29331 invoke "core.clj" 300]
  [compojure.core$routes$fn__29299$f__29300 invoke "core.clj" 198]
  [compojure.core$routes$fn__29299$f__29300$respond_SINGLEQUOTE___29301 invoke "core.clj" 197]
  [compojure.core$make_context$fn__29331 invoke "core.clj" 300]
  [compojure.core$routes$fn__29299$f__29300 invoke "core.clj" 198]
  [compojure.core$routes$fn__29299$f__29300$respond_SINGLEQUOTE___29301 invoke "core.clj" 197]
  [compojure.core$make_context$fn__29331 invoke "core.clj" 300]
  [compojure.core$routes$fn__29299$f__29300 invoke "core.clj" 198]
  [compojure.core$routes$fn__29299$f__29300$respond_SINGLEQUOTE___29301 invoke "core.clj" 197]
  [compojure.core$make_context$fn__29331 invoke "core.clj" 300]
  [compojure.core$routes$fn__29299$f__29300 invoke "core.clj" 198]
  [compojure.core$routes$fn__29299$f__29300$respond_SINGLEQUOTE___29301 invoke "core.clj" 197]
  [compojure.core$make_context$fn__29331 invoke "core.clj" 300]
  [compojure.core$routes$fn__29299$f__29300 invoke "core.clj" 198]
  [compojure.core$routes$fn__29299$f__29300$respond_SINGLEQUOTE___29301 invoke "core.clj" 197]
  [metabase.api.routes$fn__82423$fn__82426 invoke "routes.clj" 64]
  [compojure.core$routes$fn__29299$f__29300 invoke "core.clj" 198]
  [compojure.core$routes$fn__29299 invoke "core.clj" 200]
  [clojure.lang.AFn applyToHelper "AFn.java" 160]
  [clojure.lang.AFn applyTo "AFn.java" 144]
  [clojure.core$apply invokeStatic "core.clj" 667]
  [clojure.core$apply invoke "core.clj" 662]
  [metabase.server.routes$fn__82586$fn__82587 doInvoke "routes.clj" 67]
  [clojure.lang.RestFn invoke "RestFn.java" 436]
  [compojure.core$routes$fn__29299$f__29300 invoke "core.clj" 198]
  [compojure.core$routes$fn__29299 invoke "core.clj" 200]
  [compojure.core$make_context$handler__29327 invoke "core.clj" 289]
  [compojure.core$make_context$fn__29331 invoke "core.clj" 299]
  [compojure.core$routes$fn__29299$f__29300 invoke "core.clj" 198]
  [compojure.core$routes$fn__29299$f__29300$respond_SINGLEQUOTE___29301 invoke "core.clj" 197]
  [compojure.core$wrap_route_matches$fn__29280 invoke "core.clj" 153]
  [compojure.core$routes$fn__29299$f__29300 invoke "core.clj" 198]
  [compojure.core$routes$fn__29299$f__29300$respond_SINGLEQUOTE___29301 invoke "core.clj" 197]
  [compojure.core$wrap_route_matches$fn__29280 invoke "core.clj" 153]
  [compojure.core$routes$fn__29299$f__29300 invoke "core.clj" 198]
  [compojure.core$routes$fn__29299$f__29300$respond_SINGLEQUOTE___29301 invoke "core.clj" 197]
  [compojure.core$wrap_route_matches$fn__29280 invoke "core.clj" 153]
  [compojure.core$routes$fn__29299$f__29300 invoke "core.clj" 198]
  [compojure.core$routes$fn__29299$f__29300$respond_SINGLEQUOTE___29301 invoke "core.clj" 197]
  [metabase.server.routes$fn__82572$fn__82574 invoke "routes.clj" 45]
  [compojure.core$routes$fn__29299$f__29300 invoke "core.clj" 198]
  [compojure.core$routes$fn__29299 invoke "core.clj" 200]
  [metabase.server.middleware.exceptions$catch_uncaught_exceptions$fn__78945 invoke "exceptions.clj" 98]
  [metabase.server.middleware.exceptions$catch_api_exceptions$fn__78942 invoke "exceptions.clj" 86]
  [metabase.server.middleware.log$log_api_call$fn__83998$fn__83999$fn__84000 invoke "log.clj" 211]
  [metabase.driver.sql_jdbc.execute.diagnostic$do_with_diagnostic_info invokeStatic "diagnostic.clj" 15]
  [metabase.driver.sql_jdbc.execute.diagnostic$do_with_diagnostic_info invoke "diagnostic.clj" 9]
  [metabase.server.middleware.log$log_api_call$fn__83998$fn__83999 invoke "log.clj" 203]
  [toucan.db$_do_with_call_counting invokeStatic "db.clj" 236]
  [toucan.db$_do_with_call_counting invoke "db.clj" 229]
  [metabase.server.middleware.log$log_api_call$fn__83998 invoke "log.clj" 202]
  [metabase.server.middleware.browser_cookie$ensure_browser_id_cookie$fn__86895 invoke "browser_cookie.clj" 38]
  [metabase.server.middleware.security$add_security_headers$fn__63843 invoke "security.clj" 149]
  [metabase.server.middleware.json$wrap_json_body$fn__86026 invoke "json.clj" 63]
  [metabase.server.middleware.json$wrap_streamed_json_response$fn__86044 invoke "json.clj" 99]
  [metabase.server.middleware.offset_paging$handle_paging$fn__63867 invoke "offset_paging.clj" 42]
  [ring.middleware.keyword_params$wrap_keyword_params$fn__87162 invoke "keyword_params.clj" 55]
  [ring.middleware.params$wrap_params$fn__87181 invoke "params.clj" 77]
  [metabase.server.middleware.misc$maybe_set_site_url$fn__36664 invoke "misc.clj" 59]
  [metabase.server.middleware.session$reset_session_timeout$fn__48310 invoke "session.clj" 394]
  [metabase.server.middleware.session$bind_current_user$fn__48293$fn__48294 invoke "session.clj" 331]
  [metabase.server.middleware.session$do_with_current_user invokeStatic "session.clj" 310]
  [metabase.server.middleware.session$do_with_current_user invoke "session.clj" 299]
  [metabase.server.middleware.session$bind_current_user$fn__48293 invoke "session.clj" 330]
  [metabase.server.middleware.session$wrap_current_user_info$fn__48275 invoke "session.clj" 280]
  [metabase.server.middleware.session$wrap_session_id$fn__48259 invoke "session.clj" 213]
  [metabase.server.middleware.auth$wrap_api_key$fn__70479 invoke "auth.clj" 27]
  [ring.middleware.cookies$wrap_cookies$fn__87082 invoke "cookies.clj" 216]
  [metabase.server.middleware.misc$add_content_type$fn__36647 invoke "misc.clj" 27]
  [metabase.server.middleware.misc$disable_streaming_buffering$fn__36672 invoke "misc.clj" 76]
  [ring.middleware.gzip$wrap_gzip$fn__87124 invoke "gzip.clj" 86]
  [metabase.server.middleware.misc$bind_request$fn__36675 invoke "misc.clj" 93]
  [metabase.server.middleware.ssl$redirect_to_https_middleware$fn__86911 invoke "ssl.clj" 38]
  [metabase.server$async_proxy_handler$fn__82794 invoke "server.clj" 73]
  [metabase.server.proxy$org.eclipse.jetty.server.handler.AbstractHandler$ff19274a handle nil -1]
  [org.eclipse.jetty.server.handler.StatisticsHandler handle "StatisticsHandler.java" 181]
  [org.eclipse.jetty.server.handler.HandlerWrapper handle "HandlerWrapper.java" 127]
  [org.eclipse.jetty.server.Server handle "Server.java" 516]
  [org.eclipse.jetty.server.HttpChannel lambda$handle$1 "HttpChannel.java" 487]
  [org.eclipse.jetty.server.HttpChannel dispatch "HttpChannel.java" 732]
  [org.eclipse.jetty.server.HttpChannel handle "HttpChannel.java" 479]
  [org.eclipse.jetty.server.HttpConnection onFillable "HttpConnection.java" 277]
  [org.eclipse.jetty.io.AbstractConnection$ReadCallback succeeded "AbstractConnection.java" 311]
  [org.eclipse.jetty.io.FillInterest fillable "FillInterest.java" 105]
  [org.eclipse.jetty.io.ChannelEndPoint$1 run "ChannelEndPoint.java" 104]
  [org.eclipse.jetty.util.thread.strategy.EatWhatYouKill runTask "EatWhatYouKill.java" 338]
  [org.eclipse.jetty.util.thread.strategy.EatWhatYouKill doProduce "EatWhatYouKill.java" 315]
  [org.eclipse.jetty.util.thread.strategy.EatWhatYouKill tryProduce "EatWhatYouKill.java" 173]
  [org.eclipse.jetty.util.thread.strategy.EatWhatYouKill run "EatWhatYouKill.java" 131]
  [org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread run "ReservedThreadExecutor.java" 409]
  [org.eclipse.jetty.util.thread.QueuedThreadPool runJob "QueuedThreadPool.java" 883]
  [org.eclipse.jetty.util.thread.QueuedThreadPool$Runner run "QueuedThreadPool.java" 1034]
  [java.lang.Thread run nil -1]],
 :cause "Lack permission to read table in schema metabase_cache_a5484_2",
 :data {:error :persist.check/read-table, :database "DWH - Cubes"},
 :message "Lack permission to read table in schema metabase_cache_a5484_2",
 :error :persist.check/read-table,
 :database "DWH - Cubes"}

flamber · December 16, 2022, 9:38am

@Arvydas Connect to your MySQL with any client using the exact same credentials as you're using for the Metabase connection.
Then do this:

use `metabase_cache_a5484_2`;
show tables;

Arvydas · December 29, 2022, 5:48am

@flamber i connected with the same user, created a database metabase_cache_a5484_2, "show tables;" shows nothing, since this is empty, just created database.
Once I press on "turn on caching" in Metabase, I am again getting lack permission to read table in schema error... And after that error Metabase deletes this created database at all.

Any ideas?

flamber · December 29, 2022, 11:13am

@Arvydas Enable debug logging on your MySQL to see all queries executed to figure out what is going wrong.

Arvydas · January 6, 2023, 12:52pm

Finally this is solved!

Thank you for a help @flamber !

flamber · January 6, 2023, 12:55pm

@Arvydas Great, then please write what the problem was and how you fixed it, so others can learn.

Arvydas · January 6, 2023, 1:17pm

So at the end, the problem with lack permission to read table in schema *** was still existing.
After digging deeper, we noticed that the real error is not the on which Metabase was indicated.
Real error was:

2022-12-29T13:34:08.747808Z 1193499 [Warning] [MY-011071] [Server] Attempt to create or modify table without primary key: metabase_cache_a5484_2.persistence_check_7650

To solve this out, adjusted DB and now we are able to create tables without primary key.