Comments - About MariaDB Connector/J

Hi, I am using version of [ mariadb-java-client-1.4.6.jar ]

And I wonder if this maria connector is compatible to which version of mysql server and mariadb server.

Thanks.

Is there a way to specify keystore and truststore resources using the classpath like the serverSslCert option allows? This would allow applications to be more portable, to use bundled resources rather than files on the filesystem for key/trust stores.

Is there a driver connection string parameter to set the Isolation Level used for all connections? Something like the following jdbc:mysql:mymariadb:3306/MyDB?IsolationLevel=READ_UNCOMMITTED;

I've tried with combinations of IsolationLevel, isolation_level, transactionIsolation, transaction_isolation, tx_isolation, and with the parameter value as READ_COMMITTED, and READ UNCOMMITTED (with a space) but nothing sets the transaction level

If that can be set globally, you can set global variable tx_isolation There is no direct configuration option to do that, but you can set that with the option "sessionVariables". that must be something like jdbc:mariadb:host:3306/MyDB?sessionVariables=tx_isolation=READ_UNCOMMITTED;

I am using the following connection string

jdbc:mariadb:${db.server.name}:${db.server.port}/${db.name}?serverSslCert=<path to pem file>&jdbcCompliantTruncation=false&verifyServerCertificate=true&useSSL=true&enabledSslProtocolSuites=TLSv1.1&autoReconnect=true&disableSslHostnameVerification=true

I use this connection in an application which also acts as a client to servers requiring client authentication; the authentication information is provided via javax.net.ssl.keyStore(Password) properties which point to a JKS file. When defined, these properties interfere with the database connection and cause it to fail with

Unsupported record version Unknown-0.0

but when I run without these properties the connection works fine. I surmise that the driver is using these properties instead of the provided serverSslCert property; is there a way to make the driver ignore the javax.net.ssl.* properties and work with what is given in its serverSslCert property?

I imagine that the double slash have been removed due to posting format.

The connection string seems right.

That is strange because this message seems to correspond to this error https://jira.mariadb.org/browse/MDEV-12190 : When server is compiled with yassl and connector send a TLSv1.2 version, then yassl implementation was lost and send this exact message. these has been corrected since MDEV-12190. It seems that options are not taken in account. maybe the & to replace & when using jboss for example ?

We are using a 10.1.x version of MariaDb so perhaps you have identified the problem. I will see about upgrading and hope that this problem will be resolved.

The "&" are included because I took this out of an XML document where the '&' characters have to be encoded. I will check with our admins to see what version of MariaDb we are running. Thank you for your suggestion.

MDEV-12190 has been backported recently 10.1 too, so an update to latest 10.1.x version must be sufficient.

After doing a fair amount of web searching, and a log of trial and error, I got an SSL connection working with the connection to mydb defined in my persistence.xml file. I'm using MariaDB 10.3 (Alpha) and Hibernate 5.2.12, configured as JPA rather than Hibernate. I'm using IntelliJ and put "myCert.pem" into my resources directory so it ends up on the top level of my classpath. Note that the CN used in the certificate MUST match the domain used for connection. (So far, I've only used "localhost". Another server is tomorrow's task. You'll see localhost in the example, too.) The line in persistence.xml that makes the magic happen is:

<property name="javax.persistence.jdbc.url" value="jdbc:mariadb:localhost:3306/mydb?useSSL=true&amp;serverSslCert=classpath:myCert.pem"/>

The last problem was that I didn't remember initially that the XML file needed &amp; in the String. Note that user and password are already javax.persistence.jdbc properties.

When configure multiply server, how to let connector print log which server is connected?

The link above in:

"Pre-built .jar files can be downloaded from: https://mariadb.com/my_portal/download/java-client"

defaults you to the old version: 1.4.6, instead of 1.5.3.

Hi, Is there a place for a session variable such as application user in the Connector/J script? I mean 'con.setClientInfo' such as what exists in SAP HANA here: https://help.sap.com/saphelp_hanaplatform/helpdata/en/e9/0fa1f0e06e4840aa3ee2278afae16b/content.htm and https://help.sap.com/hana_one/html/_m__s_e_s_s_i_o_n__c_o_n_t_e_x_t.html Thanks!

Hi, If performance_schema variable is activated, in Table performance_schema.session_connect_attrs, for each connection, you will have :

• _client_name (example : MariaDB connector/J)
• _client_version ( 1.5.4-SNAPSHOT)
• _os (example : Windows 10)
• _pid (example : 1636)
• _thread (example : 13)
• _java_vendor (example : Oracle Corporation)
• _java_version (example : 1.8.0_91)

You can add specific information using option "connectionAttributes"

Sorry, updating the answer delete your comment. New version 1.5.4, Connection.setClientInfo() permit 3 differents Session-Specific Client Information :

• ApplicationName
• ClientUser
• ClientHostname

This without having performance_schema variable activated.

Hi again, When will version 1.5.4 be released?

version 1.5.4 is available on maven central or will be available on site download in a few hours.

Great! So, I have a MariaDB 10.1.14 and the session_connect_attrs table does not have these three new parameters, and I create a short javascript that uses the new connector to make a short connection, make a simple select and then logout, what will happen? I tried to add new variables to this table but my user didn't have the permission to do it, nor did the root user. Obviously, I switched on the performance_schema.

I may have not been clear.

There is in fact 2 differents way to achieve that : standard JDBC : using connection , you can add some specific informations:

Properties properties = new Properties();
properties.setProperty("ApplicationName", "your application");
properties.setProperty("ClientUser", "your user");
properties.setProperty("ClientHostname", "myhost");

connection.setClientInfo(properties); and so after that, you will be able to retrieve those informations : String currentHost = connection.getClientInfo("myhost");

other solution is the first explain : If performance_schema variable is activated on server, in Table performance_schema.session_connect_attrs, for each connection, you will have :

• _client_name (example : MariaDB connector/J)
• _client_version ( 1.5.4-SNAPSHOT)
• _os (example : Windows 10)
• _pid (example : 1636)
• _thread (example : 13)
• _java_vendor (example : Oracle Corporation)
• _java_version (example : 1.8.0_91)

You can add specific information using option "connectionAttributes" . Example connecting with url "jdbc:mariadb:localhost/db?user=xxx&connectionAttributes=key1:value1,key2,value2"

you will have then have 2 additionnals informations :

• key1 with value "value1"
• key2 with value "value2"

It works great, thank you so mcuh for the help and for all the fast responses!

Excellent, thanks!

Running the build of 1.4.6, errors signal a missing "testj" database. And looking at the code, thats what used in the UTs.

Either the page is to be corrected or the code changed

right, this is changed to "testj" according to unit tests

As a result, when using v. 1.2.2 driver and trusting auto-registration of JDBC driver, one gets a "No suitable driver found" error without explanation. Only when doing Class.forName("org.mariadb.jdbc.Driver") - the reason for the error shows up. So either the dependency should be moved or SLF4J should be added to the requirements in this article.

I downloaded and installed MariaDB version 5.5.5-10.16. So far I checked mostly what an application I have needs. While pleased, one thing I could not do: check primary key and auto-increment of a table with auto-increment. I used client mariadb-java-client-1.1.3 & 1.1.7.

When I used drivers mysql-connctor-java-3.1.14 and 5.0.8, I get the primary key but still no auto-increment. Any thoughts? Thank you.

Not sure what you're refering to when speaking about primary keys. For autoincrement, the things are standard, and you could use getGeneratedKeys() as described here for example http://dev.mysql.com/doc/connector-j/en/connector-j-usagenotes-last-insert-id.html .

Thank you for the link.

The situation in my case is different. Here is a simplified code for my example where normally I get the auto-increment:

ResultSet rscol = null;

String autoInc =null;

int colAutoInc=0;

Statement stmt = cnx.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

ResultSet rs = stmt.executeQuery("Select * from " + db_table);

DatabaseMetaData dbmd =cnx.getMetaData();cnx=Connection

for (i = 1; i <=numberOfColumns; i++) {

rscol = dbmd.getColumns(tableCatalog, tableSchema, db_table, columnName);

while (rscol.next()) {

autoInc = rscol.getString(12);returns "auto-increment" if column is in fact auto-increment

if (!autoInc.equals("")) colAutoInc=i;

}

}

http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getColumns%28java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String%29

It says the column #12 is REMARKS, which does not have much meaning

To retrieve primary key, there is a DatabaseMetaData.getPrimaryKeys() that does exactly that. http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getPrimaryKeys%28java.lang.String,%20java.lang.String,%20java.lang.String%29

I built an application for productivity that interfaces MySQL and MS-SQL. Drivers from MySQL and Microsoft are used and work fine. User can switch between MySQL and MS-SQL if he chooses to. Today I am in the process of adding MariaDB to the list of databases. I downloaded 2 clients from MariaDB: "mariadb-java-client-1.1.3.jar and mariadb-java-1.1.7.jar". I could not connect with either one to mySQL database. The drivers were placed in my java JRE directory like the mySQL driver.

Database used: MySQL, Vesion:4.0.21.-nt-max-log

Currently using: MySQL-AB JDBC Driver Version: mysql-connector-java-3.1.14 ($Date:2006-10-18 17:40:15 +0200 (Wed, 18 Oct 2006)$,$Revision:5888$)

That driver was not tested with server version 4.0 (nor 4.1 nor 5.0) an is not likely to work with any server version for which lifecycle support ended . MySQL Sever 4.0 is out of support maybe for 7 or 8 years something like that.

Thank you for commenting. Yes I had MSQL Server 4.0 running for at least 12 yrs on an XP-machine, and few years before that on a Window 2000. I will download a newer version to continue development on my application. I have a question about the MariaDB Client. When I run my application, I see that MariaDB client that I put in my Java JRE directory already loaded. It does not happen with MySQL driver that I have been using. So the question is: does MariaDB clients have a feature that Java recognizes and loads the Client automatically?

Yes, autoregistration is part of JDBC 4.0, which came wiith Java 6 , in 2006 The connector/j driver you are using is probably quite old if it does not have this feature.

The exception I have is "java.nio.BufferUnderflowException"

Yeah, in the long past the packet server sent was on new connection was shorter according. So underflow exception can be explained.

Thank you.

Hi,

I have posted a question on stackoverflow.com about a JDBC url for load balanced setup of MariaDB (specifically referring to MariaDB Galera), you may find it at http://stackoverflow.com/questions/19629690/what-is-the-right-mariadb-galera-jdbc-url-properties-for-loadbalance.

Since the JDBC Client is already at ver 1.1.7, this is just a followup comment if there had been any updates to the JDBC client for connecting to a clustered MariaDB (Galera) using the MariaDB JDBC Client just like how it was done to connect to MySQL Cluster with more than one IP of the SQL node e.g. jdbc:mysql:loadbalance:192.168.1.2:3306,192.168.1.3:3306/test?loadBalanceConnectionGroup=first&loadBalanceEnableJMX=true

Thanks a bunch for the effort.

Is the comment above still accurate? Looking at the source of org.mariadb.jdbc.MySQLResultSet.setFetchSize(int), I see the following:

public void setFetchSize(int rows) throws SQLException {
    // ignored - we fetch 'em all!
}

"we fetch'em all" is not accurate. However that fetch size is ignored here is accurate. The one fetch size that matters for streaming is in MySQLStatement, not in MySQLResultSet

Can someone update this page with the correct instructions?

Obviously calling ResultSet.setFetchSize(Integer.MIN_VALUE) will have no impact on the resultset.

Calling Statement.setFetchSize(Integer.MIN_VALUE) does the trick.

I updated the page with correct instructions.

My old java code uses:

Class.forName("com.mysql.jdbc.Driver");

but now it will cause

java.lang.ClassNotFoundException: com.mysql.jdbc.Driver

Could you tell me is this an issue in MariaDB connector or we should change our code to remove this statement after switch to MariaDB.

Thanks.

Yes, please remove this statement. It should work ok without it.

Is there a plan for key "zeroDateTimeBehavior" like in the original mysql client or a workaround?

I'm not sure what zeroDateTimeBehavior is however I can't see it listed in https://mariadb.atlassian.net/browse/CONJ

Perhaps you could add it as a request.