Bug #74271 Generate post-4.1 hash for mem users when old_passwords=1

• Submitted: 8 Oct 2014 12:24
• Modified: 16 Oct 2014 18:29
• Reporter: Daniël van Eeden (OCA)
• Status: Verified
• Category: MySQL Enterprise Monitor: Agent
• Severity: S3 (Non-critical)
• Version: 3.0.14
• OS: Any
• CPU Architecture: Any

[8 Oct 2014 12:24] Daniël van Eeden

Description:
When a server has old_passwords set to 1 then creating a general and/or limited user fails.

2014-10-08 14:15:15,677  WARN [main:com.mysql.etools.agent.collection.MemMysqlUserManager] Failed to manage extra mysql users due to exception.
org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [CREATE USER ?@? IDENTIFIED BY ?]; SQL state [HY000]; error code [1827]; The password
hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function.; nested exception is java.sql.SQLException: The password hash doesn't have th
e expected format. Check if the correct password algorithm is being used with the PASSWORD() function.

How to repeat:
Run mysql 5.6.20 with old_passwords=1

Suggested fix:
run "set old_passwords=0" if old_passwords=1 before creating the general/limited users. 

Running "set old_passwords=2" might work, but that might be less reliable because of missing rsa keys.

alternate fix: return a clear error message which tells the user to stop using the deprecated and very weak pre-4.1 password hashing algorithm.

[16 Oct 2014 18:29] MySQL Verification Team

Hello Daniël,

Thank you for the report.
Issue observed when server is running with old_password=1, and when trying to install agent(Agent Account Creation).

Thanks,
Umesh

[16 Oct 2014 18:30] MySQL Verification Team

// from logs/mysql-monitor-agent-full.log

2014-10-16 23:53:51,340  WARN [main:com.mysql.etools.agent.collection.MemMysqlUserManager] Failed to manage extra mysql users due to exception.
org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [CREATE USER ?@? IDENTIFIED BY ?]; SQL state [HY000]; error code [1827]; The passwo
rd hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function.; nested exception is java.sql.SQLException: The password hash doesn't h
ave the expected format. Check if the correct password algorithm is being used with the PASSWORD() function.
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:602)
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:811)
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:867)
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:875)
	at com.mysql.etools.agent.collection.MemMysqlUserManager$1.execute(MemMysqlUserManager.java:175)
	at com.mysql.etools.agent.collection.ReplicationControl.executeWithoutReplication(ReplicationControl.java:31)
	at com.mysql.etools.agent.collection.MemMysqlUserManager.generateMysqlPrivilegesForUser(MemMysqlUserManager.java:191)
	at com.mysql.etools.agent.collection.MemMysqlUserManager.manageUser(MemMysqlUserManager.java:129)
	at com.mysql.etools.agent.collection.MemMysqlUserManager.manageIndividualMysqlUser(MemMysqlUserManager.java:124)
	at com.mysql.etools.agent.collection.MemMysqlUserManager.manageMysqlUsers(MemMysqlUserManager.java:77)
	at com.mysql.etools.agent.collection.MemMysqlUserManager.tryManageMysqlUsers(MemMysqlUserManager.java:56)
	at com.mysql.etools.agent.collection.MysqlConnection$State.createNewJdbc(MysqlConnection.java:417)
	at com.mysql.etools.agent.collection.MysqlConnection$State.checkJdbc(MysqlConnection.java:399)
	at com.mysql.etools.agent.collection.MysqlConnection$State.changeJdbcConnectorSettings(MysqlConnection.java:599)
	at com.mysql.etools.agent.collection.MysqlConnection.changeJdbcConnectorSettings(MysqlConnection.java:1147)
	at com.mysql.etools.agent.order.MysqlConnectionService.editConnection(MysqlConnectionService.java:174)
	at com.mysql.etools.agent.order.MysqlConnectionService.addOrUpdateConfiguration(MysqlConnectionService.java:128)
	at com.mysql.etools.agent.order.MysqlConnectionService.addOrUpdateConfiguration(MysqlConnectionService.java:191)
	at com.mysql.etools.agent.AgentConnectionService.open(AgentConnectionService.java:67)
	at com.mysql.etools.agent.runtime.AgentConnectionUtility.createOrUpdateConnection(AgentConnectionUtility.java:353)
	at com.mysql.etools.agent.runtime.AgentConnectionUtility.launch(AgentConnectionUtility.java:325)
	at com.mysql.etools.agent.runtime.Main.main(Main.java:147)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)