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

   ### Apache Airflow version
   
   2.7.1
   
   ### What happened
   
   When attempting to upgrade from 2.4.3 to 2.7.1 with an SQLite backing DB, I 
received the following error:
   
   ```
   scheduler_1  | INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
   scheduler_1  | INFO  [alembic.runtime.migration] Will assume 
non-transactional DDL.
   scheduler_1  | INFO  [alembic.runtime.migration] Running upgrade 
6abdffdd4815 -> 98ae134e6fff, Increase length of user identifier columns in 
``ab_user`` and ``ab_register_user`` tables
   scheduler_1  | Traceback (most recent call last):
   scheduler_1  |   File 
"/home/airflow/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", 
line 1910, in _execute_context
   scheduler_1  |     self.dialect.do_execute(
   scheduler_1  |   File 
"/home/airflow/.local/lib/python3.10/site-packages/sqlalchemy/engine/default.py",
 line 736, in do_execute
   scheduler_1  |     cursor.execute(statement, parameters)
   scheduler_1  | sqlite3.OperationalError: table _alembic_tmp_ab_user already 
exists
   scheduler_1  | 
   scheduler_1  | The above exception was the direct cause of the following 
exception:
   scheduler_1  | 
   scheduler_1  | Traceback (most recent call last):
   scheduler_1  |   File "/home/airflow/.local/bin/airflow", line 8, in <module>
   scheduler_1  |     sys.exit(main())
   scheduler_1  |   File 
"/home/airflow/.local/lib/python3.10/site-packages/airflow/__main__.py", line 
59, in main
   scheduler_1  |     args.func(args)
   scheduler_1  |   File 
"/home/airflow/.local/lib/python3.10/site-packages/airflow/cli/cli_config.py", 
line 49, in command
   scheduler_1  |     return func(*args, **kwargs)
   scheduler_1  |   File 
"/home/airflow/.local/lib/python3.10/site-packages/airflow/utils/cli.py", line 
113, in wrapper
   scheduler_1  |     return f(*args, **kwargs)
   scheduler_1  |   File 
"/home/airflow/.local/lib/python3.10/site-packages/airflow/utils/providers_configuration_loader.py",
 line 55, in wrapped_function
   scheduler_1  |     return func(*args, **kwargs)
   scheduler_1  |   File 
"/home/airflow/.local/lib/python3.10/site-packages/airflow/cli/commands/db_command.py",
 line 125, in migratedb
   scheduler_1  |     db.upgradedb(
   scheduler_1  |   File 
"/home/airflow/.local/lib/python3.10/site-packages/airflow/utils/session.py", 
line 77, in wrapper
   scheduler_1  |     return func(*args, session=session, **kwargs)
   scheduler_1  |   File 
"/home/airflow/.local/lib/python3.10/site-packages/airflow/utils/db.py", line 
1629, in upgradedb
   scheduler_1  |     command.upgrade(config, revision=to_revision or "heads")
   scheduler_1  |   File 
"/home/airflow/.local/lib/python3.10/site-packages/alembic/command.py", line 
399, in upgrade
   scheduler_1  |     script.run_env()
   scheduler_1  |   File 
"/home/airflow/.local/lib/python3.10/site-packages/alembic/script/base.py", 
line 578, in run_env
   scheduler_1  |     util.load_python_file(self.dir, "env.py")
   scheduler_1  |   File 
"/home/airflow/.local/lib/python3.10/site-packages/alembic/util/pyfiles.py", 
line 93, in load_python_file
   scheduler_1  |     module = load_module_py(module_id, path)
   scheduler_1  |   File 
"/home/airflow/.local/lib/python3.10/site-packages/alembic/util/pyfiles.py", 
line 109, in load_module_py
   scheduler_1  |     spec.loader.exec_module(module)  # type: ignore
   scheduler_1  |   File "<frozen importlib._bootstrap_external>", line 883, in 
exec_module
   scheduler_1  |   File "<frozen importlib._bootstrap>", line 241, in 
_call_with_frames_removed
   scheduler_1  |   File 
"/home/airflow/.local/lib/python3.10/site-packages/airflow/migrations/env.py", 
line 117, in <module>
   scheduler_1  |     run_migrations_online()
   scheduler_1  |   File 
"/home/airflow/.local/lib/python3.10/site-packages/airflow/migrations/env.py", 
line 111, in run_migrations_online
   scheduler_1  |     context.run_migrations()
   scheduler_1  |   File "<string>", line 8, in run_migrations
   scheduler_1  |   File 
"/home/airflow/.local/lib/python3.10/site-packages/alembic/runtime/environment.py",
 line 937, in run_migrations
   scheduler_1  |     self.get_context().run_migrations(**kw)
   scheduler_1  |   File 
"/home/airflow/.local/lib/python3.10/site-packages/alembic/runtime/migration.py",
 line 624, in run_migrations
   scheduler_1  |     step.migration_fn(**kw)
   scheduler_1  |   File 
"/home/airflow/.local/lib/python3.10/site-packages/airflow/migrations/versions/0124_2_6_0_increase_length_of_user_identifier_columns.py",
 line 43, in upgrade
   scheduler_1  |     with op.batch_alter_table("ab_user") as batch_op:
   scheduler_1  |   File "/usr/local/lib/python3.10/contextlib.py", line 142, 
in __exit__
   scheduler_1  |     next(self.gen)
   scheduler_1  |   File 
"/home/airflow/.local/lib/python3.10/site-packages/alembic/operations/base.py", 
line 375, in batch_alter_table
   scheduler_1  |     impl.flush()
   scheduler_1  |   File 
"/home/airflow/.local/lib/python3.10/site-packages/alembic/operations/batch.py",
 line 161, in flush
   scheduler_1  |     batch_impl._create(self.impl)
   scheduler_1  |   File 
"/home/airflow/.local/lib/python3.10/site-packages/alembic/operations/batch.py",
 line 447, in _create
   scheduler_1  |     op_impl.create_table(self.new_table)
   scheduler_1  |   File 
"/home/airflow/.local/lib/python3.10/site-packages/alembic/ddl/impl.py", line 
354, in create_table
   scheduler_1  |     self._exec(schema.CreateTable(table))
   scheduler_1  |   File 
"/home/airflow/.local/lib/python3.10/site-packages/alembic/ddl/impl.py", line 
193, in _exec
   scheduler_1  |     return conn.execute(  # type: ignore[call-overload]
   scheduler_1  |   File 
"/home/airflow/.local/lib/python3.10/site-packages/sqlalchemy/future/engine.py",
 line 280, in execute
   scheduler_1  |     return self._execute_20(
   scheduler_1  |   File 
"/home/airflow/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", 
line 1710, in _execute_20
   scheduler_1  |     return meth(self, args_10style, kwargs_10style, 
execution_options)
   scheduler_1  |   File 
"/home/airflow/.local/lib/python3.10/site-packages/sqlalchemy/sql/ddl.py", line 
80, in _execute_on_connection
   scheduler_1  |     return connection._execute_ddl(
   scheduler_1  |   File 
"/home/airflow/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", 
line 1477, in _execute_ddl
   scheduler_1  |     ret = self._execute_context(
   scheduler_1  |   File 
"/home/airflow/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", 
line 1953, in _execute_context
   scheduler_1  |     self._handle_dbapi_exception(
   scheduler_1  |   File 
"/home/airflow/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", 
line 2134, in _handle_dbapi_exception
   scheduler_1  |     util.raise_(
   scheduler_1  |   File 
"/home/airflow/.local/lib/python3.10/site-packages/sqlalchemy/util/compat.py", 
line 211, in raise_
   scheduler_1  |     raise exception
   scheduler_1  |   File 
"/home/airflow/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", 
line 1910, in _execute_context
   scheduler_1  |     self.dialect.do_execute(
   scheduler_1  |   File 
"/home/airflow/.local/lib/python3.10/site-packages/sqlalchemy/engine/default.py",
 line 736, in do_execute
   scheduler_1  |     cursor.execute(statement, parameters)
   scheduler_1  | sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) 
table _alembic_tmp_ab_user already exists
   scheduler_1  | [SQL: 
   scheduler_1  | CREATE TABLE _alembic_tmp_ab_user (
   scheduler_1  |       id INTEGER NOT NULL, 
   scheduler_1  |       first_name VARCHAR(256) NOT NULL, 
   scheduler_1  |       last_name VARCHAR(256) NOT NULL, 
   scheduler_1  |       username VARCHAR(512) COLLATE "NOCASE" NOT NULL, 
   scheduler_1  |       password VARCHAR(256), 
   scheduler_1  |       active BOOLEAN, 
   scheduler_1  |       email VARCHAR(512) NOT NULL, 
   scheduler_1  |       last_login DATETIME, 
   scheduler_1  |       login_count INTEGER, 
   scheduler_1  |       fail_login_count INTEGER, 
   scheduler_1  |       created_on DATETIME, 
   scheduler_1  |       changed_on DATETIME, 
   scheduler_1  |       created_by_fk INTEGER, 
   scheduler_1  |       changed_by_fk INTEGER, 
   scheduler_1  |       CONSTRAINT ab_user_pkey PRIMARY KEY (id), 
   scheduler_1  |       CONSTRAINT ab_user_created_by_fk_fkey FOREIGN 
KEY(created_by_fk) REFERENCES ab_user (id), 
   scheduler_1  |       CONSTRAINT ab_user_username_uq UNIQUE (username), 
   scheduler_1  |       CONSTRAINT ab_user_changed_by_fk_fkey FOREIGN 
KEY(changed_by_fk) REFERENCES ab_user (id), 
   scheduler_1  |       CONSTRAINT ab_user_email_uq UNIQUE (email)
   scheduler_1  | )
   scheduler_1  | 
   scheduler_1  | ]
   scheduler_1  | (Background on this error at: https://sqlalche.me/e/14/e3q8)
   ```
   
   I tried dropping this temp table and running the migration again, but 
received an error that the `ab_user` table was trying to be dropped.
   
   ### What you think should happen instead
   
   The migration should be able to complete successfully without errors.
   
   ### How to reproduce
   
   1. Checkout the main branch of this repo: 
https://github.com/OrcaCollective/techbloc-airflow
   2. Run `just build` (after setting up `just`)
   3. Run `just up && just logs` - observe that the migration gets stamped and 
the webserver starts correctly
   4. Check out https://github.com/OrcaCollective/techbloc-airflow/pull/52 
locally
   5. Run `just build` to build the new image
   6. Run `just up && just logs` and observe the above issue
   
   It seems like this might be an SQLite-specific issue. I know that DB is not 
intended to be used in production, if there's not an easy resolution to this it 
can be closed 🙂
   
   The exact migration that is the issue here is 
https://github.com/apache/airflow/blob/main/airflow/migrations/versions/0124_2_6_0_increase_length_of_user_identifier_columns.py
 
   
   ### Operating System
   
   `docker.io/apache/airflow:slim-2.7.1-python3.10`
   
   ### Versions of Apache Airflow Providers
   
   apache-airflow-providers-amazon==8.6.0
   apache-airflow-providers-common-sql==1.7.1
   apache-airflow-providers-ftp==3.5.1
   apache-airflow-providers-http==4.5.1
   apache-airflow-providers-imap==3.3.1
   apache-airflow-providers-sqlite==3.4.3
   apache-airflow-providers-ssh==3.7.2
   
   
   ### Deployment
   
   Docker-Compose
   
   ### Deployment details
   
   Docker-compose with sqlite backing DB
   
   ### Anything else
   
   _No response_
   
   ### Are you willing to submit PR?
   
   - [X] 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: [email protected]

For queries about this service, please contact Infrastructure at:
[email protected]

Reply via email to