You are viewing a plain text version of this content. The canonical link for it is here.

Posted to commits@airflow.apache.org by GitBox <gi...@apache.org> on 2023/01/11 22:51:04 UTC

[GitHub] [airflow] Gollum999 opened a new issue, #23453: airflow db upgrade: FOREIGN KEY constraint failed

Gollum999 opened a new issue, #23453:
URL: https://github.com/apache/airflow/issues/23453

   ### Apache Airflow version
   
   2.3.0 (latest released)
   
   ### What happened
   
   When upgrading from airflow 2.2.4 to 2.3.0, I tried running `airflow db upgrade` and received the following error:
   
   ```
   $ airflow db upgrade                     
   /opt/conda/envs/production-airflow-2.3.0/lib/python3.9/site-packages/airflow/configuration.py:474: DeprecationWarning: The sql_alchemy_conn option in [core] has been moved to the sql_alchemy_conn option in [database] - the old setting has been used, but please update your config.
     option = self._get_option_from_config_file(deprecated_key, deprecated_section, key, kwargs, section)
   /opt/conda/envs/production-airflow-2.3.0/lib/python3.9/site-packages/airflow/configuration.py:412: FutureWarning: The 'dag_default_view' setting in [webserver] has the old default value of 'tree'. This value has been changed to 'grid' in the running config, but please update your config before Apache Airflow 3.0.
     warnings.warn(
   /opt/conda/envs/production-airflow-2.3.0/lib/python3.9/site-packages/airflow/configuration.py:412: FutureWarning: The 'log_filename_template' setting in [logging] has the old default value of '{{ ti.dag_id }}/{{ ti.task_id }}/{{ ts }}/{{ try_number }}.log'. This value has been changed to 'dag_id={{ ti.dag_id }}/run_id={{ ti.run_id }}/task_id={{ ti.task_id }}/{% if ti.map_index >= 0 %}map_index={{ ti.map_index }}/{% endif %}attempt={{ try_number }}.log' in the running config, but please update your config before Apache Airflow 3.0.
     warnings.warn(
   /opt/conda/envs/production-airflow-2.3.0/lib/python3.9/site-packages/airflow/configuration.py:474: DeprecationWarning: The auth_backend option in [api] has been renamed to auth_backends - the old setting has been used, but please update your config.
     option = self._get_option_from_config_file(deprecated_key, deprecated_section, key, kwargs, section)
   /opt/conda/envs/production-airflow-2.3.0/lib/python3.9/site-packages/airflow/configuration.py:412: FutureWarning: The 'auth_backends' setting in [api] has the old default value of 'airflow.api.auth.backend.deny_all'. This value has been changed to 'airflow.api.auth.backend.session' in the running config, but please update your config before Apache Airflow 3.0.
     warnings.warn(
   /opt/conda/envs/production-airflow-2.3.0/lib/python3.9/site-packages/airflow/configuration.py:474 DeprecationWarning: The sql_alchemy_schema option in [core] has been moved to the sql_alchemy_schema option in [database] - the old setting has been used, but please update your config.
   /opt/conda/envs/production-airflow-2.3.0/lib/python3.9/site-packages/airflow/configuration.py:474 DeprecationWarning: The max_db_retries option in [core] has been moved to the max_db_retries option in [database] - the old setting has been used, but please update your config.
   /opt/conda/envs/production-airflow-2.3.0/lib/python3.9/site-packages/airflow/configuration.py:474 DeprecationWarning: The sql_alchemy_pool_enabled option in [core] has been moved to the sql_alchemy_pool_enabled option in [database] - the old setting has been used, but please update your config.
   /opt/conda/envs/production-airflow-2.3.0/lib/python3.9/site-packages/airflow/configuration.py:474 DeprecationWarning: The sql_engine_encoding option in [core] has been moved to the sql_engine_encoding option in [database] - the old setting has been used, but please update your config.
   DB: sqlite:////home/tsanders/airflow/airflow.db
   Performing upgrade with database sqlite:////home/tsanders/airflow/airflow.db
   [2022-05-02 14:56:35,099] {db.py:1410} INFO - Creating tables
   INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
   INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
   INFO  [alembic.runtime.migration] Running upgrade 587bdf053233 -> 5e3ec427fdd3, Increase length of email and username in ``ab_user`` and ``ab_register_user`` table to ``256`` characters
   Traceback (most recent call last):
     File "/opt/conda/envs/production-airflow-2.3.0/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_context
       self.dialect.do_execute(
     File "/opt/conda/envs/production-airflow-2.3.0/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 716, in do_execute
       cursor.execute(statement, parameters)
   sqlite3.IntegrityError: FOREIGN KEY constraint failed
   
   The above exception was the direct cause of the following exception:
   
   Traceback (most recent call last):
     File "/opt/conda/envs/production-airflow-2.3.0/bin/airflow", line 11, in <module>
       sys.exit(main())
     File "/opt/conda/envs/production-airflow-2.3.0/lib/python3.9/site-packages/airflow/__main__.py", line 38, in main
       args.func(args)
     File "/opt/conda/envs/production-airflow-2.3.0/lib/python3.9/site-packages/airflow/cli/cli_parser.py", line 51, in command
       return func(*args, **kwargs)
     File "/opt/conda/envs/production-airflow-2.3.0/lib/python3.9/site-packages/airflow/utils/cli.py", line 99, in wrapper
       return f(*args, **kwargs)
     File "/opt/conda/envs/production-airflow-2.3.0/lib/python3.9/site-packages/airflow/cli/commands/db_command.py", line 82, in upgradedb
       db.upgradedb(to_revision=to_revision, from_revision=from_revision, show_sql_only=args.show_sql_only)
     File "/opt/conda/envs/production-airflow-2.3.0/lib/python3.9/site-packages/airflow/utils/session.py", line 71, in wrapper
       return func(*args, session=session, **kwargs)
     File "/opt/conda/envs/production-airflow-2.3.0/lib/python3.9/site-packages/airflow/utils/db.py", line 1411, in upgradedb
       command.upgrade(config, revision=to_revision or 'heads')
     File "/opt/conda/envs/production-airflow-2.3.0/lib/python3.9/site-packages/alembic/command.py", line 320, in upgrade
       script.run_env()
     File "/opt/conda/envs/production-airflow-2.3.0/lib/python3.9/site-packages/alembic/script/base.py", line 563, in run_env
       util.load_python_file(self.dir, "env.py")
     File "/opt/conda/envs/production-airflow-2.3.0/lib/python3.9/site-packages/alembic/util/pyfiles.py", line 92, in load_python_file
       module = load_module_py(module_id, path)
     File "/opt/conda/envs/production-airflow-2.3.0/lib/python3.9/site-packages/alembic/util/pyfiles.py", line 108, in load_module_py
       spec.loader.exec_module(module)  # type: ignore
     File "<frozen importlib._bootstrap_external>", line 850, in exec_module
     File "<frozen importlib._bootstrap>", line 228, in _call_with_frames_removed
     File "/opt/conda/envs/production-airflow-2.3.0/lib/python3.9/site-packages/airflow/migrations/env.py", line 107, in <module>
       run_migrations_online()
     File "/opt/conda/envs/production-airflow-2.3.0/lib/python3.9/site-packages/airflow/migrations/env.py", line 101, in run_migrations_online
       context.run_migrations()
     File "<string>", line 8, in run_migrations
     File "/opt/conda/envs/production-airflow-2.3.0/lib/python3.9/site-packages/alembic/runtime/environment.py", line 851, in run_migrations
       self.get_context().run_migrations(**kw)
     File "/opt/conda/envs/production-airflow-2.3.0/lib/python3.9/site-packages/alembic/runtime/migration.py", line 620, in run_migrations
       step.migration_fn(**kw)
     File "/opt/conda/envs/production-airflow-2.3.0/lib/python3.9/site-packages/airflow/migrations/versions/0097_5e3ec427fdd3_increase_length_of_email_and_username.py", line 44, in upgrade
       batch_op.alter_column('email', type_=sa.String(256))
     File "/opt/conda/envs/production-airflow-2.3.0/lib/python3.9/contextlib.py", line 126, in __exit__
       next(self.gen)
     File "/opt/conda/envs/production-airflow-2.3.0/lib/python3.9/site-packages/alembic/operations/base.py", line 374, in batch_alter_table
       impl.flush()
     File "/opt/conda/envs/production-airflow-2.3.0/lib/python3.9/site-packages/alembic/operations/batch.py", line 141, in flush
       batch_impl._create(self.impl)
     File "/opt/conda/envs/production-airflow-2.3.0/lib/python3.9/site-packages/alembic/operations/batch.py", line 431, in _create
       op_impl.drop_table(self.table)
     File "/opt/conda/envs/production-airflow-2.3.0/lib/python3.9/site-packages/alembic/ddl/impl.py", line 372, in drop_table
       self._exec(schema.DropTable(table))
     File "/opt/conda/envs/production-airflow-2.3.0/lib/python3.9/site-packages/alembic/ddl/impl.py", line 193, in _exec
       return conn.execute(construct, multiparams)
     File "/opt/conda/envs/production-airflow-2.3.0/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1200, in execute
       return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
     File "/opt/conda/envs/production-airflow-2.3.0/lib/python3.9/site-packages/sqlalchemy/sql/ddl.py", line 77, in _execute_on_connection
       return connection._execute_ddl(
     File "/opt/conda/envs/production-airflow-2.3.0/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1290, in _execute_ddl
       ret = self._execute_context(
     File "/opt/conda/envs/production-airflow-2.3.0/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1748, in _execute_context
       self._handle_dbapi_exception(
     File "/opt/conda/envs/production-airflow-2.3.0/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1929, in _handle_dbapi_exception
       util.raise_(
     File "/opt/conda/envs/production-airflow-2.3.0/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
       raise exception
     File "/opt/conda/envs/production-airflow-2.3.0/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_context
       self.dialect.do_execute(
     File "/opt/conda/envs/production-airflow-2.3.0/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 716, in do_execute
       cursor.execute(statement, parameters)
   sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) FOREIGN KEY constraint failed
   [SQL: 
   DROP TABLE ab_user]
   (Background on this error at: http://sqlalche.me/e/14/gkpj)
   ```
   
   ### What you think should happen instead
   
   `airflow db upgrade` should work when upgrading from an old version.
   
   ### How to reproduce
   
   From a fresh 2.3.0 install:
   ```
   airflow db init
   airflow db downgrade --to-revision 587bdf053233
   airflow users create -f Test -l User -u test_user -r Public -e airflow@example.com --use-random-password
   airflow db upgrade
   ```
   
   ### Operating System
   
   CentOS Stream 8
   
   ### Versions of Apache Airflow Providers
   
   N/A
   
   ### Deployment
   
   Other
   
   ### Deployment details
   
   Standalone, SQLite DB
   
   ### Anything else
   
   Seems to only break if there are users in the `ab_user` table.
   
   I found [this](https://alembic.sqlalchemy.org/en/latest/batch.html#dealing-with-referencing-foreign-keys) note in the Alembic docs which seems to be related.
   
   ### Are you willing to submit PR?
   
   - [ ] Yes I am willing to submit a PR!
   
   ### Code of Conduct
   
   - [X] I agree to follow this project's [Code of Conduct](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md)
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] airflow db upgrade: FOREIGN KEY constraint failed [airflow]

Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.

github-actions[bot] commented on issue #23453:
URL: https://github.com/apache/airflow/issues/23453#issuecomment-1947855033

   This issue has been automatically marked as stale because it has been open for 365 days without any activity. There has been several Airflow releases since last activity on this issue. Kindly asking to recheck the report against latest Airflow version and let us know if the issue is reproducible. The issue will be closed in next 30 days if no further activity occurs from the issue author.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org