Debian 6 x86_64, Connector/J 5.1.20

standalone test case

I have also managed to create a standalone test case.

If you point it to a Mysql db, it connects, if you point it to MariaDB, it fails.

I did some more debugging.

The connection failure is caused by Mariadb not sending the scramble with the
AuthSwitchRequest packet.

According to http://dev.mysql.com/doc/internals/en/connection-phase-packets.html#packet-Protocol::AuthSwitchRequest the auth plugin data is not optional (even though a scramble may already have been sent during the initial connect.), and Connector/J 5.1.20 has the same expectation.

The attached patch (against 5.5) makes sure that scramble is always sent when replying to a change user command.

This patch fixes the attached test case.

Proposed fix

Hi,

Thanks for the analysis, but I have a question about the test case you provided before we can proceed.
Could you please elaborate a bit on how it should work from your point of view, that is, what is 'success' and what is 'fail'?
The test case attempts to connect to 'db' as stoty@localhost with 'badpassword'.
From all I can see, there is no other logic in the test case.
So, is the user with the password supposed to exist and have enough grants for the 'db'? If it's not, what does 'success' mean in your experiments?

You are right on both cases. The 'badpassword' in the examples is the actual (working) password, though in hindsight it was not a good choice for clarity.

All the java test case does is try to get java.sql.Connection object via the com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource object, thus emulating, in the simplest possible way, the operation of a java Connection pooling library that uses the ConnectionPoolDataSource interface.

To use the test case, simply edit the .java file, substitute a known working server, db, username, and password (i.e. one that works from the same host via the mysql cli) and run it.

The corrent behaviour is getting a connection, printing some data about, and then exiting (as it happens with MySql).
The buggy behaviour with MariaDB is an authentication exception.

The java code in itself is not interesting, it is just a simple way to issue a "change user" command to the database, If you wrote some C code that logs in to the DB server, then issues the "change user" command, the bug would trigger the same way.

If you are not familiar with Java, I can give you the exact command sequence to compile and run the test.

Thanks, I'm no expert in Java, but to this extent I'm passable.

So, it was my theory at the beginning that the user/password/database combination is supposed to exist, and i created it, and the test went equally okay both with MySQL and MariaDB:

elenst@ubuntu12-04:~/mdev5155$ java MariaDBTestCase
Server name: MySQL
Server version: 5.5.33a-MariaDB

That made me doubt that I understood the idea of your test correctly.

Although, I have JDBC of 5.1.16-2, maybe it's the difference. I'll try 5.1.20.

Very strange. I could reproduce the failure on two different computers, with three different MariaDB builds (debian, fedora, and source)
Did your test account have a password set ? The account needs to have a non-empty password for the bug to trigger.

Yes, here is the setup on MySQL side:

MariaDB [test]> create database db;
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> grant all on db.* to stoty@localhost identified by 'badpassword';
Query OK, 0 rows affected (0.00 sec)

Authentication via MySQL client also works as expected (fails without a password, passes with the password):

elenst@ubuntu12-04:/data/releases/mariadb-5.5.33a-linux-x86_64$ bin/mysql -ustoty db
ERROR 1045 (28000): Access denied for user 'stoty'@'localhost' (using password: NO)
elenst@ubuntu12-04:/data/releases/mariadb-5.5.33a-linux-x86_64$ bin/mysql -ustoty -p db
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.33a-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [db]>

The test case output:

elenst@ubuntu12-04:~/mdev5155$ java MariaDBTestCase
Server name: MySQL
Server version: 5.5.33a-MariaDB

The only difference on the test side is the server name:

elenst@ubuntu12-04:~/mdev5155$ diff -u orig/MariaDBTestCase.java MariaDBTestCase.java
— orig/MariaDBTestCase.java 2013-10-28 17:12:58.316098764 +0400
+++ MariaDBTestCase.java 2013-10-28 12:55:22.286407372 +0400
@@ -10,7 +10,7 @@

// Setting up the DataSource object
com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource ds = new com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource();

• ds.setServerName("server");
  + ds.setServerName("127.0.0.1");
  ds.setPortNumber(3306);
  ds.setDatabaseName("db");
  ds.setUser("stoty");

It's still with 5.1.16 though, I don't rule out that there is a difference. I'll try it out shortly.

I've tried it also, 5.1.16 indeed works.
5.1.20, and 5.1.26 both trigger the bug.

Right, I can also reproduce it with 5.1.26.

java.sql.SQLException: Access denied for user 'stoty'@'localhost' (using password: NO)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4190)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4122)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:927)
at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1709)
at com.mysql.jdbc.MysqlIO.changeUser(MysqlIO.java:872)
at com.mysql.jdbc.ConnectionImpl.changeUser(ConnectionImpl.java:1252)
at com.mysql.jdbc.ConnectionImpl.resetServerState(ConnectionImpl.java:5013)
at com.mysql.jdbc.jdbc2.optional.MysqlPooledConnection.getConnection(MysqlPooledConnection.java:180)
at com.mysql.jdbc.jdbc2.optional.MysqlPooledConnection.getConnection(MysqlPooledConnection.java:157)
at MariaDBTestCase.main(MariaDBTestCase.java:22)

The suggested patch fixes it for 5.1.26, but unfortunately breaks it for 5.1.16:

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Got packets out of order
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1013)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2619)
at com.mysql.jdbc.ConnectionImpl.configureClientCharacterSet(ConnectionImpl.java:1855)
at com.mysql.jdbc.ConnectionImpl.changeUser(ConnectionImpl.java:1159)
at com.mysql.jdbc.ConnectionImpl.resetServerState(ConnectionImpl.java:4676)
at com.mysql.jdbc.jdbc2.optional.MysqlPooledConnection.getConnection(MysqlPooledConnection.java:181)
at com.mysql.jdbc.jdbc2.optional.MysqlPooledConnection.getConnection(MysqlPooledConnection.java:158)
at MariaDBTestCase.main(MariaDBTestCase.java:22)

Nevertheless, the problem does exist.

Reproducible with the provided test case (replace server1 with whatever you have, I used 127.0.0.1) and with Connector/J 5.1.26, apparently with 5.1.20 too.

proposed patch V2

I have attached a new version of the patch, that sends the scramble only if the change user packet does not have contain a password.

With this patch, the test case connects with both 5.1.16 and 5.1.20 driver.
The mysql command line client can also connect, so it does not completely brake other cases.

5.1.20 (that's where capture files come from, right?) breaks the protocol. In
the COM_CHANGE_USER packet there must be
<username><password><db><charset><plugin>. But in your capture files there's
no charset in the COM_CHANGE_USER packet, so the first two letters of the
plugin name ("my") are interpreted as the charset number, and the plugin name is read as "sql_native_password".

This was fixed as a part of http://bugs.mysql.com/bug.php?id=41752, according to the changelog in 5.1.22.

Because of that bug, the server was sending AuthSwitchRequest packet (to tell
the client to switch from "sql_native_password" to "mysql_native_password"). After
5.1.22 it shouldn't do that.

A protocol specifies, indeed, that the auth plugin data is not optional. But
the content of the data is totally up to the plugin, and it might be empty. In
fact, the protocol of the mysql_native_password plugin specified explicitly
that for COM_CHANGE_USER packet, the scramble is not resent, but taken from
the existing connection context. See (this is MySQL code, not MariaDB):

http://bazaar.launchpad.net/~mysql/mysql-server/5.5/view/head:/sql-common/client.c#L4289

So, MySQL implementation of server-side mysql_native_password had a bug of
sending the scramble in AuthSwitchRequest packet (that we have fixed eventually,
as it broke embedded server). And the Connector/J developer simply implemented
"whatever works" without checking protocol specs. It happened to work even
with a broken COM_CHANGE_USER packet (without charset) and with incorrect
implementation of the mysql_native_password plugin, but it failed with MariaDB.

Now, without AuthSwitchRequest it doesn't work either, because Connector/J
does not send the password at all in the COM_CHANGE_USER packet, see the
protocol description and look at the packet captures — the password is sent
empty, no wonder the server replies with "Access denied". That's why
Connector/J 5.1.26 cannot change_user with MariaDB.

But with MySQL it works, because of the same bug — MySQL always sends
AuthSwitchRequest packet for every COM_CHANGE_USER, even when it is
unnecessary, because the client was already using the correct plugin. It is
wrong, AuthSwitchRequest should never request a change to the same plugin that
the client is already using. But this bug and Connector/J bug together allow
Connector/J to work. Albeit with an extra packet sent over the wire.

I'll report a bug to MySQL about it.

Yes, the attached dumps are recoring 5.1.20 behaviour.

I have attached a patch against Connector/J 5.1.27 that makes it behave according to your description of the protocol.
With this patch, the test case connects successfully to MariaDB 5.5.33a.
Tomorrow I will test it against MySQL as well.

tenative patch for Connector/J 5.1.27

I have successfully tested the patch against Mysql 5.5.31 as well.

Yes, I've already reported a bug at bugs.mysql.com, let's hope it'll be fixed in the next release.

Still reproducible with Connector/J 5.1.32 and MariaDB 10.1 (10.1.4 pre-release).
The upstream bug has been staying in 'Verified' for almost 1.5 years by now.

upstream bug reports a fix in Connector/J 5.1.36 (untested by me)

Fixed in upstream jdbc connector.