feat: Using kwargs in ibis.mysql.connect

[dexalex84]

Is your feature request related to a problem?

When I'm trying to connect to Starrocks via MYSQL client in Python and can not can not use **kwargs in ibis.mysql.connect
kwargs I need for auth plugins

Details of the setup

I'm trying to use IBIS for working with Starrocks via MYSQL client.

Starrocks supports mysql driver I'm expecting to so it should work fine in Ibis. At least sqlalchemy and mysql-connector-python works fine with Starrocks

In documentation https://ibis-project.org/backends/mysql I found this

pip install 'ibis-framework[mysql]'
( I use python 3.12, no any other libs installed )

 con = ibis.mysql.connect(
    user="username",
    password="password",
    host="hostname",
    port=3306,
    database="database",
)

It did not work for me because I need to use auth plugins:
auth_plugin="mysql_clear_password"

Interesting that's mentioned that connect is a wrapper on do_connect

ibis.mysql.connect is a thin wrapper around do_connect that supports it

ibis.backends.mysql.Backend.do_connect.

What is the motivation behind your request?

Need to use any auth plugin when connection to MYSQL or MYSQL compatible DB

Describe the solution you'd like

Add support of **kwargs from do_connect or make do_connect available in ibis.mysql

Expecting that this works:

cnx = ibis.mysql.connect(
    host=host,
    port=port,
    user=login,
    password=password,
    database = db,
    auth_plugin="mysql_clear_password"
    )

Possible workaround

I have used mysql-connector-python library for creating connection

# need to install  mysql-connector-python

cnx = mysql.connector.connect(
    host=host,
    port=port,
    user=login,
    password=password,
    database = db
    auth_plugin="mysql_clear_password",
    )

ibis_con = ibis.from_connection(cnx)

What version of ibis are you running?

9.5

What backend(s) are you using, if any?

No response

Code of Conduct

• I agree to follow this project's Code of Conduct

[cpcloud]

This doesn't work out of the box because we're using MySQLdb, which has proven to be much more reliable, measured by how little we have to babysit the MySQL CI now versus when we used pymysql.

It looks like mysql-connector-python might be an even better choice given its location under the mysql GitHub org, which to me would suggest it is maintained. Explicit support for Python 3.13 is also a good sign.

I'll explore whether we can switch to mysql-connector-python without much fuss.

[cpcloud]

What is the error you get when try to use ibis.mysql.connect?

We're passing through the auth_plugin argument, and it's being set here, so I'm not 100% sure what's going wrong here without the traceback.

Moving to mysql.connector is not an option as it breaks a bunch of things.

[cpcloud]

I'm able to run the following (z)shell commands:

docker run -p 9030:9030 -d starrocks/allin1-ubuntu:3.4.0
# wait a couple seconds ...
mysql -P9030 -h 127.0.0.1 -u root --password='' <<< "CREATE DATABASE IF NOT EXISTS test; CREATE TABLE IF NOT EXISTS test.t (x int)"

followed by this Ibis code in IPython:

In [1]: from ibis.interactive import *

In [2]: con = ibis.mysql.connect(host="127.0.0.1", user="root", password="", port=9030, database="test")

In [3]: t = con.table("t")

In [4]: t
Out[4]:
┏━━━━━━━━━━━━━━━━━━━━━━┓
┃ x                    ┃
┡━━━━━━━━━━━━━━━━━━━━━━┩
│ int32                │
└──────────────────────┘

without any issue.

[dexalex84]

Thanks

1. I think it's okey not to move into mysql-connector-python.

2. I have not tried on basic mysql connection to Starrocks. In company Starrocks setup we have settings that only allow to work with client-side plugin: mysql_clear_password
   (https://dev.mysql.com/doc/refman/8.4/en/cleartext-pluggable-authentication.html)

If I'm not using it, I'm getting: "incorrect password" or user "not authenticated". Looks like by current Starrocks setup is expecting to get password in encrypted or hashed way.

I tested on mysql-connector-python without ibis.
Works WITH plugin, and does not work work without it.

I will try with PyMySQL connection to our instance with or without plugin and let you know.

About passing through the [auth_plugin argument] - looks like if it's not provided it will get: mysql_native_password that is what is happening I guess. I think it should be possible to pass it into connect method from ibis:

Here is comment in PyMySQL:
https://github.com/PyMySQL/mysqlclient/blob/2076d161e2f09f3fb99e00134c9f415063924ad9/src/MySQLdb/connections.py#L108:

        :param str auth_plugin:
            If supplied, the connection default authentication plugin will be
            changed to this value. Example values:
            `mysql_native_password` or `caching_sha2_password`

Is this possible to provide a auth_plugin IN ibis.mysql.connect:
ibis.mysql.connect(host="127.0.0.1", user="root", password="", port=9030, database="test", auth_plugin="")

?

In the doc:
I see
ibis.mysql.connect is a thin wrapper around [ibis.backends.mysql.Backend.do_connect](https://ibis-project.org/backends/mysql#ibis.backends.mysql.Backend.do_connect).

https://ibis-project.org/backends/mysql

Does it mean that it's possible ? Maybe I just don't know how to pass it properly.

[cpcloud]

You should be able to directly pass auth_plugin="mysql_clear_password", like this:

con = ibis.mysql.connect(..., auth_plugin="mysql_clear_password")