[
https://issues.apache.org/jira/browse/AIRFLOW-4867?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ash Berlin-Taylor resolved AIRFLOW-4867.
----------------------------------------
Resolution: Cannot Reproduce
> Cannot downgrade drop_user_and_chart migration script
> -----------------------------------------------------
>
> Key: AIRFLOW-4867
> URL: https://issues.apache.org/jira/browse/AIRFLOW-4867
> Project: Apache Airflow
> Issue Type: Bug
> Components: database
> Affects Versions: 1.10.3, 1.10.4
> Reporter: David Szakallas
> Priority: Major
>
> I needed to downgrade to Airflow 1.10.2 (db head rev 41f5f12752f8) from
> Airflow 1.10.4b2 (db head rev 004c1210f153).
> When running
> {code:java}
> alembic downgrade 41f5f12752f8{code}
>
> I faced the following error
> {code:java}
> INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
> INFO [alembic.runtime.migration] Will assume transactional DDL.
> [2019-06-28 20:28:02,375] {migration.py:357} INFO - Running downgrade
> 004c1210f153 -> 939bb1e647c8, increase queue name size limit
> [2019-06-28 20:28:03,719] {migration.py:357} INFO - Running downgrade
> 939bb1e647c8 -> 4ebbffe0a39a, task reschedule fk on cascade delete
> [2019-06-28 20:28:04,241] {migration.py:357} INFO - Running downgrade
> 4ebbffe0a39a -> dd4ecb8fbee3, cf5dc11e79ad, a56c9515abdc, Merge heads
> [2019-06-28 20:29:22,124] {migration.py:357} INFO - Running downgrade
> dd4ecb8fbee3 -> c8ffec048a3b, Add schedule interval to dag
> [2019-06-28 20:29:22,468] {migration.py:357} INFO - Running downgrade
> cf5dc11e79ad -> 41f5f12752f8, drop_user_and_chart
> Traceback (most recent call last):
> File
> "/Users/dszakallas/.pyenv/versions/3.6.6/envs/pds-airflow/lib/python3.6/site-packages/sqlalchemy/engine/base.py",
> line 1244, in _execute_context
> cursor, statement, parameters, context
> File
> "/Users/dszakallas/.pyenv/versions/3.6.6/envs/pds-airflow/lib/python3.6/site-packages/sqlalchemy/engine/default.py",
> line 550, in do_execute
> cursor.execute(statement, parameters)
> psycopg2.errors.DuplicateTable: relation "users" already exists
> The above exception was the direct cause of the following exception:
> Traceback (most recent call last):
> File
> "/Users/dszakallas/.pyenv/versions/3.6.6/envs/pds-airflow/bin/alembic", line
> 11, in <module>
> load_entry_point('alembic==1.0.11', 'console_scripts', 'alembic')()
> File
> "/Users/dszakallas/.pyenv/versions/3.6.6/envs/pds-airflow/lib/python3.6/site-packages/alembic/config.py",
> line 540, in main
> CommandLine(prog=prog).main(argv=argv)
> File
> "/Users/dszakallas/.pyenv/versions/3.6.6/envs/pds-airflow/lib/python3.6/site-packages/alembic/config.py",
> line 534, in main
> self.run_cmd(cfg, options)
> File
> "/Users/dszakallas/.pyenv/versions/3.6.6/envs/pds-airflow/lib/python3.6/site-packages/alembic/config.py",
> line 514, in run_cmd
> **dict((k, getattr(options, k, None)) for k in kwarg)
> File
> "/Users/dszakallas/.pyenv/versions/3.6.6/envs/pds-airflow/lib/python3.6/site-packages/alembic/command.py",
> line 317, in downgrade
> script.run_env()
> File
> "/Users/dszakallas/.pyenv/versions/3.6.6/envs/pds-airflow/lib/python3.6/site-packages/alembic/script/base.py",
> line 475, in run_env
> util.load_python_file(self.dir, "env.py")
> File
> "/Users/dszakallas/.pyenv/versions/3.6.6/envs/pds-airflow/lib/python3.6/site-packages/alembic/util/pyfiles.py",
> line 90, in load_python_file
> module = load_module_py(module_id, path)
> File
> "/Users/dszakallas/.pyenv/versions/3.6.6/envs/pds-airflow/lib/python3.6/site-packages/alembic/util/compat.py",
> line 177, in load_module_py
> spec.loader.exec_module(module)
> File "<frozen importlib._bootstrap_external>", line 678, in exec_module
> File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
> File "migrations/env.py", line 75, in <module>
> run_migrations_online()
> File "migrations/env.py", line 69, in run_migrations_online
> context.run_migrations()
> File "<string>", line 8, in run_migrations
> File
> "/Users/dszakallas/.pyenv/versions/3.6.6/envs/pds-airflow/lib/python3.6/site-packages/alembic/runtime/environment.py",
> line 839, in run_migrations
> self.get_context().run_migrations(**kw)
> File
> "/Users/dszakallas/.pyenv/versions/3.6.6/envs/pds-airflow/lib/python3.6/site-packages/alembic/runtime/migration.py",
> line 362, in run_migrations
> step.migration_fn(**kw)
> File
> "/Users/dszakallas/Projects/airflow/airflow/migrations/versions/cf5dc11e79ad_drop_user_and_chart.py",
> line 66, in downgrade
> sa.UniqueConstraint('username')
> File "<string>", line 8, in create_table
> File "<string>", line 3, in create_table
> File
> "/Users/dszakallas/.pyenv/versions/3.6.6/envs/pds-airflow/lib/python3.6/site-packages/alembic/operations/ops.py",
> line 1248, in create_table
> return operations.invoke(op)
> File
> "/Users/dszakallas/.pyenv/versions/3.6.6/envs/pds-airflow/lib/python3.6/site-packages/alembic/operations/base.py",
> line 345, in invoke
> return fn(self, operation)
> File
> "/Users/dszakallas/.pyenv/versions/3.6.6/envs/pds-airflow/lib/python3.6/site-packages/alembic/operations/toimpl.py",
> line 101, in create_table
> operations.impl.create_table(table)
> File
> "/Users/dszakallas/.pyenv/versions/3.6.6/envs/pds-airflow/lib/python3.6/site-packages/alembic/ddl/impl.py",
> line 252, in create_table
> self._exec(schema.CreateTable(table))
> File
> "/Users/dszakallas/.pyenv/versions/3.6.6/envs/pds-airflow/lib/python3.6/site-packages/alembic/ddl/impl.py",
> line 134, in _exec
> return conn.execute(construct, *multiparams, **params)
> File
> "/Users/dszakallas/.pyenv/versions/3.6.6/envs/pds-airflow/lib/python3.6/site-packages/sqlalchemy/engine/base.py",
> line 988, in execute
> return meth(self, multiparams, params)
> File
> "/Users/dszakallas/.pyenv/versions/3.6.6/envs/pds-airflow/lib/python3.6/site-packages/sqlalchemy/sql/ddl.py",
> line 72, in _execute_on_connection
> return connection._execute_ddl(self, multiparams, params)
> File
> "/Users/dszakallas/.pyenv/versions/3.6.6/envs/pds-airflow/lib/python3.6/site-packages/sqlalchemy/engine/base.py",
> line 1050, in _execute_ddl
> compiled,
> File
> "/Users/dszakallas/.pyenv/versions/3.6.6/envs/pds-airflow/lib/python3.6/site-packages/sqlalchemy/engine/base.py",
> line 1248, in _execute_context
> e, statement, parameters, cursor, context
> File
> "/Users/dszakallas/.pyenv/versions/3.6.6/envs/pds-airflow/lib/python3.6/site-packages/sqlalchemy/engine/base.py",
> line 1466, in _handle_dbapi_exception
> util.raise_from_cause(sqlalchemy_exception, exc_info)
> File
> "/Users/dszakallas/.pyenv/versions/3.6.6/envs/pds-airflow/lib/python3.6/site-packages/sqlalchemy/util/compat.py",
> line 399, in raise_from_cause
> reraise(type(exception), exception, tb=exc_tb, cause=cause)
> File
> "/Users/dszakallas/.pyenv/versions/3.6.6/envs/pds-airflow/lib/python3.6/site-packages/sqlalchemy/util/compat.py",
> line 153, in reraise
> raise value.with_traceback(tb)
> File
> "/Users/dszakallas/.pyenv/versions/3.6.6/envs/pds-airflow/lib/python3.6/site-packages/sqlalchemy/engine/base.py",
> line 1244, in _execute_context
> cursor, statement, parameters, context
> File
> "/Users/dszakallas/.pyenv/versions/3.6.6/envs/pds-airflow/lib/python3.6/site-packages/sqlalchemy/engine/default.py",
> line 550, in do_execute
> cursor.execute(statement, parameters)
> sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DuplicateTable) relation
> "users" already exists
> [SQL:
> CREATE TABLE users (
> id SERIAL NOT NULL,
> 69 sa.UniqueConstraint('username')
> username VARCHAR(250),
> email VARCHAR(500),
> password VARCHAR(255),
> superuser BOOLEAN,
> PRIMARY KEY (id),
> UNIQUE (username)
> )
> ]
> (Background on this error at: http://sqlalche.me/e/f405){code}
> I 'fixed' the migration script with only creating the tables if they exist:
> {code:java}
> diff --git a/airflow/migrations/versions/cf5dc11e79ad_drop_user_and_chart.py
> b/airflow/migrations/versions/cf5dc11e79ad_drop_user_and_chart.py
> index 52f20681..066b5dbc 100644
> --- a/airflow/migrations/versions/cf5dc11e79ad_drop_user_and_chart.py
> +++ b/airflow/migrations/versions/cf5dc11e79ad_drop_user_and_chart.py
> @@ -55,37 +55,41 @@ def upgrade():
> def downgrade():
> conn = op.get_bind()
> - op.create_table(
> - 'users',
> - sa.Column('id', sa.Integer(), nullable=False),
> - sa.Column('username', sa.String(length=250), nullable=True),
> - sa.Column('email', sa.String(length=500), nullable=True),
> - sa.Column('password', sa.String(255)),
> - sa.Column('superuser', sa.Boolean(), default=False),
> - sa.PrimaryKeyConstraint('id'),
> - sa.UniqueConstraint('username')
> - )
> -
> - op.create_table(
> - 'chart',
> - sa.Column('id', sa.Integer(), nullable=False),
> - sa.Column('label', sa.String(length=200), nullable=True),
> - sa.Column('conn_id', sa.String(length=250), nullable=False),
> - sa.Column('user_id', sa.Integer(), nullable=True),
> - sa.Column('chart_type', sa.String(length=100), nullable=True),
> - sa.Column('sql_layout', sa.String(length=50), nullable=True),
> - sa.Column('sql', sa.Text(), nullable=True),
> - sa.Column('y_log_scale', sa.Boolean(), nullable=True),
> - sa.Column('show_datatable', sa.Boolean(), nullable=True),
> - sa.Column('show_sql', sa.Boolean(), nullable=True),
> - sa.Column('height', sa.Integer(), nullable=True),
> - sa.Column('default_params', sa.String(length=5000), nullable=True),
> - sa.Column('x_is_date', sa.Boolean(), nullable=True),
> - sa.Column('iteration_no', sa.Integer(), nullable=True),
> - sa.Column('last_modified', sa.DateTime(), nullable=True),
> - sa.ForeignKeyConstraint(['user_id'], ['users.id'], ),
> - sa.PrimaryKeyConstraint('id')
> - )
> + inspector = Inspector.from_engine(conn)
> +
> + if 'users' not in inspector.get_table_names():
> + op.create_table(
> + 'users',
> + sa.Column('id', sa.Integer(), nullable=False),
> + sa.Column('username', sa.String(length=250), nullable=True),
> + sa.Column('email', sa.String(length=500), nullable=True),
> + sa.Column('password', sa.String(255)),
> + sa.Column('superuser', sa.Boolean(), default=False),
> + sa.PrimaryKeyConstraint('id'),
> + sa.UniqueConstraint('username')
> + )
> +
> + if 'chart' not in inspector.get_table_names():
> + op.create_table(
> + 'chart',
> + sa.Column('id', sa.Integer(), nullable=False),
> + sa.Column('label', sa.String(length=200), nullable=True),
> + sa.Column('conn_id', sa.String(length=250), nullable=False),
> + sa.Column('user_id', sa.Integer(), nullable=True),
> + sa.Column('chart_type', sa.String(length=100), nullable=True),
> + sa.Column('sql_layout', sa.String(length=50), nullable=True),
> + sa.Column('sql', sa.Text(), nullable=True),
> + sa.Column('y_log_scale', sa.Boolean(), nullable=True),
> + sa.Column('show_datatable', sa.Boolean(), nullable=True),
> + sa.Column('show_sql', sa.Boolean(), nullable=True),
> + sa.Column('height', sa.Integer(), nullable=True),
> + sa.Column('default_params', sa.String(length=5000),
> nullable=True),
> + sa.Column('x_is_date', sa.Boolean(), nullable=True),
> + sa.Column('iteration_no', sa.Integer(), nullable=True),
> + sa.Column('last_modified', sa.DateTime(), nullable=True),
> + sa.ForeignKeyConstraint(['user_id'], ['users.id'], ),
> + sa.PrimaryKeyConstraint('id')
> + )
> if conn.dialect.name == 'mysql':
> conn.execute("SET time_zone = '+00:00'"){code}
> After this fix the migrations successfully ran for me.
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
