persistent MySQL connection issues from an Apache Airflow instance on an Azure Linux VM for localhost:3306

Question

I am experiencing persistent connection issues with my MySQL server on Azure from an Apache Airflow instance running on an Azure Linux VM. The error message I receive is "Can't connect to MySQL server on 'localhost:3306' (111)". I installed MySQL thru VM command line and used Airflow in data factory based on the same resource group.

Despite various troubleshooting efforts, including verifying the MySQL server is running, checking firewall rules, and ensuring the correct connection string is being used, the issue persists. I have also attempted to switch to a different MySQL connector (pymysql), but this resulted in a "ModuleNotFoundError: No module named 'pymysql'" error, despite the module being installed in the system Python environment.

The issue arises when running a Python task in an Airflow DAG that attempts to establish a connection to the MySQL server using SQLAlchemy's create_engine function. The full traceback of the error is provided below for reference.

[Include the full traceback here]

mysql_conn_id = os.getenv('MYSQL_CONN_ID', 'mysql+mysqlconnector://root:abc@localhost:3306')

def check_database(mysql_conn_id):
    # Create a database engine using the MySQL connection
    engine = sqlalchemy.create_engine(mysql_conn_id)
    logging.info("create engine succeeded!")
    # Check if the database exists
    check_if_exists = text("SELECT COUNT(*) FROM information_schema.schemata WHERE schema_name = 'MTA'")

    # Obtain a connection from the engine
    with engine.connect() as connection:
        try:
            result = connection.execute(check_if_exists)
            num_of_databases = result.scalar()
            logging.info("check_if_exists query succeeded!")
        except sqlalchemy.exc.SQLAlchemyError as e:
            logging.error(f"An error occurred while interacting with the database: {e}")

        # If the database doesn't exist, create it
        if num_of_databases == 0:
            # Define the SQL statement to create the database
            create_database_sql = text("CREATE DATABASE MTA")
            # Execute the SQL statement
            try:
                connection.execute(create_database_sql)
                logging.info("CREATE DATABASE MTA")
            except sqlalchemy.exc.SQLAlchemyError as e:
                logging.error(f"An error occurred while interacting with the database: {e}")

[2023-06-28, 09:56:23 EDT] {taskinstance.py:1851} ERROR - Task failed with exception
Traceback (most recent call last):
  File "/home/airflow/.local/lib/python3.8/site-packages/mysql/connector/connection_cext.py", line 268, in _open_connection
    self._cmysql.connect(**cnx_kwargs)
_mysql_connector.MySQLInterfaceError: Can't connect to MySQL server on 'localhost:3306' (111)

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 3243, in _wrap_pool_connect
    return fn()
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 310, in connect
    return _ConnectionFairy._checkout(self)
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 868, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 476, in checkout
    rec = pool._do_get()
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/pool/impl.py", line 146, in _do_get
    self._dec_overflow()
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/pool/impl.py", line 143, in _do_get
    return self._create_connection()
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 256, in _create_connection
    return _ConnectionRecord(self)
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 371, in __init__
    self.__connect()
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 666, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 661, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/create.py", line 590, in connect
    return dialect.connect(*cargs, **cparams)
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 584, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/home/airflow/.local/lib/python3.8/site-packages/mysql/connector/pooling.py", line 286, in connect
    return CMySQLConnection(*args, **kwargs)
  File "/home/airflow/.local/lib/python3.8/site-packages/mysql/connector/connection_cext.py", line 101, in __init__
    self.connect(**kwargs)
  File "/home/airflow/.local/lib/python3.8/site-packages/mysql/connector/abstracts.py", line 1108, in connect
    self._open_connection()
  File "/home/airflow/.local/lib/python3.8/site-packages/mysql/connector/connection_cext.py", line 273, in _open_connection
    raise get_mysql_exception(
mysql.connector.errors.DatabaseError: 2003 (HY000): Can't connect to MySQL server on 'localhost:3306' (111)

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/airflow/.local/lib/python3.8/site-packages/airflow/operators/python.py", line 175, in execute
    return_value = self.execute_callable()
  File "/home/airflow/.local/lib/python3.8/site-packages/airflow/operators/python.py", line 193, in execute_callable
    return self.python_callable(*self.op_args, **self.op_kwargs)
  File "/opt/airflow/dags/MTA_hourly_ridership_pipeline.py", line 49, in check_database
    with engine.connect() as connection:
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 3197, in connect
    return self._connection_cls(self, close_with_result=close_with_result)
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 96, in __init__
    else engine.raw_connection()
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 3276, in raw_connection
    return self._wrap_pool_connect(self.pool.connect, _connection)
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 3246, in _wrap_pool_connect
    Connection._handle_dbapi_exception_noconnection(
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2100, in _handle_dbapi_exception_noconnection
    util.raise_(
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 3243, in _wrap_pool_connect
    return fn()
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 310, in connect
    return _ConnectionFairy._checkout(self)
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 868, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 476, in checkout
    rec = pool._do_get()
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/pool/impl.py", line 146, in _do_get
    self._dec_overflow()
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/pool/impl.py", line 143, in _do_get
    return self._create_connection()
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 256, in _create_connection
    return _ConnectionRecord(self)
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 371, in __init__
    self.__connect()
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 666, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 661, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/create.py", line 590, in connect
    return dialect.connect(*cargs, **cparams)
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 584, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/home/airflow/.local/lib/python3.8/site-packages/mysql/connector/pooling.py", line 286, in connect
    return CMySQLConnection(*args, **kwargs)
  File "/home/airflow/.local/lib/python3.8/site-packages/mysql/connector/connection_cext.py", line 101, in __init__
    self.connect(**kwargs)
  File "/home/airflow/.local/lib/python3.8/site-packages/mysql/connector/abstracts.py", line 1108, in connect
    self._open_connection()
  File "/home/airflow/.local/lib/python3.8/site-packages/mysql/connector/connection_cext.py", line 273, in _open_connection
    raise get_mysql_exception(
sqlalchemy.exc.DatabaseError: (mysql.connector.errors.DatabaseError) 2003 (HY000): Can't connect to MySQL server on 'localhost:3306' (111)
(Background on this error at: https://sqlalche.me/e/14/4xp6)

I suspect this might be a transient error, but it has been persistent and is impacting the operation of my Airflow workflows. I would appreciate any assistance in resolving this issue.