duckdb_mysql
duckdb_mysql copied to clipboard
duckdb
Fail to connect when using ATTACH '' with environment variable
What happens?
io error was raised when attaching with only environment variable.
-- error
ATTACH '' AS mysqldb (TYPE mysql);
COPY (
SELECT * FROM mysqldb.users
) TO 'users.csv' (FORMAT 'csv', HEADER true);
-- ok
ATTACH 'host=mysql' AS mysqldb (TYPE mysql);
COPY (
SELECT * FROM mysqldb.users
) TO 'users.csv' (FORMAT 'csv', HEADER true);
-- also, ok
ATTACH 'database=sample_db' AS mysqldb (TYPE mysql);
COPY (
SELECT * FROM mysqldb.users
) TO 'users.csv' (FORMAT 'csv', HEADER true);
ATTACH 'user=root' AS mysqldb (TYPE mysql);
COPY (
SELECT * FROM mysqldb.users
) TO 'users.csv' (FORMAT 'csv', HEADER true);
i read MySQLUtils::ParseConnectionParameters, but it looks fine. (please notice i am novice on cpp) That's why i create this pull request.
MySQLConnectionParameters MySQLUtils::ParseConnectionParameters(const string &dsn) {
MySQLConnectionParameters result;
unordered_set<string> set_options;
// parse options
idx_t pos = 0;
while (pos < dsn.size()) {
string key;
string value;
if (!ParseValue(dsn, pos, key)) {
break;
}
if (pos >= dsn.size() || dsn[pos] != '=') {
throw InvalidInputException("Invalid dsn \"%s\" - expected key=value pairs separated by spaces", dsn);
}
pos++;
if (!ParseValue(dsn, pos, value)) {
throw InvalidInputException("Invalid dsn \"%s\" - expected key=value pairs separated by spaces", dsn);
}
key = StringUtil::Lower(key);
if (key == "host") {
set_options.insert("host");
result.host = value;
} else if (key == "user") {
set_options.insert("user");
result.user = value;
} else if (key == "passwd" || key == "password") {
set_options.insert("password");
result.passwd = value;
} else if (key == "db" || key == "database") {
set_options.insert("database");
result.db = value;
} else if (key == "port") {
set_options.insert("port");
result.port = ParsePort(value);
} else if (key == "socket" || key == "unix_socket") {
set_options.insert("socket");
result.unix_socket = value;
} else if (key == "compress") {
set_options.insert("compress");
if (ParseBoolValue(value)) {
result.client_flag |= CLIENT_COMPRESS;
} else {
result.client_flag &= ~CLIENT_COMPRESS;
}
} else if (key == "compression") {
set_options.insert("compress");
auto val = StringUtil::Lower(value);
if (val == "required") {
result.client_flag |= CLIENT_COMPRESS;
} else if (val == "disabled") {
result.client_flag &= ~CLIENT_COMPRESS;
} else if (val == "preferred") {
// nop
} else {
throw InvalidInputException("Invalid dsn - compression mode must be either disabled/required/preferred - got %s",
value);
}
} else if (key == "ssl_mode") {
set_options.insert("ssl_mode");
auto val = StringUtil::Lower(value);
if (val == "disabled") {
result.ssl_mode = SSL_MODE_DISABLED;
} else if (val == "required") {
result.ssl_mode = SSL_MODE_REQUIRED;
} else if (val == "verify_ca") {
result.ssl_mode = SSL_MODE_VERIFY_CA;
} else if (val == "verify_identity") {
result.ssl_mode = SSL_MODE_VERIFY_IDENTITY;
} else if (val == "preferred") {
result.ssl_mode = SSL_MODE_PREFERRED;
} else {
throw InvalidInputException("Invalid dsn - ssl mode must be either disabled, required, verify_ca, "
"verify_identity or preferred - got %s",
value);
}
} else if (key == "ssl_ca") {
set_options.insert("ssl_ca");
result.ssl_ca = value;
} else if (key == "ssl_capath") {
set_options.insert("ssl_capath");
result.ssl_ca_path = value;
} else if (key == "ssl_cert") {
set_options.insert("ssl_cert");
result.ssl_cert = value;
} else if (key == "ssl_cipher") {
set_options.insert("ssl_cipher");
result.ssl_cipher = value;
} else if (key == "ssl_crl") {
set_options.insert("ssl_crl");
result.ssl_crl = value;
} else if (key == "ssl_crlpath") {
set_options.insert("ssl_crlpath");
result.ssl_crl_path = value;
} else if (key == "ssl_key") {
set_options.insert("ssl_key");
result.ssl_key = value;
} else {
throw InvalidInputException("Unrecognized configuration parameter \"%s\" "
"- expected options are host, "
"user, passwd, db, port, socket",
key);
}
}
// read options that are not set from environment variables
if (set_options.find("host") == set_options.end()) {
ReadOptionFromEnv("MYSQL_HOST", result.host);
}
if (set_options.find("password") == set_options.end()) {
ReadOptionFromEnv("MYSQL_PWD", result.passwd);
}
if (set_options.find("user") == set_options.end()) {
ReadOptionFromEnv("MYSQL_USER", result.user);
}
if (set_options.find("database") == set_options.end()) {
ReadOptionFromEnv("MYSQL_DATABASE", result.db);
}
if (set_options.find("socket") == set_options.end()) {
ReadOptionFromEnv("MYSQL_UNIX_PORT", result.unix_socket);
}
if (set_options.find("port") == set_options.end()) {
string port_number;
if (ReadOptionFromEnv("MYSQL_TCP_PORT", port_number)) {
result.port = ParsePort(port_number);
}
}
if (set_options.find("compress") == set_options.end()) {
string compress;
if (ReadOptionFromEnv("MYSQL_COMPRESS", compress)) {
if (ParseBoolValue(compress)) {
result.client_flag |= CLIENT_COMPRESS;
} else {
result.client_flag &= ~CLIENT_COMPRESS;
}
}
}
return result;
}
To Reproduce
Debian bookworm
SQL
ATTACH '' AS mysqldb (TYPE mysql);
COPY (
SELECT * FROM mysqldb.users
) TO 'users.csv' (FORMAT 'csv', HEADER true);
project
.
├── app
│ └── read_users_csv.sql
├── compose.yml
├── docker
│ ├── duckdb-Dockerfile
│ └── mysql-Dockerfile
└── init
└── init.sql
Dockerfile for DuckDB
FROM debian:bookworm-slim
RUN apt-get update \
&& apt-get install -y wget unzip curl gnupg2
WORKDIR /tmp
RUN wget https://github.com/duckdb/duckdb/releases/download/v1.2.0/duckdb_cli-linux-aarch64.zip
RUN unzip duckdb_cli-linux-aarch64.zip -d /usr/local/bin && \
chmod +x /usr/local/bin/duckdb && \
rm duckdb_cli-linux-aarch64.zip
WORKDIR /root
RUN echo "INSTALL mysql;" > .duckdbrc && \
echo "LOAD mysql;" >> .duckdbrc
RUN mkdir -p /etc/apt/keyrings && \
curl -fsSL https://packages.cloud.google.com/apt/doc/apt-key.gpg | gpg --dearmor -o /etc/apt/keyrings/cloud.google.gpg && \
echo "deb [signed-by=/etc/apt/keyrings/cloud.google.gpg] http://packages.cloud.google.com/apt cloud-sdk main" > /etc/apt/sources.list.d/google-cloud-sdk.list && \
apt-get update && \
apt-get install -y google-cloud-sdk && \
rm -rf /var/lib/apt/lists/*
WORKDIR /root/opt/
COPY ./app /root/opt/
CMD ["duckdb"]
Dockerfile for MySQL
FROM mysql:8.0
# Set health check for MySQL container
HEALTHCHECK --interval=30s --timeout=10s --retries=3 CMD mysqladmin ping -h localhost -u root -p${MYSQL_ROOT_PASSWORD}
compose.yml
services:
mysql:
build:
context: .
dockerfile: ./docker/mysql/Dockerfile
container_name: mysql
restart: always
environment:
MYSQL_ROOT_PASSWORD: password
MYSQL_DATABASE: sample_db
MYSQL_USER: user
MYSQL_PASSWORD: password
ports:
- "3306:3306"
volumes:
- mysql_data:/var/lib/mysql
- ./init:/docker-entrypoint-initdb.d
duckdb:
build:
context: .
dockerfile: ./docker/duckdb/Dockerfile
tty: true
environment:
MYSQL_HOST: mysql
MYSQL_DATABASE: sample_db
MYSQL_USER: root
MYSQL_PWD: password
MYSQL_TCP_PORT: 3306
volumes:
- ./app:/root/opt/
volumes:
mysql_data:
init
CREATE DATABASE IF NOT EXISTS sample_db;
USE sample_db;
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
description TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO users (username, email) VALUES
('user1', '[email protected]'),
('user2', '[email protected]'),
('user3', '[email protected]');
INSERT INTO products (name, price, description) VALUES
('Product 1', 19.99, 'This is product 1'),
('Product 2', 29.99, 'This is product 2'),
('Product 3', 39.99, 'This is product 3');
SELECT 'Sample data initialization completed successfully!' AS message;
shell
$ docker compose up -d --build
$ docker compose exec duckdb bash
$ duckdb < read_users_csv.sql
IO Error:
Failed to connect to MySQL database with parameters "host=""": Can't connect to MySQL server on '127.0.0.1:3306' (111)
Catalog Error:
Table with name users does not exist!
Did you mean "main.sqlite_master or memory.sqlite_master"?
LINE 2: SELECT * FROM mysqldb.users
^
OS:
debian
MySQL Version:
8.0
DuckDB Version:
v1.2.1 8e52ec4395
DuckDB Client:
cli
Full Name:
Shunsuke Tsuchiya
Affiliation:
Findy .inc
Have you tried this on the latest main branch?
- [x] I agree
Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?
- [x] I agree
I believe this was fixed by #120, MYSQL_HOST works correctly for me now. Closing as completed, please comment if it needs to be reopened.
