Insert/Update Operations Fail Due to Missing Index Information Retrieval in MySQL Table

[ay27]

I encountered an issue where attaching a MySQL instance works normally, and all read queries execute correctly. However, any insert or update operations consistently result in the following error:

_duckdb.BinderException: Binder Error: There are no UNIQUE/PRIMARY KEY constraints that refer to this table, specify ON CONFLICT columns manually

Following the guidance, I attempted to include an ON CONFLICT statement, for example:
INSERT INTO mysql.xxx ... ON CONFLICT (id) DO NOTHING;
This, in turn, triggers another error:
_duckdb.BinderException: Binder Error: The specified columns as conflict target are not referenced by a UNIQUE/PRIMARY KEY CONSTRAINT or INDEX

Upon further investigation, I noticed that in the code, when retrieving MySQL index information, the plugin currently returns an empty list by default:

duckdb-mysql/src/mysql_connection.cpp

Line 163 in 0971e91

vector<IndexInfo> MySQLConnection::GetIndexInfo(const string &table_name) {

I would like to inquire: Is this behavior an intentional design choice, or is there a specific reason for implementing it this way?
If this function were fixed to correctly return the index information for the table, would it allow insert/update operations to proceed normally?

[staticlibs]

@ay27

Thanks for the report! Can you share an example of a MySQL table where this can happen?

In general inserts and updates can be more reliable when using mysql_execute() and passing the input values as parameters:

CALL mysql_execute('mysqldb', 'CREATE TABLE tab1(col1 INTEGER, col2 DOUBLE, col3 DATETIME)');

CALL mysql_execute('mysqldb', 'INSERT INTO tab1 VALUES (?, ?, ?)', params=row(42, 42.123::DOUBLE, '2020-12-31 12:34:45'));

Parameters can also be passed from outside (from client language) like this:

CALL mysql_execute('mysqldb', 'INSERT INTO tab1 VALUES (?, ?, ?)', params=row(?, ?::DOUBLE, ?));

[ay27]

Sorry for the late reply. I've tested multiple MySQL tables, and they all failed.

While the mysql_execute function you mentioned does work, I would prefer to use the Relational API here instead of using values directly.

I am targeting MySQL 8.0. Here is the schema for one of the failing cases:

CREATE TABLE `task_table` (
  `id` bigint NOT NULL,
  `project_name` varchar(64) NOT NULL,
  `....`,
  `err_code` int DEFAULT '0',
  `err_msg` text,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `__sys_update_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci

[staticlibs]

@ay27

Thanks for the details! Perhaps I am missing something here, but the following insert works for me:

ATTACH 'host=localhost user=root port=3306 database=test_db' AS mysqldb (TYPE MYSQL_SCANNER);

INSERT INTO mysqldb.task_table VALUES(42, 'foo', 43, 'bar', '2020-02-14 08:47:23', '2021-02-14 08:47:23');

[ay27]

My apologies! I missed a crucial detail here. We need to execute an INSERT OR IGNORE INTO statement to reproduce the error:

INSERT OR IGNORE INTO mysqldb.task_table VALUES(42, 'foo', 43, 'bar', '2020-02-14 08:47:23', '2021-02-14 08:47:23');