Issues Upgrading MySQL (3.23 -> 4.1)

[Katixa]

Hi there,

I've been upgrading MySQL from 3.23 to 4.1 in my first dedicated server. Apparently there were no issues upgrading the files with rpm -Uvh (I installed the packages "client", "server", "shared" and "shared-compat"), but later I got the "DBI" and "client does not support protocol" error message in my WebMin (when trying to access table or user permissions logged as root).

I read and followed some tutorials out there and managed to make this work (playing with the mysql console and the old_password function), and phpMyAdmin worked too, so I started creating a table for a website I just wanted to test (PHP+MySQL).

Later, I found that when setting a password to this new user I created (for the site), all the thing messed up again.

I THEN understood that the steps I followed before were to be able to log in in MySQL using the OLD password format (I though I was just "repairing" something :/ ). So now I discovered that my installation works only if I assign old password formats to MySQL.

Webmin reports the following in the Package list when searching for "mysql":

___________
o MySQL-client 4.1.7-0 Applications/Databases MySQL - Client
o MySQL-devel 4.1.7-0 Applications/Databases MySQL - Development header files and libraries
o MySQL-server 4.1.7-0 Applications/Databases MySQL: a very fast and reliable SQL database server
o MySQL-shared 4.1.7-0 Applications/Databases MySQL - Shared libraries
o MySQL-shared-compat 4.1.7-0 Applications/Databases MySQL shared libraries for MySQL 4.1.7 and 3.23.58
o perl-DBD-MySQL 2.9002-1 Applications/Databases A MySQL interface for perl
o php-mysql 4.3.8-1.1 Development/Languages A module for PHP applications that use MySQL databases.
___________

So I guess that all is correct. Or should be...

Then... why I can't use the new password format?

When setting a new password from Webmin to this user, I get a long encrypted string (the new password format), but when trying to view the site from the web browser I get the error about "Warning: mysql_connect(): Client does not support authentication protocol requested by server; consider upgrading MySQL client".

But if I go to the SSH and open the mysql console and set a new password using the old_password function, the Web seems to work.

When the whole things stops working is when I set a new password for "root", and I can't even edit the permisions in Webmin, so I have to edit the user again manually in the console.

Any help will be appreciated.
Thanks in advance.

[wassy121]

First, upgrade webmin. This fixes a lot of problems. When you first login, there is a little button for 'configure webmin'. In there, there is an arrow pointing up that is for upgrading webmin. It usually "just works".

Second, run mysql_fix_privilege_tables . This will help in converting from old format to new.

These two things should help. If they don't, please cut and paste the actual error messages you receive in webmin. These are the errors you described as "the "DBI" and "client does not support protocol" error message". Those descriptions don't mean anything without the actual error message.

Best,

Samuel

[Katixa]

Hi, and thanks for your reply.

Ok, lets start...

I just upgraded Webmin. My new version is 1.170.

Then I ran the following command:

mysql_fix_privilege_tables --host=localhost --user=root --database=my_database --password=my_password

I just did it three times. One with the "mysql" database and then with the other databases I got. Always using the "root" username, that I guess is needed only for permissions.

Then I went to Webmin and assigned a new password to the user I have set to connect with the database in the PHP scripts. I change the password and I get listed that long string (something like *478283A7384O348E38493SW84954839). All OK. As webmin logs with "root" user, there are no errors (same as before).

Now I try to launch the web site and I got this error:

Warning: mysql_connect(): Client does not support authentication protocol requested by server; consider upgrading MySQL client in /var/www/html/a_directory/another_directory/adm/inc_conexion.php on line 2

This line is just the one that connects to a database specifying a server, username, and password.

Of course if I go to the mysql console and type this commands, as stated in this site, the thing works fine:

[root@ webmin]# /usr/bin/mysql --user=root --password
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1422 to server version: 4.1.7-standard

mysql> set password for 'my_username'@'localhost' = old_password('the_password');
Query OK, 0 rows affected (0.00 sec)

mysql> update mysql.user set password = old_password('the_password') where host = '
localhost' and user = 'my_username';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> set password for 'my_username'@'localhost' = old_password('the_password');
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

The password Webmin shows is something like "33548cd2342ee" now (the old version) and the web can be launched again.

So I'm really lost. I know this may be repetitive, and similar to my first post, but I don't know how to explain the issue on another way.

Of course the "root" password is in the old format, because if I change it, Webmin stops working. Well, not "webmin" as a whole. Only the modules where I try to access database permissions, user permissions, etc. (any module where webmin tries to -connect- with the database.


About phpmyadmin... the same happens. phpMyAdmin works fine now, logging in as root. But if I change this password from webmin to the new format, I can only login in phpMyAdmin with the "mysql" username and NO password (and no privileges, of course...).

Let me paste here some of the screens I see in webmin. Maybe are worth nothing for help, but just in case...

(I will not tab here, but the column names would be User / host / password / permissions, as seen in webmin)

User Permissions

Anonymous / *blank* / myhost.myhost.org / None
Anonymous / *blank* / localhost / None
postales / localhost / xxxxxxx (old pass) / Select | Insert | Update | Delete | Create | Drop | Process | File | References | Index | Alter | Super | Create temp | Lock | Execute | Slave | Client
root / localhost / xxxxxxx (old pass) / All
root / myhost.myhost.org / *blank* / All

Database Permissions (database / user / host / permissions)

postales\_tm / postales / localhost / All
test / Anonymous / Any / Select | Insert | Update | Delete | Create | Drop | References | Index | Alter | Create temp | Lock
test\_% / Anonymous / Any / Select | Insert | Update | Delete | Create | Drop | References | Index | Alter | Create temp | Lock

"postales_tm" is a database I was using with the "postales" user, but actually I don't use it, although the "postales" username is the one that I use to connect to the other databases.

Host Permissions
No host permissions defined


If I change the "root" password (the one listed as "localhost" and using the old password) I get this message in the moment I press "OK":

DBI connect failed : Client does not support authentication protocol requested by server; consider upgrading MySQL client
(the tab is User Permissions)

Now I simply can't edit any user information, BUT I can still click on any database listed under the "MySQL Databases" section. I click on "mysql" and I see the tables listed there.

Now I just ran the following command (again)


mysql_fix_privilege_tables --user=root --host=localhost --database=mysql --password=the_password

This script updates all the mysql privilege tables to be usable by
MySQL 4.0 and above.

This is needed if you want to use the new GRANT functions,
CREATE AGGREGATE FUNCTION, or the more secure passwords in 4.1

done


But webmin still says the DBI thing. And I was with a phpMyAdmin session opened (as root) and when clicked on a table, the user/password gump poped again, saying "wrong username/password".


Hope all this helps.

Please feel free to tell me any command or thing you would do to check a system setting, or to see a result, so I can paste it here for you to see.

Thanks again in advance.

[wassy121]

Good questions, however the best I can answer them is by pointing you towards http://dev.mysql.com/doc/mysql/en/Old_client.html which basically states to use the old password style with 4.1 until you upgrade all your clients to not rely on mysql-shared-compat (which provides the 3.23.58 backwards compatibility).

Best,

Samuel

[Katixa]

... which basically states to use the old password style with 4.1 until you upgrade all your clients to not rely on mysql-shared-compat (which provides the 3.23.58 backwards compatibility). ...

Excuse me, but I did not understand that at all. What you mean with "until I upgrade all my clients" ?. I though I got them all upgraded. I downloaded the client, server and shared, but also the "shared-compat" because I though was also necessary due to dependency issues.

How do I upgrade that thing, or just, "disable" that backwards compatibility that is the one I understand from your answer is causing me issues?

Thanks.