The most recent Stable (GA) release of MariaDB Connector/J is:
MariaDB Connector/J 2.1.2

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.

 Date  Release  Status  Min. Java Compat.  Release Notes  Changelog 
26 Sep 20172.1.2Stable (GA)Java 8Release NotesChangelog
6 Sep 20172.1.1Stable (GA)Java 8Release NotesChangelog
31 Jul 20172.1.0Stable (GA)Java 8Release NotesChangelog
26 Sep 20171.6.5Stable (GA)Java 6Release NotesChangelog
6 Sep 20171.6.4Stable (GA)Java 6Release NotesChangelog
31 Jul 20171.6.3Stable (GA)Java 6Release NotesChangelog

see all releases

About MariaDB Connector/J

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.

Obtaining the driver

MariaDB Connector/J source code tarballs can be downloaded from: https://downloads.mariadb.org/connector-java/

Pre-built .jar files can be downloaded from: https://mariadb.com/downloads/connector

Installing the driver

Installation of the client library is very simple. The jar file should be saved in an appropriate place for your application and the classpath of your application altered to include MariaDB Connector/J rather than your current connector.

Using maven :

<dependency>
    <groupId>org.mariadb.jdbc</groupId>
    <artifactId>mariadb-java-client</artifactId>
    <version>xxx</version>
</dependency>

Requirements

MariaDB Connector/J 1.6.x version is now the maintenance version for Java 6 and Java 7.

Driver version1.11.2 - 1.6.11.6 (>1.6.1)2.x
Java 6-----OK----------OK-----
Java 7-----OK----------OK----------OK-----
Java 8-----OK----------OK----------OK----------OK-----
  • com.sun.JNA is used by some library functions and a jar is available at https://github.com/twall/jna
    • only needed when connecting to the server with unix sockets or windows shared memory
  • A MariaDB or MySQL Server
  • maven (only if you want to build from source)

Source code

The source code is available on GitHub: https://github.com/MariaDB/mariadb-connector-j and the most recent development version can be obtained using the following command:

git clone https://github.com/MariaDB/mariadb-connector-j.git

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.

Building and testing the driver

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

MariaDB Client Library for Java Applications uses maven for building. You first need to ensure you have both java and maven installed on your server before you can build the driver.

To run the unit test, you'll need a MariaDB or MySQL server running on localhost (on the default TCP port 3306) and a database called 'testj', and user 'root' with an empty password

git clone https://github.com/MariaDB/mariadb-connector-j.git #  Or, unpack the source distribution tarball
cd mariadb-connector-j
# For the unit test run, start local mysqld mysqld, 
# ensure that user root with empty password can login
mvn package
# If you want to build without running unit  tests, use
# mvn -Dmaven.test.skip=true package

After that, you should have JDBC jar mariadb-java-client-x.y.z.jar in the 'target' subdirectory

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 prefered way to connect is to use https:docs.oracle.com/javase/7/docs/api/java/sql/DriverManager.html. Applications designed to use the driver manager to locate the entry point need no further configuration. MariaDB Connector/J will automatically be loaded and used in the way any previous MySQL driver would have been.

Example:

Connection connection = DriverManager.getConnection("jdbc:mariadb://localhost:3306/DB?user=root&password=myPassword");

The legacy way of loading a JDBC driver (using Class.forName("org.mariadb.jdbc.Driver")) still works.

Having MariaDB and MySQL drivers in the same classpath

Since MariaDB aims to be a drop-in replacement for MySql, the driver permits connection strings beginning with "jdbc:mariadb" or "jdbc:mysql". To permit having the MySQL and MariaDB drivers on the same classpath, since version 1.5.9, the MariaDB driver doesn't accept connection strings beginning with "jdbc:mysql" if the "disableMariaDbDriver" option is set.

jdbc:mysql://localhost:3306/db?user=someUser&disableMariaDbDriver won't be accepted by the MariaDB driver, permitting having MySQL and MariaDB in the same classpath without interfering.

Using 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 :

        final HikariDataSource ds = new HikariDataSource();
        ds.setMaximumPoolSize(20);
        ds.setDriverClassName("org.mariadb.jdbc.Driver");
        ds.setJdbcUrl("jdbc:mariadb://localhost:3306/db");
        ds.addDataSourceProperty("user", "root");
        ds.addDataSourceProperty("password", "myPassword");
        ds.setAutoCommit(false);

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

jdbc:(mysql|mariadb):[replication:|failover:|sequential:|aurora:]//<hostDescription>[,<hostDescription>...]/[database][?<key1>=<value1>[&<key2>=<value2>]] 

HostDescription:

<host>[:<portnumber>]  or address=(host=<host>)[(port=<portnumber>)][(type=(master|slave))]

Host must be a DNS name or IP address. In case of ipv6 and simple host description, the IP address must be written inside brackets. The default port is 3306. The default type is master. If replication failover is set, by default the first host is master, and the others are slaves.

Examples :

  • localhost:3306
  • [2001:0660:7401:0200:0000:0000:0edf:bdd7]:3306
  • somehost.com:3306
  • address=(host=localhost)(port=3306)(type=master)

Failover parameters

Failover was introduced in Connector/J 1.2.0.

sequentialFailover support for master replication cluster (for example Galera) without High availability. The hosts will be connected in the order in which they were declared.

Example when using the jdbc url string "jdbc:mysql:replication:host1,host2,host3/testdb" :
When connecting, the driver will always first try host1, and if not available host2 and so on. After a host fail, the driver will reconnect according to this order.
This failover method does not support load-balancing reads on slaves. The first available host is used for all queries.
since 1.3.0
failoverHigh availability (random picking connection initialisation) with failover support for master replication cluster (for example Galera).
since 1.2.0
replicationHigh availability (random picking connection initialisation) with failover support for master/slave replication cluster (one or multiple masters)
since 1.2.0
auroraHigh availability (random picking connection initialisation) with failover support for Amazon Aurora replication cluster
since 1.2.0

See failover description for more information.

Optional URL parameters

General remark: Unknown options are accepted and silently ignored.

The following options are currently supported.

Essential options

userDatabase user name.
since 1.0.0
passwordPassword of database user.
since 1.0.0
rewriteBatchedStatementsFor 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
insert into ab (i) values (1), (2).

If query cannot be rewriten in "multi-values", rewrite will use multi-queries : INSERT INTO TABLE(col1) VALUES (?) ON DUPLICATE KEY UPDATE col2=? with values [1,2] and [2,3]" will be rewritten
INSERT INTO TABLE(col1) VALUES (1) ON DUPLICATE KEY UPDATE col2=2;INSERT INTO TABLE(col1) VALUES (3) ON DUPLICATE KEY UPDATE col2=4

when active, the useServerPrepStmts option is set to false
Default: false. Since 1.1.8
connectTimeoutThe connect timeout value, in milliseconds, or zero for no timeout.
Default: 0. Since 1.1.8
useServerPrepStmtsPrepareStatement are prepared on the server side before executing. The applications that repeatedly use the same queries have value to activate this option, but the general case is to use the direct command (text protocol).
if rewriteBatchedStatements is set to true, this option will be set to false
Default: false (was true before 1.6.0). Since 1.3.0
useBatchMultiSend*Not compatible with aurora*
Driver will can send queries by batch.
If set to false, queries are sent one by one, waiting for the result before sending the next one.
If set to true, queries will be sent by batch corresponding to the useBatchMultiSendNumber option value (default 100) or according to the max_allowed_packet server variable if the packet size does not permit sending as many queries. Results will be read later, avoiding a lot of network latency when the client and server aren't on the same host.

This option is mainly effective when the client is distant from the server. More information here
Default: true (false if using aurora failover) . Since 1.5.0


TLS (SSL)

more information on Using TLS/SSL with MariaDB java connector

useSSLForce SSL/TLS on connection.
Default: false. Since 1.1.0
trustServerCertificateWhen using SSL/TLS, do not check server's certificate.
Default: false. Since 1.1.1
serverSslCertPermits providing server's certificate in DER form, or server's CA certificate. The server will be added to trustStor. 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-----" .
since 1.1.3
keyStoreFile path of the keyStore file that contain client private key store and associate certificates (similar to java System property "javax.net.ssl.keyStore", but ensure that only the private key's entries are used).(legacy alias clientCertificateKeyStoreUrl).
Since 1.3.4
keyStorePasswordPassword for the client certificate keyStore (similar to java System property "javax.net.ssl.keyStorePassword").(legacy alias clientCertificateKeyStorePassword)
Since 1.3.4
keyPasswordPassword for the private key in client certificate keyStore. (only needed if private key password differ from keyStore password).
Since 1.5.3
trustStoreFile 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.
Since 1.3.4
trustStorePasswordPassword for the trusted root certificate file (similar to java System property "javax.net.ssl.trustStorePassword").
(legacy alias trustCertificateKeyStorePassword).
Since 1.3.4
enabledSslProtocolSuitesForce TLS/SSL protocol to a specific set of TLS versions (comma separated list).
Example : "TLSv1, TLSv1.1, TLSv1.2"
(Alias "enabledSSLProtocolSuites" works too)
Default: TLSv1, TLSv1.1. Since 1.5.0
enabledSslCipherSuitesForce TLS/SSL cipher (comma separated list).
Example : "TLS_DHE_RSA_WITH_AES_256_GCM_SHA384, TLS_DHE_DSS_WITH_AES_256_GCM_SHA384"
Default: use JRE ciphers. Since 1.5.0
disableSslHostnameVerificationWhen 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
Default: false. Since 2.1.0


Log

logEnable log information.
require Slf4j version > 1.4 dependency.
Log level correspond to Slf4j logging implementation
Default: false. Since 1.5.0
maxQuerySizeToLogOnly the first characters corresponding to this options size will be displayed in logs
Default: 1024. Since 1.5.0
slowQueryThresholdNanosWill log query with execution time superior to this value (if defined )
Default: 1024. Since 1.5.0
profileSqllog query execution time.
Default: false. Since 1.5.0


Infrequently used

passwordCharacterEncodingIndicate password encoding charset. Charset value must be a Java charset.
Example : "UTF-8"
Default: null (= platform's default charset) . Since 1.5.9
useFractionalSecondsCorrectly handle subsecond precision in timestamps (feature available with MariaDB 5.3 and later).
May confuse 3rd party components (Hibernated).
Default: true. Since 1.0.0
allowMultiQueriespermit multi-queries like insert into ab (i) values (1); insert into ab (i) values (2). Default: false. Since 1.0.0
dumpQueriesOnExceptionIf set to 'true', an exception is thrown during query execution containing a query string.
Default: false. Since 1.1.0
useCompressionallow compression in the MySQL Protocol.
Default: false. Since 1.0.0
socketFactoryto use a custom socket factory, set it to the full name of the class that implements javax.net.SocketFactory.
since 1.0.0
tcpNoDelaySets corresponding option on the connection socket.
since 1.0.0
tcpKeepAliveSets corresponding option on the connection socket.
since 1.0.0
tcpAbortiveCloseThis 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.
since 1.1.1
tcpRcvBufset buffer size for TCP buffer (SO_RCVBUF).
since 1.0.0
tcpSndBufset buffer size for TCP buffer (SO_SNDBUF).
since 1.0.0
pipeOn Windows, specify named pipe name to connect to mysqld.exe.
since 1.1.3
tinyInt1isBitDatatype mapping flag, handle MySQL Tiny as BIT(boolean).
Default: true. Since 1.0.0
yearIsDateTypeYear is date type, rather than numerical.
Default: true. Since 1.0.0
sessionVariables<var>=<value> pairs separated by comma, mysql session variables, set upon establishing successful connection.
since 1.1.0
localSocketPermits 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) .
since 1.1.4
sharedMemoryPermits connecting to the database via shared memory, if the server allows it.
The value is the base name of the shared memory.
since 1.1.4
localSocketAddressHostname or IP address to bind the connection socket to a local (UNIX domain) socket.
since 1.1.7
socketTimeoutDefined the network socket timeout (SO_TIMEOUT) in milliseconds. Value of 0 disables this timeout.
Default: 0 (standard configuration) or 10000ms (using "aurora" failover configuration).
since 1.1.7
interactiveClientSession timeout is defined by the wait_timeout server variable. Setting interactiveClient to true will tell the server to use the interactive_timeout server variable.
Default: false. Since 1.1.7
useOldAliasMetadataBehaviorMetadata ResultSetMetaData.getTableName() returns the physical table name. "useOldAliasMetadataBehavior" permits activating the legacy code that sends the table alias if set.
Default: false. Since 1.1.9
createDatabaseIfNotExistthe specified database in the url will be created if nonexistent.
Default: false. Since 1.1.7
serverTimezoneDefines the server time zone.
to use only if the jre server has a different time implementation of the server.
(best to have the same server time zone when possible).
since 1.1.7
prepStmtCacheSizeif useServerPrepStmts = true, defines the prepared statement cache size.
Default: 250. Since 1.3.0
prepStmtCacheSqlLimitif useServerPrepStmts = true, defined queries larger than this size will not be cached.
Default: 2048. Since 1.3.0
jdbcCompliantTruncationTruncation error ("Data truncated for column '%' at row %", "Out of range value for column '%' at row %") will be thrown as an error, and not as a warning.
Default: true. Since 1.4.0
cacheCallableStmtsenable/disable callable Statement cache
Default: true. Since 1.4.0
callableStmtCacheSizeThis sets the number of callable statements that the driver will cache per VM if "cacheCallableStmts" is enabled.
Default: true. Since 1.4.0
useBatchMultiSendNumberWhen option useBatchMultiSend is active, indicate the maximum query send in a row before reading results.
Default: 100. Since 1.5.0
connectionAttributesWhen performance_schema is active, permit to send server some client information in a key;value pair format (example: connectionAttributes=key1:value1,key2,value2).
Those informations can be retrieved on server within tables performance_schema.session_connect_attrs and performance_schema.session_account_connect_attrs.
This can permit from server an identification of client/application
Since 1.4.0
usePipelineAuth*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.
Default: true. Since 1.6.0
enablePacketDebugDriver will save the last 16 MySQL packet exchanges (limited to first 1000 bytes). Hexadecimal value of those packets will be added to stacktrace when an IOException occur.
This option has no impact on performance but driver will then take 16kb more memory.
Default: false. Since 1.6.0, 2.0.1
useBulkStmtsUse dedicated COM_STMT_BULK_EXECUTE protocol for batch insert when possible. (batch without Statement.RETURN_GENERATED_KEYS and streams) to have faster batch. (significant only if server MariaDB &ge; 10.2.7)
Default: true. Since 2.1.0



Failover/High availability URL parameters

autoReconnectWith basic failover: if true, will attempt to recreate connection after a failover.
With standard failover: if true, will attempt to recreate connection even if there is a temporary solution (like using a master connection temporary until reconnect to a slave connection)
Default is false.
since 1.1.7
retriesAllDownWhen searching a valid host, maximum number of connection attempts before throwing an exception.
Default: 120 seconds.
since 1.2.0
failoverLoopRetriesWhen searching silently for a valid host, maximum number of connection attempts.
This differs from the "retriesAllDown" parameter because this silent search is for example used after a disconnection of a slave connection when using the master connection
Default: 120.
since 1.2.0
validConnectionTimeoutWith multiple hosts, after this time in seconds has elapsed, verifies that the connections haven’t been lost.
When 0, no verification will be done.
Default:120 seconds
since 1.2.0
loadBalanceBlacklistTimeoutWhen a connection fails, this host will be blacklisted for the "loadBalanceBlacklistTimeout" amount of time.
When connecting to a host, the driver will try to connect to a host in the list of non-blacklisted hosts and, only if none are found, attempt blacklisted ones.
This blacklist is shared inside the classloader.
Default: 50 seconds.
since 1.2.0
assureReadOnlyIf true, in high availability, and switching to a read-only host, assure that this host is in read-only mode by setting the session to read-only.
Default to false.
Since 1.3.0



JDBC API implementation notes

"LOAD DATA INFILE"

The fastest way to load lots of data is using LOAD DATA INFILE.
However, using "LOAD DATA LOCAL INFILE" (ie : loading a file from client) may be a security problem :

  • A "man in the middle" proxy server can change the actual file requested from the server so the client will send a local file to this proxy.
  • 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 deactivate functionality on the client side. The global variable local_infile can disable LOAD DATA LOCAL INFILE on the server side.

A non-JDBC method can permit using this kind of query without this security issue: The application has to create an InputStream with the file to load. If MariaDbStatement.setLocalInfileInputStream(InputStream inputStream) is set, the inputStream will be sent to the server, replacing the file content (working even with the "allowLocalInfile" option disabled).

Code example:

        Statement statement = ...
        InputStream in = new FileInputStream("/file.sql");

        if (statement.isWrapperFor(MariaDbStatement.class)) {
            MariaDbStatement mariaDbStatement = statement.unwrap(MariaDbStatement.class);
            mariaDbStatement.setLocalInfileInputStream(in);
            String sql = "LOAD DATA LOCAL INFILE 'dummyFileName'"
                        + " INTO TABLE gigantic_load_data_infile "
                        + " FIELDS TERMINATED BY '\\t' ENCLOSED BY ''"
                        + " ESCAPED BY '\\\\' LINES TERMINATED BY '\\n'";
            statement.execute(sql);
        } else {
            in.close();
            throw new RuntimeException("Mariadb JDBC adaptor must be used");
        }

Since 1.5.0, Interceptors can now filter LOAD DATA LOCAL INFILE queries according to filename.

These interceptors must implement the org.mariadb.jdbc.LocalInfileInterceptor interface. Interceptors use the ServiceLoader pattern, so interceptors must be defined in the META-INF/services/org.mariadb.jdbc.LocalInfileInterceptor file.
Example : Create the META-INF/services/org.mariadb.jdbc.LocalInfileInterceptor file with content org.project.LocalInfileInterceptorImpl.

 public class LocalInfileInterceptorImpl implements LocalInfileInterceptor {
     @Override
     public boolean validate(String fileName) {
         File file = new File(fileName);
         String absolutePath = file.getAbsolutePath();
         String filePath = absolutePath.substring(0,absolutePath.lastIndexOf(File.separator));
         return filePath.equals("/var/tmp/exchanges");
     }
 }

You can avoid defining the META-INF/services file using google auto-service framework Using the previous example, just add @AutoService(LocalInfileInterceptor.class), and your interceptor will be automatically defined.

 @AutoService(LocalInfileInterceptor.class)
 public class LocalInfileInterceptorImpl implements LocalInfileInterceptor {
     @Override
     public boolean validate(String fileName) {
         File file = new File(fileName);
         String absolutePath = file.getAbsolutePath();
         String filePath = absolutePath.substring(0,absolutePath.lastIndexOf(File.separator));
         return filePath.equals("/var/tmp/exchanges");
     }
 }

Set a query timeout

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

If goal is to set a timeout for all queries, since version 10.1.1, MariaDB server permit a solution to limit query time by setting a system variable max_statement_time.

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 :

#will set a maximum query timeout of 10 seconds for this connection
jdbc:mariadb://localhost/db?user=user&sessionVariables=max_statement_time=10

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 net_write_timeout 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:

  • if your server version > 10.1.2, 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, net_write_timeout 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 afterwards.
  • if your application usually uses a lot of long queries with fetch size, the connection can be set using option "sessionVariables=net_write_timeout=xxx"

Even using setFetchSize, the server will send all results to the client. Sending another query on the same connection will throw an exception until all results aren't read.

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 compatilibity 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" or "rewriteBatchedStatements" 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 stored procedure metadata (mysql.proc) table if both of 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 mysql.proc table.

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

Connection  connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "username", "password");
Statement stmt = connection.createStatement();
stmt.executeUpdate("CREATE TABLE a (id int not null primary key, value varchar(20))");
stmt.close();
connection.close();

Debugging

There is 2 options that can permit to debug :

  • the option "enablePacketDebug"
  • logging options

Using the option "enablePacketDebug"

When any connection exception occur, the stacktrace will a lot more verbose, containing the last 16 truncated exchanges with server.

Example :

java.sql.SQLNonTransientConnectionException: (conn=7543) Could not send query: Software caused connection abort: recv failed
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:171)
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:106)
	at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:235)
	at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:332)
	at org.mariadb.jdbc.MariaDbStatement.execute(MariaDbStatement.java:383)
	at org.mariadb.jdbc.ConnectionTest.testEnablePacketDebug(ConnectionTest.java:530)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
	at org.junit.rules.TestWatcher$1.evaluate(TestWatcher.java:55)
	at org.junit.rules.RunRules.evaluate(RunRules.java:20)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
	at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
	at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
	at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
	at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
	at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
	at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
	at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
Caused by: java.sql.SQLException: Could not send query: Software caused connection abort: recv failed
send at 2017-10-04T16:48:02.444Z
39 00 00 00 03 49 4E 53  45 52 54 20 49 4E 54 4F     9....INSERT INTO
20 74 65 73 74 45 6E 61  62 6C 65 50 61 63 6B 65      testEnablePacke
74 44 65 62 75 67 20 28  74 65 73 74 29 20 56 41     tDebug (test) VA
4C 55 45 53 20 28 27 68  65 6A 61 27 29              LUES ('heja')

send at 2017-10-04T16:48:02.446Z
39 00 00 00 03 49 4E 53  45 52 54 20 49 4E 54 4F     9....INSERT INTO
20 74 65 73 74 45 6E 61  62 6C 65 50 61 63 6B 65      testEnablePacke
74 44 65 62 75 67 20 28  74 65 73 74 29 20 56 41     tDebug (test) VA
4C 55 45 53 20 28 27 6A  61 70 70 27 29              LUES ('japp')

send at 2017-10-04T16:48:02.447Z
07 00 00 00 03 43 4F 4D  4D 49 54                    .....COMMIT

send at 2017-10-04T16:48:02.450Z
24 00 00 00 03 53 45 4C  45 43 54 20 2A 20 46 52     $....SELECT * FR
4F 4D 20 74 65 73 74 45  6E 61 62 6C 65 50 61 63     OM testEnablePac
6B 65 74 44 65 62 75 67                              ketDebug

send at 2017-10-04T16:48:02.452Z
3F 00 00 00 03 49 4E 53  45 52 54 20 49 4E 54 4F     ?....INSERT INTO
20 74 65 73 74 45 6E 61  62 6C 65 50 61 63 6B 65      testEnablePacke
74 44 65 62 75 67 20 28  74 65 73 74 29 20 56 41     tDebug (test) VA
4C 55 45 53 20 28 27 72  6F 6C 6C 6D 65 62 61 63     LUES ('rollmebac
6B 27 29                                             k')

read at 2017-10-04T16:48:02.453Z
07 00 00 01                                          ....
00 01 03 01 00 00 00                                 .......

send at 2017-10-04T16:48:02.454Z
09 00 00 00 03 52 4F 4C  4C 42 41 43 4B              .....ROLLBACK

send at 2017-10-04T16:48:02.457Z
2F 00 00 00 03 53 45 4C  45 43 54 20 2A 20 46 52     /....SELECT * FR
4F 4D 20 74 65 73 74 45  6E 61 62 6C 65 50 61 63     OM testEnablePac
6B 65 74 44 65 62 75 67  20 57 48 45 52 45 20 69     ketDebug WHERE i
64 3D 33                                             d=3

send at 2017-10-04T16:48:02.458Z
11 00 00 00 03 73 65 74  20 61 75 74 6F 63 6F 6D     .....set autocom
6D 69 74 3D 31                                       mit=1

send at 2017-10-04T16:48:02.459Z
0F 00 00 00 03 53 45 4C  45 43 54 20 27 65 72 72     .....SELECT 'err
6F 72 27                                             or'

Query is: SELECT 'error'
	at org.mariadb.jdbc.internal.util.LogQueryTool.exceptionWithQuery(LogQueryTool.java:119)
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:162)
	at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:326)
	... 29 more
Caused by: java.net.SocketException: Software caused connection abort: recv failed
	at java.net.SocketInputStream.socketRead0(Native Method)
	at java.net.SocketInputStream.socketRead(SocketInputStream.java:116)
	at java.net.SocketInputStream.read(SocketInputStream.java:171)
	at java.net.SocketInputStream.read(SocketInputStream.java:141)
	at java.io.BufferedInputStream.fill(BufferedInputStream.java:246)
	at java.io.BufferedInputStream.read1(BufferedInputStream.java:286)
	at java.io.BufferedInputStream.read(BufferedInputStream.java:345)
	at org.mariadb.jdbc.internal.io.input.StandardPacketInputStream.getPacketArray(StandardPacketInputStream.java:238)
	at org.mariadb.jdbc.internal.io.input.StandardPacketInputStream.getPacket(StandardPacketInputStream.java:208)
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1299)
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1280)
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:159)
	... 30 more

Using logging options

Driver rely on Slf4j framework. Slf4j is an abstraction for logging, permitting to use the logger implementation that have your preference.

Dependencies must be set using maven or be defined in classpath.

Example using Logback implementation and maven :

        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>[1.4.0,1.7.25]</version>
        </dependency>
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-classic</artifactId>
            <version>1.2.3</version>
        </dependency>

Configurations are implementation dependant, but, most of them need to indicate the package name that have to be logged, and log level. Driver package is "org.mariadb.jdbc".

Be careful with "trace" level, purpose is to log all exchanges with server. This mean huge amount of data. Bad configuration can lead to problems, like quickly filling disk.

Log level on slf4j are trace, debug, info, warn or error.

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

<?xml version="1.0" encoding="UTF-8"?>

<configuration>

    <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
        <encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">
            <pattern>%d{yyyy-MM-dd HH:mm:ss} [%thread] %-5level %logger{36} - %msg%n</pattern>
        </encoder>
    </appender>

    <logger name="org.mariadb.jdbc" level="trace" additivity="false">
        <appender-ref ref="STDOUT"/>
    </logger>

    <root level="error">
        <appender-ref ref="STDOUT"/>
    </root>

</configuration>

Last step is to indicate driver to generate logging, setting options "profileSql" or "log" to true.

Exemple of generated logs :

2017-10-04 19:13:44 [main] TRACE o.m.j.i.i.o.StandardPacketOutputStream - send: conn=7548(M)
33 00 00 00 03 49 4E 53  45 52 54 20 49 4E 54 4F     3....INSERT INTO
20 44 72 69 76 65 72 74  33 30 20 28 74 65 73 74      Drivert30 (test
29 20 56 41 4C 55 45 53  20 28 27 72 6F 6C 6C 6D     ) VALUES ('rollm
65 62 61 63 6B 27 29                                 eback')

2017-10-04 19:13:44 [main] TRACE o.m.j.i.i.i.StandardPacketInputStream - read: conn=7548(M)
07 00 00 01                                          ....
00 01 03 01 00 00 00                                 .......

2017-10-04 19:13:44 [main] TRACE o.m.j.i.i.o.StandardPacketOutputStream - send: conn=7548(M)
09 00 00 00 03 52 4F 4C  4C 42 41 43 4B              .....ROLLBACK

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 net_write_timeout 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:

  • if your server version >= MariaDB 10.1.2, 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, net_write_timeout 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 afterwards.
  • 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()

Comments

Comments loading...