Atomicorp

Having recently upgraded to mysql-server-4.1.15-1.rh90.art, I had a call from a user who was having problems with a PHP script that would not connect to MySQL.

It was the dreaded old/new password style type problem.

Only setting old_passwords=1 in my.conf did not cure it -- I had to run a query to manually update the password for that user to be in the old style.

I assume this was because the database was already created, with the new password type? And by setting old_password=1 I won't have to worry about this for future databases?

But here's what's really worrying me: I logged in as admin in mysql and did SHOW FULL PROCESSLIST; and was horrified to apparently see only admin's processes. At least that's what it looked like -- in the past when I did this I would see a whole load of db user processes and there would be a reasonable number of them, especially when I ran a MySQL-powered site at the same time. But there's pretty much nothing now.

I checked the MySQL manual, and found it said this:

SHOW PROCESSLIST shows you which threads are running. You can also get this information using the mysqladmin processlist command. If you have the SUPER privilege, you can see all threads. Otherwise, you can see only your own threads (that is, threads associated with the MySQL account that you are using). See Section 13.5.5.3, “KILL Syntax”. If you do not use the FULL keyword, only the first 100 characters of each statement are shown in the Info field.


So I did a SHOW PRIVELIGES and checked, and it seems to indicate I have SUPER prives as admin (as should be the case)

Super | Server Admin | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.

Am I worred about nothiing? All MySQL sites seem to be working perfectly. Could this lack of processes be down to having query caching enabled or something?

Faris.

Thats a better question for the mysql.com folks, I dont really get into the internals much. Out of curiosity are you using php 4.4.2? That is linked against mysql 4.1, and should fix the password sync'ing issues from the php side. My guess was that when you're linked against the mysql3 client libraries, the apps can only authenticate the old_password format, whereas the mysql4 libraries would let you use both.

Hi Scott,

I've been checking and I can say that the lack of activity in show processlist in mysql is down to caching, so all is well there. It is just the password issue that's left to worry me.

Yup, php 4.4.2 - the latest from your archive. All my php and mysql stuff comes from you I'm addicted I'm afraid.

I've never had any problems until now. All was fine on the versions of php and mysql that you put out before this one.

I'm afraid I don't understand the way Plesk, php and mysql and scripts interact, so I'm a little in the dark. However, the database and database user will probably have been created through Plesk. I have no idea how Plesk talks to mysql - could it use something non-php to create the dbs and users, thus the new password style got used instead of the old one because I didn't have old_passwords=1 in my.cnf (until now)?

Faris.

Yeah that PSA->Mysql connection could be the culprit too. On anything other than RHEL4/CentOS4 they're going to be using the mysql-compat (v3) library to talk to mysql. My guess there is that they can only create old_password formatted entries, so that could be causing a mixed password environment. I'm kind of stuck on a solution here, since I cant manipulate the PSA instance of PHP, only the OS one.

When I link php against mysql3, I get a whole different set of problems. The really bad news here might be that you just can't use mysql 4.1 with consistancy on anything lower than 4ES or FC4.

One Idea, and its a total hack, is an external script to check the mysql users table for consistancy (sort of a diet-coke version of a database trigger). I hate to do something that ugly, since it's not really fixing the problem, just moving it around, but at this point there might not be any other solution.

But I though old_passwords=1 forced the use of the old password style? If that's the case, surely there should not be a problem? Or am I missing something fundamental?

It doesn't matter really though. I'll be upgrading to Centos 4 very soon given that I'm on RH9 at the moment and SW-Soft won't be supporting my OS anymore. And now that I know the cause and the fix for this issue I can deal with it on a case by case basis whenever it crops up until then.

Faris.