Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Postgres migration bug in 1.4.4 #2664

Closed
ishioni opened this issue Sep 16, 2024 · 12 comments
Closed

Postgres migration bug in 1.4.4 #2664

ishioni opened this issue Sep 16, 2024 · 12 comments
Labels
bug fixed

Comments

@ishioni
Copy link

ishioni commented Sep 16, 2024

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)
@ishioni ishioni mentioned this issue Sep 16, 2024
Closed
1 task
@calvinbui
Copy link

same for me on PostgreSQL 16.4

@morpheus65535
Copy link
Owner

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
Copy link

calvinbui commented Sep 17, 2024 •
edited
Loading

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
Copy link
Owner

morpheus65535 commented Sep 18, 2024 •
edited
Loading

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
Copy link

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)

@morpheus65535
Copy link
Owner

Could you show us the content of this table?

@calvinbui
Copy link

Could you show us the content of this table?

image

@JaiZed
Copy link
Collaborator

JaiZed commented Sep 27, 2024

Can you run this SQL to see if it fixes the column type and values properly?

ALTER TABLE "table_languages_profiles"
ALTER COLUMN "originalFormat"
SET DATA TYPE INTEGER
USING CASE WHEN "originalFormat" IS TRUE THEN 1 ELSE 0 END;

@calvinbui
Copy link

Can you run this SQL to see if it fixes the column type and values properly?

ALTER TABLE "table_languages_profiles"
ALTER COLUMN "originalFormat"
SET DATA TYPE INTEGER
USING CASE WHEN "originalFormat" IS TRUE THEN 1 ELSE 0 END;

That worked.

I ran the sql:

Status: Executed
Duration: 70 ms
Updated Rows: 0

ALTER TABLE "table_languages_profiles"
ALTER COLUMN "originalFormat"
SET DATA TYPE INTEGER
USING CASE WHEN "originalFormat" IS TRUE THEN 1 ELSE 0 END

image

I re-created the container to remove all the changes I did earlier to the migration scripts and it is now up and running.

image

morpheus65535 added a commit that referenced this issue Sep 27, 2024
@morpheus65535
Fixed database migration issue with postgresql. #2664
25098e5
@morpheus65535
Copy link
Owner

Should be fixed in upcoming beta. Let us know if it's not the case!

@ishioni
Copy link
Author

ishioni commented Oct 2, 2024 •
edited
Loading

@morpheus65535 Still no go for me :(
Manually changing the column like the above fixed it tho

Python version greater than 3.11.x is unsupported. Current version is 3.12.7. Keep in mind that even if it works, you're on your own.
Bazarr starting child process with PID 8...
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" is of type boolean but expression is of type integer
LINE 1: ...ATE table_languages_profiles SET "originalFormat"=0 WHERE ta...
                                                             ^
HINT:  You will need to rewrite or cast the expression.


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

Traceback (most recent call last):
  File "/app/bin/bazarr/main.py", line 52, 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
    op.execute(sa.update(TableLanguagesProfiles)
  File "<string>", line 8, in execute
  File "<string>", line 3, in execute
  File "/app/bin/bazarr/../libs/alembic/operations/ops.py", line 2537, in execute
    return operations.invoke(op)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/app/bin/bazarr/../libs/alembic/operations/base.py", line 445, in invoke
    return fn(self, operation)
           ^^^^^^^^^^^^^^^^^^^
  File "/app/bin/bazarr/../libs/alembic/operations/toimpl.py", line 224, in execute_sql
    operations.migration_context.impl.execute(
  File "/app/bin/bazarr/../libs/alembic/ddl/impl.py", line 214, in execute
    self._exec(sql, execution_options)
  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/elements.py", line 513, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/app/bin/bazarr/../libs/sqlalchemy/engine/base.py", line 1630, in _execute_clauseelement
    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" is of type boolean but expression is of type integer
LINE 1: ...ATE table_languages_profiles SET "originalFormat"=0 WHERE ta...
                                                             ^
HINT:  You will need to rewrite or cast the expression.

[SQL: UPDATE table_languages_profiles SET "originalFormat"=%(originalFormat)s WHERE table_languages_profiles."originalFormat" IS NULL]
[parameters: {'originalFormat': 0}]
(Background on this error at: https://sqlalche.me/e/20/f405)

@coryallegory
Copy link
Contributor

Upgrading from 1.4.3 to 1.4.5 I ran into this issue, using postgres.

Running

ALTER TABLE "table_languages_profiles"
ALTER COLUMN "originalFormat"
SET DATA TYPE INTEGER
USING CASE WHEN "originalFormat" IS TRUE THEN 1 ELSE 0 END;

as noted above fixed it.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug fixed
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
5 participants
@coryallegory @calvinbui @morpheus65535 @JaiZed @ishioni