Uploaded image for project: 'Apache Airflow'
  1. Apache Airflow
  2. AIRFLOW-4867

Cannot downgrade drop_user_and_chart migration script

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Cannot Reproduce
    • Affects Version/s: 1.10.3, 1.10.4
    • Fix Version/s: None
    • Component/s: database
    • Labels:
      None

      Description

      I needed to downgrade to Airflow 1.10.2 (db head rev 41f5f12752f8) from Airflow 1.10.4b2 (db head rev 004c1210f153).

      When running

      alembic downgrade 41f5f12752f8

       

      I faced the following error

      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)

      I 'fixed' the migration script with only creating the tables if they exist:

      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'")

      After this fix the migrations successfully ran for me.

       

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                David Szakallas David Szakallas
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: