Description:
Migrating from mySQL 4.1.7 to mySQL 4.1.9 i backed up the mySQL databases
./mysqldump –-all-databases > /tmp/mysql_bkup.dat
and installed the new binary, before restoring the databases.
./mysql –u root < /tmp/mysql_bkup.dat
However my application which was attempting to connect through the mySQI C API could not connect.
Further investigation revealed that the problem was apparent from the command prompt as well. I could not connect with my username (retry) and password (retry):
ERROR 1045 (28000): Access denied for user 'retry'@'localhost' (using password: YES)
I had created my user retry with the following script
-- Set up users/passwords
SET PASSWORD FOR 'root'@localhost=OLD_PASSWORD('');
GRANT ALL PRIVILEGES ON *.* TO 'retry'@'localhost' IDENTIFIED BY 'retry' WITH GRANT OPTION;
SET PASSWORD FOR 'retry'@localhost=OLD_PASSWORD('retry');
FLUSH PRIVILEGES;
And had connected successfully on mySQL 4.1.7, however when i upgraded to 4.1.9 i was no longer able to connect.
Guessing that this was something in the way the user retry was set up i checked the mySQL graphical administration (of windows) for both version mySQL 4.1.7 and 4.1.9.
In both cases, both before and after the upgrade, the administrator showed no hosts that the user retry could connect from, even though localhost is specified in the above script. The only way i could get the user to be connected to is if i included a second GRANT with the user to a different hostname. See repeat directions for more info.
How to repeat:
on hp-ux 11
on mySQL 4.1.7, or mySQL 4.1.9,
run the following script : ./mysql < test2.sql
where test2.sql is
-- Set up users/passwords
GRANT ALL PRIVILEGES ON *.* TO 'test2'@'localhost' IDENTIFIED BY 'test2' WITH GRANT OPTION;
SET PASSWORD FOR 'test2'@localhost=OLD_PASSWORD('test2');
FLUSH PRIVILEGES;
Now check this user in the mySQL Administrater, you will see no hosts that can connect to it (if you select the user tab and double click on the user name)
now run the following script : ./mysql < test3.sql
where test3.sql is
-- Set up users/passwords
GRANT ALL PRIVILEGES ON *.* TO 'test3'@'localhost' IDENTIFIED BY 'test2' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'test3'@'%' IDENTIFIED BY 'test2' WITH GRANT OPTION;
SET PASSWORD FOR 'test3'@localhost=OLD_PASSWORD('test2');
FLUSH PRIVILEGES;
Now check this user in the mySQL Administrater, you will see that now localhost can connect to it (if you select the user tab and double click on the user name).
Note: both times you can still connect to the user if you try to connect using ./mysql -u retry -pretry. But if you upgrade (like i did) then this problem becomes apparent.
Suggested fix:
The solution i have used so far is to GRANT 2 hostnames in this case localhost and 'wildcard' to retry, and this should ensure no problems between different versions. But i think something is happening here in hostname granting.
Description: Migrating from mySQL 4.1.7 to mySQL 4.1.9 i backed up the mySQL databases ./mysqldump –-all-databases > /tmp/mysql_bkup.dat and installed the new binary, before restoring the databases. ./mysql –u root < /tmp/mysql_bkup.dat However my application which was attempting to connect through the mySQI C API could not connect. Further investigation revealed that the problem was apparent from the command prompt as well. I could not connect with my username (retry) and password (retry): ERROR 1045 (28000): Access denied for user 'retry'@'localhost' (using password: YES) I had created my user retry with the following script -- Set up users/passwords SET PASSWORD FOR 'root'@localhost=OLD_PASSWORD(''); GRANT ALL PRIVILEGES ON *.* TO 'retry'@'localhost' IDENTIFIED BY 'retry' WITH GRANT OPTION; SET PASSWORD FOR 'retry'@localhost=OLD_PASSWORD('retry'); FLUSH PRIVILEGES; And had connected successfully on mySQL 4.1.7, however when i upgraded to 4.1.9 i was no longer able to connect. Guessing that this was something in the way the user retry was set up i checked the mySQL graphical administration (of windows) for both version mySQL 4.1.7 and 4.1.9. In both cases, both before and after the upgrade, the administrator showed no hosts that the user retry could connect from, even though localhost is specified in the above script. The only way i could get the user to be connected to is if i included a second GRANT with the user to a different hostname. See repeat directions for more info. How to repeat: on hp-ux 11 on mySQL 4.1.7, or mySQL 4.1.9, run the following script : ./mysql < test2.sql where test2.sql is -- Set up users/passwords GRANT ALL PRIVILEGES ON *.* TO 'test2'@'localhost' IDENTIFIED BY 'test2' WITH GRANT OPTION; SET PASSWORD FOR 'test2'@localhost=OLD_PASSWORD('test2'); FLUSH PRIVILEGES; Now check this user in the mySQL Administrater, you will see no hosts that can connect to it (if you select the user tab and double click on the user name) now run the following script : ./mysql < test3.sql where test3.sql is -- Set up users/passwords GRANT ALL PRIVILEGES ON *.* TO 'test3'@'localhost' IDENTIFIED BY 'test2' WITH GRANT OPTION; GRANT ALL PRIVILEGES ON *.* TO 'test3'@'%' IDENTIFIED BY 'test2' WITH GRANT OPTION; SET PASSWORD FOR 'test3'@localhost=OLD_PASSWORD('test2'); FLUSH PRIVILEGES; Now check this user in the mySQL Administrater, you will see that now localhost can connect to it (if you select the user tab and double click on the user name). Note: both times you can still connect to the user if you try to connect using ./mysql -u retry -pretry. But if you upgrade (like i did) then this problem becomes apparent. Suggested fix: The solution i have used so far is to GRANT 2 hostnames in this case localhost and 'wildcard' to retry, and this should ensure no problems between different versions. But i think something is happening here in hostname granting.