MySQL Bugs: #67325: Impossible to open connection for expired password user in MySQL 5.6

Bug #67325	Impossible to open connection for expired password user in MySQL 5.6
Submitted:	22 Oct 2012 13:23	Modified:	28 Jan 2013 20:38
Reporter:	Alfredo Kojima	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / C++	Severity:	S3 (Non-critical)
Version:	1.0, 1.1.1	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In MySQL 5.6 a new ALTER USER.. EXPIRE PASSWORD feature was added.
 http://dev.mysql.com/doc/refman/5.6/en/alter-user.html

This makes connections for users with expired password always return an error for any queries except SET PASSWORD until SET PASSWORD is issued.

The problem is that conn/c++ executes some commands on the connection after it's opened, making it impossible for such users to get an open connection to change the password.

How to repeat:
Try to connect to a MySQL 5.6 server using an account with a expired password.

Thank you for the report.

Verified as described.

I used following code:

#include <stdlib.h>
#include <iostream>
#include <sstream>
#include <stdexcept>

#include "mysql_connection.h"

#include <cppconn/driver.h>
#include <cppconn/exception.h>
#include <cppconn/resultset.h>
#include <cppconn/statement.h>
#include <cppconn/prepared_statement.h>

#define EXAMPLE_HOST "127.0.0.1:13000"
#define EXAMPLE_USER "root"
#define EXAMPLE_PASS ""
#define EXAMPLE_DB "mysql"

using namespace std;

int main(int argc, const char **argv)
{

	string url(argc >= 2 ? argv[1] : EXAMPLE_HOST);
	const string user(argc >= 3 ? argv[2] : EXAMPLE_USER);
	const string pass(argc >= 4 ? argv[3] : EXAMPLE_PASS);
	const string database(argc >= 5 ? argv[4] : EXAMPLE_DB);

	cout << "Connector/C++ tutorial framework..." << endl;
	cout << endl;

	try {

		/* INSERT TUTORIAL CODE HERE! */
		sql::Driver *driver = get_driver_instance();
		
		std::auto_ptr<sql::Connection> con(driver->connect((sql::SQLString)url, (sql::SQLString)user, (sql::SQLString)pass));

	} catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
		return EXIT_FAILURE;
	}
	
	cout << "Done." << endl;
	return EXIT_SUCCESS;
}

If turn general query log to on, then see what is in we can find:

| 2012-11-30 00:30:02 | [root] @ localhost [127.0.0.1]     |        15 |         1 | Connect      | root@localhost on                                       |
| 2012-11-30 00:30:02 | root[root] @ localhost [127.0.0.1] |        15 |         1 | Query        | set autocommit=1                                        |
| 2012-11-30 00:30:02 | root[root] @ localhost [127.0.0.1] |        15 |         1 | Query        | SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ |
| 2012-11-30 00:30:02 | root[root] @ localhost [127.0.0.1] |        15 |         1 | Query        | SHOW SESSION VARIABLES LIKE 'lower_case_table_names'    |
| 2012-11-30 00:30:02 | root[root] @ localhost [127.0.0.1] |        15 |         1 | Quit         |                                                         |

Noted in 1.1.2 changelog.

Connector/C++ applications could not handle connecting to the server
using an account for which the password had expired. Connector/C++
now supports three new connection options:

*  OPT_CAN_HANDLE_EXPIRED_PASSWORDS: If true, this indicates to the
  driver that the application can handle expired passwords.

  If the application specifies OPT_CAN_HANDLE_EXPIRED_PASSWORDS but the
  underlying libmysql library does not support it, the driver returns
  sql::mysql:deCLIENT_DOESNT_SUPPORT_FEATURE(820).

*  preInit: A string containing queries to run before driver
  initialization.

*  postInit: A string containing queries to run after driver
  initialization. 

A new file driver/mysql_error.h is being added to the MSI package.
This file defines an enum DRIVER_ERROR, which contains the definition
of deCL_CANT_HANDLE_EXP_PWD.

In addition to the preceding changes, these problems with
Statement::executeUpdate were fixed:

* If Statement::executeUpdate executed multiple statements, the
  connection became unusable.

* There was no exception if one of queries returned a resultset. Now
  executeUpdate returns and update count for the last executed query.