Learn about the specific extensions and differences in the MariaDB protocol compared to MySQL, such as extended capability flags and metadata.
Here is a list of the differences between MariaDB and MySQL in terms of protocol, in order to help community driver maintainers.
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).
MARIADB_CLIENT_CACHE_METADATA: Enables clients to cache metadata and avoid repeated network transmissions (since ).
MARIADB_CLIENT_EXTENDED_METADATA : Provides more detailed column metadata information for specific data types (since ).
MARIADB_CLIENT_STMT_BULK_OPERATIONS: Introduces a dedicated command, , for efficient batch execution of statements.
See .
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 result set format indicates if metadata follows or is skipped:
int column count,
if (MARIADB_CLIENT_CACHE_METADATA capability set) int<1> metadata follows (0 / 1).
Example
Java code:
Results with metadata caching:
The same, without metadata caching:
When the MARIADB_CLIENT_EXTENDED_METADATA capability is set, 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'.
The MARIADB_CLIENT_STMT_BULK_OPERATIONS capability enables the 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 result set containing for each unitary results (containing auto generated ids and affected rows).
Example
Java code:
Client send :
Server response:
MariaDB has specific authentication methods.
(from MariaDB 11.6.1)
MariaDB permits .
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 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.
A feature that enables TLS certificate validation without requiring client-side certificate configuration.
Requires a nonempty password.
Only supports the following authentication methods:
mysql_native_password
client_ed25519
When no SSL certificates are pre-configured, the server automatically generates a temporary self-signed certificate.
During connection establishment, the server embeds a special validation hash in the connection's "OK_Packet" information field.
The client connector must postpone SSL error handling until the connection phase is complete.
The client captures and stores the SHA256 fingerprint of the server's certificate.
If SSL errors occur, the client can only use specific authentication plugins (mysql_native_password/ed25519/parsec) to prevent potential password exposure.
The password hash;
The server's seed;
Stored certificate fingerprint.
The SSL-error connection proceeds only if the client-generated hash matches the server-provided hash.
mysql_native_password:
Hash generation: SHA1(SHA1(password)).
ed25519:
MySQL and MariaDB support session tracking when the CLIENT_SESSION_TRACK capability is set.
This is useful for connectors which have a method to set the transaction type, retrieving database for example to always have the server current value when changed. This permit to avoid executing some queries when not needed
Example of ending connection OK_Packet :
It indicates:
autocommit = ON
time_zone = SYSTEM
character_set_client = utf8mb4
character_set_connection = utf8mb4
A connector knows that character_set_client set to utf8mb4, then could avoid executing a command like "SET NAMES utf8mb4".
The X protocol is not supported.
Unsupported features and associate capabilities:
CLIENT_OPTIONAL_RESULTSET_METADATA: permits setting no METADATA at all for a connection. See 's MariaDB implementation choice.
CLIENT_QUERY_ATTRIBUTES adds some metadata attributes
CLIENT_ZSTD_COMPRESSION_ALGORITHM permits zstd compression
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 does 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 that some features like using COM_RESET_CONNECTION has no capability, and depend on the MySQL or MariaDB server version.
Connectors usually follow a two-step process for prepared statements:
Prepare: Send a command to the server, receiving a statement ID in response.
Execute: Send a command, using the statement ID obtained in the previous step.
When the server support MARIADB_CLIENT_STMT_BULK_OPERATIONS capability, a specific statement ID value of -1 (or 0xffffffff in hexadecimal) can be used to indicate that the previously prepared statement can be reused. This enables connectors to pipeline the preparation and execution steps into a single request:
Send a then a with statement ID -1 (0xffffffff) commands to the server.
Read the prepare and execute responses.
If the command returns an error (ERR_Packet), the subsequent with statement ID -1 also fails and returns 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 , wait for results, then execute with statement_id received from the prepare result.
This description is for , but works exactly the same way.
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 ...
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 they support session tracking, connectors can check if the character set of initially tracked variable character_set_connection corresponds to the expected value, then permit skipping this SET NAMES statement ( 'server default collation' from cannot be trusted, since truncated to one byte. Recent mysql and mariadb collation can go on 2 bytes).
This page is licensed: CC BY-SA / Gnu FDL
MARIADB_CLIENT_BULK_UNIT_RESULTS: Allows for individual result sets for each bulk operation (since ).
SERVER_STATUS_IN_TRANS in the "OK_Packet," "ERR_Packet," or "EOF_Packet."parsec
At connection conclusion, the server sends an OK_Packet with a validation hash.
The client generates a hash using:
Uses the Ed25519 cryptographic algorithm for hash generation.
parsec:
Hash generation involves combining
'P' character;
Number of iterations;
Salt;
Raw public key.
redirect_url =
MULTI_FACTOR_AUTHENTICATION Multifactor Authentication capability.
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();
}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 | ......."... |
+------+--------------------------------------------------+------------------+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 | ......."... |
+------+--------------------------------------------------+------------------+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();
}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 |
+------+--------------------------------------------------+------------------+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 |
+------+--------------------------------------------------+------------------++--------------------------------------------------+
| 0 1 2 3 4 5 6 7 8 9 a b c d e f |
+------+--------------------------------------------------+------------------+
|000000| A6 00 00 02 00 00 00 02 40 00 00 00 9D 00 0E 0A | ........@....... |
|000010| 61 75 74 6F 63 6F 6D 6D 69 74 02 4F 4E 00 11 09 | autocommit.ON... |
|000020| 74 69 6D 65 5F 7A 6F 6E 65 06 53 59 53 54 45 4D | time_zone.SYSTEM |
|000030| 00 1D 14 63 68 61 72 61 63 74 65 72 5F 73 65 74 | ...character_set |
|000040| 5F 63 6C 69 65 6E 74 07 75 74 66 38 6D 62 34 00 | _client.utf8mb4. |
|000050| 21 18 63 68 61 72 61 63 74 65 72 5F 73 65 74 5F | !.character_set_ |
|000060| 63 6F 6E 6E 65 63 74 69 6F 6E 07 75 74 66 38 6D | connection.utf8m |
|000070| 62 34 00 1E 15 63 68 61 72 61 63 74 65 72 5F 73 | b4...character_s |
|000080| 65 74 5F 72 65 73 75 6C 74 73 07 75 74 66 38 6D | et_results.utf8m |
|000090| 62 34 00 0E 0C 72 65 64 69 72 65 63 74 5F 75 72 | b4...redirect_ur |
|0000a0| 6C 00 01 06 05 74 65 73 74 6A | l....testj |
+------+--------------------------------------------------+------------------+