:message "ERROR: syntax error at end of input\n Position: 45",
Anyone could help pointing to good direction?
{
"browser-info": {
"language": "en-US",
"platform": "MacIntel",
"userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.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.16+8-post-Ubuntu-0ubuntu120.04",
"java.vendor": "Ubuntu",
"java.vendor.url": "https://ubuntu.com/",
"java.version": "11.0.16",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.16+8-post-Ubuntu-0ubuntu120.04",
"os.name": "Linux",
"os.version": "5.4.0-62-generic",
"user.language": "en",
"user.timezone": "Asia/Singapore"
},
"metabase-info": {
"databases": [
"postgres"
],
"hosting-env": "unknown",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "15.8"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.7.3"
}
},
"run-mode": "prod",
"plan-alias": "",
"version": {
"date": "2024-07-30",
"tag": "v0.50.18",
"hash": "c323ffc"
},
"settings": {
"report-timezone": "Asia/Jakarta"
}
}
}
that's odd. It's building the query to check from the cache wrongly. Was it just a simple backup/restore?
I was trying to move from neon.tech -> Supabase. So it cannot be done using backup/restore. I was using DBeaver to Export Data from neon.tech -> Supabase (DBeaver Documentation)
Then I got error as mentioned when I switched to new db conn (supa).
Strange enough, when I revert back to prev db conn (neon) it also happened same.
I didn't touch/alter primary db
Interesting you mentioned
It's building the query to check from the cache wrongly
Why you think it was cache? How to invalidate cache or check what it was?
New update, I turned off caching for questions and models. Now questions and models, work without error. But still dashboard has same error. Any chane how to remove/purge Dashboard Cache @Luiggi ?
Now I truncated query table that contains query_hash. Idk, what would do next to have dashboard run again, questions could work without cache
@flamber sorry to tag you, any ideas on how to deal with this?
Right now it seems that you're going bonzo to try to rescue your Metabase instance which I don't recommend, specially manipulating the app db without knowing the side effects of it. There's information here that we don't have:
it seems that you're going bonzo to try to rescue your Metabase instance which I don't recommend, specially manipulating the app db without knowing the side effects of it.
it is, I thought it was cache then truncate all query and query_hash. Right now I realize, there's something checker for cache on query_hash that broken in Metabase Query Engine
yes it is same version
Pg v15 for neon and supabase
yes, I'll try with this one afterall
What was your assumptions on wrong cache at first @Luiggi ?
sorry, my bad, it was just checking if there was a query with a specific query_hash on the query table, but it seems that it was building the query to send to the DB wrongly... which is odd, since toucan (our wrapper for building queries) should not build wrong queries at all
do you have a way to check the query that's being sent and what's the error that is happening?
BTW: truncating the query table "should" fix that error
I already truncated query and query_* table.
Then I got this error
2024-11-11 09:22:38,509 ERROR middleware.log :: GET /api/dashboard/22/query_metadata 500 252.2 ms (14 DB calls) {:metabase-user-id 32}
0|metabase | {:via
0|metabase | [{:type clojure.lang.ExceptionInfo,
0|metabase | :message "ERROR: syntax error at or near \",\"\n Position: 47",
0|metabase | :data
0|metabase | {:toucan2/context-trace
0|metabase | [["execute SQL with class com.mchange.v2.c3p0.impl.NewProxyConnection"
0|metabase | {:toucan2.jdbc.query/sql-args
0|metabase | ["SELECT * FROM \"query\" WHERE \"query_hash\" IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
0|metabase | #object["[B" 0x30ffa758 "[B@30ffa758"]
...
:cause "ERROR: syntax error at or near \",\"\n Position: 47",
0|metabase | :message "ERROR: syntax error at or near \",\"\n Position: 47"}
When trying to render/view a dashboard ID
2024-11-11 09:22:44,930 ERROR middleware.process-userland-query :: Error saving query execution info
0|metabase | clojure.lang.ExceptionInfo: ERROR: syntax error at or near ")"
0|metabase | Position: 1688 {:toucan2/context-trace [["execute SQL with class com.mchange.v2.c3p0.impl.NewProxyConnection" {:toucan2.jdbc.query/sql-args ["UPDATE \"query\" SET \"query\" = ?, \"average_execution_time\" = CAST(ROUND((0.9 * \"average_execution_time\") + 29.0, 0) AS integer) WHERE (\"query_hash\" = ?) AND (\"query\" IS NULL)" "{\"constraints\":{\"max-results\":10000,\"max-results-bare-rows\":2000},\"type\":\"query\",\"middleware\":{\"js-int-to-string?\":true,\"ignore-cached-results?\":false,\"process-viz-settings?\":false,\"userland-query?\":true},\"cache-strategy\":null,\"viz-settings\":{\"graph.show_goal\":false,\"graph.show_values\":true,\"graph.x_axis.labels_enabled\":true,\"table.cell_column\":\"metadata → payment_in_response → statusCode\",\"graph.series_order_dimension\":null,\"graph.y_axis.labels_enabled\":false,\"graph.label_value_frequency\":\"all\",\"graph.metrics\":[\"sum\"],\"graph.y_axis.axis_enabled\":false,\"graph.show_stack_values\":\"all\",\"graph.label_value_formatting\":\"compact\",\"graph.series_order\":null,\"table.pivot_column\":\"metadata → payment_in_response → messages\",\"series_settings\":{\"sum\":{\"color\":\"#87BCEC\"},\"sum_2\":{\"color\":\"#F2A86F\"}},\"graph.dimensions\":[\"paid_at\",\"business_name\"],\"stackable.stack_type\":\"stacked\"},\"database\":2,\"query\":{\"aggregation\":[[\"sum\",[\"field\",\"requested_amount\",{\"base-type\":\"type/Decimal\"}]],[\"count\"]],\"breakout\":[[\"field\",\"paid_at\",{\"base-type\":\"type/DateTimeWithLocalTZ\",\"temporal-unit\":\"month\"}],[\"field\",\"business_name\",{\"base-type\":\"type/Text\"}],[\"field\",\"provider\",{\"base-type\":\"type/Text\"}]],\"order-by\":[[\"desc\",[\"aggregation\",0]]],\"source-table\":\"card__493\",\"filter\":[\"and\",[\"or\",[\"=\",[\"field\",\"status\",{\"base-type\":\"type/Text\"}],\"completed\"],[\"=\",[\"field\",\"status\",{\"base-type\":\"type/Text\"}],\"waiting_for_proceed\"]],[\"time-interval\",[\"field\",\"created_at\",{\"base-type\":\"type/DateTimeWithLocalTZ\"}],-3,\"month\",{\"include-current\":true}]]}}" #object["[B" 0x56c257ae "[B@56c257ae"]]}] ["resolve connection" {:toucan2.connection/connectable metabase.db.connection.ApplicationDB}] ["resolve connection" {:toucan2.connection/connectable :default}] ["resolve connection" {:toucan2.connection/connectable nil}] {:toucan2.pipeline/rf #object[clojure.core$completing$fn__8558 0x10c10127 "clojure.core$completing$fn__8558@10c10127"]}
...
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near ")"
0|metabase | Position: 1688
When trying to view questions (it works show a graph at FE, but failed to store some info on BE)
I thought it was my bad truncated query_* table also, it should be just query table? When I read those logs yesterday
Updated here
Right now stay at prev version v50 while checking for new metabase version 
