Migration doesn't like rows with null characters

[bagafoot]

Describe the bug

Hello,
I'm running metabase service on ubuntu and postgresql 14 as database.
psql (14.9 (Ubuntu 14.9-0ubuntu0.22.04.1))
Trying to update from 48.8 to 49.2 but metabase service doent work properly,
when check journalctl there are some errors as

Caused by: liquibase.exception.MigrationFailedException: Migration failed for changeset migrations/001_update_migrations.yaml::v49.2024-01-22T11:52:00::qnkhuat:
Reason: clojure.lang.ExceptionInfo: ERROR: unsupported Unicode escape sequence

another error:

ERROR jdbcjobstore.JobStoreTX :: MisfireHandler: Error handling misfires: Couldn't store trigger 'DEFAULT.metabase.task.truncate-audit-log.trigger' for 'DEFAULT.metabase.task.truncate-audit-log.job' job:Cou>
 org.quartz.JobPersistenceException: Couldn't store trigger 'DEFAULT.metabase.task.truncate-audit-log.trigger' for 'DEFAULT.metabase.task.truncate-audit-log.job' job:Couldn't retrieve job because a required class was not found: met>

full log:
https://hackmd.io/@OdFNQ07XQeCmq8QL3wcWzw/BJluLgOJ0

To Reproduce

follow update instructions.
Change newer merabase.jar and start metabase service

Expected behavior

No response

Logs

No response

Information about your Metabase installation

Chrome Version 121.0.6167.140 (Official Build) (64-bit)
Ubuntu 22.04.1
Postgresql 14.9
Metabase 0.48.8
Jar File on Ubuntu
Postgresql 14.9

Severity

FATAL

Additional context

No response

[qnkhuat]

the query being executed is

UPDATE revision
               SET object = jsonb_set(
                  object::jsonb, '{type}',
                  to_jsonb(CASE
                              WHEN (object::jsonb->>'dataset')::boolean THEN 'model'
                              ELSE 'question'
                           END)::jsonb, true)
               WHERE model = 'Card' AND (object::jsonb->>'dataset') IS NOT NULL;

it's probably something weird in your revision.object that makes this query fail.

[paoliniluis]

Apr 01 11:34:37 METABASE06S01 metabase[1162568]:   Detail: \u0000 cannot be converted to text.
Apr 01 11:34:37 METABASE06S01 metabase[1162568]:   Where: JSON data, line 1: ....\\..\\..\\..\\..\\..\\..\\windows\\win.ini\u0000... {:toucan2/context-trace [["execute SQL with class com.mchange.v2.c3p0.impl.NewProxyConnection" {:toucan2.jdbc.query/sql-args ["UPDATE revision\n >

this is interesting, what's that win.ini thing in the database?

[bagafoot]

@qnkhuat @paoliniluis
this is the problematic row

{
  "description": "Description",
  "archived": false,
  "collection_position": null,
  "table_id": 180,
  "database_id": 2,
  "enable_embedding": false,
  "collection_id": null,
  "query_type": "query",
  "name": "Beptr FactTumProjeler",
  "embedding_params": null,
  "cache_ttl": null,
  "dataset_query": {
    "database": 2,
    "type": "query",
    "query": {
      "source-table": 180
    }
  },
  "parameter_mappings": [],
  "display": "table",
  "collection_preview": true,
  "visualization_settings": {
    "table.pivot_column": "ProjectState_Id",
    "table.cell_column": "..\\..\\..\\..\\..\\..\\..\\..\\..\\..\\..\\..\\..\\..\\..\\..\\windows\\win.ini\u0000Project_Id"
  },
  "parameters": [],
  "dataset": false
}

[paoliniluis]

So the fix is simple, wipe that stuff from the database. I just changed the name of the issue to reflect the actual problem

[calherries]

resolved by #41584, the issue was that for postgres, converting text to JSONB doesn't work if the text contains any null characters.