The Ultimate Guide to High Availability with MariaDB

MariaDB Protocol Differences with MySQL

Here is a list of the differences between MariaDB and MySQL in terms of protocol, in order to help community driver 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 then being MariaDB).

Enhanced Capabilities

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

See Connection Capabilities.

Prepare Statement Skipping Metadata

since MariaDB 10.6.0

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. The server won't then send them again, unless they change. This significantly improves the performance of subsequent executions, especially for large metadata sets.

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

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

java code:

stmt.execute("CREATE TABLE test_table (id int, val varchar(32))");
stmt.execute("INSERT INTO test_table VALUES (1, 'a'), (2, 'b')");
try (PreparedStatement prep = sharedConnBinary.prepareStatement("SELECT * FROM test_table WHERE id = ?")) {
    prep.setInt(1, 1);
    prep.executeQuery();
}

Results with metadata caching:

Column count packet:
       +--------------------------------------------------+
       |  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+------+--------------------------------------------------+------------------+
|000000| 02 00 00 01 02 00                                | ......           |
+------+--------------------------------------------------+------------------+
row:
       +--------------------------------------------------+
       |  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+------+--------------------------------------------------+------------------+
|000000| 08 00 00 02 00 00 01 00  00 00 01 61             | ...........a     |
+------+--------------------------------------------------+------------------+

OK_Packet with a 0xFE header:
       +--------------------------------------------------+
       |  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+------+--------------------------------------------------+------------------+
|000000| 07 00 00 03 FE 00 00 22  00 00 00                | ......."...      |
+------+--------------------------------------------------+------------------+

same without metadata caching:

Column count packet:
       +--------------------------------------------------+
       |  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+------+--------------------------------------------------+------------------+
|000000| 01 00 00 01 02                                   | .....            |
+------+--------------------------------------------------+------------------+

Column Definition packet:
       +--------------------------------------------------+
       |  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+------+--------------------------------------------------+------------------+
|000000| 33 00 00 02 03 64 65 66  05 74 65 73 74 6A 0A 74 | 3....def.testj.t |
|000010| 65 73 74 5F 74 61 62 6C  65 0A 74 65 73 74 5F 74 | est_table.test_t |
|000020| 61 62 6C 65 02 69 64 02  69 64 0C 3F 00 0B 00 00 | able.id.id.?.... |
|000030| 00 03 00 00 00 00 00                             | .......          |
+------+--------------------------------------------------+------------------+

Column Definition packet:
       +--------------------------------------------------+
       |  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+------+--------------------------------------------------+------------------+
|000000| 35 00 00 03 03 64 65 66  05 74 65 73 74 6A 0A 74 | 5....def.testj.t |
|000010| 65 73 74 5F 74 61 62 6C  65 0A 74 65 73 74 5F 74 | est_table.test_t |
|000020| 61 62 6C 65 03 76 61 6C  03 76 61 6C 0C FF 00 80 | able.val.val.... |
|000030| 00 00 00 FD 00 00 00 00  00                      | .........        |
+------+--------------------------------------------------+------------------+

row:
       +--------------------------------------------------+
       |  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+------+--------------------------------------------------+------------------+
|000000| 08 00 00 04 00 00 01 00  00 00 01 61             | ...........a     |
+------+--------------------------------------------------+------------------+

OK_Packet with a 0xFE header:
       +--------------------------------------------------+
       |  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+------+--------------------------------------------------+------------------+
|000000| 07 00 00 05 FE 00 00 22  00 00 00                | ......."...      |
+------+--------------------------------------------------+------------------+

Extended Column Information

since MariaDB 10.5.2

When the MARIADB_CLIENT_EXTENDED_METADATA 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

since MariaDB 10.2.0 - MariaDB 11.5.1 for unit results

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. The server will then return a resultset containing for each unitary results (containing auto generated ids and affected rows)

Example

java code: 

Statement stmt = connection.createStatement();
stmt.execute("CREATE TABLE test_table (id int, val varchar(32))");

try (PreparedStatement prep = connection.prepareStatement("INSERT INTO test_table VALUES (?, ?)")) {

    prep.setInt(1, 1);
    prep.setString(2, "a");
    prep.addBatch();

    prep.setInt(1, 2);
    prep.setString(2, "b");
    prep.addBatch();

    prep.executeBatch();
}

client send :

MARIADB_CLIENT_STMT_BULK_OPERATIONS:
       +--------------------------------------------------+
       |  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+------+--------------------------------------------------+------------------+
|000000| 1B 00 00 00 FA FF FF FF  FF 80 00 03 00 FD 00 00 | ................ |
|000010| 01 00 00 00 00 01 61 00  02 00 00 00 00 01 62    | ......a.......b  |
+------+--------------------------------------------------+------------------+

server response:

OK_Packet:
       +--------------------------------------------------+
       |  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+------+--------------------------------------------------+------------------+
|000000| 2E 00 00 01 00 02 00 02  00 00 00 26 52 65 63 6F | ...........&Reco |
|000010| 72 64 73 3A 20 32 20 20  44 75 70 6C 69 63 61 74 | rds: 2  Duplicat |
|000020| 65 73 3A 20 30 20 20 57  61 72 6E 69 6E 67 73 3A | es: 0  Warnings: |
|000030| 20 30                                            |  0               |
+------+--------------------------------------------------+------------------+

Authentication Plugins

MariaDB has specific authentication methods.

Redirection

since MariaDB 11.3.1 or Maxscale 25.08.0

MariaDB permits 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.

Connectors can support 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

since MariaDB 11.4.1

Since a feature permitting TLS certificate validation without needing 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 5.7.0 and MariaDB 10.2.2 support session tracking when the CLIENT_SESSION_TRACK capability is set.

One difference is that since MariaDB 11.5.1, connection ending OK_Packet lists all the current variables of tracked variable.

MySQL Features Not Supported

  • X protocol is not supported

Unsupported features and associate capabilities:

  • CLIENT_OPTIONAL_RESULTSET_METADATA: permits setting 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 permits 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 depend on the MySQL or MariaDB server version.

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 server support MARIADB_CLIENT_STMT_BULK_OPERATIONS capability (since MariaDB 10.2), a specific statement ID value of -1 (or 0xffffffff in hexadecimal) can be used to indicate that the previously prepared statement could be reused. This enables connectors to pipeline the preparation and execution steps into a single request:

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 improves performance for the first execution.

Traditionally, connectors send COM_STMT_PREPARE, wait for results, then execute COM_STMT_EXECUTE with statement_id received from the prepare result.

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

Query Timeout

Since MariaDB 10.1.2

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 ...

Collations

Connectors don't care about collations, but normally want 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, connectors can check if charset of initial tracked variable 'character_set_connection' corresponds to the expected value, then permit skipping this SET NAMES command.

( 'server default collation' from initial handshare packet cannot be trusted, since truncated to one byte. Recent mysql and mariadb collation can go on 2 bytes)

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.