MYSQL_OPT_RECONNECT is deprecated. When exec airflow db upgrade.

Sunny-Island commented 1 year ago

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

[X] I agree to follow this project's Code of Conduct

boring-cyborg[bot] commented 1 year ago

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 commented 1 year ago

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

potiuk commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

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

Taragolis commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

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: https://github.com/apache/airflow/issues/32708#issuecomment-1722267498 and I can confirm that there are no such of warning messages with latest MariaDB client libraries (Note: this is experimental support)

Taragolis commented 1 year ago

@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

apache / airflow