Sync error with PosgresSQL 9.1.24

ifrolov · October 17, 2019, 3:01pm

hi, i’m have some problem.

PosgresSQL 9.1.24

    10-17 17:49:23 ERROR middleware.log :: GET /api/automagic-dashboards/database/33/candidates 500 12,6 ms (6 DB calls)
{:message
 "Syntax error in SQL statement \"SELECT \"\"TABLE_ID\"\", COUNT(*) AS \"\"COUNT\"\" FROM \"\"METABASE_FIELD\"\" WHERE ((\"\"TABLE_ID\"\" IN NULL[*]) AND \"\"ACTIVE\"\" = TRUE AND (\"\"SPECIAL_TYPE\"\" IN (?, ?))) GROUP BY \"\"TABLE_ID\"\" \"; expected \"(\"; SQL statement:\nSELECT \"TABLE_ID\", count(*) AS \"COUNT\" FROM \"METABASE_FIELD\" WHERE ((\"TABLE_ID\" in NULL) AND \"ACTIVE\" = TRUE AND (\"SPECIAL_TYPE\" in (?, ?))) GROUP BY \"TABLE_ID\" [42001-197]",
 :type org.h2.jdbc.JdbcSQLException,
 :stacktrace
 ("org.h2.message.DbException.getJdbcSQLException(DbException.java:357)"
  "org.h2.message.DbException.getSyntaxError(DbException.java:217)"
  "org.h2.command.Parser.getSyntaxError(Parser.java:555)"
  "org.h2.command.Parser.read(Parser.java:3518)"
  "org.h2.command.Parser.readCondition(Parser.java:2433)"
  "org.h2.command.Parser.readAnd(Parser.java:2342)"
  "org.h2.command.Parser.readExpression(Parser.java:2334)"
  "org.h2.command.Parser.readTerm(Parser.java:3252)"
  "org.h2.command.Parser.readFactor(Parser.java:2587)"
  "org.h2.command.Parser.readSum(Parser.java:2574)"
  "org.h2.command.Parser.readConcat(Parser.java:2544)"
  "org.h2.command.Parser.readCondition(Parser.java:2370)"
  "org.h2.command.Parser.readAnd(Parser.java:2342)"
  "org.h2.command.Parser.readExpression(Parser.java:2334)"
  "org.h2.command.Parser.readTerm(Parser.java:3252)"
  "org.h2.command.Parser.readFactor(Parser.java:2587)"
  "org.h2.command.Parser.readSum(Parser.java:2574)"
  "org.h2.command.Parser.readConcat(Parser.java:2544)"
  "org.h2.command.Parser.readCondition(Parser.java:2370)"
  "org.h2.command.Parser.readAnd(Parser.java:2342)"
  "org.h2.command.Parser.readExpression(Parser.java:2334)"
  "org.h2.command.Parser.parseSelectSimple(Parser.java:2291)"
  "org.h2.command.Parser.parseSelectSub(Parser.java:2133)"
  "org.h2.command.Parser.parseSelectUnion(Parser.java:1946)"
  "org.h2.command.Parser.parseSelect(Parser.java:1919)"
  "org.h2.command.Parser.parsePrepared(Parser.java:463)"
  "org.h2.command.Parser.parse(Parser.java:335)"
  "org.h2.command.Parser.parse(Parser.java:311)"
  "org.h2.command.Parser.prepareCommand(Parser.java:278)"
  "org.h2.engine.Session.prepareLocal(Session.java:611)"
  "org.h2.engine.Session.prepareCommand(Session.java:549)"
  "org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1247)"
  "org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:76)"
  "org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:304)"
  "com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxyConnection.java:387)"
  "clojure.java.jdbc$prepare_statement.invokeStatic(jdbc.clj:672)"
  "clojure.java.jdbc$prepare_statement.invoke(jdbc.clj:619)"
  "clojure.java.jdbc$db_query_with_resultset_STAR_.invokeStatic(jdbc.clj:1094)"
  "clojure.java.jdbc$db_query_with_resultset_STAR_.invoke(jdbc.clj:1075)"
  "clojure.java.jdbc$query.invokeStatic(jdbc.clj:1164)"
  "clojure.java.jdbc$query.invoke(jdbc.clj:1126)"
  "toucan.db$query.invokeStatic(db.clj:285)"
  "toucan.db$query.doInvoke(db.clj:281)"
  "clojure.lang.RestFn.invoke(RestFn.java:410)"
  "--> automagic_dashboards.core$enhance_table_stats.invokeStatic(core.clj:1247)"
  "automagic_dashboards.core$enhance_table_stats.invoke(core.clj:1224)"
  "automagic_dashboards.core$candidate_tables.invokeStatic(core.clj:1286)"
  "automagic_dashboards.core$candidate_tables.invoke(core.clj:1266)"
  "automagic_dashboards.core$candidate_tables.invokeStatic(core.clj:1276)"
  "automagic_dashboards.core$candidate_tables.invoke(core.clj:1266)"
  "api.automagic_dashboards$fn__60347.invokeStatic(automagic_dashboards.clj:63)"
  "api.automagic_dashboards$fn__60347.invoke(automagic_dashboards.clj:60)"
  "middleware.auth$enforce_authentication$fn__47314.invoke(auth.clj:14)"
  "routes$fn__66170$fn__66171.doInvoke(routes.clj:56)"
  "middleware.exceptions$catch_uncaught_exceptions$fn__47270.invoke(exceptions.clj:104)"
  "middleware.exceptions$catch_api_exceptions$fn__47267.invoke(exceptions.clj:92)"
  "middleware.log$log_api_call$fn__47180$fn__47181.invoke(log.clj:170)"
  "middleware.log$log_api_call$fn__47180.invoke(log.clj:164)"
  "middleware.security$add_security_headers$fn__47231.invoke(security.clj:122)"
  "middleware.json$wrap_json_body$fn__47397.invoke(json.clj:61)"
  "middleware.json$wrap_streamed_json_response$fn__47415.invoke(json.clj:97)"
  "middleware.session$bind_current_user$fn__47529$fn__47530.invoke(session.clj:193)"
  "middleware.session$do_with_current_user.invokeStatic(session.clj:176)"
  "middleware.session$do_with_current_user.invoke(session.clj:170)"
  "middleware.session$bind_current_user$fn__47529.invoke(session.clj:192)"
  "middleware.session$wrap_current_user_id$fn__47518.invoke(session.clj:161)"
  "middleware.session$wrap_session_id$fn__47503.invoke(session.clj:123)"
  "middleware.auth$wrap_api_key$fn__47322.invoke(auth.clj:27)"
  "middleware.misc$maybe_set_site_url$fn__47295.invoke(misc.clj:56)"
  "middleware.misc$bind_user_locale$fn__47298.invoke(misc.clj:72)"
  "middleware.misc$add_content_type$fn__47283.invoke(misc.clj:28)"
  "middleware.misc$disable_streaming_buffering$fn__47306.invoke(misc.clj:87)"),
 :sql-exception-chain
 ["JdbcSQLException:"
  "Message: Syntax error in SQL statement \"SELECT \"\"TABLE_ID\"\", COUNT(*) AS \"\"COUNT\"\" FROM \"\"METABASE_FIELD\"\" WHERE ((\"\"TABLE_ID\"\" IN NULL[*]) AND \"\"ACTIVE\"\" = TRUE AND (\"\"SPECIAL_TYPE\"\" IN (?, ?))) GROUP BY \"\"TABLE_ID\"\" \"; expected \"(\"; SQL statement:"
  "SELECT \"TABLE_ID\", count(*) AS \"COUNT\" FROM \"METABASE_FIELD\" WHERE ((\"TABLE_ID\" in NULL) AND \"ACTIVE\" = TRUE AND (\"SPECIAL_TYPE\" in (?, ?))) GROUP BY \"TABLE_ID\" [42001-197]"
  "SQLState: 42001"
  "Error Code: 42001"]}

```json
{
  "browser-info": {
    "language": "ru",
    "platform": "Win32",
    "userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.120 Safari/537.36",
    "vendor": "Google Inc."
  },
  "system-info": {
    "java.runtime.name": "Java(TM) SE Runtime Environment",
    "java.runtime.version": "1.8.0_221-b11",
    "java.vendor": "Oracle Corporation",
    "java.vendor.url": "http://java.oracle.com/",
    "java.version": "1.8.0_221",
    "java.vm.name": "Java HotSpot(TM) Client VM",
    "java.vm.version": "25.221-b11",
    "os.name": "Windows 10",
    "os.version": "10.0",
    "user.language": "ru",
    "user.timezone": "Europe/Moscow"
  },
  "metabase-info": {
    "databases": [
      "h2",
      "postgres"
    ],
    "hosting-env": "unknown",
    "application-database": "h2",
    "run-mode": "prod",
    "version": {
      "tag": "v0.33.4",
      "date": "2019-10-07",
      "branch": "release-0.33.x",
      "hash": "9559406"
    },
    "settings": {
      "report-timezone": "Europe/Moscow"
    }
  }
}
```

flamber · October 17, 2019, 6:26pm

Hi @ifrolov
It looks like Metabase is trying to find table IDs from NULL[*], which is probably why it fails.
I’m not sure if the problem comes from the old version of Postgres, but H2 should not fail like that.
The error you’re showing is from X-rays, but do you also see the error while syncing? Perhaps it logs more clearly which table/column is failing.