Using Spring JDBCTemplate with MariaDB Connector

Java web application using Spring.

We are currently using MySQL and wish to migrate to MariaDB. So we also planned to migrate from MySQL Connector to MariaDB Client.

Our software uses multiple databases on a same server, and some tables can have the same names, but on two different databases. On MySQL Connector, that was not a problem. We simply used a datasource that was already connected to the right database, and JDBCTemple knows that it has to search for the table in the current schema.

MariaDB Client, on the other hand, doesn't use schema to differentiate databases, it uses catalog. Spring does not seems to be aware of that. So when we test our software after the migration, JDBCTemplate finds multiple tables with the same name, since the schema is always null, and seems the just use the last one on the list to get the table's metadatas. Which obviously leads to unexpected errors everywhere.

We manage to correct this behavior by adding withCatalogName the the JDBCTemplace calls (see code below), but doing so at large would not be very elegant solution.

    SimpleJdbcInsert insertStatement = new SimpleJdbcInsert(getJdbcTemplate());
    [...] // business code
    insertStatement.executeBatch(sqlParameterSource); // fails
    insertStatement.withCatalogName("dbName").executeBatch(sqlParameterSource); // succeeds

So my question is, is there a way to tell Spring that we are using the MariaDB Client, and that it should always use the catalog and not the schema to get metadatas ?

We did set up the URL connection with the jdbc:mariadb prefix already, so Spring is aware that we are using MariaDB and not MySQL.

We can't realisticly modify our software so everything is in the same database. Even just changing table names to unique names accross the serveur would cost a lot more than what we planned for the MariaDB migration.

Edit : we also already set org.mariadb.jdbc.Driver as the new driver in the connection properties.

Edit 2: found the problem, we used nullDatabaseMeansCurrent in the connection properties so that Spring can access the table metadata without fuss, but this property is not supported by MariaDB connector. Don't know yet if there is a workaround.

When using metadata, connector request metadata depending on parameters. For example DatabaseMetaData.html#getColumns when setting not catalog and table name will search all tables corresponding in all catalogs, schema setting will be ignored.

mysql connector has 2 options that mariadb connector don't have : nullDatabaseMeansCurrent in order to search for current database even when no database is set, and databaseTerm that indicate that use setting catalog or schema.

I imagine that you use either one of these option, either schema is set with nullDatabaseMeansCurrent or using setting databaseTerm and connector then use schema parameter.

This second part will be implemented in https://jira.mariadb.org/projects/CONJ/issues/CONJ-1088. It might be the time to create an issue on jira to have nullDatabaseMeansCurrent support as well

I´m not 100% sure here but you need to change the spring properties/.yml file and tell spring wich sql driver and db will use. like this(just change postgre or mysql for mariaDB.):

spring:
 datasource:
 username: myuser
 password: secret
 url: jdbc://postgresql://localhost:5432/mydatabase
 driver-class-name: org.postgresql.Driver