Metabase Sync Error Invalid Operation

zeehasham · March 11, 2021, 5:28am

I am running latest metabase 0.38 and getting a sync error at the beginning. Here is the Diagnostic Infor and Troubleshoot log. Metabase is using Postgres as backend database and I am trying to sync my redshift database tables.

{
“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/89.0.4389.72 Safari/537.36”,
“vendor”: “Google Inc.”
},
“system-info”: {
“file.encoding”: “UTF-8”,
“java.runtime.name”: “OpenJDK Runtime Environment”,
“java.runtime.version”: “11.0.10+9”,
“java.vendor”: “AdoptOpenJDK”,
“java.vendor.url”:
“java.version”: “11.0.10”,
“java.vm.name”: “OpenJDK 64-Bit Server VM”,
“java.vm.version”: “11.0.10+9”,
“os.name”: “Linux”,
“os.version”: “3.10.0-1160.11.1.el7.x86_64”,
“user.language”: “en”,
“user.timezone”: “GMT”
},
“metabase-info”: {
“databases”: [
“h2”,
“redshift”
],
“hosting-env”: “unknown”,
“application-database”: “postgres”,
“application-database-details”: {
“database”: {
“name”: “PostgreSQL”,
“version”: “11.5”
},
“jdbc-driver”: {
“name”: “PostgreSQL JDBC Driver”,
“version”: “42.2.18”
}
},
“run-mode”: “prod”,
“version”: {
“date”: “2021-03-03”,
“tag”: “v0.38.1”,
“branch”: “release-x.38.x”,
“hash”: “79ef63a”
},
“settings”: {
“report-timezone”: “GMT”
}
}

[8c09401e-b708-4447-a212-125808ecdab7] 2021-03-11T00:07:40-05:00 ERROR metabase.models.field-values Error fetching field values
java.sql.SQLException: Amazon Invalid operation: could not identify an ordering operator for type xid
Hint: Use an explicit ordering operator or modify the query.;
Caused by: com.amazon.support.exceptions.ErrorException: Amazon Invalid operation: could not identify an ordering operator for type xid
Hint: Use an explicit ordering operator or modify the query.;
… 174 more

flamber · March 11, 2021, 12:04pm

Hi @zeehasham
Which column type is xid ? Can you provide a sample schema to reproduce the problem?

zeehasham · March 11, 2021, 3:35pm

How can I access this table metabase.models.field-values to get the schema?

flamber · March 11, 2021, 3:42pm

@zeehasham I was asking for the DDL of your database table.
You can access everything Metabase stores in it’s application database (Postgres 11.5 in your case), but that’s not what I was asking for.

zeehasham · March 11, 2021, 4:10pm

The redshift database is big and I do not know which table has this field xid

Here are the list of errors I am getting

ERROR metabase.models.field-values Error fetching field values
java.sql.SQLException: Amazon Invalid operation: could not identify an ordering operator for type xid
Hint: Use an explicit ordering operator or modify the query.;
at com.amazon.redshift.client.messages.inbound.ErrorResponse.toErrorException(Unknown Source)

ERROR metabase.task.upgrade-checks Error fetching version info
clojure.lang.ExceptionInfo: clj-http: status 403 {:object {:cached nil, :request-time 1435, :repeatable? false, :protocol-version {:name “HTTP”, :major 1, :minor 1}, :streaming? true, :http-client #object[org.apache.http.impl.client.InternalHttpClient 0x6cbe8e01 “org.apache.http.impl.client.InternalHttpClient@6cbe8e01”], :chunked? false, :type :clj-http.client/unexceptional-status, :reason-phrase “Forbidden”, :headers {“x-amz-request-id” “C1357F09P59A78F2”, “x-amz-id-2” “0Ht3d9tc1tVYcxfJp/H1Rkx1xMEjS+EDP9ysPZzwziHOa0CZp3s535hjqUQIsQka0KHslUBNdHQ=”, “Content-Type” “text/html; charset=utf-8”, “Content-Length” “303”, “Date” “Thu, 11 Mar 2021 06:15:00 GMT”, “Server” “AmazonS3”, “Connection” “close”}, :orig-content-encoding nil, :status 403, :length 303, :body “\n403 Forbidden\n\n

403 Forbidden

\n

Code: AccessDenied
Message: Access Denied
RequestId: C1357F09P59A78F2
HostId: 0Ht3d9tc1tVYcxfJp/H1Rkx1xMEjS+EDP9ysPZzwziHOa0CZp3s535hjqUQIsQka0KHslUBNdHQ=

\n

\n\n\n”, :trace-redirects []}, :environment {req {:content-type “application/json”, :url “http://static.metabase.com/version-info.json”, :request-method :get, :flatten-nested-keys (:query-params), :headers {“content-type” “application/json”}}, p__30054 {:cached nil, :request-time 1435, :repeatable? false, :protocol-version {:name “HTTP”, :major 1, :minor 1}, :streaming? true, :http-client #object[org.apache.http.impl.client.InternalHttpClient 0x6cbe8e01 “org.apache.http.impl.client.InternalHttpClient@6cbe8e01”], :chunked? false, :reason-phrase “Forbidden”, :headers {“x-amz-request-id” “C1357F09P59A78F2”, “x-amz-id-2” “0Ht3d9tc1tVYcxfJp/H1Rkx1xMEjS+EDP9ysPZzwziHOa0CZp3s535hjqUQIsQka0KHslUBNdHQ=”, “Content-Type” “text/html; charset=utf-8”, “Content-Length” “303”, “Date” “Thu, 11 Mar 2021 06:15:00 GMT”, “Server” “AmazonS3”, “Connection” “close”}, :orig-content-encoding nil, :status 403, :length 303, :body “\n403 Forbidden\n\n

403 Forbidden

\n

Code: AccessDenied
Message: Access Denied
RequestId: C1357F09P59A78F2
HostId: 0Ht3d9tc1tVYcxfJp/H1Rkx1xMEjS+EDP9ysPZzwziHOa0CZp3s535hjqUQIsQka0KHslUBNdHQ=

\n

\n\n\n”, :trace-redirects []}, map__30055 {:cached nil, :request-time 1435, :repeatable? false, :protocol-version {:name “HTTP”, :major 1, :minor 1}, :streaming? true, :http-client #object[org.apache.http.impl.client.InternalHttpClient 0x6cbe8e01 “org.apache.http.impl.client.InternalHttpClient@6cbe8e01”], :chunked? false, :reason-phrase “Forbidden”, :headers {“x-amz-request-id” “C1357F09P59A78F2”, “x-amz-id-2” “0Ht3d9tc1tVYcxfJp/H1Rkx1xMEjS+EDP9ysPZzwziHOa0CZp3s535hjqUQIsQka0KHslUBNdHQ=”, “Content-Type” “text/html; charset=utf-8”, “Content-Length” “303”, “Date” “Thu, 11 Mar 2021 06:15:00 GMT”, “Server” “AmazonS3”, “Connection” “close”}, :orig-content-encoding nil, :status 403, :length 303, :body “\n403 Forbidden\n\n

403 Forbidden

\n

Code: AccessDenied
Message: Access Denied
RequestId: C1357F09P59A78F2
HostId: 0Ht3d9tc1tVYcxfJp/H1Rkx1xMEjS+EDP9ysPZzwziHOa0CZp3s535hjqUQIsQka0KHslUBNdHQ=

\n

\n\n\n”, :trace-redirects []}, resp {:cached nil, :request-time 1435, :repeatable? false, :protocol-version {:name “HTTP”, :major 1, :minor 1}, :streaming? true, :http-client #object[org.apache.http.impl.client.InternalHttpClient 0x6cbe8e01 “org.apache.http.impl.client.InternalHttpClient@6cbe8e01”], :chunked? false, :reason-phrase “Forbidden”, :headers {“x-amz-request-id” “C1357F09P59A78F2”, “x-amz-id-2” “0Ht3d9tc1tVYcxfJp/H1Rkx1xMEjS+EDP9ysPZzwziHOa0CZp3s535hjqUQIsQka0KHslUBNdHQ=”, “Content-Type” “text/html; charset=utf-8”, “Content-Length” “303”, “Date” “Thu, 11 Mar 2021 06:15:00 GMT”, “Server” “AmazonS3”, “Connection” “close”}, :orig-content-encoding nil, :status 403, :length 303, :body “\n403 Forbidden\n\n

403 Forbidden

\n

Code: AccessDenied
Message: Access Denied
RequestId: C1357F09P59A78F2
HostId: 0Ht3d9tc1tVYcxfJp/H1Rkx1xMEjS+EDP9ysPZzwziHOa0CZp3s535hjqUQIsQka0KHslUBNdHQ=

\n

\n\n\n”, :trace-redirects []}, status 403, data {:cached nil, :request-time 1435, :repeatable? false, :protocol-version {:name “HTTP”, :major 1, :minor 1}, :streaming? true, :http-client #object[org.apache.http.impl.client.InternalHttpClient 0x6cbe8e01 “org.apache.http.impl.client.InternalHttpClient@6cbe8e01”], :chunked? false, :type :clj-http.client/unexceptional-status, :reason-phrase “Forbidden”, :headers {“x-amz-request-id” “C1357F09P59A78F2”, “x-amz-id-2” “0Ht3d9tc1tVYcxfJp/H1Rkx1xMEjS+EDP9ysPZzwziHOa0CZp3s535hjqUQIsQka0KHslUBNdHQ=”, “Content-Type” “text/html; charset=utf-8”, “Content-Length” “303”, “Date” “Thu, 11 Mar 2021 06:15:00 GMT”, “Server” “AmazonS3”, “Connection” “close”}, :orig-content-encoding nil, :status 403, :length 303, :body “\n403 Forbidden\n\n

403 Forbidden

\n

Code: AccessDenied
Message: Access Denied
RequestId: C1357F09P59A78F2
HostId: 0Ht3d9tc1tVYcxfJp/H1Rkx1xMEjS+EDP9ysPZzwziHOa0CZp3s535hjqUQIsQka0KHslUBNdHQ=

\n

\n\n\n”, :trace-redirects []}}}
at slingshot.support$stack_trace.invoke(support.clj:199)

ERROR metabase.models.field-values Error fetching field values
clojure.lang.ExceptionInfo: Error executing query {:sql “-- /* partner: “metabase”, {“dashboard_id”:null,“chart_id”:null,“optional_user_id”:null,“optional_account_id”:“e068a9f3-d5bd-40e0-84d4-93f495dabd8b”,“filter_values”:{}} */ Metabase\nSELECT “admin”.“v_get_tbl_priv_by_user”.“sel” AS “sel” FROM “admin”.“v_get_tbl_priv_by_user” GROUP BY “admin”.“v_get_tbl_priv_by_user”.“sel” ORDER BY “admin”.“v_get_tbl_priv_by_user”.“sel” ASC LIMIT 5000”, :params nil, :type :driver}

Amazon Invalid operation: schema “pg_temp_12” does not exist;
… 13 more

Let me know what information I can provide to resolve it. Many thanks

flamber · March 11, 2021, 4:22pm

@zeehasham The update check error is completely unrelated and probably because you’re blocking or proxying outgoing connections.

I would guess you could do something like this on Redshift:
select column_name,data_type from information_schema.columns where data_type like '%xid%'

Or check the Metabase application database (Postgres 11.5 in your case) table metabase_field.base_type perhaps contains xid - otherwise look in one of the other columns.

Otherwise you would have to enable debug logging - JAR - example:
java -Dlog4j.configurationFile="https://log4j.us/v2/templates/metabase?trace=metabase.sync" -jar metabase.jar

zeehasham · March 11, 2021, 4:25pm

Okay. I am running a docker so how can I enable the debug logging and provide you more details?

flamber · March 11, 2021, 4:26pm

@zeehasham Then you’re using a custom image I can see.
Docker - add this environmental variable to the docker run ... command:
… -e JAVA_OPTS="-Dlog4j.configurationFile=https://log4j.us/v2/templates/metabase?trace=metabase.sync" …

zeehasham · March 11, 2021, 5:02pm

I found xid in the following tables

schema	table name	column name	data type
pg_catalog	pg_bar_state	bar_state_restore_xid	xid
pg_catalog	pg_bar_ddllog	bar_ddllog_xid	xid
pg_catalog	pg_test	tesxid	xid
pg_catalog	pg_database_info	datfrozenxid	xid
pg_catalog	pg_database_info	datvacuumxid	xid
pg_catalog	pg_locks	transaction	xid
pg_catalog	pg_database	datfrozenxid	xid
pg_catalog	pg_database	datvacuumxid	xid

flamber · March 11, 2021, 5:22pm

@zeehasham Okay, then you should be able to create a sample schema, so the problem can be reproduced. And then it would be much appreciated if you open an issue about this:
https://github.com/metabase/metabase/issues/new/choose

flamber · March 11, 2021, 6:35pm

@zeehasham I meant open a bug report with steps on how to reproduce:
https://github.com/metabase/metabase/issues/new?assignees=&labels=.Needs+Triage%2C+Type%3ABug&template=bug_report.md&title=

zeehasham · March 11, 2021, 6:36pm

I opened it under question
Metabase Sync Error Invalid Operation .Needs Triage Type:Question

#15137 opened 3 minutes ago by zeehasham

flamber · March 11, 2021, 6:42pm

@zeehasham I know, but there’s no detailed information or full stacktraces from the log or anything that can be used to reproduce this. And you have included logs of totally unrelated stuff.
Please open a Bug issue, and close the Question issue.

zeehasham · March 11, 2021, 6:43pm

Okay let me open bug report with steps to reproduce