MySQL.jl Documentation

MySQL.Connection

A MySQL connection. Obtain one with DBInterface.connect(MySQL.Connection, host, user, password; kw...); see MySQL.ConnectOptions for the accepted keywords (removed 1.x ones explain why they fail). Operations are serialized by the connection lock. The first task to consume a streaming cursor owns it; a transaction is owned by the task that starts it.

MySQL.Statement

A prepared statement on the native backend, from DBInterface.prepare(conn, sql). Execute it with DBInterface.execute(stmt, params); close it with DBInterface.close!(stmt) (the COMSTMTCLOSE is deferred to the next command, never sent from a finalizer).

MySQL.TextCursor{buffered}
MySQL.BinaryCursor{buffered}

The cursor returned by DBInterface.execute: TextCursor for execute(conn, sql) (text protocol), BinaryCursor for execute(stmt, params) (binary protocol). It iterates rows and satisfies the Tables.jl row interface. buffered=true (mysql_store_result=true, the default) reads the whole result set at execute time under max_buffered_bytes; buffered=false streams rows on each iterate and ties up the connection until exhausted. A row is valid only while it is the cursor's current row; the values taken from it (including DataString/DataBytes views) stay valid.

ConnectOptions

Validated, fully resolved connection parameters (see ConnectOptions(host, user, password; kw...)).

DBInterface.connect(MySQL.Connection, host, user, passwd=nothing; db=nothing, port=nothing, kw...)

Connects to a MySQL server. Keywords are the 1.x connection options plus ssl_mode=:preferred, get_server_public_key, tls_version, zero_dates, time_type, local_infile_handler, max_buffered_bytes, …; see MySQL.ConnectOptions. An omitted db/port falls back to the option files' database/port (when option files are read), like host/user/password.

DBInterface.close!(conn)

Sends COM_QUIT (best effort) and closes the transport. Idempotent; unfinished streaming cursors become invalid. Buffered cursors retain their own bytes and remain readable.

DBInterface.close!(c::MySQL.Cursor)

Closes c and invalidates its current row. Further iteration yields no rows, for both buffered and streaming cursors. Repeated calls have no effect.

If c still owns a pending response, discards the remaining rows and result sets for its command.

DBInterface.close!(stmt::MySQL.Statement)

Closes the prepared statement. The COMSTMTCLOSE is parked and sent before the next command (never from a finalizer). Idempotent.

DBInterface.execute(conn::MySQL.Connection, sql; mysql_store_result=true) -> TextCursor

Runs sql with the text protocol and returns a cursor over the first result. With mysql_store_result=false rows are streamed (the connection is busy until the cursor is exhausted or closed). Further results of a multi-statement or CALL response are discarded by the next operation; use DBInterface.executemultiple to consume them. Passing params prepares, executes and returns a binary-protocol cursor bound to a one-shot statement.

DBInterface.execute(stmt::MySQL.Statement, params=(); mysql_store_result=true) -> BinaryCursor

Executes the prepared statement with params bound as the ? markers and returns a binary-protocol cursor. A tuple, named tuple, vector, or Tables.AbstractRow supplies values in iteration order; names do not select SQL parameters. A bare scalar binds one parameter. Wrap a binary byte vector as (bytes,) to bind it as one value. Named SQL markers such as :name are not supported. mysql_store_result=false streams rows (the connection is busy until the cursor is exhausted or closed).

DBInterface.executemultiple(stmt::MySQL.Statement, params=(); kw...) -> Cursors

Iterates every result set of a prepared CALL (or multi-result statement) as a distinct binary cursor, like the connection-level executemultiple.

DBInterface.prepare(conn::MySQL.Connection, sql) -> Statement

Prepares sql on the server and returns a Statement.

DBInterface.transaction(f, conn)

Runs f() inside START TRANSACTION / COMMIT (or ROLLBACK when f throws) and returns f()'s value. The connection lock is held for the whole callback: other tasks block until the transaction ends, so f must not wait on tasks that need this connection.

DBInterface.lastrowid(c::MySQL.Cursor)

The last_insert_id the server reported in this cursor's own OK packet (the DML result, or the result-set terminator), not the connection's current state.

MySQL.ping(conn) -> Bool

COM_PING round trip; throws when the connection is unusable.

MySQL.connection_id(conn) -> Int

The server-side id of this connection (what CONNECTION_ID() returns), e.g. to cancel a long-running statement with KILL QUERY <id> from another connection.

MySQL.server_version(conn) -> VersionNumber

The server version announced in the greeting (a MariaDB 5.5.5- prefix is stripped), for gating on server features; MySQL.server_kind(conn) tells :mysql from :mariadb.

MySQL.server_kind(conn) -> Symbol

:mysql, :mariadb, :tidb, or :vitess, detected from the greeting.

MySQL.escape(conn, str) -> String

Escapes str for use inside a single-quoted SQL literal on this connection's character set (utf8mb4): \, ', ", NUL, newline, carriage return and Control-Z are backslash-escaped; under the session's NO_BACKSLASH_ESCAPES mode only ' is doubled.

MySQL.escape_identifier(name) -> String

Backtick-quotes an identifier, doubling embedded backticks.

MySQL.send_long_data!(stmt, parameter_number, data)

Sends one copied string or byte chunk for the zero-based prepared-statement parameter number. Repeated calls append chunks. The next execute omits that parameter's inline value and retains the copied chunks until its first response, so a 1615 or reconnect re-prepare can replay them.

MySQL.reset_statement!(stmt)

Resets a prepared statement's accumulated long data. The statement id and cached parameter signature remain valid when the session generation did not change.

MySQL.load(table, conn, name; append=true, quoteidentifiers=true, limit=typemax(Int64), batchsize=1000, createtableclause=nothing, coltypes=Dict(), columnsuffix=Dict(), auto_increment_primary_key_name=nothing, debug=false)
table |> MySQL.load(conn, name; kw...)

Loads a Tables.jl source table into the table name of the database conn is connected to, and returns the (quoted) table name.

It first detects the Tables.Schema of the table source and generates a CREATE TABLE statement with the appropriate column names and types. If no table name is provided, one will be autogenerated, like mysql_xxxxx. The CREATE TABLE clause can be provided manually by passing the createtableclause keyword argument (default "CREATE TABLE IF NOT EXISTS" with append=true, else "CREATE TABLE"), which would allow specifying a temporary table. With append=false the existing rows are deleted before the new ones are inserted. Column types can be overridden by providing the coltypes keyword argument as a Dict of column name (given as a Symbol) to a string of the SQL type. This allows, for example, using a LONGBLOB instead of BLOB for large binary data by doing coltypes=Dict(:Photo => "LONGBLOB"). Column definitions can also be enhanced by providing arguments to columnsuffix as a Dict of column name (given as a Symbol) to a string of the enhancement that will come after name and type like [column name] [column type] enhancements. This allows, for example, specifying the charset of a string column by doing something like columnsuffix=Dict(:Name => "CHARACTER SET utf8mb4"). auto_increment_primary_key_name adds an INT AUTO_INCREMENT PRIMARY KEY column of that name in front of the source columns.

Rows are inserted inside one transaction with prepared multi-row INSERT statements of up to batchsize rows each (fewer when a batch would approach the packet limit, and at most 65535 bound values per statement, further bounded by max_columns); limit stops after that many rows. The packet budget uses the client's max_allowed_packet option; set it no higher than the server's value. Byte vectors are copied before advancing the source.

debug=true logs the generated statements without row values; debug=:values also logs each inserted row's values.

Do note that databases vary wildly in requirements for CREATE TABLE and column definitions so it can be extremely difficult to load data generically. You may just need to tweak some of the provided keyword arguments, but you may also need to execute the CREATE TABLE and INSERT statements yourself. If you run into issues, you can open an issue and we can see if there's something we can do to make it easier to use this function.

MySQL.juliatype(field_type, notnullable, isunsigned, isbinary, decimals=6) -> Type

The Julia type a result column decodes to, given its wire type, flags, and fractional precision: unsigned integer widening, binary BLOB (DataBytes) vs text (DataString), DATETIME/TIMESTAMP as Timestamp{P} per decimals (see MySQL.timestamp_type), exact DataDecimals values for DECIMAL, and Union{Missing, T} for nullable columns.

juliatype(def::Protocol.ColumnDef, opts::ResultOptions) -> Type

The column's Julia type: MySQL.juliatype applied to the wire type, flags, and fractional precision (decimals), then the decoding policies: time_type, and zero_dates=:missing widening every date column to Union{Missing, T} regardless of NOT NULL.

MySQL.Bit

The value of a BIT(n) column (n ≤ 64): the big-endian value of all bytes the server sent, stored in bits::UInt64. (MySQL.API.Bit before 2.0.)

MySQL.DecimalResult

The type DECIMAL/NUMERIC columns decode to: DataDecimals.DecimalValue{DataDecimals.Int256}, which holds all 65 digits and the column's scale exactly.

MySQL.timestamp_type(decimals) -> Type{<:Timestamp}

The Timestamp{P} a DATETIME/TIMESTAMP column with decimals fractional-second digits (its fsp, 0–6) decodes to: Timestamp{Second} for 0, Timestamp{Millisecond} for 1–3, Timestamp{Microsecond} for 4–6. Every MySQL value is represented exactly.

MySQLError

Root of the native backend's exception hierarchy.

• ServerError (Error, StmtError): a server ERR or a client connection-loss error
• ProtocolError: invalid protocol state, bytes, or a resource limit; wire faults close the connection
• AuthError / UnsupportedAuthError: authentication policy or plugin problems
• TimeoutError: a deadline expired
• ConversionError: a wire value cannot be represented by the requested Julia type
• TLSNegotiationError: the TLS handshake failed while establishing the connection
• LocalInfileRefused: the LOCAL INFILE handler declined a server request

Error(errno, msg, sqlstate="")

Server ERR or client connection-loss error. errno::Cuint and msg keep the field names and types of the Connector/C-backed MySQL.API.Error; sqlstate is new.

StmtError(errno, msg, sqlstate="")

Server ERR packet raised by prepared-statement operations (distinct type on purpose so 1.x-style @test_throws MySQL.StmtError dispatch keeps working; the 1.x name was MySQL.API.StmtError).

MySQL.Protocol

Native implementation of the MySQL client/server wire protocol on top of Reseau transports: constants generated from the server headers, bounded codecs, packet framing with reassembly, the phase machine, handshake and capability negotiation, authentication plugins (mysql_native_password, caching_sha2_password, sha256_password, mysql_clear_password) with OpenSSL-backed RSA-OAEP, STARTTLS, generic responses, column definitions, text and binary row scanning, and the command/response framing of COMQUERY, the COMSTMT_* family, LOCAL INFILE and the simple commands. It has no DBInterface/Tables dependency; the MySQL driver layer (value decoding, connections, cursors, statements) builds on it. See docs/protocol-notes.md.