Knowledge Drop

MYSQL—HowTo: Why does MySQL need database permissions for derived tables?

  • 7 May 2021
  • 1 reply

Userlevel 2

Last tested: Mar 9, 2021

At the time the code for the MySQL dialect was written, it did not support WITH, so we had to write derived tables a bit differently. Instead of WITH we use CREATE temp TABLE, which requires database permissions. (This is still different than a PDT since we are not writing the table to the schema, just creating a temp table)

This error can occur when trying to use derived tables without setting up the database permissions:

temp derived_table sql_availability_audit creation failed: SQL Error in CREATE TABLE as SELECT: java.sql.SQLSyntaxErrorException: Access denied for user 'looker'@'%' to database 'looker_tmp'


Later versions of MySQL now support CTEs (WITH), but as of Mar. 2021 Looker still uses temporary tables for ephemeral derived tables.


This content is subject to limited support.                




1 reply

Hey @rachel.johnson did you find a solution for this?

I still believe we need to explicitly provide access to all the DB Users to create Temp tables according to this community forum. Isnt it so?