MySQL TCP port argument is ignored

[brian-pond]

What language are you using?

Python 3.11.1

What version are you using?

connectorx 0.3.3

What database are you using?

MariaDB 10.11.6

What dataframe are you using?

Arrow

Can you describe your bug?

Environment Configuration

My host environment has 2 different MariaDB server instances. Each is bound to a different socket:

• Instance A: TCP port 3306
  • Does not have a SQL table named "foo". :no_entry_sign:
• Instance B: TCP port 9999
  • Does have a SQL table named "foo". :heavy_check_mark:

I have triple-checked and tested all credentials and tables. I did this using the standard mysql CLI tool that ships with the server software.

Code

My goal is to query Instance B and create a dataframe from its table "foo"

I have the following Python code:

sql_query = "SELECT name FROM `foo`"
db_uri = "mysql://user:[email protected]:9999/mydatabase"
arrow_table = connectorx.read_sql(db_uri, sql_query, return_type="arrow")

Scenario 1: Both servers online and active

Unexpectedly, my Python code fails with an error: :confused:

RuntimeError: MySqlError { ERROR 1146 (42S02): Table 'mydatabase.foo' doesn't exist }

This error should not happen. My database on TCP port 9999 definitely exists, and definitely has a table "foo". After much testing and troubleshooting, I thought to try another scenario.

Scenario 2: What if I shutdown -only- Instance B?

I completely shut down my Instance B. Nothing is bound to TCP 9999 anymore. I verify that with mysql CLI tool.

I try my code again. The result is a different (but expected) error: [2024-10-08T20:17:22Z ERROR r2d2] DriverError { Could not connect to address `127.0.0.1:9999': Connection refused (os error 111) }

This error ^ is a Good Thing. It's what I expected. My database instance is offline, so connectorx should definitely fail.

Scenario 3: What if I shutdown -only- Instance A?

I turn Instance B back on. And on a hunch, I shutdown my Instance A. So now nothing is bound to TCP 3306 anymore. This shouldn't make a difference. Because I'm not trying to connect to 3306. My URI clearly states 9999.

db_uri = "mysql://user:[email protected]:9999/mydatabase"

I try my code again. And it works perfectly. :eyes: :exploding_head:

Conclusion

Based on the above, I have to conclude this is what's happening:

1. When connectorx is initialized, the software initially tries to establish a connection to the Port specified in your URI connection string. I demonstrated this in Scenario 2.
2. But then
   • If TCP 3306 is online and available? Then connectorx tries to run your SQL query there. No matter what TCP port you actually specified.
   • If TCP 3306 is not online and available, then connectorx tries to use the port you told it to.