Bug #57689 mysql_change_user() breaks user connection on older clients
Submitted: 24 Oct 2010 4:32 Modified: 20 Nov 2010 17:15
Reporter: Elena Stepanova Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.5.7-rc OS:Any
Assigned to: Georgi Kodinov CPU Architecture:Any
Tags: regression

[24 Oct 2010 4:32] Elena Stepanova
Description:
Client libraries of version 5.1 or 5.5.6- fail to process reply to COM_CHANGE_USER sent by the new server. It causes further statements to fail with 'Lost connection to MySQL server during query' or 'Got packets out of order' errors.

Output for tests from 'How to repeat' section:

-------------------------
Variant 1 (mysqltest):
-------------------------
# Calling change_user...
main.test                                [ fail ]
        Test ended at 2010-10-24 05:39:56

mysqltest: At line 3: query 'SELECT 1' failed: 2013: Lost connection to MySQL server during query

-------------------------
Variant 2 (C test)
-------------------------
Calling change_user...
mysql_change_user: error= res=0
SELECT: error=Lost connection to MySQL server during query res=1

-------------------------
Variant 3 (Java test)
-------------------------

Got packets out of order

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Got packets out of order
at java.lang.reflect.Constructor.newInstance(Constructor.java:532)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.Util.getInstance(Util.java:384)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3566)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3498)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2562)
at com.mysql.jdbc.ConnectionImpl.configureClientCharacterSet(ConnectionImpl.java:1834)
at com.mysql.jdbc.ConnectionImpl.changeUser(ConnectionImpl.java:1141)
at com.mysql.jdbc.ConnectionImpl.resetServerState(ConnectionImpl.java:4630)
at com.mysql.jdbc.ReplicationConnection.resetServerState(ReplicationConnection.java:707)
at com.mysql.jdbc.jdbc2.optional.MysqlPooledConnection.getConnection(MysqlPooledConnection.java:183)
at com.mysql.jdbc.jdbc2.optional.MysqlPooledConnection.getConnection(MysqlPooledConnection.java:160)
at testsuite.regression.ConnectionRegressionTest.testBug34937(ConnectionRegressionTest.java:2278)

How to repeat:
-------------------------
Variant 1 (mysqltest)
Run the MTR test using mysqltest from 5.1 or 5.5.6- and server 5.5.7:
-------------------------

--echo # Calling change_user...
--change_user root,,test
SELECT 1;
--exit

# End of test

-------------------------
Variant 2 (C test)
Run with libmysqlclient from 5.1 or 5.5.6- and server 5.5.7:
------------------------

#include <stdio.h>
#include <mysql.h>

int main(void)
{
        MYSQL *mysql;
        int res;
        mysql = mysql_init(NULL);
        mysql = mysql_real_connect(mysql, "127.0.0.1", "root", NULL, "test", 3306, NULL, 0);
        printf("Calling change_user...\n");
        res = mysql_change_user(mysql, "root", NULL, "test");
        printf("mysql_change_user: error=%s res=%d\n", mysql_error(mysql), res);
        res = mysql_real_query(mysql, "SELECT 1", sizeof("SELECT 2")-1);
        printf("SELECT: error=%s res=%d\n", mysql_error(mysql), res);

        mysql_close(mysql);
}

------------------------
Variant 3 (Java test)
Use Connector/J 5.1.13 and server 5.5.7
------------------------
Run testsuite.regression.ConnectionRegressionTest.testBug34937
[25 Oct 2010 8:45] Georgi Kodinov
Can repeat this with the current 5.1 and 5.1 bugteam trees. It's a missing state in the new state diagram introduced by WL#1054.
[25 Oct 2010 13:11] Vladislav Vaintroub
See also Bug#57442. 
It looks like extra stuff that is in the packet is 0xfe'mysql_native_password' or similar.
[25 Oct 2010 15:13] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/121832

3268 Georgi Kodinov	2010-10-25
      Bug #57689: mysql_change_user() breaks user connection on older clients
      
      COM_CHANGE_USER was always handled like an implicit request to change the
      client plugin, so that the client can re-use the same code path for both normal 
      login and COM_CHANGE_USER. However this doesn't really work well with old 
      clients because they don't understand the request to change a client plugin.
      
      Fixed by implementing a special state in the code (and old client issuing 
      COM_CHANGE_USER). In this state the server parses the COM_CHANGE_USER 
      package and pushes back the password hash, the user name and the database 
      to the input stream in the same order that the native password server side plugin 
      expects. As a result it replies with an OK/FAIL just like the old server does thus
      making the new server compatible with older clients.
      
      No test case added, since it would requre an old client binary. Tested using 
      accounts with and without passwords. Tested with a correct and incorrect 
      password.
[27 Oct 2010 8:43] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/122048

3101 Georgi Kodinov	2010-10-25
      Bug #57689: mysql_change_user() breaks user connection on older clients
      
      COM_CHANGE_USER was always handled like an implicit request to change the
      client plugin, so that the client can re-use the same code path for both normal 
      login and COM_CHANGE_USER. However this doesn't really work well with old 
      clients because they don't understand the request to change a client plugin.
      
      Fixed by implementing a special state in the code (and old client issuing 
      COM_CHANGE_USER). In this state the server parses the COM_CHANGE_USER 
      package and pushes back the password hash, the user name and the database 
      to the input stream in the same order that the native password server side plugin 
      expects. As a result it replies with an OK/FAIL just like the old server does thus
      making the new server compatible with older clients.
      
      No test case added, since it would requre an old client binary. Tested using 
      accounts with and without passwords. Tested with a correct and incorrect 
      password.
[13 Nov 2010 16:16] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:alexander.nozdrin@oracle.com-20101113152450-2zzcm50e7i4j35v7) (merge vers: 5.6.1-m4) (pib:21)
[13 Nov 2010 16:28] Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (pib:21)
[20 Nov 2010 17:15] Paul Dubois
Noted in 5.5.8 changelog.

Clients using a client library older than MySQL 5.5.7 suffered loss
of connection after executing mysql_change_user() while connected to
a 5.5.7 server.
[16 Dec 2010 22:25] Bugs System
Pushed into mysql-5.5 5.5.9 (revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (version source revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (merge vers: 5.5.9) (pib:24)