localhost doesn't seem to work, but 127.0.0.1 does

[cpcloud]

We're adding support for reading mysql tables in Ibis and despite the documentation using localhost, and localhost also being the default, it seems that localhost doesn't work.

First, here's a command showing I can access MySQL from the mariadb client, using localhost as the hostname:

❯ mariadb --host localhost --port 3306 --user ibis -D ibis_testing <<< 'select 1 + 1 as two'
two
2

Using the same connection parameters in ATTACH, here's what happens:

D install mysql;
D load mysql;
D attach 'host=localhost user=ibis password=ibis port=3306 database=ibis_testing' AS mydb (type mysql);
Error: IO Error: Failed to connect to MySQL database with parameters "host=localhost user=ibis password=ibis port=3306 database=ibis_testing": Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

Doing the same with 127.0.0.1 I can successfully attach the MySQL database:

D install mysql;
D load mysql;
D attach 'host=127.0.0.1 user=ibis password=ibis port=3306 database=ibis_testing' AS mydb (type mysql);
D show databases;
┌───────────────┐
│ database_name │
│    varchar    │
├───────────────┤
│ memory        │
│ mydb          │
└───────────────┘

[ncclementi]

For the record, I tried this with with the nightly version of the extension, and it also gives the same error.

FORCE INSTALL mysql FROM 'http://nightly-extensions.duckdb.org'

[Mytherin]

Thanks for reporting! This does seem to work for me on my Macbook:

$ > duckdb
-- Loading resources from /Users/myth/.duckdbrc
v0.10.0 20b1486d11
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D attach 'mysql:host=localhost user=root database=mysqlscanner' as mysqldb;
D use mysqldb;
D show tables;
┌────────────────────┐
│        name        │
│      varchar       │
├────────────────────┤
│ SaMeCaSeNaMe       │
│ TaBlE              │
│ address            │
│    ·               │
│    ·               │
│    ·               │
│ zero_date          │
│ zero_fill_integers │
├────────────────────┤
│ 49 rows (5 shown)  │
└────────────────────┘

Could you perhaps expand on the MySQL version/OS you are running on?

[cpcloud]

It's been reproduced on:

• Linux and MacOS

This is with mariadb:

❯ mariadb --host localhost --port 3306 --user ibis -D ibis_testing <<< 'select version()'
version()
11.3.2-MariaDB-1:11.3.2+maria~ubu2204

[cpcloud]

The database is running as container with 3306 exposed to the host.

The other tools I've tried have no problem with localhost (pymysql, mariadb client).

[Mytherin]

We use libmysql and pass in the host string directly into mysql_real_connect. I suspect it has something to do with this behavior:

The value of host may be either a host name or an IP address. The client attempts to connect as follows:

* If host is NULL or the string "localhost", a connection to the local host is assumed:

* On Windows, the client connects using a shared-memory connection, if the server has shared-memory connections enabled.

* On Unix, the client connects using a Unix socket file. The unix_socket argument or the MYSQL_UNIX_PORT environment variable may be used to specify the socket name.

* On Windows, if host is ".", or TCP/IP is not enabled and no unix_socket is specified or the host is empty, the client connects using a named pipe, if the server has named-pipe connections enabled. If named-pipe connections are not enabled, an error occurs.

* Otherwise, TCP/IP is used.

You can also influence the type of connection to use with the MYSQL_OPT_PROTOCOL or MYSQL_OPT_NAMED_PIPE options to [mysql_options()](https://dev.mysql.com/doc/c-api/8.0/en/mysql-options.html). The type of connection must be supported by the server.

We should probably convert localhost to 127.0.0.1 and add another option if connecting through a socket is desired.

[danilobellini]

I'm converting to 127.0.0.1 manually as I thought this was more related to an issue upstream. While running on Docker, the UNIX socket file is not in the host filesystem. We can add a volume while running MySQL in Docker just to do so. When it's running "natively" (not in a container, or in the same container of the script using DuckDB), the socket file is there.

[Mytherin]

I have a fix up in https://github.com/duckdb/duckdb_mysql/pull/47

[ncclementi]

Thanks @Mytherin will this be included in the next release of duckdb? I assume for now it'll be accessible in the nightlies, though.

[Mytherin]

Yes this will make it into the MySQL scanner in v0.10.1 when we push it out

[ncclementi]

@Mytherin but it looks like v0.10.1 went out 2 days ago, does this mean we'll need to wait until v0.10.2, I'm still learning how extension releases work.

[Mytherin]

Extensions are released separately - and v0.10.1 actually doesn't have a MySQL extension published yet. We can also update it independently of DuckDB.

[Mytherin]

The extension including the fix is now published for v0.10.1