MySQL Catalog fails on 2nd insert to a table

[ryanschneider]

I was curious how well the different catalog providers would handle "multi-tenant" so wrote up a very hacky script to create a bunch of databases, but came across this issue almost immediately with the MySQL catalog:

Rather than bore you with the hacky script, I've isolated the exact MySQL failure below:

# as a quick test, I ran mysql w/ an empty root password locally
# docker run --name mysql-test -e MYSQL_ALLOW_EMPTY_PASSWORD=1 -p 3306:3306 -d mysql:8.4
# in mysql, create a new database
CREATE DATABASE foo;

# now in duckdb
ATTACH 'ducklake:mysql:db=foo host=127.0.0.1 user=root' AS my_ducklake (DATA_PATH '/tmp/');
USE my_ducklake;
CREATE TABLE IF NOT EXISTS my_table AS (SELECT 'hello' as greeting) WITH NO DATA;
INSERT INTO my_table SELECT 'hello' as greeting;
CREATE TABLE IF NOT EXISTS my_table AS (SELECT 'hello' as greeting) WITH NO DATA;
INSERT INTO my_table SELECT 'good morning' as greeting;

TransactionContext Error:
Failed to commit: Failed to commit DuckLake transaction: Failed to update stats information in DuckLake: Unsupported operator type HASH_JOIN in UPDATE statement - only simple deletes (e.g. UPDATE FROM tbl WHERE x=y) are supported in the MySQL connector

The above steps all worked as expected in postgres, sqlite, and duckdb datalogs.

[Tishj]

That issue is more relevant here: https://github.com/duckdb/duckdb-mysql If it doesnt already exist there

[Mytherin]

Thanks for filing! This is indeed a limitation in the MySQL connector and should likely be fixed there.

[ryanschneider]

Thanks I created https://github.com/duckdb/duckdb-mysql/issues/130 with more details.