Today I thought I would grant permissions to use a mysql database. I did the regular thing, but it didn’t work:
mysql> grant all on *.* to 'user'@'169.254.1.1' identified by 'somesecret'; ERROR 1827 (HY000): The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function.
The reason turned out to be this:
[root@mysqlserver ~]# grep passwords /etc/my.cnf | old_passwords = 1
I don’t know how that affects it, but way to grant permissions is to override that setting:
mysql> set old_passwords=0; Query OK, 0 rows affected (0.00 sec) mysql> grant all on *.* to 'user'@'169.254.1.1' identified by 'somesecret'; Query OK, 0 rows affected (0.01 sec)
And viola it works!