Redash db migration for v25.1.0 fails

[artemsafiyulin]

Issue Summary

The database migration command docker-compose run --rm server manage db upgrade for upgrading to 25.1.0 version fails on "change type of json fields from varchar to json" step

Steps to Reproduce

1. docker-compose down
2. change image version in docker-compose.yml to 25.1.0
3. docker-compose run --rm server manage db upgrade

result:

[root@redash-test-upgrade redash]# docker-compose run --rm server manage db upgrade
Creating network "redash_default" with the default driver
Creating redash_postgres_1 ... done
Creating redash_redis_1    ... done
[2025-01-17 11:43:35,276][PID:1][INFO][xmlschema] Resource 'XMLSchema.xsd' is already loaded
[2025-01-17 11:43:36,094][PID:1][INFO][alembic.runtime.migration] Context impl PostgresqlImpl.
[2025-01-17 11:43:36,094][PID:1][INFO][alembic.runtime.migration] Will assume transactional DDL.
[2025-01-17 11:43:36,118][PID:1][INFO][alembic.runtime.migration] Running upgrade 89bc7873a3e0 -> fd4fc850d7ea, Convert user details to jsonb and move user profile image url into details column
[2025-01-17 11:43:38,551][PID:1][INFO][alembic.runtime.migration] Running upgrade fd4fc850d7ea -> 1038c2174f5d, Make case insensitive hash of query text
[2025-01-17 11:43:49,577][PID:1][INFO][alembic.runtime.migration] Running upgrade 1038c2174f5d -> 7ce5925f832b, create sqlalchemy_searchable expressions
[2025-01-17 11:43:49,581][PID:1][INFO][alembic.runtime.migration] Running upgrade 7ce5925f832b -> 7205816877ec, change type of json fields from varchar to json
Traceback (most recent call last):
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.UntranslatableCharacter: unsupported Unicode escape sequence
DETAIL:  \u0000 cannot be converted to text.
CONTEXT:  JSON data, line 1: ...": 35, "index": 0, "type": "column", "yAxis": 0},...


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/app/manage.py", line 9, in <module>
    manager()
  File "/usr/local/lib/python3.10/site-packages/click/core.py", line 1130, in __call__
    return self.main(*args, **kwargs)
  File "/usr/local/lib/python3.10/site-packages/click/core.py", line 1055, in main
    rv = self.invoke(ctx)
  File "/usr/local/lib/python3.10/site-packages/click/core.py", line 1657, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/usr/local/lib/python3.10/site-packages/click/core.py", line 1657, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/usr/local/lib/python3.10/site-packages/click/core.py", line 1404, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/local/lib/python3.10/site-packages/click/core.py", line 760, in invoke
    return __callback(*args, **kwargs)
  File "/usr/local/lib/python3.10/site-packages/click/decorators.py", line 26, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/usr/local/lib/python3.10/site-packages/flask/cli.py", line 357, in decorator
    return __ctx.invoke(f, *args, **kwargs)
  File "/usr/local/lib/python3.10/site-packages/click/core.py", line 760, in invoke
    return __callback(*args, **kwargs)
  File "/usr/local/lib/python3.10/site-packages/flask_migrate/cli.py", line 134, in upgrade
    _upgrade(directory, revision, sql, tag, x_arg)
  File "/usr/local/lib/python3.10/site-packages/flask_migrate/__init__.py", line 95, in wrapped
    f(*args, **kwargs)
  File "/usr/local/lib/python3.10/site-packages/flask_migrate/__init__.py", line 280, in upgrade
    command.upgrade(config, revision, sql=sql, tag=tag)
  File "/usr/local/lib/python3.10/site-packages/alembic/command.py", line 403, in upgrade
    script.run_env()
  File "/usr/local/lib/python3.10/site-packages/alembic/script/base.py", line 583, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/usr/local/lib/python3.10/site-packages/alembic/util/pyfiles.py", line 95, in load_python_file
    module = load_module_py(module_id, path)
  File "/usr/local/lib/python3.10/site-packages/alembic/util/pyfiles.py", line 113, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
  File "<frozen importlib._bootstrap_external>", line 883, in exec_module
  File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
  File "/app/migrations/env.py", line 93, in <module>
    run_migrations_online()
  File "/app/migrations/env.py", line 85, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/usr/local/lib/python3.10/site-packages/alembic/runtime/environment.py", line 948, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/usr/local/lib/python3.10/site-packages/alembic/runtime/migration.py", line 627, in run_migrations
    step.migration_fn(**kw)
  File "/app/migrations/versions/7205816877ec_change_type_of_json_fields_from_varchar_.py", line 67, in upgrade
    op.alter_column('visualizations', 'options',
  File "<string>", line 8, in alter_column
  File "<string>", line 3, in alter_column
  File "/usr/local/lib/python3.10/site-packages/alembic/operations/ops.py", line 1943, in alter_column
    return operations.invoke(alt)
  File "/usr/local/lib/python3.10/site-packages/alembic/operations/base.py", line 445, in invoke
    return fn(self, operation)
  File "/usr/local/lib/python3.10/site-packages/alembic/operations/toimpl.py", line 53, in alter_column
    operations.impl.alter_column(
  File "/usr/local/lib/python3.10/site-packages/alembic/ddl/postgresql.py", line 174, in alter_column
    self._exec(
  File "/usr/local/lib/python3.10/site-packages/alembic/ddl/impl.py", line 207, in _exec
    return conn.execute(construct, multiparams)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/ddl.py", line 72, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1068, in _execute_ddl
    ret = self._execute_context(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1316, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception
    util.raise_(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.DataError: (psycopg2.errors.UntranslatableCharacter) unsupported Unicode escape sequence
DETAIL:  \u0000 cannot be converted to text.
CONTEXT:  JSON data, line 1: ...": 35, "index": 0, "type": "column", "yAxis": 0},...

[SQL: ALTER TABLE visualizations ALTER COLUMN options TYPE JSONB USING options::jsonb]
(Background on this error at: http://sqlalche.me/e/13/9h9h)
[root@redash-test-upgrade redash]#

I try to upgrade from redash/redash:10.1.0.b50633

Technical details:

• Redash Version: 25.1.0
• Browser/OS: Safari/macOS
• How did you install Redash: Docker

[mathieubossaert]

Hi @artemsafiyulin ,

I achieve the same migration this week with success :-) . Did you also change the postgresql image in the docker-compose to pgautoupgrade/pgautoupgrade:lates ? My instance 10.1 was still using PostgreSQL 9.6

[artemsafiyulin]

Yes, my 10.1.0 instance still work with postgres:9.6-alpine because I didn't see any recommendations for upgrade postgresql before upgrade Redash and in the same time I didn't see information about Redash compatibility with newer versions of postgreSQL.

How it works for you with latest image?

And maybe there is some information about compatibility versions or about recommended version for PG and also fro another components (nginx/redis)

[mathieubossaert]

I didn't see any recommendations for upgrade postgresql before upgrade Redash

Me neither, but I have in mind that 9.6 has been EOL for a long time

How it works for you with latest image?

It works really fine. But as we mainly use PostgreSQL datasource I can't tell you for other datasources.

And maybe there is some information about compatibility versions or about recommended version for PG and also fro another components (nginx/redis)

You're right ! I checked those version in this file : https://github.com/getredash/setup/blob/master/data/compose.yaml

-> redis:7-alpine -> redash/nginx:latest

Disclaimer : I am a simple user, maybe redash gurus will have better recommanditions. And I first tried the whole upgrade on a image of my instance ;-)

[eradman]

sqlalchemy.exc.DataError: (psycopg2.errors.UntranslatableCharacter) unsupported Unicode escape sequence DETAIL: \u0000 cannot be converted to text. CONTEXT: JSON data, line 1: ...": 35, "index": 0, "type": "column", "yAxis": 0},...

[SQL: ALTER TABLE visualizations ALTER COLUMN options TYPE JSONB USING options::jsonb] (Background on this error at: http://sqlalche.me/e/13/9h9h)

Looks like you have some non-UTF8 characters, which cannot be translated to JSON. Try connecting to the database erase this escape sequence

docker compose exec postgres psql -U postgres

UPDATE visualizations
SET options = replace(options::text, '\u0000', '')::json
WHERE strpos(options::text, '\u0000') > 0;