Connecting to MariaDB using JDBC / c3p0

Question

We are just in the process of switching from MySQL to MariaDb. I was hoping it will be as easy as updating the JDBC URL and the JDBC Driver but unfortunately this wasn't enough.

MariaDb version: 5.5.47-MariaDB

Config:

db.driver=org.mariadb.jdbc.Driver
db.url=jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&autoReconnect=true
db.user=user
db.password=secret

Driver:

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

c3po definition, nothing has changed here, it is the same as with MySQL:

<bean id="c3p0dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    <!-- access -->
    <property name="driverClass" value="${db.driver}" />
    <property name="jdbcUrl" value="${db.url}" />
    <property name="user" value="${db.user}" />
    <property name="password" value="${db.password}" />
    ....

The error:

    Last acquisition attempt exception: 
java.sql.SQLNonTransientConnectionException: Client does not support authentication protocol requested by server. Consider upgrading MariaDB client. plugin was = 
        at org.mariadb.jdbc.internal.util.ExceptionMapper.get(ExceptionMapper.java:123)
        at org.mariadb.jdbc.internal.util.ExceptionMapper.throwException(ExceptionMapper.java:69)
        at org.mariadb.jdbc.Driver.connect(Driver.java:110)
        at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:134)
        at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:182)
        at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:171)
        at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:137)
        at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)
        at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)
        at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)
        at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
Caused by: org.mariadb.jdbc.internal.util.dao.QueryException: Client does not support authentication protocol requested by server. Consider upgrading MariaDB client. plugin was = 
        at org.mariadb.jdbc.internal.protocol.authentication.DefaultAuthenticationProvider.processAuthPlugin(DefaultAuthenticationProvider.java:94)
        at org.mariadb.jdbc.internal.protocol.authentication.AuthenticationProviderHolder$1.processAuthPlugin(AuthenticationProviderHolder.java:69)
        at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.authentication(AbstractConnectProtocol.java:490)
        at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.handleConnectionPhases(AbstractConnectProtocol.java:448)
        at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.connect(AbstractConnectProtocol.java:359)
        at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.connectWithoutProxy(AbstractConnectProtocol.java:703)
        at org.mariadb.jdbc.internal.util.Utils.retrieveProxy(Utils.java:471)
        at org.mariadb.jdbc.Driver.connect(Driver.java:105)
        ... 8 more

I have tried few things already but so far nothing helps:

• Tried db.url=jdbc:mariadb://localhost...

When I try using new password format: update mysql.user set Password = PASSWORD('secret') where user = 'user';

I receive the following exception:

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
        at sun.reflect.GeneratedConstructorAccessor47.newInstance(Unknown Source)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:408)
        at com.mysql.jdbc.Util.getInstance(Util.java:383)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1023)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:997)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:983)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:928)
        at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2407)
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2328)
        at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:832)
        at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46)
        at sun.reflect.GeneratedConstructorAccessor17.newInstance(Unknown Source)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:408)
        at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:417)
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:344)
        at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:134)
        at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:182)
        at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:171)
        at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:137)
        at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)
        at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)
        at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)
        at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
Caused by: java.sql.SQLException: Unable to load authentication plugin ''.
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:935)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:932)
        at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1769)
        at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1288)
        at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2508)
        at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2346)
        ... 17 more

Any help would be appreciated.

Answer 1

After some additional tests I found out that updating to new password did not work:

MariaDB [test]> SELECT PASSWORD('123456'), OLD_PASSWORD('123456');
+--------------------+------------------------+
| PASSWORD('123456') | OLD_PASSWORD('123456') |
+--------------------+------------------------+
| 565491d704013245   | 565491d704013245       |
+--------------------+------------------------+

Turns out we had old_passwords option set in /etc/my.cnf. After turning it off and restarting the MariaDb the new PASSWORD() started working correctly and we can connect now. Hope it will be useful for someone in their debugging struggles.

> SHOW VARIABLES LIKE 'old_passwords';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | 0     |
+---------------+-------+

> SELECT PASSWORD('123456'), OLD_PASSWORD('123456');
+-------------------------------------------+------------------------+
| PASSWORD('123456')                        | OLD_PASSWORD('123456') |
+-------------------------------------------+------------------------+
| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 565491d704013245       |
+-------------------------------------------+------------------------+