AIRFLOW__DATABASE__SQL_ALCHEMY_CONN_CMD not working as expected in airflow 2.4.3

[dviru]

Hi Team, Currently we are using the airflow 2.2.5 and deployed in EKS cluster. RDS credentials are stored at aws secrets-manager, So we are using the AIRFLOW__DATABASE__SQL_ALCHEMY_CONN_CMD to construct the sql connection URI, in airflow 2.2.5 it is working as expected, but in airflow 2.4.3 adding one extra line at the end of connection URL for example is given below.

in airflow 2.2.5
"postgresql://username:[email protected]:5432/airflow"

in airflow 2.4.3
"postgresql://username:[email protected]:5432/airflow "

When I further debug and came to know that, the below line of code(click here) appending one extra line in airflow 2.2.5 and 2.4.3
SQL_ALCHEMY_CONN = conf.get('database', 'SQL_ALCHEMY_CONN')

but below line of code(click here) is behaving differently in airflow 2.2.5 and 2.4.5, in airflow 2.2.5 it is removing the extra line from the connection url and 2.4.3 it is not
engine = create_engine(SQL_ALCHEMY_CONN, connect_args=connect_args, **engine_args)

So if install SQLAlchemy==1.4.15 in airflow 2.4.3 it will work fine, if we install SQLAlchemy<1.4.15 then we are getting this issue.

By default airflow 2.4.3 install SQLAlchemy==1.4.27 because of that we are getting this issue.

Please do needful help and let me if you need any further information on it.

[potiuk]

I believe your command should sanitize and remove the extra characters. It could be things were working fine accidentally - because SQLAlchemy would remove trailing whitespace, but it should not be there in the first place. Make sure your _CMD removes the trailing whiltespace simply.

[potiuk]

Maybe it's trailing space.

[potiuk]

Or tab

[potiuk]

Or invisibe \r

[Taragolis]

Or echo without -n key (only for bash)

from shutil import which
from subprocess import check_output

for shell_cmd in [which("bash"), which("sh")]:
    print(f" Execute shell commands by {shell_cmd!r} ".center(72, "="))

    o = check_output([shell_cmd, "-c", "echo 0"])
    str_output = o.decode()
    print(repr(str_output))

    o = check_output([shell_cmd, "-c", "echo -n 0"])
    str_output = o.decode()
    print(repr(str_output))

    o = check_output([shell_cmd, "-c", "printf 0"])
    str_output = o.decode()
    print(repr(str_output))

[dviru]

@potiuk, @Taragolis Thanks I will check and let you know.

[prlakhani]

Hi, I believe I'm facing a similar issue in airflow 2.9.3 . The command I run correctly returns a connection string without a trailing newline, but when putting this command in sql_alchemy_conn_cmd, the database connection fails with a database ___ does not exist. Notably, the ___ in the error ends in a newline. If I run that command and pipe the output to a file, there's no newline.

In my installation, I tried editing the run_command function in

airflow/airflow/configuration.py

Line 120 in c6eb262

return output

to conclude it with return output.rstrip() and this resolves my issue. @potiuk @Taragolis is it worth further discussion to submit this as a PR? I'd be concerned about introducing a new unexpected behavior here, and I'm not sure what else this might break/change, or how to dig and test further. Would appreciate any guidance here, and happy to try to contribute where I can!

[sollymay]

I am also facing exactly the same issue. The weird thing is that I use CMD for other environment variables that support CMD and don't have the issue. Seems that the rstrip for now is the only way I was able to make it work as well. As soon as I change that, all the services worked.

Running 2.4.3 on my end