The MySQL engine needs a real MySQL (MariaDB is covered)

[WaylandYang]

The mysql engine (#303, PR #315) has never talked to a real server. The machine it was written on runs a Docker daemon whose internal proxy cannot reach the registry, so no MySQL image could be started there.

The type table — the part that fails silently, by turning a column into nulls — was checked against the driver's own ColumnType::name and per-type compatible predicates rather than against a live server, and that found two real defects before merge (unsigned integers carry a UNSIGNED suffix in the reported type name; DECIMAL is excluded from both the f64 and String decoders). So the risk that remains is not the mapping but the shape of three statements:

• the information_schema.columns query in fetch_schema (column names differ from Postgres: column_type, column_comment)
• SET SESSION TRANSACTION READ ONLY, and the two timeout spellings — max_execution_time on MySQL, max_statement_time on MariaDB. The MariaDB branch is reached only when the MySQL form fails, so it has never executed
• a real round trip: mount a database, sync the schema, ask a question in Chat, and compare the answer against the same query run directly

Worth covering MySQL and MariaDB separately, since the timeout branch is the one difference between them that this engine cares about. TiDB, OceanBase, Doris or StarRocks would each be a bonus — they speak the same protocol, but nobody has confirmed that their information_schema answers the same way.

Same standing as #240, #241 and #242 for the HTTP engines: it needs someone with the thing running, not more code.

[WaylandYang]

Most of what this issue originally asked for is now done. PR #315 ran the mysql engine against a real MariaDB 11.4 and left the check behind as a test: query_engine::mysql::tests::a_live_server_answers_with_typed_values, guarded by UTOPIA_TEST_MYSQL_URL so it skips where no server is running.

To run it:

docker run -d --name mysql-verify -e MARIADB_ROOT_PASSWORD=pw -e MARIADB_DATABASE=sales -p 13306:3306 mariadb:11.4

then create the table it expects:

CREATE TABLE sales.orders (
  id INT PRIMARY KEY, region VARCHAR(32) COMMENT 'Sales region',
  amount DECIMAL(12,2) COMMENT 'Order total in CNY', discount DOUBLE,
  qty BIGINT UNSIGNED, flag TINYINT(1), placed_on DATE, window_open TIME,
  created_at DATETIME, synced_at TIMESTAMP NULL, meta JSON, blob_col BLOB);
INSERT INTO sales.orders VALUES
 (1,'east',1234.56,0.15,18446744073709551615,1,'2023-06-01','09:30:00',
  '2023-06-01 09:30:00','2023-06-01 09:30:00','{"channel":"web"}',UNHEX('1f8b0800')),
 (2,'west',890.10,0.05,42,0,'2024-07-15','14:00:00','2024-07-15 14:00:00',
  '2024-07-15 14:00:00','{"channel":"store"}',NULL),
 (3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);

and UTOPIA_TEST_MYSQL_URL=mysql://root:pw@127.0.0.1:13306/sales cargo test -p utopia-server query_engine::mysql.

Confirmed on MariaDB: the information_schema query (types at full precision, comments attached, empty comments filtered), value round trips for every type in the table, and the MariaDB timeout branch — MariaDB rejects max_execution_time with ERROR 1193, so the max_statement_time fallback is what carried the query.

What is still open:

• MySQL proper. Two arms only it takes: max_execution_time (MariaDB has no such variable, so that branch has never run) and the native JSON column type. MariaDB aliases JSON to LONGTEXT, so Cell::Json went untaken — on MySQL a JSON column should come back as a nested object rather than a JSON string.
• The protocol-compatible engines: TiDB, OceanBase, Doris, StarRocks. They speak the same wire protocol, but nobody has confirmed their information_schema answers the same shape.

Anyone with one of these running can settle it with the same one-line test.

[WaylandYang]

Tested against a real MySQL 8.4.11 and MariaDB 11.4.13 (containers, reached over an SSH tunnel), running the gated live test a_live_server_answers_with_typed_values against each.

It found a real bug. fetch_schema could not read information_schema.columns on either server:

error occurred while decoding column 0: mismatched types;
Rust type `alloc::string::String` (as SQL type `VARCHAR`) is not compatible with SQL type `VARBINARY`

Since MySQL 8.0, information_schema is views over the data dictionary and its string columns arrive as VARBINARY through the binary protocol; sqlx's strict decode rejects them into a String. The raw query looks correct from the CLI — the CLI does no typed decode — which is exactly why only a live driver round trip catches it. Fixed in #323 by wrapping each column in CAST(... AS CHAR). Both live tests pass after the fix.

The three statements this issue flagged, confirmed:

• information_schema.columns — the column names (column_type, column_comment) are right; column_type carries precision (decimal(12,2), bigint unsigned), and the no-comment case is an empty string, which the engine already filters to None. The only defect was the binary-type decode above.

• the two timeout spellings — confirmed opposite on the two servers, so the fallback branch is real and now exercised:

  	max_execution_time	max_statement_time
  MySQL 8.4	accepted	ERROR 1193 Unknown system variable
  MariaDB 11.4	ERROR 1193 Unknown system variable	accepted

  On MariaDB the engine tries max_execution_time first, gets 1193, and falls through to max_statement_time — the branch that had never executed.

• a real round trip — the value decode returns typed values on both: BIGINT UNSIGNED at the u64 ceiling (18446744073709551615), DECIMAL as 1234.56, TINYINT(1) as true, DATE as 2023-06-01, and an all-NULL row as JSON nulls rather than zero-values. SET SESSION TRANSACTION READ ONLY is accepted on both, and the write guard still rejects DELETE.

MySQL and MariaDB are both covered now, including the one branch that differs between them. Closing; TiDB/OceanBase/Doris/StarRocks remain a separate "bonus" not blocked on this.

[WaylandYang]

Real MySQL and MariaDB testing is done (evidence above); the one bug it turned up is fixed in #323. Closing this — the remaining TiDB/OceanBase/Doris/StarRocks coverage is a separate bonus, not this issue.