Catch error for incorrect credentials in Python

[guilhermedelyra]

What language are you using?

Python

What version are you using?

0.3.3

What database are you using?

MySql (same applies for all databases)

What dataframe are you using?

N/A

Can you describe your bug?

I want to be able to test if the credentials i'm using is valid. The problem is:

When i pass an incorrect connection_string (with invalid credentials), i'm stuck for ~30s looking at connector-x retrying it multiple times before i can actually deal with the error.

An important side-effect of this is that, for example, this action of automatically retrying might block an user from accessing the database because of 'multiple wrong attempts' [e.g: this happens in Oracle]

Even if i hit 'CTRL+C' multiple times, i have to wait those 30s before i can exit the program.

What are the steps to reproduce the behavior?

from connectorx import read_sql
from time import time
from datetime import datetime

start = time()
start_in_timestamp = datetime.fromtimestamp(start)

connection_string = "mysql://myuser:[email protected]:3306/mydb"

print(start_in_timestamp, " - starting...")

try:
    result = read_sql(
        connection_string,
        sql_builder.build_get_schema_and_tables_query(),
    )
except Exception as e:
    print("//////////////")
    print(e)
    print("//////////////")

end = time()
elapsed_time = end - start
end_in_timestamp = datetime.fromtimestamp(end)

print(elapsed_time, "seconds")
print(end_in_timestamp, " - ending...")

$ python main.py

2024-11-20 01:44:17.801953  - starting...
[2024-11-20T04:44:18Z ERROR r2d2] DriverError { Could not connect to address `127.0.0.1:3306': Connection refused (os error 111) }
[2024-11-20T04:44:18Z ERROR r2d2] DriverError { Could not connect to address `127.0.0.1:3306': Connection refused (os error 111) }
[2024-11-20T04:44:19Z ERROR r2d2] DriverError { Could not connect to address `127.0.0.1:3306': Connection refused (os error 111) }
[2024-11-20T04:44:21Z ERROR r2d2] DriverError { Could not connect to address `127.0.0.1:3306': Connection refused (os error 111) }
[2024-11-20T04:44:24Z ERROR r2d2] DriverError { Could not connect to address `127.0.0.1:3306': Connection refused (os error 111) }
[2024-11-20T04:44:30Z ERROR r2d2] DriverError { Could not connect to address `127.0.0.1:3306': Connection refused (os error 111) }
[2024-11-20T04:44:43Z ERROR r2d2] DriverError { Could not connect to address `127.0.0.1:3306': Connection refused (os error 111) }
//////////////
timed out waiting for connection: DriverError { Could not connect to address `127.0.0.1:3306': Connection refused (os error 111) }
//////////////
30.611172437667847 seconds
2024-11-20 01:44:48.413125  - ending...

As you can see in the output, the e Exception is only printed after all those error logs appears (~25s)

[guilhermedelyra]

it would be great if there were at least an option in 'read_sql' for something like number_of_retries

[pangjunrong]

Hey @guilhermedelyra, this is not something we can control for sources using the r2d2 rust package (including r2d2_oracle) for connection pooling. In the r2d2 source code, there is an exponential backoff logic adopted for the retry strategy and connection attempts are separated by a delay which doubles after each unsuccessful retry. This is capped at 30 seconds, which is defined in their config. As such, any form of connection failure (be it due to driver, authentication or something related) will result in this logic being triggered.

You would be better off suggesting an alternative strategy to handle retries on their GitHub Issues.

[guilhermedelyra]

Hey @guilhermedelyra, this is not something we can control for sources using the r2d2 rust package (including r2d2_oracle) for connection pooling. In the r2d2 source code, there is an exponential backoff logic adopted for the retry strategy and connection attempts are separated by a delay which doubles after each unsuccessful retry. This is capped at 30 seconds, which is defined in their config. As such, any form of connection failure (be it due to driver, authentication or something related) will result in this logic being triggered.

You would be better off suggesting an alternative strategy to handle retries on their GitHub Issues.

thanks for responding so quickly!
I've made a comment in their repo 🙂