MySQL and MariaDB
The MySQL adapter uses MariaDB Connector/C (libmariadb.so.3), which talks to MySQL and MariaDB servers. It is tested with MariaDB 11 and MySQL 8.4. libmysqlclient cannot take its place.
import Db from "pkg/db/db"
import Mysql from "pkg/db/mysql"
def run() -> Result[Void]
conn = try Db.open("mysql://user:pass@127.0.0.1:3306/app?connect_timeout=5", [Mysql.driver()])
rows = try Db.query(conn, "select id, name from users where age > ?", [Db.Value.Int(28)])
puts(rows.size)
Db.close(conn)
return ok()
end
def main() -> Void
case run()
ok(_) -> puts("done")
error(message) -> IO.warn(message)
end
endThe url #
The url is mysql://user:password@host:port/database?parameter=value, and mariadb:// is the same.
- The user, the password and the database are percent-decoded: write
p%40ssforp@ss. - The host is required, the port is 3306 by default, an IPv6 host is
[::1], and the database is optional. - Use an address. The host
localhostmakes the client library use the local socket, which is not where a container listens. - The parameters are
connect_timeout(seconds; set it, since an unreachable host otherwise waits as long as the operating system does),timeout_ms(the limit of each statement, see timeouts),ssl_modeandssl_ca(see TLS), and for the native driver (the FFI one ignores them),statement_cache(prepared statements kept per connection, 0 to 10000, default100),batch_rows(rows that one call gives, 1 to 1000, default100) andbatch_bytes(bytes of values that one call gives, about, 1 to 262144, default262144). Any other parameter is an error, and so is a number out of its range.
A bad url is mysql error: <message>, and the message never repeats a part of the url. A server that does not accept the connection is mysql error <code>: <message>, with the code of the client library or the server (2002 unreachable, 1045 access denied, 1049 unknown database).
Timeouts #
timeout_ms (a url parameter, or the timeout_ms argument of Db.query and Db.execute: -1 takes the connection’s, 0 has none) is set on the server, and the adapter reads at open which server it is. MySQL gets max_execution_time, which limits only a SELECT: it fails with mysql error 3024. A DO or an INSERT is not bounded on MySQL. An interrupted SLEEP() ends early without an error: select sleep(5) on MySQL 8.4, DO SLEEP(20) on MariaDB. MariaDB gets max_statement_time, which limits every statement: mysql error 1969. Db.timeout?(e.message) recognizes both, and the connection works after them. A call with its own limit sets the variable before the statement and puts the connection’s back after it (two more round trips, none when the call asks for the limit the connection has; the server’s default comes back when the url has no timeout_ms). Without timeout_ms nothing is bounded.
A url with timeout_ms also sets the client read and write timeouts, in whole seconds, to the limit plus 10 seconds, as a backstop for what the server does not time (a DO or an INSERT on MySQL, a lost network). A statement that hits it fails with mysql error 2013: Lost connection to server during query and the connection is gone (Db.healthy?(conn) is false). The backstop is fixed at open, so a call that asks for a limit longer than the url’s, 0 included, is cut at the backstop: open with the longer timeout_ms for it.
Db.in_transaction?(conn) reads the status flag of the last reply from the server, and Db.healthy?(conn) asks the server (a ping on the FFI driver, SELECT 1 on the native one).
TLS #
?ssl_mode=requireduses TLS and accepts any certificate. It hides the traffic, but it does not prove the server.?ssl_mode=verify&ssl_ca=PATHuses TLS and checks the certificate against the PEM file of authorities atPATH.verifywithoutssl_ca, andssl_cawithoutverify, are errors.- Without
ssl_modethe library decides, and Connector/C 3.4 connected without TLS. Useverifyover any network you do not trust. - A client certificate is not supported.
Types #
| MySQL | Db.Value |
|---|---|
tinyint, smallint, mediumint, int, bigint, year, and the unsigned ones that fit an Int | Int |
float, double | Float (no NaN or infinity; a MariaDB server gives a -0.0 back as 0.0, MySQL keeps it) |
boolean (a tinyint(1)) | Int |
binary, varbinary, the blobs, bit, geometry | Blob |
| NULL | Null |
char, varchar, the texts, json, enum, set | Text |
decimal, date, time, datetime, timestamp, a bigint unsigned past Int | Text |
A Text parameter goes into a column of any of those types. A datetime reads as 2024-02-29 13:14:15. The connection character set is utf8mb4.
Statements #
- Every statement with parameters is prepared and its values are bound. The sql must be one statement; the scanner refuses a second one before the server sees it.
- MySQL does not prepare some statements (
START TRANSACTION,BEGIN,SAVEPOINT,USE,LOCK TABLES). When the server answers1295to a statement with no placeholder, the adapter sends it as text. A statement with parameters never does. executecounts the rows ofINSERT,UPDATE,DELETEandREPLACEthat match. AnINSERT ... ON DUPLICATE KEY UPDATEor aREPLACEover an existing row counts 2. Other statements give 0.CALLgives its first result set. ACREATE PROCEDUREwith a body of several statements cannot be sent: create it with themysqlclient.LOAD DATA LOCAL INFILEis off, so a server cannot ask for a file of this machine.
Drivers #
Two drivers run the statements, with the same values and errors:
- The native driver is a small C module of the package (
src/db/native/db_mysql.c) that opens the systemlibmariadb.so.3itself. It runs a query through the non-blocking calls of Connector/C: the query sends its statement, parks its task on the socket until the server answers, and reads the rows in short calls ofbatch_rowsrows orbatch_bytesbytes, so it takes no compute call and other tasks run meanwhile. Each connection keeps up tostatement_cacheprepared statements, keyed by the sql text: aSELECT,INSERT,UPDATE,DELETE,REPLACE,WITH,VALUESorTABLEthat ran before runs in one round trip; its first run takes two (a prepare, then the run). When the cache is full the statement used least long ago goes, and it is closed on the server with the next statement (a close has no reply, so it costs no wait). ACREATE,ALTER,DROP,RENAMEorTRUNCATElets the whole cache go. When another connection changes a table of a cached statement, the server prepares it again by itself. When the change adds or drops columns of its result, the server has already run the statement when the library sees it (error 2057): a cached statement that does not write is prepared again and runs once more, so what it does besides reading (aNEXTVAL, a stored function that writes) runs twice; a write, and a statement that was not cached, givemysql error 2057instead. A write that gives rows (INSERT ... RETURNING) is not cached. - The FFI driver calls Connector/C through
externfunctions: it prepares each statement again, waits for the server on the compute pool, and reads the result one value per call.
open takes the native driver on Linux when its library loads (it opens libmariadb.so.3), else the FFI one. Agentica builds the library with the system C compiler like the SQLite one (no client headers are needed); without a compiler it warns and the program runs the FFI driver. The non-blocking calls of Connector/C run on a stack of their own, so the module runs them under an_blocking of Agentica’s native API, which keeps the garbage collector off that stack. AGENTICA_DB_MYSQL_DRIVER=native makes a driver that does not run here an error of open, and AGENTICA_DB_MYSQL_DRIVER=ffi always runs the FFI driver.
The native driver is tested with Debian’s and Ubuntu’s libmariadb3; it relies on Connector/C resuming a suspended call on another thread, which other builds of the library may not do, so on another build set AGENTICA_DB_MYSQL_DRIVER=ffi if queries misbehave.
A prepared statement belongs to the server session: behind a proxy that hands each transaction another session, set statement_cache=0, and the native driver prepares each statement again, as the FFI one does.
Transactions #
A transaction starts with START TRANSACTION. They do not nest: BEGIN and START TRANSACTION fail with mysql error: a transaction is already in progress while the server reports one open. See transactions.
Connections #
The end of the task that opened the connection closes it, or call Db.close earlier. mysql_close talks to the server and blocks the scheduler while it runs, so open the connection in the task that keeps it. A connection used after close is mysql error: the connection is closed.
Other tasks keep running while the server works: open runs on the compute pool, a query of the native driver waits on the socket, and one of the FFI driver runs on the compute pool. Use a connection from one task at a time, and never close it while a query runs. With the native driver, queries of several tasks on one connection run one after the other, and a task cancelled while its query runs leaves it on the connection: the next query sends KILL QUERY for it on a connection of its own (which gives up after 5 seconds to connect, and 15 for the answer), only when that connection reaches the same server, not restarted since (as for a pool’s lease_ms), and reads its end first. A killed statement does not end an open transaction. With the FFI driver, the call of a cancelled task still runs to its end and its result is dropped: close that connection and open a new one.