Download MariaDB Connector/J

Installing MariaDB Connector/J with a Package Manager

The recommended way to install MariaDB Connector/J is to use a package manager like or .

Installing MariaDB Connector/J with Maven

To install MariaDB Connector/J with , add the following dependency to your pom.xml configuration file:

Ensure to replace the $VERSION with a valid MariaDB Connector/J version number. See to determine which MariaDB Connector/J release series supports your Java version.

Installing MariaDB Connector/J with Gradle

To install MariaDB Connector/J with , add the following dependency to your build.gradle configuration file:

Ensure to replace the $VERSION with a valid MariaDB Connector/J version number. See to determine which MariaDB Connector/J release series supports your Java version.

.

Installing MariaDB Connector/J Manually with the JAR File

It is not generally the recommended method, but MariaDB Connector/J can also be installed by manually installing the .jar file to a directory in your . MariaDB Connector/J .jar files can be downloaded from the following URL:

Installing MariaDB Connector/J from Source

This section deals with building the connector from the source and testing it. If you have downloaded a ready-built connector in the package as a jar file, then this section may be skipped.

The source code is available at the on GitHub. You can clone it by executing the following:

If you would prefer a packaged source tarball release, then MariaDB Connector/J .jar source code tarballs can be downloaded from the following URL:

MariaDB Connector/J has the following build requirements:

• Java JDK

If you would like to run the unit tests, then you'll need a MariaDB or MySQL server. It has to meet the following requirements:

• It must be listening on localhost a TCP port 3306.

• It must have a database called testj.

• It must have a root user account with an empty password.

If you would like to build MariaDB Connector/J and run the unit tests, then execute the following:

If you would like to build MariaDB Connector/J without running the unit tests, then execute the following:

Once the build is complete, you should have a .jar file with a name like the one mariadb-java-client-x.y.z.jar in the target subdirectory.

MariaDB has supported GSSAPI authentication since when the authentication plugin was added.

The subsections below describe how to configure and use GSSAPI authentication with MariaDB Connector/J:

Support history,

• version 1.4.0 : java connector support

• Version 1.5.0: added native Windows implementation.

General configuration

The authentication plugin must be installed on the database server. The relevant user account must also be configured to use the plug-in for authentication.

For example:

And then this user account could be used to connect to the database server with the Java connector by specifying the user name in the Java connection URL.

For example:

Since the user account is configured to use the authentication plugin on the server, the Java connector will use GSSAPI authentication when connecting.

The service principal name must be the one defined for the user account on the database server unless a different one is specified with the parameter in the connection URL.

The database server will wait for a ticket associated with the principal defined in the user ('userOne@EXAMPLE'). It means on the client, the user must have obtained a TGT beforehand.

As part of the security context establishment, the driver will initiate a context that will be authenticated by the database. The database is also authenticated back to the driver ("mutual authentication").

GSSAPI configuration

Java system properties

Realm information is generally defined by DNS, but this can be forced using system properties. "java.security.krb5.kdc" defined the Key Distribution Center (KDC), and the realm was defined by "java.security.krb5.realm". Example :

Logging can be set using additional properties:

Java JCE

Depending on the Kerberos ticket encryption, you may have to install the Unlimited Strength Jurisdiction Policy File. (CentOS/Red Hat Enterprise Linux 5.6 or later and Ubuntu are using AES-256 encryption by default for tickets).

On Unix, you can execute the "klist -e" command to view the encryption type in use: If AES is being used, output like the following is displayed after you type the klist command (note that AES-256 is included in the output):

Implementations

On Windows, the GSSAPI implementation is SSPI. The Java 8 native implementation has many limitations ().

The driver contains 2 different implementations:

• A Java standard implementation will use JAAS to allow Java to access TGT.

• A windows native implementation based on

Standard java SSPI implementation

Jaas

The driver will use the native ticket cache to get the TGT available in it using JAAS. If the system property "java.security.auth.login.config" is empty, the driver will use the following configuration:

This permit to use the current user TGT cache

limitation on windows

Main limitations are:

• To permit Java to retrieve TGT (Ticket-Granting-Ticket), the windows host needs to have a registry entry set.

• The Kinit command must have been executed previously to the connection.

Windows native java implementation

Implementation is based on that supports Windows SSPI based on .

If waffle-jna (and dependencies) is in the classpath, the native implementation will automatically be used. (This permit to avoid any specific problem with admin right, registry, kinit ...)

Dependencies:

Possible errors

• "GSSException: Failure unspecified at GSS-API level (Mechanism level: No Kerberos credentials available)"

There is no active credential. Check with Klist that there is an existing credential. If not, create it with the "kinit" command

• "java.sql.SQLInvalidAuthorization SpecException: Could not connect:GSSAPI name mismatch, requested 'userOne@EXAMPLE.COM', actual name 'userTwo@EXAMPLE.COM'.

There is an existing credential, but it doesn't correspond to the connection user. example: if user is created with a command like

klist must show the same principal (userTwo@EXAMPLE.COM in this example)

• "GSSException: No valid credentials provided (Mechanism level: Clock skew too great (37))." The Kerberos protocol requires the time of the client

and server to match: if the system clocks of the client do not match that of the KDC server, authentication will fail with this kind of error. The simplest way to synchronize the system clocks is to use a Network Time Protocol (NTP) server.

This guide will cover:

• The load balancing and high availability concepts in MariaDB Connector/J.

• The different options.

This guide will cover:

• The load balancing and high availability concepts in MariaDB Connector/J for versions before 3.0

• The different options.

Failover and high availability were introduced in 1.2.0.

Load balancing and failover distinction

Failover occurs when a connection to a primary database server fails, and the connector opens up a connection to another database server.

For example, server A has the current connection. After a failure (server crash, network down …), the connection will switch to another server (B).

Load balancing allows load (read and write) to be distributed over multiple servers.

Replication cluster type

In MariaDB (and MySQL) replication, there are 2 different replication roles:

• Master role: Database server that permits read and write operations

• Slave role: Database server that permits only read operations

This document describes the configuration and implementation for 3 types of clusters:

• Multi-Master replication cluster. All hosts have a master replication role. (example: Galera)

• Master/slaves cluster: one host has the master replication role with multiple hosts in the slave replication role.

• Hybrid cluster: multiple hosts in the master replication role with multiple hosts in the slave replication role.

Load balancing implementation

Random picking

When initializing a connection or after a failed connection, the connector will attempt to connect to a host with a certain role (slave/master). The connection is selected randomly among the valid hosts. Thereafter, all statements will run on that database server until the connection is closed (or fails).

The load-balancing will include a pooling mechanism. Example: when creating a pool of 60 connections, each one will use a random host. With 3 master hosts, the pool will have about 20 connections to each host.

Master/slave distributed load

For a cluster composed of masters and slaves on connection initialization, there will be 2 underlying connections: one with a master host, another with a slave host. Only one connection is used at a time. For a cluster composed of master hosts only, each connection has only one underlying connection. The load will be distributed due to the random distribution of connections..

Master/slave connection selection

It’s the application that has to decide to use a master or slave connection (the master connection is set by default). Switching the type of connection is done by using the JDBC method. Setting read-only to true will use the slave connection; false, the master connection.

Example in standard Java:

Some frameworks render this kind of operation easier, for example Spring readOnly parameter (since Spring 3.0.1). In this example, setting readOnly to false will call the connection.setReadOnly(false) and therefore use the master connection.

Generated Spring Data repository objects use the same logic: the find* method will use the slave connection, and other use master connection without having to explicitly set that for each method.

On a cluster with master hosts only, the use of connection.setReadOnly(true) does not change the connection, but if the database version is 10.0.0 or higher, the session is set to readOnly if option assureReadOnly is set to true, which means that any write query will throw an exception.

Failover behaviour

Basic failover

When no failover/high availability parameter is set, the failover support is basic. Before executing a query, if the connection with the host is discarded, the connection will be reinitialized if parameter “autoReconnect” is set to true.

Standard failover

When a failover/high availability parameter is set. Check the section for an overview of how to set the parameters.

The failure can be multiple fail causes. When a failure occurs, many things will be done:

• The failed host address will be put on a blacklist (shared by JVM). This host will not be used for the amount of time defined by the “loadBalanceBlacklistTimeout” parameter (default to 50 seconds). The only time a blacklisted address can be used is if all hosts of the same type (master/slave) are blacklisted.

• The connector will check the connection (with the MySQL ). If the connection is back, is not read-only, and is in a transaction, the transaction will be rolled back (there is no way to know if the last query has been received by the server and executed).

• If the failure relates to a slave connection

It’s up to the application to take measures to handle SQLException. See details in the .

1. Connection loop When initializing a connection or after a failure, the driver will launch a connection loop. The only case when this connection loop will not be executed is when a failure occurred on a slave with an active master. This connection loop will try to connect to a valid host until finding a new connection or until the number of connections exceeds the parameter “retriesAllDown” value (default to 120).

This loop will attempt to connect sequentially to hosts in the following order:

For a master connection :

• random connect to master host not blacklisted

• random connect to master blacklisted

For a slave connection :

• random connect to slave host not blacklisted

• random connect to master host not blacklisted (if no active master connection)

• random connect to the slave blacklisted

• random connect to master host blacklisted (if no active master connection) The sequence stops as soon as all the underlying needed connections are found. Every time an attempt fails, the host will be blacklisted. If after an entire loop a master connection is missing, the connection will be marked as closed.

Additional threads

Failover reconnection threads

A thread pool is created in case of a master/slave cluster, the size is defined according to the number of connection. After a failure on a slave connection, readonly operations are temporary executed on the master connection. Some “failover threads” will try to reconnect the failed underlying connections. When a new slave connection is retrieved, this one will be immediately used if connection was still in read-only mode.

Connection validation thread

An additional thread is created when setting the option "validConnectionTimeout". This thread will very that connections are all active. This is normally done by pool that call .

Application concerns

When a failover happen a SQLException with sqlState like "08XXX" or "25S03" may be thrown.

Here are the different connection error codes:

A connection pool will detect connection error in SQLException (SQLState begin with "08"), and this connection will be discarded from pool.

When a failover occurs, the connector cannot know if the last request has been received by the database server and executed. Applications may have failover design to handle these particular cases:

• If the application was in autoCommit mode (not recommended), the last query may have been executed and committed. The application will have no possibility to know that but the application will be functional.

• If not in autoCommit mode, the query has been launched in a transaction that will not be committed. Depending of what caused the exception, the host may have the connection open on his side during a certain amount of time. Take care of level that may lock too much rows.

Configuration

(See for all connection parameters) JDBC connection string format is :

The standard option "connectTimeout" defines the socket connection timeout. By default, this option is set to 0 (no timeout).

Since there are many servers, setting this option to a small amount of time make sense. During the , the driver will try to connect to the server sequentially until the creation of an active connection.

Set this option to a small value (such as 2000ms - to be set according to your environment) which will permit rejecting a faulty server quickly.

Failover and Load Balancing Modes

Each parameter corresponds to a specific use case:

Failover and Load Balancing Parameters

Specifics for Amazon Aurora

Amazon Aurora is a Master/Slaves cluster composed of one master instance with a maximum of 15 slave instances. Amazon Aurora includes automatic promotion of a slave instance in case of the master instance failing. The MariaDB connector/J implementation for Aurora is specific to handle this automatic failover.

To permit development/integration on a single-node cluster, only one host can be defined. In this case, the driver behaves as for the configuration failover.

Aurora failover implementation

Aurora failover management steps :

• Instance A is in write replication mode, instance B and C are in read replication mode.

• Instance A fails.

• Aurora detects A failure, and promote instance B in write mode. Instance C will change his master to use B.

• Cluster end-point will change to instance B end-point.

Aurora configuration

Aurora endpoints and discovery

Every Aurora instance has a specific endpoint, i.e., an URL that identifies the host. Those endpoints look like xxx.yyy.zzz.rds.amazonaws.com.

There is another endpoint named "cluster endpoint" (format xxx.cluster-yyy.zzz.rds.amazonaws.com), which is assigned to the current master instance and will change when a new master is promoted.

In versions before 1.5.1, cluster endpoint use was discouraged, since when a failover occurs, this cluster endpoint can point for a limited time to a host that isn't the current master any more. The old recommendation was to list all specific end-points. This kind of url string will still work, but now, recommended url string has to use only cluster endpoint.

Driver will automatically discover master and slaves of this cluster from current cluster end-point during connection time. This permits adding new replicas to the cluster instance which will be discovered without changing driver configuration.

This discovery appends at connection time, so if you are using pool framework, check if this framework as a property that controls the maximum lifetime of a connection in the pool, and set a value to avoid infinite lifetime. When this lifetime is reached, pool will discard the current connection, and create a new one (if needed). New connections will use the new replicas. (If connections are never discarded, new replicas will begin to be used only when a failover occur)

JDBC connection string

The implementation is activated by specifying the “aurora” failover parameter. Recommended connection string is using cluster end-point:

Before driver version 1.5.1, the connection string has to list all end-points:

If setting many endpoints, the replication role of each instance must not be defined for Aurora, because the role of each instance changes over time. The driver will check the instance role after connection initialization.

Example of connection string

Another difference is the option "socketTimeout" that defaults to 10 seconds, meaning that - if not changed - queries exceeding 10 seconds will throw exceptions.

Aurora connection loop

When searching for the master instance and connecting to a slave instance, the connection order will be:

• Every Aurora instance knows the hostname of the current master. If the host has been described using their instance endpoint, that will permit knowing the master instance and connecting directly to it.

• If this isn’t the current master (because using IP, or possibly after a failover between step 2 and 3), the loop will connect randomly the other not blacklisted instance (minus the current slave instance).

• Connect randomly to a blacklisted instance.

When searching for a slave instance, the loop connection order will be:

• random not blacklisted instances (excluding the current host if connected).

• random blacklisted instances . The loop will retry until the connections are found or the value of the “retriesAllDown” parameter is exceeded.

Aurora master verification

Without any query during the time defined by the validConnectionTimeout parameter (defaults to 120s), and if not set to 0, a verification will be done that the replication role of the underlying connections hasn't changed.

Aurora connection validation thread

Aurora is a specific implementation. Since the role of each instance can change over time, this will validate that connections are active AND roles have not changed.

MariaDB Connector/J is used to connect applications developed in Java to MariaDB and MySQL databases using the standard JDBC API.

Prerequisites

• A MariaDB / MySQL server running on localhost using the default port 3306

• Java version >= 8

• Maven

Create Maven Project

Create a simple Java project with Maven:

Replace "com.mycompany.app" and "my-app" with appropriate values

The new project will be created in the folder "my-app". This folder contains the file pom.xml that permits configuring Maven.

Get MariaDB Java Driver

Declares driver in pom.xml (and setting java minimal version to 1.8) :

pom.file will then be :

Connection

Standard JDBC methods are used to connect to the database.

Basic url string is standardized for the MariaDB driver : jdbc:(mysql|mariadb):[replication:|failover:|sequential:|aurora:]//<hostDescription>[,<hostDescription>...]/[database][?<key1>=<value1>[&<key2>=<value2>]]

The MariaDB driver is registered automatically for a url that begins with "jdbc:mariadb:" or "jdbc:mysql:".

Assuming a server is installed on the local machine with default port 3306, the url String is then "jdbc:mariadb://localhost/".

Basic maven archetype has created a simple Java file App.java in src/main/java/com/mycompany/app. Update the file App.java with the following content: (assuming a server is installed on the local machine, with a user "root" with no password) :

Compile project:

Maven will automatically download the driver and compile the App file.

Run it using maven:

See Also

• More information at

Definition

Since 1.5.0, the "useBatchMultiSend" option permits sending queries by batch. If disabled, queries are sent one by one, waiting for the result before sending the next one. If enabled, queries will be sent by batch corresponding to the value of the useBatchMultiSendNumber option (default 100). Results will be read after a while, avoiding a lot of network latency when the client and the server aren't on the same host. This option is only used for JDBC executeBatch(). This option is particularly efficient when the client is distant from the server.

Here is a benchmark using a client and server on 2 different hosts (ping of 0.350 ms between 2 hosts):

MariaDB Connector/J is used to connect applications developed in Java to MariaDB and MySQL databases using the standard JDBC API.

Prerequisites

• A MariaDB/MySQL server running on localhost using the default port 3306

• Java version >= 8

• Gradle

Create Gradle Project

Create a simple Java project with Gradle:

The new project will be created in the current folder. This folder contains the file build. gradle that permits configuring Gradle.

Get MariaDB Java Driver

Declares driver in build.gradle (and setting Java minimal version to 1.8) :

The build. gradle file will then be :

Connection

Standard JDBC methods, , are used to connect to the database.

The basic URL string is standardized for the MariaDB driver: jdbc:(mysql|mariadb):[replication:|failover:|sequential:|aurora:]//<hostDescription>[,<hostDescription>...]/[database][?<key1>=<value1>[&<key2>=<value2>]]

The MariaDB driver is registered automatically for a URL that begins with "jdbc:mariadb:" or "jdbc:mysql:".

Assuming a server is installed on the local machine with default port 3306, the URL string is then "jdbc:mariadb://localhost/".

Create a new file App.java in src/main/java with the following content:(assuming a server is installed on the local machine, with a user "root" with no password):

To run the class app, add a new task in the build. Gradle:

Build project:

Gradle will automatically download the driver and compile the app file. To run the App class, just launch the previously defined task "run":

See Also

• More information at

Overview

This document explains how to configure the MariaDB Java driver to support TLS/SSL.

Data can be encrypted during transfer using the Transport Layer Security (TLS) protocol. TLS/SSL permits transfer encryption and, optionally, server and client identity validation.

Server configuration

To ensure that SSL is correctly configured on the server, the query "SELECT @@have_ssl;" must return YES. If not, please refer to the .

Connecting to a server that doesn't support TLS with the TLS option set, an exception "Trying to connect with SSL, but SSL not enabled in the server" will be thrown.

TLS Protocol Version Selection

The MariaDB Java driver by default uses Java's default supported protocols. If the servers are MariaDB on Unix or version >= 10.2, consider adding the TLSv1.2 protocol. This can be set using the "enabledSslProtocolSuites" option (example: enabledSslProtocolSuites=TLSv1.2, TLSv1.3).

In addition to the protocol, the driver relies on the Java default cipher list. The Java default enabled ciphers are . JAVA allows cipher suites to be removed/excluded from use in the security policy using the Java system property "jdk.tls.disabledAlgorithms." The specific list of ciphers to be used can be set using the "enabledSslCipherSuites" driver option (example: "enabledSSLCipherSuites=TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256,...")

One-way SSL authentication

By default, the driver can be configured to use TLS even if the authentication user is not explicitly required to use it. However, it is recommended to create a user with the REQUIRE SSL option to enforce TLS. For details, see CREATE USER to ensure the connector always uses TLS.

Example:

Since version 3 of the connector, TLS is enabled with the client-side setting option "sslMode." The following values are supported:

• Disable: Do not use SSL/TLS (default)

• Trust: Only use SSL/TLS for encryption. Do not perform certificate or hostname verification.

• verify-ca: Use SSL/TLS for encryption and perform certificate verification, but do not perform hostname verification.

• verify-full:Use SSL/TLS for encryption, certificate verification, and hostname verification. This is the standard TLS behavior. Aliases "true"/"1" are possible like "sslMode=true."

To ensure TLS is enabled server-side, first try with the option "sslMode=trust."

This configuration is not safe for production. While all data exchanges are encrypted, the server’s identity is not verified, leaving the connection vulnerable to man-in-the-middle attacks by malicious servers.

To ensure the server’s identity is validated, the client must trust the server’s root and intermediate certificates. This can be accomplished in several ways:

• Java includes a default truststore containing certificates from well-known Certificate Authorities (CAs), such as Let’s Encrypt (added in Java 8u101), VeriSign, Entrust, and GTE CyberTrust. If the server’s certificate chain is signed by a root CA present in this default truststore, no additional client-side configuration is required. The default truststore location is defined by the system property javax.net.ssl.trustStore.

• Alternatively, you can explicitly provide the server’s certificate using the serverSslCert option.

• Starting with Connector version 3.4+ and MariaDB Server 11.4+, zero-configuration TLS encryption is supported. For details, see the dedicated chapter.

By default, when sslMode is enabled (and not set to DISABLED), the connector will use the certificate specified in serverSslCertif provided, or fall back to the default truststore if not. This fallback behavior can be disabled by setting the fallbackToSystemTrustStore option to false.

Java default truststore

Java trustStore is a file that contains certificates of trusted SSL servers or certificate authorities trusted to identify servers. Truststore can be protected by a password.

The Java search order for locating the trust store is

1. system property "javax.net.ssl.trustStore"

2. $JAVA_HOME/lib/security/jssecacerts

3. $JAVA_HOME/lib/security/cacerts (shipped by default)

To include a certificate from a CA that is not already in the truststore, first locate your system’s default truststore. By default, it can be found at: $JAVA_HOME/jre/lib/security/cacerts.

If you’re referring to the database connection string from our earlier example, switching sslMode to "verify-full" just means updating that property in your config or connection URL.

Provide the certificate directly

The serverSslCert option specifies the location of the server’s SSL certificate. The location can be provided in one of the following three formats:

1. Full file path – e.g., serverSslCert=/path/to/cert.pem

2. Classpath-relative path – e.g., serverSslCert=classpath:relative/cert.pem (relative to the current classpath)

3. Inline certificate string – a verbatim DER-encoded certificate, e.g., "-----BEGIN CERTIFICATE-----..."

Example :

Zero-configuration TLS encryption

In MariaDB Server 11.4, TLS configuration is simpler than before. When connecting with MariaDB Connector/J 3.4 or later, you can enable SSL encryption by setting sslMode=verify-full—no other SSL parameters are needed. During the TLS handshake, the server provides its certificate, and the client confirms the server’s identity using the certificate fingerprint along with password hashing. For this verification to succeed, the password must not be empty.

Mutual (2-way) authentication

Mutual SSL authentication, also known as certificate-based mutual authentication, is a process where two parties verify each other’s identity by validating the digital certificates presented, ensuring that both sides can trust the authenticity of the other.

To enable mutual authentication, the user must be created with "REQUIRE X509" so the server asks the driver for client certificates. See for more details.

Example:

If the user is not set with REQUIRE X509, only one- way authentication will be done

The client (driver) must also have its own certificate along with the corresponding private key. If the driver does not present a certificate and the user account is configured with REQUIRE X509, the server will respond with a simple “Access denied for user” message.

In Java, the client certificate and its private key are stored in a keystore file. A keystore is similar to a truststore—in fact, they are often the same file.

Example: Generating a keystore in JKS format

Like the truststore, you can use the Java default keystore without requiring any additional options. Alternatively, you can specify a dedicated keystore by using the keyStore option to provide its location and the keyStorePassword option to set its password. If the JKS keystore includes a separate password for a specific key, you can set it with the keyPassword option.

Troubleshooting

SSL not enabled on server

If you encounter the error:

It means SSL is disabled on the server. Since the connection was made with the useSSL=true option, it failed. To verify the SSL status on the server, run:

Server certificate not provided to the client

When the driver attempts an SSL connection without a provided certificate, or when trustServerCertificate=true is not set, the connection will fail with an exception similar to:

Solution:

• Not recommended: Set the sslMode option to trust.

• Add the server certificate to the driver (see documentation above).

Access Denied for User Error:

This can occur due to:

1. Incorrect username or password.

2. SSL requirements are set for the user that the connection does not meet. You can check the SSL settings for a user with:

Pool Datasource Implementation

MariaDB Connector/J provides 2 different Datasource pool implementations:

• MariaDbDataSource: The basic implementation. It creates a new connection each time the getConnection() method is called.

What is MariaDB Connector/J?

Download MariaDB Connector/J

MariaDB Connector/J is used to connect applications developed in Java to MariaDB and MySQL databases using the standard JDBC API. The library is LGPL licensed.

MariaDB Connector/J is a Type 4 JDBC driver. It was developed specifically as a lightweight JDBC connector for use with MariaDB and MySQL database servers. It was originally based on the Drizzle JDBC code with numerous additions and bug fixes.

Server Compatibility

MariaDB Connector/J is compatible with all MariaDB and MySQL server versions.

Java Compatibility

To determine which MariaDB Connector/J release series would be best to use for each Java version, please see the following table:

1. see parsec authentication restriction

Installing MariaDB Connector/J

MariaDB Connector/J can be installed using , , or by manually putting the .jar file in your . See for more information.

MariaDB Connector/J .jar files and source code tarballs can be downloaded from the following URL:

Installing Dependencies

JNA (net.java.dev.jna:jna) and JNA-PLATFORM (net.java.dev.jna:jna-platform) 4.2.1 or greater are also needed when you would like to connect to the server with Unix sockets or windows pipes.

Using the Driver

The following subsections show the formatting of JDBC connection strings for MariaDB and MySQL database servers. Additionally, sample code is provided that demonstrates how to connect to one of these servers and create a table.

Getting a New Connection

There are two standard ways to get a connection:

Using DriverManager

The preferred way to get a connection with MariaDB Connector/J is to use the class. When the DriverManager class is used to locate and load MariaDB Connector/J, the application needs no further configuration. The DriverManager class will automatically load MariaDB Connector/J and allow it to be used in the same way as any other JDBC driver.

For example:

jdbc:mysql scheme compatibility

MariaDB Connector/J 3.0 only accepts jdbc:mariadb: as the protocol in connection strings by default. When both MariaDB Connector/J and the MySQL drivers are found in the class-path, using jdbc:mariadb: as the protocol helps to ensure that Java chooses MariaDB Connector/J.

Connector/J still allows jdbc:mysql: as the protocol in connection strings when the permitMysqlScheme option is set. For example:

jdbc:mysql://HOST/DATABASE?permitMysqlScheme

(2.x version did permit connection URLs beginning with both jdbc:mariadb and jdbc:mysql)

Using a Pool

Another way to get a connection with MariaDB Connector/J is to use a connection pool.

MariaDB Connector/J provides 2 different Datasource pool implementations:

• MariaDbDataSource: The basic implementation. It creates a new connection each time the getConnection() method is called.

• MariaDbPoolDataSource: A connection pool implementation. It maintains a pool of connections, and when a new connection is requested, one is borrowed from the pool.

Internal Pool

The driver's internal pool configuration provides a very fast pool implementation and deals with the issues most of the java pool have:

• 2 different connection states cleaning after release

• deals with non-activity (connections in the pool will be released if not used after some time, avoiding the issue created when the server closes the connection after @wait_timeout is reached).

See the for more information.

External pool

When using an external connection pool, the MariaDB Driver class org.mariadb.jdbc.Driver must be configured.

Example using hikariCP JDBC connection pool :

Please note that the driver class provided by MariaDB Connector/J is not com.mysql.jdbc.Driver but org.mariadb.jdbc.Driver!

The org.mariadb.jdbc.MariaDbDataSource class can be used when the pool datasource configuration only permits the java.sql.Datasource implementation.

Connection Strings

The format of the JDBC connection string is:

HostDescription:

Some notes about this:

• The host must be a DNS name or IP address.

• If the host is an IPv6 address, then it must be inside square brackets.

• The default port is 3306.

• The default type is master.

Examples:

• localhost:3306

• [2001:0660:7401:0200:0000:0000:0edf:bdd7]:3306

• somehost.com:3306

The jdbc:mariadb:sequential:address=(localSocket=/socket)(sslMode=disable),10.0.0.1:3306/DB?sslMode=verify-full connection string will permit to connect to local unix socket if available, or to host 10.0.0.1 using SSL if not.

Failover and Load-Balancing Modes

Failover and Load-Balancing Modes were introduced in

sequential

• Description: This mode supports connection failover in a multi-master environment, such as MariaDB Galera Cluster. This mode does not support load-balancing reads on replicas. The connector will try to connect to hosts in the order in which they were declared in the connection URL, so the first available host is used for all queries.

For example, let's say that the connection URL is the following: jdbc:mariadb:sequential:host1,host2,host3/testdbWhen the connector tries to connect, it will always try host1 first. If that host is not available, then it will try host2. etc. When a host fails, the connector will try to reconnect to hosts in the same order.

• Introduced: 1.3.0

loadbalance

• Description: This mode supports connection load-balancing in a multi-master environment, such as MariaDB Galera Cluster. This mode does not support load-balancing reads on replicas. The connector performs load-balancing for all queries by randomly picking a host from the connection URL for each connection, so queries will be load-balanced as a result of the connections getting randomly distributed across all hosts. Before 2.4.2, this option was named failover - alias still exist for compatibility -

• Introduced: 1.2.0

replication

• Description: This mode supports connection failover in a primary-replica environment, such as a MariaDB Replication cluster. The mode supports environments with one or more masters. This mode does support load-balancing reads on replicas if the connection is set to read-only before executing the read. The connector performs load-balancing by randomly picking a replica from the connection URL to execute read queries for a connection

• Introduced: 1.2.0

load-balance-read

• Description: When running a multi-master cluster (i.e. Galera), writing to more than one node can lead to optimistic locking errors ("deadlocks"). Writing concurrently to multiple nodes also doesn't bring a whole lot of performance, due to having to (synchronously) replicate to all nodes anyway. This mode supports connection failover in a multi-master environment, such as MariaDB Galera Cluster. This mode does support load-balancing reads on replicas. The connector will try to connect to primary hosts in the order in which they were declared in the connection URL, so the first available host is used for all queries.

For example, let's say that the connection URL is the following: jdbc:mariadb:load-balance-read:primary1,primary2,address=(host=replica1)(type=replica),address=(host=replica2)(type=replica)/DBWhen the connector tries to connect, it will always try primary1 first. If that host is not available, then it will try primary2. etc. When a primary host fails, the connector will try to reconnect to hosts in the same order.For replica hosts, the connector performs load-balancing for all queries by randomly picking a replica host from the connection URL for each connection, so queries will be load-balanced as a result of the connections getting randomly distributed across all replica hosts.

• Introduced: 3.5.1

aurora

• Description: This mode supports connection failover in an Amazon Aurora cluster. This mode does support load-balancing reads on replica instances if the connection is set to read-only before executing the read. The connector performs load-balancing by randomly picking a replica instance to execute read queries for a connection

• Introduced: 1.2.0 and not supported anymore since 3.0 version

See for more information.

Optional URL Parameters

General remark: Unknown options are accepted and silently ignored.

The following options are currently supported.

Essential Parameters

allowLocalInfile

• Description: Permit loading data from file. see . Having this option enable can impact batch performance. Disabling it can permit some batch improvement

• Data Type: boolean

• Default Value: true

connectTimeout

• Description: The connect timeout value, in milliseconds, or zero for no timeout

• Data Type: integer

• Default Value: 30 000

• Introduced: 1.1.8

initSql

• Description: permit to execute commands at connection creation. Multiple commands can be passed. example: initSql=SET @myVar='YourVar';SET @myVar2='YourVar2'.

• Data Type: string

• Default Value: null

• Introduced: 3.0.0

password

• Description: password

• Data Type: string

• Default Value: null

• Introduced: 1.0.0

user

• Description: Username.

• Data Type: string

• Default Value: null

• Introduced: 1.0.0

useServerPrepStmts

• Description: The Text protocol (default) is a universally safe option that works reliably in all situations. The Binary protocol (useServerPrepStmts=true) can offer performance benefits, but its impact depends on whether the prepared statement cache is used:

  • Cache miss: Preparing before execution adds overhead, potentially causing up to ~50% performance loss.

  • Cache hit: Can improve performance by around 5–10%, though the gain is often small since most queries have simple execution plans.

TLS Parameters

more information on

sslMode

• Description: Enables SSL/TLS in a specific mode. this option replaces the deprecated options: disableSslHostnameVerification, trustServerCertificate, useSsl

• Data Type: string

• Default Value: disable

serverSslCert

• Description: |Permits providing the server's certificate in DER form, or the server's CA certificate. The server will be added to trustStore. This permits a self-signed certificate to be trusted.Can be used in one of 3 forms : * serverSslCert=/path/to/cert.pem (full path to certificate)* serverSslCert=classpath:relative/cert.pem (relative to current classpath)* or as verbatim DER-encoded certificate string "------BEGIN CERTIFICATE-----"

• Data Type: boolean

• Default Value: false

keyStore

• Description: File path of the keyStore file that contains the client private keycontains store and associate certificates (similar to java System property "javax.net.ssl.keyStore", but ensure that only the private key's entries are used).

• Data Type: string

• Default Value: null

keyStorePassword

• Description: Password for the client certificate keyStore (similar to java System property "javax.net.ssl.keyStorePassword")

• Data Type: string

• Default Value: null

• Alias: clientCertificateKeyStorePassword

enabledSslCipherSuites

• Description: Force TLS/SSL cipher (comma separated list). Example : "TLS_DHE_RSA_WITH_AES_256_GCM_SHA384, TLS_DHE_DSS_WITH_AES_256_GCM_SHA384"

• Data Type: string

• Default Value: use JRE ciphers

enabledSslProtocolSuites

• Description: Force TLS/SSL protocol to a specific set of TLS versions (comma separated list). Example : "TLSv1,TLSv1.1,TLSv1.2

• Data Type: string

• Default Value: use JRE default

fallbackToSystemKeyStore

• Description: keystoreXXX options are used to permit mutual authentication. When keystore option is not specified, this setting determines the connector's behavior: if set to false, not using any keystore; if set to true, the connector will follow standard Java convention and use the trust store defined by the "javax.net.ssl.trustStore" system property.

• Data Type: boolean

• Default Value: false

fallbackToSystemTrustStore

• Description: Server certificates can be validated using either the serverSslCert or trustStore options. When neither option is specified, this setting determines the connector's behavior: if set to false, all certificates will be rejected; if set to true, the connector will follow standard Java convention and use the trust store defined by the "javax.net.ssl.trustStore" system property.

• Data Type: boolean

• Default Value: false

disableSslHostnameVerification

• Description: deprecated, use sslMode instead When using ssl, the driver checks the hostname against the server's identity as presented in the server's certificate (checking alternative names or the certificate CN) to prevent man-in-the-middle attacks. This option permits deactivating this validation. Hostname verification is disabled when the trustServerCertificate option is set

• Data Type: boolean

• Default Value: false

useSsl

• Description: deprecated, use sslMode instead Force (useSSL can be used as alias).

• Data Type: boolean

• Default Value: false

• Introduced: 1.1.0

trustServerCertificate

• Description: deprecated, use sslMode instead When using SSL/TLS, do not check server's certificate

• Data Type: boolean

• Default Value: false

• Introduced: 1.1.1

Pool Parameters

See the for pool configuration.

pool

• Description: Use pool. This option is useful only if not using a DataSource object, but only a connection object

• Data Type: boolean

• Default Value: false

• Introduced: 2.2.0

poolName

• Description: Pool name that permits identifying threads.default: auto-generated as MariaDb-pool-

• Data Type: string

• Default Value: MariaDB-pool

• Introduced: 2.2.0

maxPoolSize

• Description: The maximum number of physical connections that the pool should contain

• Data Type: integer

• Default Value: 8

• Introduced: 2.2.0

minPoolSize

• Description: When connections are removed due to not being used for longer than "maxIdleTime", connections are closed and removed from the pool. "minPoolSize" indicates the number of physical connections the pool should keep available at all times. Should be less or equal to maxPoolSize.

• Data Type: integer

• Default Value: maxPoolSize value

poolValidMinDelay

• Description: When asking a connection to pool, the pool will validate the connection state. "poolValidMinDelay" permits disabling this validation if the connection has been borrowed recently avoiding useless verifications in case of frequent reuse of connections. 0 means validation is done each time the connection is asked. In milleseconds.

• Data Type: integer

• Default Value: 1000

maxIdleTime

• Description: The maximum amount of time in seconds that a connection can stay in the pool when not used. This value must always be below @wait_timeout value - 45s

• Data Type: integer

• Default Value: 600 minimum value is 60 seconds

useResetConnection

• Description: When a connection is closed() (given back to pool), the pool resets the connection state. Setting this option, the prepare command will be deleted, session variables changed will be reset, and user variables will be destroyed when the server permits it (>= MySQL 5.7.3), permitting saving memory on the server if the application make extensive use of variables

• Data Type: boolean

• Default Value: false

registerJmxPool

• Description: Register JMX monitoring pools

• Data Type: boolean

• Default Value: true

• Introduced: 2.2.0

Infrequently Used Parameters

allowMultiQueries

• Description: permit multi-queries like insert into ab (i) values (1); insert into ab (i) values (2).

• Data Type: boolean

• Default Value: false

allowPublicKeyRetrieval

• Description: Authorize client to retrieve RSA server public key when serverRsaPublicKeyFile is not set (for sha256_password and caching_sha2_password authentication password)

• Data Type: boolean

• Default Value: false

• Introduced: 2.5.0

autocommit

• Description: Set default autocommit value on connection initialization.

• Data Type: boolean

• Default Value: true

• Introduced: 2.2.0

blankTableNameMeta

• Description: Resultset metadata getTableName always return blank. This option is mainly for ORACLE db compatibility.

• Data Type: boolean

• Default Value: false

• Introduced: 2.4.3

cacheCallableStmts

• Description:enable/disable callable Statement cache

• Data Type: boolean

• Default Value: true

• Introduced: 1.4.0

cachedCodecs

• Description: permit to enable/disable caching of codecs (FIELD encoder/decoder).

• Data Type: boolean

• Default Value: false

• Introduced: 3.5.4

cachePrepStmts

• Description: Enable caching of PREPARE commands using an LRU (Least Recently Used) cache to prevent redundant command preparation. Note that in versions prior to 3.x, this cache was only activated when the useServerPrepStmts option was enabled.

• Data Type: boolean

• Default Value: true

connectionAttributes

• Description: When performance_schema is active, permit sending the server some client information in a key;value pair format (example: connectionAttributes=key1:value1,key2,value2).The information can be retrieved on the server in the tables performance_schema.session_connect_attrs and performance_schema.session_account_connect_attrs. This can enable the server to identify the client/application

• Data Type: string

• Default Value: null

connectionCollation

• Description: Connector force utf8mb4 charset at connection. Indicate what utf8mb4 collation to use if set. if not set, the server default collation for utf8mb4 will be used. Useful only for the server before , because then a better solution would be to set character_set_collations

• Data Type: string

• Default Value: null

createDatabaseIfNotExist

• Description: the specified database in the url will be created if nonexistent.

• Data Type: boolean

• Default Value: false

• Introduced: 1.1.7

credentialType

• Description: Indicate the credential plugin type to use. Plugin must be present in classpath

• Data Type: string

• Default Value: null

• Introduced: 2.5.0

defaultFetchSize

• Description: The driver will call setFetchSize(n) with this value on all newly-created Statements

• Data Type: integer

• Default Value: 0

• Introduced: 2.4.2

disconnectOnExpiredPasswords

• Description: On the connection creation, indicate behavior when password is expired. When true (default) throw an expired password error. When false, the connection succeed in "sandbox" mode, only queries related to password change are allowed.

• Data Type: boolean

• Default Value: true

dumpQueriesOnException

• Description: If set to 'true', an exception is thrown during query execution containing a query string. This is useful in development, but can lead to security issue if logs are available.

• Data Type: boolean

• Default Value: false

galeraAllowedState

• Description: Usually, Connection.isValid just send an empty packet to server, and server send a small response to ensure connectivity. When this option is set, connector will ensure Galera server state "wsrep_local_state" correspond to allowed values (separated by comma). example "4,5", recommended is "4". see

• Data Type: string

• Default Value: null

includeInnodbStatusInDeadlockExceptions

• Description: add "SHOW ENGINE INNODB STATUS" result to exception trace when having a deadlock exception.

• Data Type: boolean

• Default Value: false

• Introduced: 2.3.0

includeThreadDumpInDeadlockExceptions

• Description: add thread dump to exception trace when having a deadlock exception.

• Data Type: boolean

• Default Value: false

• Introduced: 2.3.0

localSocket

• Description: Permits connecting to the database via Unix domain socket, if the server allows it. The value is the path of Unix domain socket (i.e "socket" database parameter : select @@socket) .

• Data Type: string

• Default Value: null

localSocketAddress

• Description: Hostname or IP address to bind the connection socket to a local (UNIX domain) socket.

• Data Type: string

• Default Value: null

• Introduced: 1.1.7

maxQuerySizeToLog

• Description: Only the first characters corresponding to this options size will be displayed in logs

• Data Type: integer

• Default Value: 1024

• Introduced: 1.5.0

metaExportedKeys

• Description: Possible implementation DatabaseMetadata.getExportedKey. Either use INFORMATION_SCHEMA or SHOW CREATE TABLE to retrieve metadata information. When set to "auto", the method will automatically choose between the INFORMATION_SCHEMA approach or the SHOW CREATE implementation based on whether the database server is running locally or remotely. Possible values: "UseInformationSchema", "UseShowCreate", or "auto".

• Data Type: string

oldModeNoPrecisionTimestamp

• Description: When enabled, Timestamps string representation will be compatible with 2.7's behavior (fractional part will only be displayed if required, not according to timestamp precision) .

• Data Type: boolean

• Default Value: false

permitMysqlScheme

• Description: when added to connection string, permit jdbc:mysql: prefix in connection string

• Data Type: boolean

• Default Value: false

permitNoResults

• Description: Indicate if Statement/PreparedStatement.executeQuery for command that produce no result will return an exception or just an empty result-set. When enabled, command not returning no data will end returning an empty result-set, when disabled, command not returning no data will end throwing an exception

• Data Type: boolean

• Default Value: true

pinGlobalTxToPhysicalConnection

• Description: When set, commands with a specific XID will reuse the previous connection used for this XID.

• Data Type: boolean

• Default Value: false

• Introduced: 3.4.1

pipe

• Description: On Windows, specify named pipe name to connect (windows equivalent of unix socket)

• Data Type: string

• Default Value: null

• Introduced: 1.1.3

prepStmtCacheSize

• Description: When cachePrepStmts is enabled, this value indicates the prepared statement cache size.

• Data Type: integer

• Default Value: 250

• Introduced: 1.3.0

restrictedAuth

• Description: permits to restrict authentication plugins (comma separated). For example, the following connection string only allows the mysql_native_password and client_ed25519 client authentication plugins:jdbc:mariadb:HOST/DATABASE?restrictedAuth=mysql_native_password,client_ed25519. If not set, permit all authentication plugins.

• Data Type: string

• Default Value: null

returnMultiValuesGeneratedIds

• Description: for connector 2.x compatibility only, getGeneratedKeys() will then returns all ids of multi-value inserts. This is not compatible with galera servers

• Data Type: boolean

• Default Value: false

• Introduced: 3.3.2

rewriteBatchedStatements

• Description: For insert queries, rewrite batchedStatement to execute in a single executeQuery.

  • example: 'insert into ab (i) values (?)' with first batch values = 1, second = 2 will be rewritten as 'insert into ab (i) values (1), (2)'.

  • When enabled, the useServerPrepStmts option will be forced to false

serverRsaPublicKeyFile

• Description: Indicate path to RSA server public key file for sha256_password and caching_sha2_password authentication password

• Data Type: string

• Default Value: null

• Introduced: 2.5.0

servicePrincipalName

• Description: When using , use this value as the Service Principal Name (SPN) instead of the one defined for the user account on the database server.

• Data Type: string

• Default Value: null

• Introduced: 2.4.0

sessionVariables

• Description: = pairs separated by comma, mysql session variables, set upon establishing successful connection.

• Data Type: string

• Default Value: null

• Introduced: 1.1.4

socketFactory

• Description: to use a custom socket factory, set it to the full name of the class that implements javax.net.SocketFactory

• Introduced: 1.1.0

socketTimeout

• Description: Defined the network socket timeout (SO_TIMEOUT) in milliseconds. Value of 0 disables this timeout. If the goal is to set a timeout for all queries, the server has permitted a solution to limit the query time by setting a system variable, . The advantage is that the connection then is still usable.

• Data Type: integer

• Default Value: 0

tcpAbortiveClose

• Description: This option can be used in environments where connections are created and closed in rapid succession. Often, it is not possible to create a socket in such an environment after a while, since all local "ephemeral" ports are used up by TCP connections in TCP_WAIT state. Using tcpAbortiveClose works around this problem by resetting TCP connections (abortive or hard close) rather than doing an orderly close. It is accomplished by using socket.setSoLinger(true,0) for abortive close.

• Data Type: boolean

• Default Value: false

tcpKeepAlive

• Description: Sets corresponding option on the connection socket. Default to true since 3.0.0 (was false before)

• Data Type: boolean

• Default Value: true

• Introduced: 1.0.0

tcpKeepCount

• Description: Permit to set socket option TCP_KEEPCOUNT (only if java 11+)

• Data Type: integer

• Default Value: 0

• Introduced: 3.0.0

tcpKeepIdle

• Description: Permit to set socket option TCP_KEEPIDLE (only if java 11+)

• Data Type: integer

• Default Value: 0

• Introduced: 3.0.0

tcpKeepInterval

• Description: Permit to set socket option TCP_KEEPINTERVAL (only if java 11+)

• Data Type: integer

• Default Value: 0

• Introduced: 3.0.0

tinyInt1isBit

• Description: Datatype mapping flag, handle MySQL Tiny as BIT(boolean).

• Data Type: boolean

• Default Value: true

• Introduced: 1.0.0

tlsSocketType

• Description: Indicate the TLS org.mariadb.jdbc.tls.TlsSocketPlugin plugin type to use. Plugin must be present in classpath

• Data Type: string

• Default Value: null

• Introduced: 2.5.0

transactionReplay

• Description: Enables transaction caching. If a failover occurs before a transaction is committed or rolled back, the transaction's cached statements are re-executed on the new primary server. Connector/J requires that applications only use idempotent queries. If the number of statements in the transaction cache exceeds transactionReplaySize, caching will be disabled until the transaction is committed or rolled back.

• Data Type: boolean

• Default Value: false

transactionReplaySize

• Description: Sets the number of statements that should be saved in the transaction cache when transactionReplay is enabled.

• Data Type: integer

• Default Value: 64

• Introduced: 3.0.0

trustStore

• Description: File path of the trustStore file (similar to java System property "javax.net.ssl.trustStore"). (legacy alias trustCertificateKeyStoreUrl)Use the specified file for trusted root certificates.When set, overrides serverSslCert. (see trustStorePassword in case if a jks truststore with a password)

• Data Type: string

• Default Value: null

trustStorePassword

• Description: Password for the trusted root certificate file (similar to java System property "javax.net.ssl.trustStorePassword").(legacy alias trustCertificateKeyStorePassword).

• Data Type: string

• Default Value: null

• Introduced: 3.5.0 (or 1.3.4 in 1.x, 2.0.0 in 2.x)

trustStoreType

• Description: Indicate trust store type (JKS/PKCS12). default is null, then using java default type.(legacy alias trustCertificateKeystoreType).

• Data Type: string

• Default Value: null

• Introduced: 3.5.0 (or 2.4.0 in 2.x)

useBulkStmts

• Description: Use dedicated COM_STMT_BULK_EXECUTE protocol for batch insert when possible. (batch without Statement.RETURN_GENERATED_KEYS and streams) to have faster batch.

• Data Type: boolean

• Default Value: true

• Introduced: 3.0.0 (was false since version >= 2.3.0)

useCatalogTerm

• Description: "schema" and "database" are server synonymous. Connector historically get/set database using Connection.setCatalog()/getCatalog(), setSchema()/getSchema() being no-op. Setting option useCatalogTerm to "schema" will change that behavior to use Schema in place of Catalog. Affected changes : database change will be done with either Connection.setCatalog()/getCatalog() or Connection.setSchema()/getSchema(), 2: DatabaseMetadata methods that use catalog or schema filtering, 3: ResultsetMetadata getCatalogName/getSchemaName

• Data Type: string

• Default Value: CATALOG

useCompression

• Description: Compresses the exchange with the database through gzip. This permits better performance when the database is not in the same location.

• Data Type: boolean

• Default Value: false

• Introduced: 1.0.0

useMysqlMetadata

• Description: return "MariaDB" or "MySQL" according to server type

• Data Type: boolean

• Default Value: false

• Introduced: 2.4.0

useReadAheadInput

• Description: Use a buffered inputSteam that read socket available data

• Data Type: boolean

• Default Value: true

• Introduced: 2.4.0

usePipelineAuth

• Description: Not compatible with aurora*During connection, different queries are executed. When option is active those queries are send using pipeline (all queries are send, then only all results are reads), permitting faster connection creation.

• Data Type: boolean

• Default Value: true

yearIsDateType

• Description: returns Year as date type, rather than numerical.

• Data Type: boolean

• Default Value: true

• Introduced: 1.0.0

Removed options

JDBC API Implementation Notes

Size consideration

GSSAPI in windows isn't well supported in java, causing recurrent issues. Since 3.1, waffle-jna is marked as a dependency to provide good GSSAPI support without problems. This has the drawback to make connector and dependencies to a size of around 4Mb.

If size is important, the dependency can be removed, the connector working great, just will have some limitation using GSSAPI on windows :

this can be done like this:

• using maven

• using graddle:

Parsec authentication

Since 3.5.1, parsec authentication is implemented in connector. This requires java 15+ (to use java native ed25519 Algorithm implementation).

In order to use parsec authentication with previous version of java, BouncyCastle is required as dependency:

Timezone consideration

The Ideal Scenario

The simplest approach to avoid time zone headaches is for the client and server to operate in the same time zone.

Java Connector Timezone Options

There are 3 options that control timestamps behavior in the java connector:

• connectionTimeZone: (LOCAL | SERVER | ) - This option defines the connection's time zone. LOCAL retrieves the JVM's default time zone, SERVER fetches the server's global time zone upon connection creation, and allows specifying a server time zone without requesting it during connection establishment.

• forceConnectionTimeZoneToSession: (true | false) - This setting dictates whether the connector enforces the connection time zone for the session.

• preserveInstants: (true | false) - This option controls whether the connector converts Timestamp values to the connection's time zone.

Recommendation

By default, the connector adopts the JVM's default time zone. If the client and server reside in different time zones, it's recommended to configure the connection time zone to match the JVM's default by setting forceConnectionTimeZoneToSession to true. This ensures proper operation of time functions.

(This isn’t the default behavior because there is a server Requirements to set tzinfo depending on the JVM's time zones)

TIMESTAMP vs. DATETIME: Know the Difference

Just like Java's Instant and LocalDateTime, server-side TIMESTAMP and DATETIME fields serve distinct purposes. One represents a specific point in time (a moment), while the other doesn't.

• TIMESTAMP: This represents an exact moment on the timeline, expressed using the connection's time zone. When stored, it gets converted to UTC (Coordinated Universal Time) for consistency. Upon retrieval, it's converted back to the connection's time zone for display.

• DATETIME: This combines date and time-of-day information but doesn't represent a specific moment.

The Misuse of DATETIME:

Due to its wider range, DATETIME is sometimes mistakenly used to store a specific point in time. While this might work if the client and server share the same time zone, it creates problems when they differ.

A (Discouraged) Workaround:

While using DATETIME instead of TIMESTAMP is generally discouraged, a specific combination of settings ("preserveInstants=true&connectionTimeZone=SERVER") can force all Java Timestamp exchanges to be converted to the connection's time zone during storage and retrieval. However, this approach is not recommended for long-term solutions.

Compatibility with Older Connectors (Pre-3.4):

The MariaDB Connector/J versions before 3.4 offered a single "timezone" option. While this functionality remains compatible, it's now separated into two distinct settings: connectionTimeZone and forceConnectionTimeZoneToSession. Here's a breakdown of how the old option translates to the new ones: "timezone=America/Los_Angeles" is equivalent to "connectionTimeZone=America/Los_Angeles&forceConnectionTimeZone=true

To mimic the behavior of the "useLegacyDatetimeCode=false" option from MariaDB 2.x, you can set the following combination: “connectionTimeZone=SERVER&preserveInstants=true”

Note: Unlike the MySQL Connector, the MariaDB Connector/J defaults connectionTimeZone to LOCAL (JVM's default) instead of SERVER.

"LOAD DATA INFILE"

The fastest way to load lots of data is using . However, using "LOAD DATA LOCAL INFILE" (ie: loading a file from the client) may be a security problem if someone can execute a query from the client, he can have access to any file on the client (according to the rights of the user running the client process).

A specific option "allowLocalInfile" (default to true) can disable this functionality on the client side. The global variable can disable LOAD DATA LOCAL INFILE on the server side.

You can provide custom stream as well using a specific setLocalInfileInputStream

Contrary to mysql connector, setLocalInfileInputStream value can only be used for next execution.

Set a Query Timeout

Driver follow the JDBC specifications, permitting Statement.setQueryTimeout() for a particular statement.

If the goal is to set a timeout for all queries, the server permits a by setting the system variable .

This solution will handle query timeout better (and faster) than java solutions (JPA2, "javax.persistence.query.timeout", Pools integrated solution like tomcat jdbc-pool "queryTimeout"...).

Option "sessionVariables" permit to set this system variable easily : Example :

Streaming Result Sets

By default, Statement.executeQuery() will read the full result set from the server. With large result sets, this will require large amounts of memory.

To avoid using too much memory, rather use Statement.setFetchSize(int numberOfRowInMemory) to indicate the number of rows that will be stored in memory Example : using Statement.setFetchSize(1000) indicates that 1000 rows will be stored in memory. So, when the query has executed, 1000 rows will be in memory. After 1000 ResultSet.next(), the next 1000 rows will be stored in memory, and so on.

Note that the server usually expects clients to read off the result set relatively quickly. The server variable controls this behavior (defaults to 60s). If you don't expect results to be handled in this amount of time there is a different possibility:

• With you can use the query "SET STATEMENT net_write_timeout=10000 FOR XXX" with XXX your "normal" query. This will indicate that specifically for this query, will be set to a longer time (10000 in this example).

• for older servers, a specific query will have to temporarily set net_write_timeout ("SET STATEMENT net_write_timeout=..."), and set it back afterward.

• if your application usually uses a lot of long queries with fetch size, the connection can be set using the option "sessionVariables=net_write_timeout=xxx"

Even using setFetchSize, the server will send all results to the client.

If another query is executed on the same connection when a streaming resultset has not been fully read, the connector will put the whole remaining streaming resultset in memory in order to execute the next query. This can lead to OutOfMemoryError if not handled.

Before version 1.4.0, the only accepted value for fetch size was Statement.setFetchSize(Integer.MIN_VALUE) (equivalent to Statement.setFetchSize(1)). This value is still accepted for compatibility reasons, but rather use Statement.setFetchSize(1), since according to JDBC the value must be >= 0.

Prepared Statements

The driver uses server prepared statements as a standard to communicate with the database (since 1.3.0). If the "allowMultiQueries" options are set to true, the driver will only use text protocol. Prepared statements (parameter substitution) is handled by the driver, on the client side.

CallableStatement

Callable statement implementation won't need to access the stored procedure metadata () table if both of the following are true

• CallableStatement.getMetadata() is not used

• Parameters are accessed by index, not by name

When possible, following the two rules above provides both better speed and eliminates concerns about SELECT privileges on the table.

Generated keys limitation

Java permit retrieving last generated keys,using .

Example:

Only the first generated key will be returned, meaning that for multi-insert the generated key retrieved will correspond to the first generated value of the command.

If retrieving all generated values for multiple insert is needed, please use command (since ).

Optional JDBC Classes

The following optional interfaces are implemented by the org.mariadb.jdbc.MariaDbDataSource class : javax.sql.DataSource, javax.sql.ConnectionPoolDataSource, javax.sql.XADataSource

careful : org.mariadb.jdbc.MySQLDataSource doesn't exist anymore and should be replaced with org.mariadb.jdbc.MariaDbDataSource since v1.3.0

Usage Examples

The following code provides a basic example of how to connect to a MariaDB or MySQL server and create a table.

Creating a Table on a MariaDB or MySQL Server

Services

The driver implements 3 kinds of services:

• Credential service: permit giving credential

• Authentication service: permit adding client authentication plugins.

• SSL factory service: custom TSL implementation

Credential service

Credentials are usually set using user/password in the connection string or by using DriverManager.getConnection(String url, String user, String password).

Credential plugins permit to provide credential information from other means. Those plugins have to be activated setting option credentialType to designated plugin.

The driver has 3 default plugins :

AWS IAM

This permits AWS database IAM authentication. The plugin generate a token using IAM credential and region. Token is valid for 15 minutes and cached for 10 minutes.

To use this credential authentication, com.amazonaws:aws-java-sdk-rds dependency must be registered in classpath. Implementation use SDK DefaultAWSCredentialsProviderChain and DefaultAwsRegionProviderChain to get IAM credential and region. see and to check how those information can be retrieved (environment variable / system properties, files, ...)

Example: jdbc:mariadb://host/db?credentialType=AWS-IAM&useSsl&serverSslCert=/somepath/rds-combined-ca-bundle.pem

with AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY and AWS_REGION environment variable set.

Environment

User and Password are retrieved from environment variables. default environment variables are MARIADB_USER and MARIADB_PWD, but can be changed by setting additional option userKey and pwdKey

Example : using connection string jdbc:mariadb://host/db?credentialType=ENV user and password will be retrieved from environment variable MARIADB_USER and MARIADB_PWD.

Property

User and Password are retrieved from java properties. default property name are mariadb.user and mariadb.pwd, but property names can be changed by setting additional option userKey and pwdKey

Example : using connection string jdbc:mariadb://host/db?credentialType=PROPERTY&userKey=mariadbUser&pwdKey=mariadbPwd user and password will be retrieved from java properties mariadbUser and mariadbPwd

Authentication service

are now defined as services. This permits to easily add new client authentication plugins.

List of authentication plugins in java connector :

• mysql_clear_password

• auth_gssapi_client

• client_ed25519

• mysql_native_password

New authentication plugins can be created implementing interface org.mariadb.jdbc.authentication.AuthenticationPlugin, and listing new plugin in a META-INF/services/org.mariadb.jdbc.authentication.AuthenticationPlugin file.

SSL factory service

Custom SSL implementation can be used implementing A connection to a server initially creates a socket. When set, SSL socket is layered over this existing socket. Implementing org.mariadb.jdbc.tls.TlsSocketPlugin permit to provide custom SSL implementation for example create a new implementation.

Custom implementation need to implement org.mariadb.jdbc.tls.TlsSocketPlugin and register service META-INF/services/org.mariadb.jdbc.tls.TlsSocketPlugin

Custom implementation are activated using option tlsSocketType

Easy to use logging

In MariaDB Connector/J 3.0, logging can now be enabled at runtime. Connector/J uses the slf4j API if it is installed. Otherwise, Connector/J uses the JDK logger / console.

logger name is "org.mariadb.jdbc".

Connector/J supports the following Java logging levels:

Example of configuring "trace" level on driver for logback: file logback.xml in src/main/resources/

Example of generated logs :

Continuous Integration and Automated Tests

For MariaDB Connector/J's continuous integration and automated test results, please see .

Reporting Bugs

If you find a bug, please report it via the on .

Source Code

The source code is available at the on GitHub.

License

GNU Lesser General Public License as published by the Free Software Foundation; either version 2.1 of the License, or (at your option) any later version.

For licensing questions, see the .

F.A.Q.

Error "Could not read resultset: unexpected end of stream, read 0 bytes from 4"

There is an issue communicating with the server.

Most of the time this will be caused by reading a query that has a large resultset; the server usually expects clients to read off the result set relatively quickly. The server variable controls this behavior (defaults to 60s). If the client doesn't read the whole resultset in that amount of time, the server will discard the connection. If you don't expect results to be handled in this amount of time there is another possibility:

• You can use the query "SET STATEMENT net_write_timeout=10000 FOR XXX" with XXX being your "normal" query. This will indicate that specifically for this query, will be set to a longer time (10000 in this example).

• for older servers, a specific query will have to temporarily set net_write_timeout ("SET STATEMENT net_write_timeout=..."), and set it back afterward.

• if your application usually uses a lot of long queries with fetch size, the connection can be set using the "sessionVariables=net_write_timeout=xxx" option.

How to Do a Lightweight Ping / Avoid Mass "select 1"

• Connection.isValid() is a good approach.

• Connection.isValid() is doing a ping (ping in mysql protocol, not network ping).

• Connection pool using JDBC4 Validation are using automatically this Connection.isValid()