robinaly opened a new issue, #40928:
URL: https://github.com/apache/airflow/issues/40928
### Apache Airflow version
2.9.3
### If "Other Airflow 2 version" selected, which one?
_No response_
### What happened?
When upgrading the database from 2.7.0 or 2.9.2 via ``airflow db migrate``,
I get the following error message
```
INFO [alembic.runtime.migration] Context impl MySQLImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
INFO [alembic.runtime.migration] Running upgrade 1949afb29106 ->
bff083ad727d, Remove ``idx_last_scheduling_decision`` index on
last_scheduling_decision in dag_run table
INFO [alembic.runtime.migration] Running upgrade bff083ad727d ->
686269002441, Fix inconsistency between ORM and migration files.
Traceback (most recent call last):
File "/opt/venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py",
line 1910, in _execute_context
self.dialect.do_execute(
File
"/opt/venv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line
736, in do_execute
cursor.execute(statement, parameters)
File "/opt/venv/lib/python3.12/site-packages/pymysql/cursors.py", line
153, in execute
result = self._query(query)
^^^^^^^^^^^^^^^^^^
File "/opt/venv/lib/python3.12/site-packages/pymysql/cursors.py", line
322, in _query
conn.query(q)
File "/opt/venv/lib/python3.12/site-packages/pymysql/connections.py", line
563, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/venv/lib/python3.12/site-packages/pymysql/connections.py", line
825, in _read_query_result
result.read()
File "/opt/venv/lib/python3.12/site-packages/pymysql/connections.py", line
1199, in read
first_packet = self.connection._read_packet()
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/venv/lib/python3.12/site-packages/pymysql/connections.py", line
775, in _read_packet
packet.raise_for_error()
File "/opt/venv/lib/python3.12/site-packages/pymysql/protocol.py", line
219, in raise_for_error
err.raise_mysql_exception(self._data)
File "/opt/venv/lib/python3.12/site-packages/pymysql/err.py", line 150, in
raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right
syntax to use near 'prepare stmt from @var;\n execute stmt;\n
deallocate prepare stmt' at line 8")
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/opt/venv/bin/airflow", line 8, in <module>
sys.exit(main())
^^^^^^
File "/opt/venv/lib/python3.12/site-packages/airflow/__main__.py", line
58, in main
args.func(args)
File "/opt/venv/lib/python3.12/site-packages/airflow/cli/cli_config.py",
line 49, in command
return func(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^
File "/opt/venv/lib/python3.12/site-packages/airflow/utils/cli.py", line
114, in wrapper
return f(*args, **kwargs)
^^^^^^^^^^^^^^^^^^
File
"/opt/venv/lib/python3.12/site-packages/airflow/utils/providers_configuration_loader.py",
line 55, in wrapped_function
return func(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^
File
"/opt/venv/lib/python3.12/site-packages/airflow/cli/commands/db_command.py",
line 130, in migratedb
db.upgradedb(
File "/opt/venv/lib/python3.12/site-packages/airflow/utils/session.py",
line 79, in wrapper
return func(*args, session=session, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/venv/lib/python3.12/site-packages/airflow/utils/db.py", line
1632, in upgradedb
command.upgrade(config, revision=to_revision or "heads")
File "/opt/venv/lib/python3.12/site-packages/alembic/command.py", line
406, in upgrade
script.run_env()
File "/opt/venv/lib/python3.12/site-packages/alembic/script/base.py", line
582, in run_env
util.load_python_file(self.dir, "env.py")
File "/opt/venv/lib/python3.12/site-packages/alembic/util/pyfiles.py",
line 95, in load_python_file
module = load_module_py(module_id, path)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/venv/lib/python3.12/site-packages/alembic/util/pyfiles.py",
line 113, in load_module_py
spec.loader.exec_module(module) # type: ignore
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "<frozen importlib._bootstrap_external>", line 995, in exec_module
File "<frozen importlib._bootstrap>", line 488, in
_call_with_frames_removed
File "/opt/venv/lib/python3.12/site-packages/airflow/migrations/env.py",
line 120, in <module>
run_migrations_online()
File "/opt/venv/lib/python3.12/site-packages/airflow/migrations/env.py",
line 114, in run_migrations_online
context.run_migrations()
File "<string>", line 8, in run_migrations
File
"/opt/venv/lib/python3.12/site-packages/alembic/runtime/environment.py", line
946, in run_migrations
self.get_context().run_migrations(**kw)
File
"/opt/venv/lib/python3.12/site-packages/alembic/runtime/migration.py", line
628, in run_migrations
step.migration_fn(**kw)
File
"/opt/venv/lib/python3.12/site-packages/airflow/migrations/versions/0142_2_9_2_fix_inconsistency_between_ORM_and_migration_files.py",
line 46, in upgrade
conn.execute(
File "/opt/venv/lib/python3.12/site-packages/sqlalchemy/future/engine.py",
line 286, in execute
return self._execute_20(
^^^^^^^^^^^^^^^^^
File "/opt/venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py",
line 1710, in _execute_20
return meth(self, args_10style, kwargs_10style, execution_options)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py",
line 334, in _execute_on_connection
return connection._execute_clauseelement(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py",
line 1577, in _execute_clauseelement
ret = self._execute_context(
^^^^^^^^^^^^^^^^^^^^^^
File "/opt/venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py",
line 1953, in _execute_context
self._handle_dbapi_exception(
File "/opt/venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py",
line 2134, in _handle_dbapi_exception
util.raise_(
File "/opt/venv/lib/python3.12/site-packages/sqlalchemy/util/compat.py",
line 211, in raise_
raise exception
File "/opt/venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py",
line 1910, in _execute_context
self.dialect.do_execute(
File
"/opt/venv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line
736, in do_execute
cursor.execute(statement, parameters)
File "/opt/venv/lib/python3.12/site-packages/pymysql/cursors.py", line
153, in execute
result = self._query(query)
^^^^^^^^^^^^^^^^^^
File "/opt/venv/lib/python3.12/site-packages/pymysql/cursors.py", line
322, in _query
conn.query(q)
File "/opt/venv/lib/python3.12/site-packages/pymysql/connections.py", line
563, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/venv/lib/python3.12/site-packages/pymysql/connections.py", line
825, in _read_query_result
result.read()
File "/opt/venv/lib/python3.12/site-packages/pymysql/connections.py", line
1199, in read
first_packet = self.connection._read_packet()
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/venv/lib/python3.12/site-packages/pymysql/connections.py", line
775, in _read_packet
packet.raise_for_error()
File "/opt/venv/lib/python3.12/site-packages/pymysql/protocol.py", line
219, in raise_for_error
err.raise_mysql_exception(self._data)
File "/opt/venv/lib/python3.12/site-packages/pymysql/err.py", line 150, in
raise_mysql_exception
raise errorclass(errno, errval)
sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1064, "You
have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near 'prepare stmt from
@var;\n execute stmt;\n deallocate prepare stmt' at line 8")
[SQL:
set @var=if((SELECT true FROM information_schema.TABLE_CONSTRAINTS
WHERE
CONSTRAINT_SCHEMA = DATABASE() AND
TABLE_NAME = 'connection' AND
CONSTRAINT_NAME = 'unique_conn_id' AND
CONSTRAINT_TYPE = 'UNIQUE') = true,'ALTER TABLE connection
DROP INDEX unique_conn_id','select 1');
prepare stmt from @var;
execute stmt;
deallocate prepare stmt;
]
(Background on this error at: https://sqlalche.me/e/14/f405)
```
### What you think should happen instead?
The db migration should successfully finish.
### How to reproduce
Start with airflow 2.9.3, an empty mysql database. The initial new creation
of new database objects works well.
Now run, for example
```
airflow db downgrade --to-version=2.9.0 --yes
```
Run
```
airflow db migrate
```
### Operating System
Debian GNU/Linux 11 (bullseye)
### Versions of Apache Airflow Providers
apache-airflow-providers-amazon==8.26.0
apache-airflow-providers-common-io==1.3.2
apache-airflow-providers-common-sql==1.14.2
apache-airflow-providers-fab==1.2.1
apache-airflow-providers-ftp==3.10.0
apache-airflow-providers-hashicorp==3.7.1
apache-airflow-providers-http==4.12.0
apache-airflow-providers-imap==3.6.1
apache-airflow-providers-smtp==1.7.1
apache-airflow-providers-sqlite==3.8.1
### Deployment
Other Docker-based deployment
### Deployment details
Mysql version (docker) mysql:8.0.34
### Anything else?
Found on staging, error logs come from development environment which seemed
identical.
### 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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
