Can't access some tables after update

Hello,
We migrated on v0.46.6.4 last week and now can't access some tables.

This is the error message I’m receiving (Ubuntu 20.04.3 and Mozilla Firefox 115.0.2 or Chrome 115.0.5790.170)

My colleague (also Ubuntu + Mozilla) is getting:
ERROR: invalid input syntax for type boolean: "{"min": 0}"

We use Postgres, a Docker environment with the default H2 database type.

To reproduce, click on tables Campaign or Action Pages on dashboard.proca.app.

Thanks for looking into it!
Ivana

Ivana, we don't have those dashboards, they're yours.

we'll need to know what data types your tables have and even better, the server logs

Hi Luiggi,
We did some further investigation, and we think we got more clues on what is going on.
First, we run another update and we are now on version v0.46.7, but the problem persists.

It seems to be about our JSON 'config' columns, which are now apparently destructured, and their parts are wrongly identified as some other data types (for example booleans).

Those columns usually look something like this:
{
"config": {
"component": {
"camera": {},
"counter": {
"min": 20000
},
"digest": {
"minConstituency": 100
},
"email": {
"field": {
"message": false,
"subject": false
},
"filter": [
"country"
],
"server": {
"processing": false
}
},
"register": {
"custom": {
"bottom": [
"field_StickerImage"
]
},
"field": {
"country": {
“required”: false},
"locality": true
}
}...

After recent updates when we query tables with this column, in the dashboard we get errors like invalid input syntax for type boolean: "{"required": false}" or invalid input syntax for type boolean: "{"min": 0}"

I'll post some logs in the next reply, I tried to upload it as .odt here, but that is not supported,
Thanks,
Ivana

{:database 2,
:query {:source-table 9},
:type "query",
:parameters ,
:middleware {:js-int-to-string? true, :add-default-userland-constraints? true}},
:native
{:query
"SELECT ("public"."action_pages"."config"#>> array[?, ?]::text)::text AS "config → campaign → name", ("public"."action_pages"."config"#>> array[?]::text)::text AS "config → component", ("public"."action_pages"."config"#>> array[?, ?, ?]::text)::boolean AS "config → component → consent → benefit", ("public"."action_pages"."config"#>> array[?, ?, ?]::text)::boolean AS "config → component → consent → checkbox", ("public"."action_pages"."config"#>> array[?, ?, ?]::text)::boolean AS "config → component → consent → confirm", ("public"."action_pages"."config"#>> array[?, ?, ?]::text)::boolean AS "config → component → consent → confirmProcessing", ("public"."action_pages"."config"#>> array[?, ?, ?]::text)::text AS "config → component → consent → content", ("public"."action_pages"."config"#>> array[?, ?, ?, ?]::text)::boolean AS "config → component → consent → email → confirmOptIn", ("public"."action_pages"."config"#>> array[?, ?, ?]::text)::boolean AS "config → component → consent → gdpr", ("public"."action_pages"."config"#>> array[?, ?, ?]::text)::boolean AS "config → component → consent → intro", ("public"."action_pages"."config"#>> array[?, ?, ?]::text)::text AS "config → component → consent → privacyPolicy", ("public"."action_pages"."config"#>> array[?, ?, ?]::text)::boolean AS "config → component → consent → split", ("public"."action_pages"."config"#>> array[?, ?]::text)::boolean AS "config → component → counter", ("public"."action_pages"."config"#>> array[?, ?, ?]::text)::bigint AS "config → component → counter → min", ("public"."action_pages"."config"#>> array[?, ?]::text)::text AS "config → component → country", ("public"."action_pages"."config"#>> array[?, ?, ?, ?]::text)::text AS "config → component → DonateAmount → currency → code", ("public"."action_pages"."config"#>> array[?, ?, ?, ?]::text)::text AS "config → component → DonateAmount → currency 1434d677", ("public"."action_pages"."config"#>> array[?, ?, ?, ?]::text[])::text AS "config → component → DonateAmount → oneoff → default", ("public"."action_pages"."config"#>> array[?, ?, ?, ?, ?]::text[])::text AS "config → component → donation → amount → on_225894a7", ("public"."action_pages"."config"#>> array[?, ?, ?, ?]::text[])::text AS "config → component → donation → external → url", ("public"."action_pages"."config"#>> array[?, ?, ?, ?]::text[])::text AS "config → component → donation → paypal → clientId", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::bigint AS "config → component → eci → actionpage", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::text AS "config → component → eci → apiUrl", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::boolean AS "config → component → eci → readMore", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::text AS "config → component → eci → registrationNumber", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::text AS "config → component → eci → starts", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::text AS "config → component → email → aalistUrl", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::text AS "config → component → email → bcc", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::boolean AS "config → component → email → cc", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::text AS "config → component → email → listUrl", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::text AS "config → component → initiative → prefixActionPage", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::boolean AS "config → component → loader → comment", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::boolean AS "config → component → picture → upload", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::text AS "config → component → redirect → url", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::text AS "config → component → register → actionType", ("public"."action_pages"."config"#>> array[?, ?, ?, ?]::text[])::text AS "config → component → register → captcha → sitekey", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::text AS "config → component → register → field", ("public"."action_pages"."config"#>> array[?, ?, ?, ?]::text[])::boolean AS "config → component → register → field → country", ("public"."action_pages"."config"#>> array[?, ?, ?, ?]::text[])::boolean AS "config → component → register → field → locality", ("public"."action_pages"."config"#>> array[?, ?, ?, ?, ?]::text[])::boolean AS "config → component → register → field → org_6a96eca", ("public"."action_pages"."config"#>> array[?, ?, ?, ?]::text[])::boolean AS "config → component → register → field → postcode", ("public"."action_pages"."config"#>> array[?, ?, ?, ?, ?]::text[])::boolean AS "config → component → register → field → pos_72830468", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::text AS "config → component → register → hcaptcha", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::boolean AS "config → component → register → next", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::boolean AS "config → component → share → anonymous", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::boolean AS "config → component → share → next", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::boolean AS "config → component → share → utm", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::text AS "config → component → twitter → listUrl", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::text AS "config → component → twitter → ok.listUrl", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::boolean AS "config → component → wall → language", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::boolean AS "config → component → widget → autoStart", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::boolean AS "config → component → widget → fab", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::boolean AS "config → component → widget → forceWidth", ("public"."action_pages"."config"#>> array[?, ?]::text[])::text AS "config → config → test", ("public"."action_pages"."config"#>> array[?]::text[])::text AS "config → journey", ("public"."action_pages"."config"#>> array[?]::text[])::text AS "config → layout", ("public"."action_pages"."config"#>> array[?, ?]::text[])::bigint AS "config → layout → built", ("public"."action_pages"."config"#>> array[?, ?]::text[])::text AS "config → layout → HtmlTemplate", ("public"."action_pages"."config"#>> array[?, ?]::text[])::text AS "config → layout → template", ("public"."action_pages"."config"#>> array[?, ?]::text[])::text AS "config → lead → name", ("public"."action_pages"."config"#>> array[?, ?]::text[])::text AS "config → lead → title", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::text AS "config → locales → action → eci", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::text AS "config → locales → campaign: → code", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::text AS "config → locales → campaign: → description", ("public"."action_pages"."config"#>> array[?, ?, ?, ?]::text[])::text AS "config → locales → campaign: → donation → intro", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::text AS "config → locales → campaign: → original", ("public"."action_pages"."config"#>> array[?, ?, ?, ?]::text[])::text AS "config → locales → campaign: → share → default", ("public"."action_pages"."config"#>> array[?, ?, ?, ?]::text[])::text AS "config → locales → campaign: → share → twitter", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::text AS "config → locales → campaign: → site", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::text AS "config → locales → campaign: → title", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::text AS "config → locales → consent → aaintro", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::text AS "config → locales → consent → confirm", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::text AS "config → locales → consent → confirmOptIn", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::text AS "config → locales → consent → emailSent", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::text AS "config → locales → consent → intro", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::text AS "config → locales → consent → opt-in", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::text AS "config → locales → consent → optInConfirmed", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::text AS "config → locales → consent → opt-out", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::text AS "config → locales → consent → processing", ("public"."action_pages"."config"#>> array[?, ?]::text[])::text AS "config → locales → Country", ("public"."action_pages"."config"#>> array[?, ?]::text[])::text AS "config → locales → dialogTitle", ("public"."action_pages"."config"#>> array[?, ?]::text[])::text AS "config → locales → eci:common.form.property.street", ("public"."action_pages"."config"#>>
array[?, ?, ?, ?, ?, ?]::text[])::text AS "config → locales → eci: → common → form →_530878e5", ("public"."action_pages"."config"#>> array[?, ?, ?, ?]::text[])::text AS "config → locales → eci: → congratulations →_b6e028b9", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::text AS "config → locales → email → body<2", ("public"."action_pages"."config"#>> array[?, ?]::text[])::text AS "config → locales → guideline", ("public"."action_pages"."config"#>> array[?, ?]::text[])::text AS "config → locales → Last name", ("public"."action_pages"."config"#>> array[?, ?]::text[])::text AS "config → locales → Next", ("public"."action_pages"."config"#>> array[?, ?]::text[])::text AS "config → locales → No Thanks", ("public"."action_pages"."config"#>> array[?, ?]::text[])::text AS "config → locales → progress_plural", ("public"."action_pages"."config"#>> array[?, ?]::text[])::text AS "config → locales → register", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::text AS "config → locales → share → dummy.message", ("public"."action_pages"."config"#>> array[?, ?, ?, ?]::text[])::text AS "config → locales → share → email → body", ("public"."action_pages"."config"#>> array[?, ?, ?, ?]::text[])::text AS "config → locales → share → email → subject", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::text AS "config → locales → share → message", ("public"."action_pages"."config"#>> array[?, ?, ?, ?]::text[])::text AS "config → locales → step → register → title", ("public"."action_pages"."config"#>> array[?, ?, ?]::text[])::text AS "config → locales → twitter → actionUrl", ("public"."action_pages"."config"#>> array[?]::text[])::text AS "config → organisation", ("public"."action_pages"."config"#>> array[?]::text[])::text AS "config → portal", ("public"."action_pages"."config"#>> array[?]::text[])::boolean AS "config → template", "public"."action_pages"."id" AS "id", "public"."action_pages"."name" AS "name", "public"."action_pages"."locale" AS "locale", "public"."action_pages"."campaign_id" AS "campaign_id", "public"."action_pages"."inserted_at" AS "inserted_at", "public"."action_pages"."updated_at" AS "updated_at", "public"."action_pages"."org_id" AS "org_id", "public"."action_pages"."delivery" AS "delivery", "public"."action_pages"."extra_supporters" AS "extra_supporters", "public"."action_pages"."thank_you_template" AS "thank_you_template", "public"."action_pages"."config" AS "config", "public"."action_pages"."live" AS "live", "public"."action_pages"."supporter_confirm_template" AS "supporter_confirm_template" FROM "public"."action_pages" LIMIT 1048575",
:params
("campaign"
"name"
"component"
"component"
"consent"
"benefit"
"component"
"consent"
"checkbox"
"component"
"consent"
"confirm"
"component"
"consent"
"confirmProcessing"
"component"
"consent"
"content"
"component"
"consent"
"email"
"confirmOptIn"
"component"
"consent"
"gdpr"
"component"
"consent"
"intro"
"component"
"consent"
"privacyPolicy"
"component"
"consent"
"split"
"component"
"counter"
"component"
"counter"
"min"
"component"
"country"
"component"
"DonateAmount"
"currency"
"code"
"component"
"DonateAmount"
"currency"
"symbol"
"component"
"DonateAmount"
"oneoff"
"default"
"component"
"donation"
"amount"
"oneoff"
"default"
"component"
"donation"
"external"
"url"
"component"
"donation"
"paypal"
"clientId"
"component"
"eci"
"actionpage"
"component"
"eci"
"apiUrl"
"component"
"eci"
"readMore"
"component"
"eci"
"registrationNumber"
"component"
"eci"
"starts"
"component"
"email"
"aalistUrl"
"component"
"email"
"bcc"
"component"
"email"
"cc"
"component"
"email"
"listUrl"
"component"
"initiative"
"prefixActionPage"
"component"
"loader"
"comment"
"component"
"picture"
"upload"
"component"
"redirect"
"url"
"component"
"register"
"actionType"
"component"
"register"
"captcha"
"sitekey"
"component"
"register"
"field"
"component"
"register"
"field"
"country"
"component"
"register"
"field"
"locality"
"component"
"register"
"field"
"organisation"
"mandatory"
"component"
"register"
"field"
"postcode"
"component"
"register"
"field"
"postcode"
"required"
"component"
"register"
"hcaptcha"
"component"
"register"
"next"
"component"
"share"
"anonymous"
"component"
"share"
"next"
"component"
"share"
"utm"
"component"
"twitter"
"listUrl"
"component"
"twitter"
"ok.listUrl"
"component"
"wall"
"language"
"component"
"widget"
"autoStart"
"component"
"widget"
"fab"
"component"
"widget"
"forceWidth"
"config"
"test"
"journey"
"layout"
"layout"
"built"
"layout"
"HtmlTemplate"
"layout"
"template"
"lead"
"name"
"lead"
"title"
"locales"
"action"
"eci"
"locales"
"campaign:"
"code"
"locales"
"campaign:"
"description"
"locales"
"campaign:"
"donation"
"intro"
"locales"
"campaign:"
"original"
"locales"
"campaign:"
"share"
"default"
"locales"
"campaign:"
"share"
"twitter"
"locales"
"campaign:"
"site"
"locales"
"campaign:"
"title"
"locales"
"consent"
"aaintro"
"locales"
"consent"
"confirm"
"locales"
"consent"
"confirmOptIn"
"locales"
"consent"
"emailSent"
"locales"
"consent"
"intro"
"locales"
"consent"
"opt-in"
"locales"
"consent"
"optInConfirmed"
"locales"
"consent"
"opt-out"
"locales"
"consent"
"processing"
"locales"
"Country"
"locales"
"dialogTitle"
"locales"
"eci:common.form.property.street"
"locales"
"eci:"
"common"
"form"
"property"
"street_number"
"locales"
"eci:"
"congratulations"
"successfully-title"
"locales"
"email"
"body<2"
"locales"
"guideline"
"locales"
"Last name"
"locales"
"Next"
"locales"
"No Thanks"
"locales"
"progress_plural"
"locales"
"register"
"locales"
"share"
"dummy.message"
"locales"
"share"
"email"
"body"
"locales"
"share"
"email"
"subject"
"locales"
"share"
"message"
"locales"
"step"
"register"
"title"
"locales"
"twitter"
"actionUrl"
"organisation"
"portal"
"template")},
:status :failed,
:class org.postgresql.util.PSQLException,
:stacktrace
["org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)"
"org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)"
"org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)"
"org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496)"
"org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413)"
"org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190)"
"org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:134)"
"com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:1471)"
"--> driver.sql_jdbc.execute$fn__71778.invokeStatic(execute.clj:380)"
"driver.sql_jdbc.execute$fn__71778.invoke(execute.clj:378)"
"driver.sql_jdbc.execute$execute_statement_or_prepared_statement_BANG
.invokeStatic(execute.clj:393)"
"driver.sql_jdbc.execute$execute_statement_or_prepared_statement_BANG_.invoke(execute.clj:389)"
"driver.sql_jdbc.execute$execute_reducible_query$fn__71855.invoke(execute.clj:503)"
"driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:502)"
"driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:489)"
"driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:497)"
"driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:489)"
"driver.sql_jdbc$fn__105200.invokeStatic(sql_jdbc.clj:63)"
"driver.sql_jdbc$fn__105200.invoke(sql_jdbc.clj:61)"
"query_processor.context$executef.invokeStatic(context.clj:60)"
"query_processor.context$executef.invoke(context.clj:49)"
"query_processor.context.default$default_runf.invokeStatic(default.clj:68)"
"query_processor.context.default$default_runf.invoke(default.clj:66)"
"query_processor.context$runf.invokeStatic(context.clj:46)"
"query_processor.context$runf.invoke(context.clj:40)"
"query_processor.reducible$identity_qp.invokeStatic(reducible.clj:15)"
"query_processor.reducible$identity_qp.invoke(reducible.clj:12)"
"query_processor.middleware.cache$maybe_return_cached_results$maybe_return_cached_results_STAR___69239.invoke(cache.clj:224)"
"query_processor.middleware.permissions$check_query_permissions$fn__64711.invoke(permissions.clj:126)"
"query_processor.middleware.mbql_to_native$mbql__GT_native$fn__68130.invoke(mbql_to_native.clj:24)"
"query_processor$fn__70758$combined_post_process__70763$combined_post_process_STAR___70764.invoke(query_processor.clj:243)"
"query_processor$fn__70758$combined_pre_process__70759$combined_pre_process_STAR___70760.invoke(query_processor.clj:240)"
"query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__69150$fn__69155.invoke(resolve_database_and_driver.clj:36)"
"driver$do_with_driver.invokeStatic(driver.clj:92)"
"driver$do_with_driver.invoke(driver.clj:88)"
"query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__69150.invoke(resolve_database_and_driver.clj:35)"
"query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__65021.invoke(fetch_source_query.clj:316)"
"query_processor.middleware.store$initialize_store$fn__65199$fn__65200.invoke(store.clj:12)"
"query_processor.store$do_with_store.invokeStatic(store.clj:47)"
"query_processor.store$do_with_store.invoke(store.clj:41)"
"query_processor.middleware.store$initialize_store$fn__65199.invoke(store.clj:11)"
"query_processor.middleware.normalize_query$normalize$fn__69439.invoke(normalize_query.clj:25)"
"query_processor.middleware.constraints$add_default_userland_constraints$fn__66377.invoke(constraints.clj:54)"
"query_processor.middleware.process_userland_query$process_userland_query$fn__69375.invoke(process_userland_query.clj:151)"
"query_processor.middleware.catch_exceptions$catch_exceptions$fn__69752.invoke(catch_exceptions.clj:171)"
"query_processor.reducible$async_qp$qp_STAR___59520$thunk__59522.invoke(reducible.clj:103)"
"query_processor.reducible$async_qp$qp_STAR___59520.invoke(reducible.clj:109)"
"query_processor.reducible$async_qp$qp_STAR___59520.invoke(reducible.clj:94)"
"query_processor.reducible$sync_qp$qp_STAR___59532.doInvoke(reducible.clj:129)"
"query_processor$process_userland_query.invokeStatic(query_processor.clj:366)"
"query_processor$process_userland_query.doInvoke(query_processor.clj:362)"
"query_processor$fn__70807$process_query_and_save_execution_BANG___70816$fn__70819.invoke(query_processor.clj:377)"
"query_processor$fn__70807$process_query_and_save_execution_BANG___70816.invoke(query_processor.clj:370)"
"query_processor$fn__70852$process_query_and_save_with_max_results_constraints_BANG___70861$fn__70864.invoke(query_processor.clj:389)"
"query_processor$fn__70852$process_query_and_save_with_max_results_constraints_BANG___70861.invoke(query_processor.clj:382)"
"api.dataset$run_query_async$fn__86953.invoke(dataset.clj:73)"
"query_processor.streaming$streaming_response_STAR_$fn__54383$fn__54384.invoke(streaming.clj:166)"
"query_processor.streaming$streaming_response_STAR_$fn__54383.invoke(streaming.clj:165)"
"async.streaming_response$do_f_STAR_.invokeStatic(streaming_response.clj:69)"
"async.streaming_response$do_f_STAR_.invoke(streaming_response.clj:67)"
"async.streaming_response$do_f_async$task__36968.invoke(streaming_response.clj:88)"],
:card_id nil,
:context :ad-hoc,
:error "ERROR: invalid input syntax for type boolean: "{"min": 0}"",
:row_count 0,
:running_time 0,
:preprocessed

Morning @Luiggi ,
What could we do about this situation? Some of our services are disabled, and we would appreciate any advice.
Is there a way to disable JSON destructing?
If you need more data, I'll be happy to provide it,
Thanks,
Ivana

settings->admin->database->select your DB->advanced settings->disable JSON

That solves the problem.
Thanks!