MySQL and MariaDB connections

Part of aner.db, the native database module. The module reference covers shared credentials, connection lifetime, result conversion and all four connectors.

aner.db.mysql connects directly with MariaDB Connector/C 3.4 or newer and the native MySQL protocol. Native prepared statements bind values separately, reuse a TCP session and read typed results into bounded Aner storage. See the shared database API and Credentials.

Requirements and private setup

Use a compatible installed Aner executable with optional MySQL support and the MariaDB Connector/C runtime and authentication plugins needed by your server. db.available("mysql") reports support in the executable; server and authentication compatibility are checked when connecting.

aner secrets set example-mysql-password --store system
aner secrets status example-mysql-password --store system

The hidden prompt stores the value separately from source. The program uses secrets.stored("example-mysql-password"). Headless environments can inject ANER_MYSQL_PASSWORD privately and use secrets.env("ANER_MYSQL_PASSWORD"). Linux system storage is unavailable; the explicit POSIX file fallback pairs --store file with secrets.file, and stores plaintext with owner only permissions. Read the Credentials guide before choosing a store.

Connect and bind a query

All connection settings below are fictional. Replace them privately for your server:

import aner.db;
import aner.db.mysql;
import aner.secrets;
import aner.data;

let connection: DBConnection = mysql.connect(
    host: "mysql.example.invalid", port: 3306, database: "example_science",
    user: "example_reader", password: secrets.stored("example-mysql-password"),
    sslmode: "verify-full", timeout_seconds: 10);
let result: DBResult = db.query(connection,
    "SELECT sample_id, feature_a, feature_b, feature_c, label FROM aner_demo_samples WHERE label = ? ORDER BY sample_id",
    List("2"), max_rows: 1000, max_bytes: 1048576);
print(db.rows(result));
if db.rows(result) > 0 {
    let table: DataTable = db.to_table(result, 1048576);
    print(data.integer(table, 0, "label"));
}
db.close(connection);

mysql.connect and db.mysql.connect require import aner.db.mysql. The password must be a SecretRef. The default port example is 3306; accepted ports are 1 through 65535. The connection timeout defaults to 10 seconds and accepts 1 through 300. It sets native connect, read and write timeouts rather than a server query cancellation deadline. TCP is explicit, including for localhost.

TLS and trusted certificates

For a private CA, set ANER_DB_MYSQL_CA privately to an absolute PEM CA bundle path. Otherwise the client uses its native trust store. Verified mode does not accept the client's password based trust fallback. Encrypted modes reject a server without TLS before authentication, and the connector checks that the returned session is encrypted. There is no automatic downgrade.

SQL values, results and transactions

MySQL uses positional ? markers. Supply a List of String or null entries in marker order; counts must match. Use SQL context or an explicit cast such as CAST(? AS SIGNED) to select the server type. Identifiers cannot be bound values. Multi statement SQL and multiple result sets are unsupported.

let started = db.execute(connection, "START TRANSACTION");
let updated = db.execute(connection,
    "UPDATE aner_demo_samples SET label = ? WHERE sample_id = ?", List("3", "7"));
let rolled_back = db.execute(connection, "ROLLBACK");

Run this fragment before closing an active connection. Use COMMIT when a change should persist. Transactions depend on the storage engine; the supplied fixture uses InnoDB. DDL can commit implicitly. db.execute returns changed rows using the client's default semantics, so an update retaining the same value can return zero. Automatic reconnect and local file loading are disabled.

Wider BIT, binary and geometry columns reject. Convert them explicitly in SQL, for example HEX(binary_column). Exact decimal strings require an explicit numeric precision choice before tensor conversion.

db.query defaults to 100000 rows and 67108864 retained bytes. Overflow returns no partial result. These limits exclude native session, metadata and packet buffers. db.to_table has its own memory budget. Select complete numeric features for data.to_tensor. Native diagnostics omit server messages, SQL and parameter values; they report fixed explanations and native numeric codes. Connection aliases share close behavior, and completed results survive close.

Four class demonstration

Run db_mock_mysql.sql once in the selected development database using your administration tool. It creates class and sample tables with 400 synthetic rows, three numeric attributes and labels 0 through 3. Repeated setup preserves existing rows and adds missing IDs. The complete Aner example binds a label filter and converts numeric features into a tensor.

aner check examples/db_mysql.aner
aner run examples/db_mysql.aner

Checking opens no connection. Running uses the private settings and stored credential. The fixture is demonstration data. Local native validation does not establish compatibility with every live server or authentication plugin.

Native reference: MariaDB Connector/C options.