[Resolved] DB Connection Failure

[goosehunter]

I've just had to reformat my MySQL/PHP server drive and can connect to MySQL using PHPMyAdmin. I'm rebuilding databases and trying to set up access acounts with privileges. But my PHP scripts are failing to connect to the appropriate databases.

I've tried granting privileges from both Terminal and from within PHPMyAdmin and then either Flush Privileges or reboot. Something is not allowing my script to make the db connection.

[kkobashi]

Not to be rude, but your post is more of a blog entry than a question.

If you can connect via phpMyAdmin and you can see all the tables with correctness in data, then the problem is not a general database setup/config problem.

Also, a lot of problems can be resolved by just echo'ing out statements near the source of error, and hone in.

Start echo'ing out arguments near the database connection statement and work your way down the script.

You really don't leave us with much info to go on here...

[goosehunter]

The error I get is "mysql_pconnect(): Access denied for user: 'labsurfer@localhost' (Using password: YES)" which represents the username I've set up for this web user. It appears to be a privileges issue but I don't know how to fix it.

I originally used Terminal to grant privileges for this user with a password. When I browsed this user with PHPMyAdmin, the password was encoded. Thinking that this might cause a mismatch, I changed the password to the English spelling. Nothing I do seems to enable connection to the database.

[kkobashi]

You know goosehunter, I've seen this problem before... your lucky I ran into this one too because it could take DAYS to resolve. 😃

Did you by chance download a new version of mySQL recently?

If so, the problem lies in the way passwords are stored in newer versions of mySQL.

This bug is so goddamn stupid that I cannot believe mySQL AB didn't test for this properly before release because it is so basic and falls within a smoke test acceptance phase (a testing phase where QA does not accept the release until it passes basic functionality).

Anyways, it has something to do with the administration's root password being stored differently under newer versions of mySQL. It requires for you to change the password and set a flag to tell the database system that your password should be interpreted under the old scheme.

Download MySQL Administrator off the mySQL site. Install it and run the program. Go to the Startup Variables section and click on the Security tab. Then, click the Use Old Passwords checkbox.

I think that should do it

[goosehunter]

No, the MySQL version is 4.0.18 and PHP is 4.3.2 and were part of my server installer CD from last summer.

Weeks ago, all of my scripts were working fine as I worked through "PHP and MySQL Web Development." Then my server became unstable and would not boot reliably. Since the drive is one of 2 in this computer, I reformatted and reinstalled the server software. I only boot into it when working on PHP.

I see what you mean about the password encoding. But here's something strange. I deleted the user I'm trying to connect with and used grant to set up a new one with various privileges from Terminal. When viewing that user within PHPMyAdmin, the privileges were set to "N" instead of "Y." Changing them to "Y" still does not enable the db connection.

[kkobashi]

What happens when you login under the root/admin account and set your scripts to access the database under the root/admin account? Does it work?

You mentioned that you accessed the database via phpMyAdmin. Therefore you had to login via the administrator/root account, cause it worked.

I want to prove that case first. Then tackle the problem of solving for a particular user in the database system.

[goosehunter]

Yes, that works. Setting the script to log in as root and with that password does indeed work.

I'm just not able to set up a new user properly.

[kkobashi]

Maybe what you should do is view this from a mySQL perspective only?

What I'm puzzled about is why you are setting up multiple user accounts in mySQL.

Is your HTTP server meant to be accessed publicly by anonymous users?

If not, shouldn't you authenticate the user through your HTTP web server and then write code to allow/prevent access to different areas of your website pages? Includding those that perform mySQL database requests?

[goosehunter]

I'm just trying to work though examples presented in a textbook. These are probably vaild considerations that I will review when I tackle an actual project.

I finally solved the problelm. When I granted privileges in Terminal I did not specify a host and MySQL used the "%" for host for that user. Thinking it was a wildcard character that would work with any host name, I didn't try changing the host to "localhost" until tonight.

$db = mysql_pconnect('localhost', 'labsurfer', 'technician456');

Now the user matches the PHP script.

[kkobashi]

Hey goose

Glad you found it!

Do me a favor and mark this thread as resolved so I can see those that are open and done for.

have a nice night.