The Ultimate Guide to High Availability with MariaDB

MariaDB protocol difference with MySQL

You are viewing an old version of this article. View the current version here.

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.

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:

  • int<lenenc> column count
  • if (MARIADB_CLIENT_CACHE_METADATA capability set) int<1> metadata follows (0 / 1)

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.

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

Connector can check if charset of initial 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 assocate capabilities.

  • CLIENT_OPTIONAL_RESULTSET_METADATA: permit to set no METADATA at all for a connection. See [[#prepare-statement-skipping-metadata|Prepare statement skipping metadata]'s mariadb implementation choice
  • CLIENT_QUERY_ATTRIBUTES add 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 versions starting from 10.2 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 ...

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.