You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@airflow.apache.org by "vinitpayal (via GitHub)" <gi...@apache.org> on 2025/12/01 12:05:29 UTC
[I] Airflow migration from 2.11 to 3.1.3 MySQL wrong index creation syntax [airflow]
vinitpayal opened a new issue, #58894:
URL: https://github.com/apache/airflow/issues/58894
### Apache Airflow version
3.1.3
### If "Other Airflow 2/3 version" selected, which one?
_No response_
### What happened?
We had an existing Airflow instance running with Airflow 2.11 and using MySQL 8.0.35 as metadata database.
Now while upgrading it gives error in migrating database in **_/airflow/providers/fab/migrations/versions/0001_1_4_0_create_ab_tables_if_missing.py_**, index creation query here is wrong as MySQL doesn't support syntax `CREATE INDEX IF NOT EXISTS`
```
│ File "/usr/local/lib/python3.12/site-packages/airflow/providers/fab/migrations/versions/0001_1_4_0_create_ab_tables_if_missing.py", line 141, in upgrade │
│ with op.batch_alter_table("ab_group_role", schema=None) as batch_op: │
│ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ │
│ File "/usr/local/lib/python3.12/contextlib.py", line 144, in __exit__ │
│ next(self.gen) │
│ File "/usr/local/lib/python3.12/site-packages/alembic/operations/base.py", line 397, in batch_alter_table │
│ impl.flush() │
│ File "/usr/local/lib/python3.12/site-packages/alembic/operations/batch.py", line 115, in flush │
│ fn(*arg, **kw) │
│ File "/usr/local/lib/python3.12/site-packages/alembic/ddl/impl.py", line 452, in create_index │
│ self._exec(schema.CreateIndex(index, **kw)) │
│ File "/usr/local/lib/python3.12/site-packages/alembic/ddl/impl.py", line 246, in _exec │
│ return conn.execute(construct, params) │
│ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ │
│ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/future/engine.py", line 286, in execute │
│ return self._execute_20( │
│ ^^^^^^^^^^^^^^^^^ │
│ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1710, in _execute_20 │
│ return meth(self, args_10style, kwargs_10style, execution_options) │
│ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ │
│ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/ddl.py", line 80, in _execute_on_connection │
│ return connection._execute_ddl( │
│ ^^^^^^^^^^^^^^^^^^^^^^^^ │
│ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1477, in _execute_ddl │
│ ret = self._execute_context( │
│ ^^^^^^^^^^^^^^^^^^^^^^ │
│ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1953, in _execute_context │
│ self._handle_dbapi_exception( │
│ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2134, in _handle_dbapi_exception │
│ util.raise_( │
│ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/util/compat.py", line 211, in raise_ │
│ raise exception │
│ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context │
│ self.dialect.do_execute( │
│ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute │
│ cursor.execute(statement, parameters) │
│ File "/usr/local/lib/python3.12/site-packages/MySQLdb/cursors.py", line 179, in execute │
│ res = self._query(mogrified_query) │
│ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^ │
│ File "/usr/local/lib/python3.12/site-packages/MySQLdb/cursors.py", line 330, in _query │
│ db.query(q) │
│ File "/usr/local/lib/python3.12/site-packages/MySQLdb/connections.py", line 280, in query │
│ _mysql.connection.query(self, query) │
│ sqlalchemy.exc.ProgrammingError: (MySQLdb.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 'IF NOT EXISTS idx_group_id ON ab_group_role (gr │
│ [SQL: CREATE INDEX IF NOT EXISTS idx_group_id ON ab_group_role (group_id)] │
│ (Background on this error at: https://sqlalche.me/e/14/f405)
```
### What you think should happen instead?
Migrations should work with MySQL(I think an issue with the alembic which we are using to apply the migrations).
### How to reproduce
- Create an airflow instance with version 2.11 and MySQL being database
- Upgrade airflow version to 3.1.3/3.1.2
- Will encounter the migration error
### Operating System
Debian GNU/Linux 12 (bookworm)
### Versions of Apache Airflow Providers
apache-airflow-providers-amazon==9.16.0
apache-airflow-providers-cncf-kubernetes==10.7.0
apache-airflow-providers-common-compat==1.8.0
apache-airflow-providers-common-io==1.6.4
apache-airflow-providers-common-sql==1.28.2
apache-airflow-providers-fab==3.0.1
apache-airflow-providers-http==5.4.0
apache-airflow-providers-mysql==6.3.4
apache-airflow-providers-slack==9.4.0
apache-airflow-providers-smtp==2.3.1
apache-airflow-providers-standard==1.9.1
### Deployment
Official Apache Airflow Helm Chart
### Deployment details
_No response_
### 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: commits-unsubscribe@airflow.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Airflow migration from 2.11 to 3.1.3 MySQL wrong index creation syntax [airflow]
Posted by "meher1993 (via GitHub)" <gi...@apache.org>.
meher1993 commented on issue #58894:
URL: https://github.com/apache/airflow/issues/58894#issuecomment-3618371985
Yeah, that was a miss, I was focusing on postgres backend and didn't consider MySQL. We can probably use [inspector.get_indexe](https://docs.sqlalchemy.org/en/20/core/reflection.html#sqlalchemy.engine.reflection.Inspector.get_indexes) to check if the index exists and remove the if_not_exists=True for the index creations.
--
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
Re: [I] Airflow migration from 2.11 to 3.1.3 MySQL wrong index creation syntax [airflow]
Posted by "dheerajturaga (via GitHub)" <gi...@apache.org>.
dheerajturaga commented on issue #58894:
URL: https://github.com/apache/airflow/issues/58894#issuecomment-3993458271
> Seems that it will be fixed soon:[#62308](https://github.com/apache/airflow/pull/62308) In version 3.4 of FAB provider
yes #62308 fixes this problem.
--
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
Re: [I] Airflow migration from 2.11 to 3.1.3 MySQL wrong index creation syntax [airflow]
Posted by "fsilberstein (via GitHub)" <gi...@apache.org>.
fsilberstein commented on issue #58894:
URL: https://github.com/apache/airflow/issues/58894#issuecomment-3616486198
Yes, we have the same issue here. It's directly linked to MySQL CREATE INDEX ddl syntax and SQLAlchemy adding the "IF NOT EXISTS" while this is not compatible.
FAB change: https://github.com/apache/airflow/pull/56328/files
SQLAlchemy main branch is still adding this option: https://github.com/sqlalchemy/sqlalchemy/blob/9a0d00433134b44a132104618b96516e47fff224/lib/sqlalchemy/dialects/mysql/base.py#L2242
We downgraded to `apache-airflow-providers-fab = "==2.4.4"` and it worked (we just had to delete a table already existing)
--
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
Re: [I] Airflow migration from 2.11 to 3.1.3 MySQL wrong index creation syntax [airflow]
Posted by "vinitpayal (via GitHub)" <gi...@apache.org>.
vinitpayal commented on issue #58894:
URL: https://github.com/apache/airflow/issues/58894#issuecomment-3596318172
@meher1993 I think this is because of [this FAB migration](https://github.com/apache/airflow/blob/main/providers/fab/src/airflow/providers/fab/migrations/versions/0001_1_4_0_create_ab_tables_if_missing.py), code says it's a placeholder migration BUT if that's the case it shouldn't break when we run `airflow db migrate` during the upgrade process
--
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
Re: [I] Airflow migration from 2.11 to 3.1.3 MySQL wrong index creation syntax [airflow]
Posted by "eladkal (via GitHub)" <gi...@apache.org>.
eladkal closed issue #58894: Airflow migration from 2.11 to 3.1.3 MySQL wrong index creation syntax
URL: https://github.com/apache/airflow/issues/58894
--
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
Re: [I] Airflow migration from 2.11 to 3.1.3 MySQL wrong index creation syntax [airflow]
Posted by "Mourya1319 (via GitHub)" <gi...@apache.org>.
Mourya1319 commented on issue #58894:
URL: https://github.com/apache/airflow/issues/58894#issuecomment-3644960288
Same issue here. Getting this error:
```
sqlalchemy.exc.ProgrammingError: (MySQLdb.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 'IF NOT EXISTS idx_group_id ON ab_group_role (group_id)' at line 1")
[SQL: CREATE INDEX IF NOT EXISTS idx_group_id ON ab_group_role (group_id)]
(Background on this error at: https://sqlalche.me/e/14/f405)
```
The fab version we use: `apache-airflow-providers-fab==3.0.1`
Should i manually run the sql queries in this below file to finish the migration and update the alembic version? `/airflow/providers/fab/migrations/versions/0001_1_4_0_create_ab_tables_if_missing.py`
Also should i run `airflow db migrate` command again to check for any missing migrations?
Or if i downgrade `apache-airflow-providers-fab` to `2.4.4` as @fsilberstein said, should i run migrate command again?
Tagging @vinitpayal @meher1993 to confirm my next steps!
--
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