Upgraded to PHP5/MySQL5, scripts denied access to db?

February 2, 2006 2:34 PM

WAMPfilter: Just upgraded to PHP5 and MySQL5, and now my scripts are getting access denied when connecting to the database. I can login from the command line with the same account just fine.

Many thanks for the suggestions to my previous question about PHP under IIS (ick).

I've persuaded the CTO to allow me to switch to Apache2 as we migrate the production server to new hardware. I figured now would be a good time to upgrade to PHP5 and MySQL5 as well--because I like pain.

Everything is going well, but my PHP scripts, which I changed to use mysqli_connect instead of mysql_connect, are now getting access denied:

Warning: mysqli_connect() [function.mysqli-connect]: (28000/1045): Access denied for user 'calendar'@'localhost' (using password: YES)

Where is gets weird: With the same password and username I can login to mysql from the command prompt. Also, my install of phpmyadmin is working just fine, so PHP *can* talk to the mysql service.

What am I doing wrong? I dont think this is the issue, but just for kicks here is the function that's throwing the error:


$dbhost="localhost";
$dbusername="calendar";
$dbuserpass="____"; // obscured for public consumption
$default_dbname="calendar";

$mysqli_ERRNO = ';
$mysqli_ERROR = ';

function db_connect($dbname=') {
global $dbhost, $dbusername, $dbuserpass;
global $mysqli_ERRNO, $mysqli_ERROR;

$link_id = mysqli_connect($dbhost, $dbusername, $dbuserpass, $default_dbname);
if (!link_id) {
$mysqli_ERRNO = 0;
$mysqli_ERROR = "Connection to $dbhost failed.";
return 0;
}

ELSE IF (empty($dbname) && !mysqli_select_db($default_dbname)) {
$mysqli_ERRNO = mysqli_errno();
$mysqli_ERROR = mysqli_error();
return 0;
}

ELSE IF (!empty($dbname) && !mysqli_select_db($dbname)) {
$mysqli_ERRNO = mysqli_errno();
$mysqli_ERROR = mysqli_error();
return 0;
}

ELSE RETURN $link_id;
}

posted by CaptApollo to Computers & Internet (8 answers total)

Password handling changed between 4.0 and 4.1. Something like that may have happened again. Look for the exact wording in the MySQL handbook, but you may need to either upgrade your mysql libs and recompile the PHP mysql modules (I've only done this with perl - YMMV), or make MySQL use the old-style password encrypting so older clients can connect.
posted by Captain_Tenille at 2:55 PM on February 2, 2006

I think Captain_Tenille is on the right track. Besides recompiling the mysql modules, you can use OLD_PASSWORD or run mysqld w/ --old-passwords.

See: http://dev.mysql.com/doc/refman/4.1/en/old-client.html
posted by lhl at 3:07 PM on February 2, 2006

Response by poster: Hm, I'm running under windows, so no "compiling," as I understand it. I tried adding "old-passwords" to the last line of my.ini, restarted the service, and restarted apache. No change. Good idea though! Maybe I just executed it improperly?
posted by CaptApollo at 3:53 PM on February 2, 2006

We hit a similar issue recently with differing versions of php libraries. Manually updating the mysql/user table to set the password field = OLD_PASSWORD(password) did the trick. You can give that a try but make sure to flush your permissions after you update the table.
posted by phearlez at 4:37 PM on February 2, 2006

Response by poster: Thanks, phearlez! To be clear, you mean something along the lines of SQL:

update users set password=old_password(password)

? I'm sure my syntax is off... You can really convert between hashes like that? Weird.
posted by CaptApollo at 4:50 PM on February 2, 2006

What do you mean no compiling? How do you think those binaries were made? Sombody had to compile them, and it seems like the PHP binaries you are using were compiled against an older version of the MySQL client libraries. So either rebuild them yourself, find someone who has already done this, or use the OLD_PASSWORD crutch -- but that's just a kludge. You really do want PHP compiled against the same client library version as the server otherwise you might run into other strange incompatibilities. If the person you are getting these binaries from can't get that right, then find another source or compile them yourself.
posted by Rhomboid at 5:07 PM on February 2, 2006

Response by poster: Um, ok. I am using the libraries from php.net, and I'm just saying that you don't have to recompile PHP each time, as the binaries work as extensions under windows.
posted by CaptApollo at 6:29 AM on February 3, 2006

Lee - uhh, CaptApollo - no, you can't, I tossed that off too quickly and was unclear, sorry. OLD_PASSWORD is a macro that will encrypt any string using the old hash method. Someone who's more of a SQL smartie than me might have a one-liner that would update them all but you probably don't want to anyway - that might break the command line tool or a different library.

You want to use this:

UPDATE user SET password = OLD_PASSWORD('somepassword') WHERE user = 'usernametofix';

then FLUSH PRIVILEGES ;

good luck!
posted by phearlez at 12:02 PM on February 3, 2006