MariaDB protocol difference with MySQL

Here is a list of the difference between MariaDB and MySQL in term of protocol, in order help community drivers maintainers

MariaDB Capabilities Extension

MariaDB/MySQL servers can advertise feature support using capabilities. To expand the capabilities beyond the original 4 bytes, MariaDB utilizes 4 bytes, unused by MySQL, in the Initial handshake packet (server capabilities 3rd part). In order to avoid incompatibility in the future, those 4 bytes have to be read only if capability CLIENT_MYSQL is not SET (server being then MariaDB).

Enhanced Capabilities

• MARIADB_CLIENT_CACHE_METADATA: Enables clients to cache metadata and avoid repeated network transmissions.
• MARIADB_CLIENT_EXTENDED_TYPE_INFO: Provides more detailed column metadata information for specific data types.
• MARIADB_CLIENT_STMT_BULK_OPERATIONS: Introduces a dedicated command, COM_STMT_BULK_EXECUTE, for efficient batch execution of statements.
• MARIADB_CLIENT_BULK_UNIT_RESULTS: Allows for individual result sets for each bulk operation.

See Connection Capabilities.

Pipelining Prepare Execute

Connectors usually follow a two-step process for prepared statements:

• Prepare: Send a COM_STMT_PREPARE command to the server, receiving a statement ID in response.
• Execute: Send a COM_STMT_EXECUTE command, using the statement ID obtained in the previous step.

When the MARIADB_CLIENT_STMT_BULK_OPERATIONS capability is enabled, connectors can pipeline those 2 commands:

• Send a COM_STMT_PREPARE then a COM_STMT_EXECUTE with statement ID -1 commands to the server.
• Read the prepare and execute responses

If the COM_STMT_PREPARE command returns an error (ERR_Packet), the subsequent COM_STMT_EXECUTE with statement ID -1 will also fail and return an error.

By eliminating the round trip for the separate COM_STMT_EXECUTE command, this approach improve performance for the first execution.

Traditionnally, connector sent COM_STMT_PREPARE, wait for results, then execute COM_STMT_EXECUTE with statement_id received from prepare result.

This description has been done for COM_STMT_EXECUTE, but COM_STMT_BULK_EXECUTE works exactly the same way.

Prepare statement skipping metadata

Prepared statement metadata, which typically remains unchanged except during table alterations, can be cached by clients when the MARIADB_CLIENT_CACHE_METADATA capability is enabled, server won't then send them again, unless they changes. This significantly improves the performance of subsequent executions, especially for large metadata sets.

When MARIADB_CLIENT_CACHE_METADATA capability is set, Resultset Column count packet format indicate if metadata follows or are skipped:

Extended Column Information

When the MARIADB_CLIENT_EXTENDED_TYPE_INFO capability is set, column definition packet can include additional type and format information.

• For geometric fields: Detailed geometric data type (e.g., 'point', 'polygon')
• For JSON fields: Type 'json'
• For UUID fields: Type 'uuid'

Bulk

The MARIADB_CLIENT_STMT_BULK_OPERATIONS capability enables the COM_STMT_BULK_EXECUTE command for efficient batch processing. However, note that only one result (OK or ERROR) is returned per batch, containing the total affected rows and the first auto-generated ID. For individual results, the MARIADB_CLIENT_BULK_UNIT_RESULTS capability can be set, server will then return a resultset containing for each unitary results (containing auto generated ids and affected rows)

Authentication plugins

MariaDB has specifics authentication methods.

• ED25519 plugin
• PARSEC plugin
• GSSAPI plugin

Redirection

MariaDB permit connection redirection.

Use Cases:

• Proxy Scenarios: Connection redirection is particularly beneficial when multiple servers share a single proxy.
• Server Management: This feature can also be used during planned server shutdowns or restarts, allowing for a graceful transition to a new server.

Connector can supports 2 different levels:

• On Connection Creation only: The redirection information is included in the initial OK_Packet sent by the server to the client. This allows the client to connect directly to the target server immediately.
• Anytime Redirection: If redirection information becomes available later, the connector can handle it based on the existing transaction state.
  • No Transaction: If no transaction is in progress, the connector can redirect the connection directly.
  • Transaction in Progress: If a transaction is ongoing, the redirection information is stored until the transaction is completed. The transaction state is determined using server status flags like SERVER_STATUS_IN_TRANS in the "OK_Packet," "ERR_Packet," or "EOF_Packet."

No configuration SSL

MariaDB 11.4.1 introduces a feature permits TLS certificate validation without needs to provide certificate client side.

Benefits:

• Simplified Setup: No need to install and manage client certificates, making initial configuration much easier.

Limitations:

• Password Requirement: To ensure security, this approach requires users to connect with a password.
• Authentication Methods: Only mysql_native_password and client_ed25519 authentication methods are currently supported.

How it Works:

• Self-Signed Certificates: The server can utilize a self-signed certificate, which doesn't require a trusted Certificate Authority (CA).
• Fingerprint Storage: The client connector needs to handle self-signed certificates. It first saves the SHA256 fingerprint of the server certificate.
• Server Validation: Upon connection, the server sends a special hash within the "OK_Packet" info field.
• Local Validation: The client generates a hash by combining the password hash, server seed, and the stored certificate fingerprint.
• Comparison: If the client-generated hash matches the server-sent hash, the connection is deemed secure and proceeds. Otherwise, the connection must be terminated for security reasons.

Initial session tracking

MySQL and MariaDB support session tracking with CLIENT_SESSION_TRACK capability set. On difference is that since MariaDB 11.5.1, connection ending OK_Packet list all the current variables of tracked variable.

Collations

Connector doesn't care of collations, but normally wants to ensure charset in connection exchanges.

The only good solution is to use SET NAMES utf8mb4 or SET NAMES utf8mb4 COLLATE someUtf8mb4collation

If supporting session tracking, connector can check if charset of initial tracked variable 'character_set_connection' correspond to expected value, then permitting to skipping this SET NAMES command.

MySQL features not supported

• X protocol is not supported

Not supported features and associate capabilities:

• CLIENT_OPTIONAL_RESULTSET_METADATA: permit to set no METADATA at all for a connection. See Prepare statement skipping metadata's MariaDB implementation choice.
• CLIENT_QUERY_ATTRIBUTES adds some metadata attributes
• CLIENT_ZSTD_COMPRESSION_ALGORITHM permit to have a zstd compression
• MULTI_FACTOR_AUTHENTICATION Multifactor Authentication capability.

TIPS

Identifying MariaDB server.

MariaDB connectors use specific criteria to determine if a server is a MariaDB instance during the initial handshake process.

The two key indicators used are:

• Missing CLIENT_MYSQL Capability: MariaDB 10.2 and newer versions do not set the CLIENT_MYSQL capability flag in the initial handshake packet.
• Server Version String: The server's version string is examined for the presence of the word "mariadb" (ignoring case sensitivity).

The reason is some features like using COM_RESET_CONNECTION has no capability, and depends on MySQL or MariaDB server version.

Query timeout

Since MariaDB 10.1.2 (so all supported server have it), setting a timeout for all commands can be set using SET max_statement_time=XXX with XXX in seconds.

Setting it for a specific query can be done using SET STATEMENT max_statement_time=XXX FOR ...