pkg/42352: Remote access problems with MySQL

[randolf+netbsd.org%inter-corporate.com@localhost]

>Number:         42352
>Category:       pkg
>Synopsis:       Remote access problems with MySQL
>Confidential:   no
>Severity:       serious
>Priority:       medium
>Responsible:    pkg-manager
>State:          open
>Class:          doc-bug
>Submitter-Id:   net
>Arrival-Date:   Fri Nov 20 15:20:00 +0000 2009
>Originator:     Randolf Richardson
>Release:        NetBSD 5.0.1
>Organization:
Inter-Corporate Computer & Network Services, Inc.
>Environment:
NetBSD netbsd.unix.lumbercartel.ca 5.0.1 NetBSD 5.0.1 (LUMBERCARTEL_GENERIC) 
#0: Fri Sep 25 14:25:28 PDT 2009  
root%netbsd.unix.kingoffloors.com@localhost:/usr/obj/sys/arch/amd64/compile/LUMBERCARTEL_GENERIC
 amd64
>Description:
The mysql-server-5.0.67nb3 package from pkgsrc-current doesn't work properly 
for remote login until after the password has been reset, resulting in this 
error on systems not on localhost:

ERROR 1045 (28000): Access denied for user ... (using password: YES)

Please add the following note to the final output that appears after "make && 
make install" so that system administrators can quickly resolve the problem for 
this error (the solution is difficult to find):

If you receive "ERROR 1045: Access denied" errors, you may need to reset the 
password with the following commands from the server:
   mysql -u root -p mysql
   update user set Password=PASSWORD('password') where user='username';
   flush privileges;
   \q

>How-To-Repeat:
Install the "databases/mysql5-server" package from pkgsrc.

Grant access to the user called "username" with the following commands (the 
username "root" should already be in the system by default):
   mysql -u root -p mysql
   grant all privileges on *.* to 'username'@'10.%' identified by "PASSWORD";
   flush privileges;
   \q

Attempt connection from any IP from a different host on the 10/8 network (if 
your network is using a different IP range than 10/8, you'll also need to 
update the "10.%" portion of the query accordingly) using the correct username 
and password, and the following error will result (assuming your remote host's 
IP address is 10.8.0.178):

ERROR 1045 (28000): Access denied for user 'username'@'10.8.0.178' (using 
password: YES)

>Fix:
Type these commands from the server:
   mysql -u root -p mysql
   update user set Password=PASSWORD('password') where user='username';
   flush privileges;
   \q

The next attempt to login resolves this problem.