MYSQL_OPT_RECONNECT is deprecated. When exec airflow db upgrade.

[Sunny-Island]

Apache Airflow version

2.6.3

What happened

When I install airflow can set backend database, I set mysql as my backend. And I exec airflow db upgrade
It shows many warning info contains "WARNING: MYSQL_OPT_RECONNECT is deprecated and will be removed in a future version."

What you think should happen instead

No response

How to reproduce

mysql_config --version
8.0.34
mysql --version
mysql Ver 8.0.34 for Linux on x86_64 (MySQL Community Server - GPL)
setup airflow backend and run airflow db upgrade

Operating System

CentOS 7

Versions of Apache Airflow Providers

No response

Deployment

Other

Deployment details

No response

Anything else

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[boring-cyborg]

Thanks for opening your first issue here! Be sure to follow the issue template! If you are willing to raise PR to address this issue please do so, no need to wait for approval.

[Sunny-Island]

To address this warning, you may consider using the OPT_RECONNECT option of MySQL Connector/C++ instead of MYSQL_OPT_RECONNECT.

[potiuk]

I believe it's you who have to change it.

Tthere is nothing we can do about it because we do not set this option.

It likely comes from your configuration on version of driver you are using or sqlalchemy. You should investigate where it comes from (look at database section configuration where you can set your options or check in the documentation of sqlalchemy/drivers in the version that you are using adds this option.

[Sunny-Island]

When I run airflow standalone, there are too many :
scheduler | WARNING: MYSQL_OPT_RECONNECT is deprecated and will be removed in a future version.
triggerer | WARNING: MYSQL_OPT_RECONNECT is deprecated and will be removed in a future version.
I google realted config but find nothing. @potiuk

[Sunny-Island]

It seem that MYSQL_OPT_RECONNECT will be deprecated, but I dont know where to config, sqlalchemy or airflow or mysqlclient or mysql

[Sunny-Island]

I solve this question by change diver from mysqlclient to mysql-connector-python

[Taragolis]

I could confirm that this error persist and we could see it in CI tests for MySQL backend, and to be honest we do not have a lot of options here.

Spoiler Alert: It not affect our ARM image, only x86_64

Oracle deprecate this options in their C API https://dev.mysql.com/doc/c-api/8.0/en/c-api-auto-reconnect.html in 8.0.34 , which provided by libmysqlclient21 which are used in our regular Airflow Images for x86_64. And after that warning started appear in the different places:

• Ubuntu Launchpad about client
• Ruby
• Perl
• And in mysqlclient, see discussion. This method called in SQLAclhemy's Pool if pre_ping activated

Possible options (I can miss something)

• Recommend set [database] sql_alchemy_pool_pre_ping to False, which might be a reason for other issues
• Switch to MariaDB client libraries - not sure if we can do this for x86_64 without vote/lazy consensus
• Switch to Oracle's mysql-connector-python - Nor Airflow, nor SQLAlchemy test this driver
• Switch to PyMySQL - it pure python implementations which do not use C API, so it should be less performant. We never do not have tests against this library even if we have some parts in airflow codebase
• Close this issue as won't fix

cc: @potiuk

[bmarquesplanet]

Guys, is there a workaround to suppress this warning at all? Tried some of the suggestions here https://airflow.apache.org/docs/apache-airflow/stable/configurations-ref.html#logging and the effect is applied only for airflow specific logging messages

[Taragolis]

Guys, is there a workaround to suppress this warning at all?

No. The messages printed directly to stdout/stderr by libmysqlclient, until it doesn't changed in upstream libraries (mysqlclient / sqlalchemy) nothing we could do here.

Best solution for some one who only started with Airflow and doesn't have anything critical in production is use Postgres as DB Backend 🙄

Other potential solutions described here: #32708 (comment) and I can confirm that there are no such of warning messages with latest MariaDB client libraries (Note: this is experimental support)

[Taragolis]

@bmarquesplanet @Sunny-Island good news, SQLAlchemy 1.4.50 was released 2 days ago and seems like this version fixed pre-ping behaviour for mysqlclient driver, see: https://docs.sqlalchemy.org/en/20/changelog/changelog_14.html#change-1.4.50-mysql