How to fix java.sql.SQLException: Server is running in --secure-auth mode, but 'user'@'host' has a password in the old format; (...)?

Question

After upgrading MySQL 5.1 to 5.6, trying to start JBoss failed with this exception:

java.sql.SQLException: 
  Server is running in --secure-auth mode, but 'user'@'localhost' 
  has a password in the old format;  please change the password to the new format

How to fix this issue?

Solution 1

This issue was fixed by:

1) Update the value of the password hash of the 'user' user

UPDATE mysql.user set password = PASSWORD('my password text') where user= 'user';

2) Stop 'mysql' server

/etc/init.d/mysql stop

3) Set the flag old_passwords=1 on the /etc/my.cnf file

4) Start 'mysql' server

/etc/init.d/mysql start

See also documentation: MySQL password hashing

Solution 2

First, you need to locate the my.cnf file, which should be at the following path:

MySQL Server 5.6\my.cnf

Next find the line which says old_passwords=0 and change it to old_passwords=1. This will tell MySQL to use a password hashing algorithm compatible going all the way back to version 4.0, which covers your use case.

Now when you start your server, the password problem should go away. If you want to upgrade your passwords to latest version, then you can use SET PASSWORD for each user with the flag old_passwords=0.

Please keep in mind that MySQL upgraded their password algorithm for security reasons, so you should not view setting old_passwords=1 as a long term solution.

See here for more information.