search

morpheus65535 / bazarr

Bazarr is a companion application to Sonarr and Radarr. It manages and downloads subtitles based on your requirements. You define your preferences by TV show or movie and Bazarr takes care of everything for you.
https://www.bazarr.media
GNU General Public License v3.0
2.78k stars 218 forks source link

Postgres migration bug in 1.4.4 #2664

Open ishioni opened 4 days ago

ishioni commented 4 days ago

Describe the bug Upgrading from 1.4.3 to 1.4.4 throws a postgresql error during migrations. Postgres is 16.2

Bazarr starting child process with PID 8...
/app/bin/bazarr/../custom_libs/subzero/language.py:165: SyntaxWarning: invalid escape sequence '\.'
  IETF_MATCH = ".+\.([^-.]+)(?:-[A-Za-z]+)?$"
/app/bin/bazarr/../custom_libs/subzero/language.py:166: SyntaxWarning: invalid escape sequence '\.'
  ENDSWITH_LANGUAGECODE_RE = re.compile("\.([^-.]{2,3})(?:-[A-Za-z]{2,})?$")
/app/bin/bazarr/../custom_libs/subzero/language.py:170: SyntaxWarning: invalid escape sequence '\.'
  language_match = re.match(".+\.([^\.]+)$" if not ietf
Traceback (most recent call last):
  File "/app/bin/bazarr/../libs/sqlalchemy/engine/base.py", line 1960, in _exec_single_context
    self.dialect.do_execute(
  File "/app/bin/bazarr/../libs/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.DatatypeMismatch: column "originalFormat" cannot be cast automatically to type integer
HINT:  You might need to specify "USING "originalFormat"::integer".

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

Traceback (most recent call last):
  File "/app/bin/bazarr/main.py", line 51, in <module>
    migrate_db(app)
  File "/app/bin/bazarr/app/database.py", line 327, in migrate_db
    flask_migrate.upgrade(directory=migrations_directory)
  File "/app/bin/bazarr/../libs/flask_migrate/__init__.py", line 111, in wrapped
    f(*args, **kwargs)
  File "/app/bin/bazarr/../libs/flask_migrate/__init__.py", line 200, in upgrade
    command.upgrade(config, revision, sql=sql, tag=tag)
  File "/app/bin/bazarr/../libs/alembic/command.py", line 403, in upgrade
    script.run_env()
  File "/app/bin/bazarr/../libs/alembic/script/base.py", line 583, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/app/bin/bazarr/../libs/alembic/util/pyfiles.py", line 95, in load_python_file
    module = load_module_py(module_id, path)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/app/bin/bazarr/../libs/alembic/util/pyfiles.py", line 113, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "<frozen importlib._bootstrap_external>", line 995, in exec_module
  File "<frozen importlib._bootstrap>", line 488, in _call_with_frames_removed
  File "/app/bin/migrations/env.py", line 117, in <module>
    run_migrations_online()
  File "/app/bin/migrations/env.py", line 106, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/app/bin/bazarr/../libs/alembic/runtime/environment.py", line 948, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/app/bin/bazarr/../libs/alembic/runtime/migration.py", line 627, in run_migrations
    step.migration_fn(**kw)
  File "/app/bin/migrations/versions/b183a2ac0dd1_.py", line 23, in upgrade
    with op.batch_alter_table('table_languages_profiles') as batch_op:
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/contextlib.py", line 144, in __exit__
    next(self.gen)
  File "/app/bin/bazarr/../libs/alembic/operations/base.py", line 398, in batch_alter_table
    impl.flush()
  File "/app/bin/bazarr/../libs/alembic/operations/batch.py", line 116, in flush
    fn(*arg, **kw)
  File "/app/bin/bazarr/../libs/alembic/ddl/postgresql.py", line 174, in alter_column
    self._exec(
  File "/app/bin/bazarr/../libs/alembic/ddl/impl.py", line 207, in _exec
    return conn.execute(construct, multiparams)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/app/bin/bazarr/../libs/sqlalchemy/engine/base.py", line 1408, in execute
    return meth(
           ^^^^^
  File "/app/bin/bazarr/../libs/sqlalchemy/sql/ddl.py", line 180, in _execute_on_connection
    return connection._execute_ddl(
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/app/bin/bazarr/../libs/sqlalchemy/engine/base.py", line 1519, in _execute_ddl
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/app/bin/bazarr/../libs/sqlalchemy/engine/base.py", line 1839, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/app/bin/bazarr/../libs/sqlalchemy/engine/base.py", line 1979, in _exec_single_context
    self._handle_dbapi_exception(
  File "/app/bin/bazarr/../libs/sqlalchemy/engine/base.py", line 2335, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/app/bin/bazarr/../libs/sqlalchemy/engine/base.py", line 1960, in _exec_single_context
    self.dialect.do_execute(
  File "/app/bin/bazarr/../libs/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DatatypeMismatch) column "originalFormat" cannot be cast automatically to type integer
HINT:  You might need to specify "USING "originalFormat"::integer".

[SQL: ALTER TABLE table_languages_profiles ALTER COLUMN "originalFormat" TYPE INTEGER ]
(Background on this error at: https://sqlalche.me/e/20/f405)
calvinbui commented 4 days ago

same for me on PostgreSQL 16.4

morpheus65535 commented 3 days ago

Is one of you able to edit this file: https://github.com/morpheus65535/bazarr/blob/master/migrations/versions/b183a2ac0dd1_.py#L24

And replace with this: batch_op.alter_column('originalFormat', type_=sa.Integer(), postgresql_using='originalFormat::integer')

Restart Bazarr and let me know if ti fixes the issue. Thanks!

calvinbui commented 3 days ago

Is one of you able to edit this file: https://github.com/morpheus65535/bazarr/blob/master/migrations/versions/b183a2ac0dd1_.py#L24

And replace with this: batch_op.alter_column('originalFormat', type_=sa.Integer(), postgresql_using='originalFormat::integer')

Restart Bazarr and let me know if ti fixes the issue. Thanks!

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedColumn) column "originalformat" does not exist
LINE 1: ... ALTER COLUMN "originalFormat" TYPE INTEGER USING originalFo...
                                                             ^
HINT:  Perhaps you meant to reference the column "table_languages_profiles.originalFormat".

[SQL: ALTER TABLE table_languages_profiles ALTER COLUMN "originalFormat" TYPE INTEGER USING originalFormat::integer]
(Background on this error at: https://sqlalche.me/e/20/f405)
morpheus65535 commented 2 days ago

And if you use this instead in the file?

batch_op.alter_column('originalFormat', type_=sa.Integer(), postgresql_using='table_languages_profiles.originalFormat::integer')

Thanks for testing out, I don't have a postgresql instance to test this. If it doesn't works, I'll deploy one but I would better use my spare time else where for the moment ;-)

calvinbui commented 2 days ago

Is one of you able to edit this file: https://github.com/morpheus65535/bazarr/blob/master/migrations/versions/b183a2ac0dd1_.py#L24 And replace with this: batch_op.alter_column('originalFormat', type_=sa.Integer(), postgresql_using='originalFormat::integer') Restart Bazarr and let me know if ti fixes the issue. Thanks!

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedColumn) column "originalformat" does not exist
LINE 1: ... ALTER COLUMN "originalFormat" TYPE INTEGER USING originalFo...
                                                             ^
HINT:  Perhaps you meant to reference the column "table_languages_profiles.originalFormat".

[SQL: ALTER TABLE table_languages_profiles ALTER COLUMN "originalFormat" TYPE INTEGER USING originalFormat::integer]
(Background on this error at: https://sqlalche.me/e/20/f405)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedColumn) column table_languages_profiles.originalformat does not exist
LINE 1: ... ALTER COLUMN "originalFormat" TYPE INTEGER USING table_lang...
                                                             ^
HINT:  Perhaps you meant to reference the column "table_languages_profiles.originalFormat".

[SQL: ALTER TABLE table_languages_profiles ALTER COLUMN "originalFormat" TYPE INTEGER USING table_languages_profiles.originalFormat::integer]
(Background on this error at: https://sqlalche.me/e/20/f405)