[Metabase] Unable to sync fields on latest version on metabase v0.51.2

[pauldheinrichs]

Describe the bug
https://github.com/metabase/metabase/pull/48576/files - the following change to sync table fields is no longer supported from metabase v0.51.2 in cube and prevents metabase from syncing cubes fields to metabase resulting in continuous failures

Seemingly sourced from the macro FORMAT newly leveraged which is missing from cube.

The failure message from metabase

{"status":"failed","exception":"class clojure.lang.ExceptionInfo","message":"Error executing query: ERROR: Initial planning error: Error during planning: Invalid function 'format'\nQUERY: SELECT \"c\".\"column_name\" AS \"name\", \"c\".\"udt_name\" AS \"database-type\", \"c\".\"ordinal_position\" - 1 AS \"database-position\", \"c\".\"table_schema\" AS \"table-schema\", \"c\".\"table_name\" AS \"table-name\", \"pk\".\"column_name\" IS NOT NULL AS \"pk?\", COL_DESCRIPTION(__cube_regclass_cast(FORMAT('%I.%I', CAST(\"c\".\"table_schema\" AS TEXT), CAST(\"c\".\"table_name\" AS TEXT))), \"c\".\"ordinal_position\") AS \"field-comment\", ((\"column_default\" IS NULL) OR (LOWER(\"column_default\") = 'null')) AND (\"is_nullable\" = 'NO') AND NOT (((\"column_default\" IS NOT NULL) AND (\"column_default\" LIKE '%nextval(%')) OR (\"is_identity\" <> 'NO')) AS \"database-required\", ((\"column_default\" IS NOT NULL) AND (\"column_default\" LIKE '%nextval(%')) OR (\"is_identity\" <> 'NO') AS \"database-is-auto-increment\" FROM \"information_schema\".\"columns\" AS \"c\" LEFT JOIN (SELECT \"tc\".\"table_schema\", \"tc\".\"table_name\", \"kc\".\"column_name\" FROM \"information_schema\".\"table_constraints\" AS \"tc\" JOIN \"information_schema\".\"key_column_usage\" AS \"kc\" ON (\"tc\".\"constraint_name\" = \"kc\".\"constraint_name\") AND (\"tc\".\"table_schema\" = \"kc\".\"table_schema\") AND (\"tc\".\"table_name\" = \"kc\".\"table_name\") WHERE \"tc\".\"constraint_type\" = 'PRIMARY KEY') AS \"pk\" ON (\"c\".\"table_schema\" = \"pk\".\"table_schema\") AND (\"c\".\"table_name\" = \"pk\".\"table_name\") AND (\"c\".\"column_name\" = \"pk\".\"column_name\") WHERE c.table_schema !~ '^information_schema|catalog_history|pg_' AND (\"c\".\"table_schema\" IN ('replaced_placeholder')) ORDER BY \"table-schema\" ASC, \"table-name\" ASC, \"database-position\" ASC","stacktrace":["--> driver.sql_jdbc.execute$reducible_query$reify__86010$fn__86011$fn__86012.invoke(execute.clj:761)","driver.sql_jdbc.execute$reducible_query$reify__86010$fn__86011.invoke(execute.clj:757)","driver.sql_jdbc.execute$fn__85799$fn__85800.invoke(execute.clj:398)","driver.sql_jdbc.execute$do_with_resolved_connection85769__85770.invokeStatic(execute.clj:338)","driver.sql_jdbc.execute$do_with_resolved_connection85769__85770.invoke(execute.clj:321)","driver.sql_jdbc.execute$fn__85799.invokeStatic(execute.clj:392)","driver.sql_jdbc.execute$fn__85799.invoke(execute.clj:390)","driver.sql_jdbc.execute$reducible_query$reify__86010.reduce(execute.clj:751)","sync.sync_metadata.fields$sync_fields_BANG_89200__89201$fn__89202.invoke(fields.clj:82)","sync.util$do_with_error_handling.invokeStatic(util.clj:191)","sync.util$do_with_error_handling.invoke(util.clj:184)","sync.sync_metadata.fields$sync_fields_BANG_89200__89201.invokeStatic(fields.clj:76)","sync.sync_metadata.fields$sync_fields_BANG_89200__89201.invoke(fields.clj:71)","sync.util$run_step_with_metadata65837__65839$fn__65841$fn__65844.invoke(util.clj:495)","models.task_history$do_with_task_history65674__65675.invokeStatic(task_history.clj:121)","models.task_history$do_with_task_history65674__65675.invoke(task_history.clj:109)","sync.util$run_step_with_metadata65837__65839$fn__65841.doInvoke(util.clj:488)","sync.util$with_start_and_finish_logging_STAR_.invokeStatic(util.clj:132)","sync.util$with_start_and_finish_logging_STAR_.invoke(util.clj:126)","sync.util$do_with_start_and_finish_debug_logging.invokeStatic(util.clj:150)","sync.util$do_with_start_and_finish_debug_logging.invoke(util.clj:146)","sync.util$run_step_with_metadata65837__65839.invokeStatic(util.clj:482)","sync.util$run_step_with_metadata65837__65839.invoke(util.clj:477)","sync.util$run_sync_operation65887__65888$fn__65889$fn__65897.invoke(util.clj:568)","sync.util$run_sync_operation65887__65888$fn__65889.invoke(util.clj:566)","models.task_history$do_with_task_history65674__65675.invokeStatic(task_history.clj:121)","models.task_history$do_with_task_history65674__65675.invoke(task_history.clj:109)","sync.util$run_sync_operation65887__65888.invokeStatic(util.clj:563)","sync.util$run_sync_operation65887__65888.invoke(util.clj:558)","sync.sync_metadata$sync_db_metadata_BANG_89796__89797$fn__89798.invoke(sync_metadata.clj:70)","sync.util$do_with_error_handling.invokeStatic(util.clj:191)","sync.util$do_with_error_handling.invoke(util.clj:184)","driver$fn__58386.invokeStatic(driver.clj:892)","driver$fn__58386.invoke(driver.clj:892)","sync.util$sync_in_context$fn__65746.invoke(util.clj:167)","sync.util$with_db_logging_disabled$fn__65743.invoke(util.clj:159)","sync.util$with_start_and_finish_logging_STAR_.invokeStatic(util.clj:132)","sync.util$with_start_and_finish_logging_STAR_.invoke(util.clj:126)","sync.util$with_start_and_finish_logging$fn__65730.invoke(util.clj:144)","sync.util$with_sync_events65720__65721$fn__65725.invoke(util.clj:118)","sync.util$with_duplicate_ops_prevented$fn__65710.invoke(util.clj:90)","sync.util$do_sync_operation65761__65762.invokeStatic(util.clj:216)","sync.util$do_sync_operation65761__65762.invoke(util.clj:210)","sync.sync_metadata$sync_db_metadata_BANG_89796__89797.invokeStatic(sync_metadata.clj:68)","sync.sync_metadata$sync_db_metadata_BANG_89796__89797.invoke(sync_metadata.clj:65)","api.database$fn__104016$fn__104021.invoke(database.clj:1016)"],"ex-data":{"driver":"postgres","sql":["SELECT","  \"c\".\"column_name\" AS \"name\",","  \"c\".\"udt_name\" AS \"database-type\",","  \"c\".\"ordinal_position\" - 1 AS \"database-position\",","  \"c\".\"table_schema\" AS \"table-schema\",","  \"c\".\"table_name\" AS \"table-name\",","  \"pk\".\"column_name\" IS NOT NULL AS \"pk?\",","  COL_DESCRIPTION(","    CAST(","      CAST(","        FORMAT(","          '%I.%I',","          CAST(\"c\".\"table_schema\" AS TEXT),","          CAST(\"c\".\"table_name\" AS TEXT)","        ) AS REGCLASS","      ) AS OID","    ),","    \"c\".\"ordinal_position\"","  ) AS \"field-comment\",","  (","    (\"column_default\" IS NULL)","    OR (LOWER(\"column_default\") = 'null')","  )","  AND (\"is_nullable\" = 'NO')","  AND NOT (","    (","      (\"column_default\" IS NOT NULL)","      AND (\"column_default\" LIKE '%nextval(%')","    )","    OR (\"is_identity\" <> 'NO')","  ) AS \"database-required\",","  (","    (\"column_default\" IS NOT NULL)","    AND (\"column_default\" LIKE '%nextval(%')","  )","  OR (\"is_identity\" <> 'NO') AS \"database-is-auto-increment\"","FROM","  \"information_schema\".\"columns\" AS \"c\"","  LEFT JOIN (","    SELECT","      \"tc\".\"table_schema\",","      \"tc\".\"table_name\",","      \"kc\".\"column_name\"","    FROM","      \"information_schema\".\"table_constraints\" AS \"tc\"","      INNER JOIN \"information_schema\".\"key_column_usage\" AS \"kc\" ON (\"tc\".\"constraint_name\" = \"kc\".\"constraint_name\")","      AND (\"tc\".\"table_schema\" = \"kc\".\"table_schema\")","      AND (\"tc\".\"table_name\" = \"kc\".\"table_name\")","    WHERE","      \"tc\".\"constraint_type\" = 'PRIMARY KEY'","  ) AS \"pk\" ON (\"c\".\"table_schema\" = \"pk\".\"table_schema\")","  AND (\"c\".\"table_name\" = \"pk\".\"table_name\")","  AND (\"c\".\"column_name\" = \"pk\".\"column_name\")","WHERE","  c.table_schema !~ '^information_schema|catalog_history|pg_'","  AND (\"c\".\"table_schema\" IN (?))","ORDER BY","  \"table-schema\" ASC,","  \"table-name\" ASC,","  \"database-position\" ASC"],"params":["public"]},"original-info":null}

The converted queries

SELECT
  c.column_name AS name,
  c.udt_name AS database_type,
  c.ordinal_position - 1 AS database_position,
  c.table_schema AS table_schema,
  c.table_name AS table_name,
  pk.column_name IS NOT NULL AS pk,
  COL_DESCRIPTION(
    CAST(
      CAST(
        FORMAT(
          '%I.%I',
          CAST(c.table_schema AS TEXT),
          CAST(c.table_name AS TEXT)
        ) AS REGCLASS
      ) AS OID
    ),
    c.ordinal_position
  ) AS field_comment,
  (
    (column_default IS NULL)
    OR (LOWER(column_default) = 'null')
  )
  AND (is_nullable = 'NO')
  AND NOT (
    (
      (column_default IS NOT NULL)
      AND (column_default LIKE '%nextval(%')
    )
    OR (is_identity <> 'NO')
  ) AS database_required,
  (
    (column_default IS NOT NULL)
    AND (column_default LIKE '%nextval(%')
  )
  OR (is_identity <> 'NO') AS database_is_auto_increment
FROM
  information_schema.columns AS c
  LEFT JOIN (
    SELECT
      tc.table_schema,
      tc.table_name,
      kc.column_name
    FROM
      information_schema.table_constraints AS tc
      INNER JOIN information_schema.key_column_usage AS kc ON (tc.constraint_name = kc.constraint_name)
      AND (tc.table_schema = kc.table_schema)
      AND (tc.table_name = kc.table_name)
    WHERE
      tc.constraint_type = 'PRIMARY KEY'
  ) AS pk ON (c.table_schema = pk.table_schema)
  AND (c.table_name = pk.table_name)
  AND (c.column_name = pk.column_name)
WHERE
  c.table_schema !~ '^information_schema|catalog_history|pg_'
  AND (c.table_schema IN ('public'))
ORDER BY
  table_schema ASC,
  table_name ASC,
  database_position ASC;

SELECT
  c.column_name AS name,
  c.udt_name AS database_type,
  c.ordinal_position - 1 AS database_position,
  c.table_schema AS table_schema,
  c.table_name AS table_name,
  pk.column_name IS NOT NULL AS pk,
  COL_DESCRIPTION(
    __cube_regclass_cast(
      FORMAT('%I.%I', CAST(c.table_schema AS TEXT), CAST(c.table_name AS TEXT))
    ),
    c.ordinal_position
  ) AS field_comment,
  (
    (column_default IS NULL)
    OR (LOWER(column_default) = 'null')
  )
  AND (is_nullable = 'NO')
  AND NOT (
    (
      (column_default IS NOT NULL)
      AND (column_default LIKE '%nextval(%')
    )
    OR (is_identity <> 'NO')
  ) AS database_required,
  (
    (column_default IS NOT NULL)
    AND (column_default LIKE '%nextval(%')
  )
  OR (is_identity <> 'NO') AS database_is_auto_increment
FROM
  information_schema.columns AS c
  LEFT JOIN (
    SELECT
      tc.table_schema,
      tc.table_name,
      kc.column_name
    FROM
      information_schema.table_constraints AS tc
      JOIN information_schema.key_column_usage AS kc ON (tc.constraint_name = kc.constraint_name)
      AND (tc.table_schema = kc.table_schema)
      AND (tc.table_name = kc.table_name)
    WHERE
      tc.constraint_type = 'PRIMARY KEY'
  ) AS pk ON (c.table_schema = pk.table_schema)
  AND (c.table_name = pk.table_name)
  AND (c.column_name = pk.column_name)
WHERE
  c.table_schema !~ '^information_schema|catalog_history|pg_'
  AND (c.table_schema IN ('replaced_placeholder'))
ORDER BY
  table_schema ASC,
  table_name ASC,
  database_position ASC;

[itestyoy]

Hi! Also jumping on this with the same issue.

[adamfeldman]

My team has also run into this. I see and am excited about the new PR #9072 which we are eagerly awaiting to unblock our use of Cube Cloud with Metabase (as a workaround for Cube Cloud having not yet implemented a solution to the Metabase API changes in Metabase 0.51).

[igorlukanin]

The fix was released in v1.1.16 but it looks like there's something else to be addressed: #9126

The PR is coming though: #9129

[igorlukanin]

The fix was released in v1.1.17. Everything should be fine now.