You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@airflow.apache.org by "David Szakallas (JIRA)" <ji...@apache.org> on 2019/06/28 19:07:00 UTC
[jira] [Updated] (AIRFLOW-4867) Cannot downgrade
drop_user_and_chart migration script
[ https://issues.apache.org/jira/browse/AIRFLOW-4867?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
David Szakallas updated AIRFLOW-4867:
-------------------------------------
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
{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.
was:
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
{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.
> 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
(v7.6.3#76005)