DBI/DBD mysql upgrade woes

shemp has asked for the wisdom of the Perl Monks concerning the following question:

Im having some difficulty with the perl DBI/DBD in conjunction with a MySQL upgrade. Here's the situation:

On the DB server machine, i have MySQL 4.1.3beta running for testing upgrading, but MySQL 4.0.2 is also running (production).

I can connect locally on the DB server through various clients, and have permissions as needed. But when i try to connect via a web server, i get various connect errors based on the params i pass. The error that is telling me the most is:

DBI connect(...) failed: Client does not support authentication protocol requested by server; consider upgrading MySQL client at <script line XXX>

But, i upgraded the DBI to 1.43, and the MySQL DBD to 2.9004 on both the DB server and the webserver (DB client). What im thinking the problem is: the webserver is running MySQL for other apps, and the DBD is built based on that MySQL, which is pre 4.1 series, so it doesnt understand the new MySQL auth protocols. (Note the webserver is running Perl 5.6.1)

But, i didnt think that the DBD was built based on a MySQL server, because the rest of the webservers in the pool dont run MySQL, but i can still install the DBI and DBD. So im at a loss here. Can i tell the DBD to build based on the DB servers MySQL install, or does that question not even make sense?

Note: I absolutely cannot upgrade mysql or Perl on the webserver.

Comment on DBI/DBD mysql upgrade woes

Replies are listed 'Best First'.
Re: DBI/DBD mysql upgrade woes by ikegami (Patriarch) on Aug 17, 2004 at 19:21 UTC
MySQL docs say this. Maybe you're compiling your DBD using an old library? The linked doc suggests a workaround. For comparison, I have a newer mysqld, an older DBI and DBD::mysql, yet it works for me. I don't know if my admin uses --old-passwords or not. `$ mysql -u ... -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 723796 to server version: 4.0.18 ... $ perl -MDBI -le'print($DBI::VERSION)' 1.40 $ perl -MDBD::mysql -le'print($DBD::mysql::VERSION)' 2.0419` [download]	[reply] [d/l]
Re^2: DBI/DBD mysql upgrade woes by shemp (Deacon) on Aug 17, 2004 at 19:30 UTC
Setting the passwords using OLD_PASSWORD() works for now. I'm calling this a temporary solution until i can upgrade the mysql client libraries on the web servers.	[reply]
Re: DBI/DBD mysql upgrade woes by edan (Curate) on Aug 18, 2004 at 09:35 UTC
DBD::mysql does need to be built against a specific version of MySQL client- or server-libraries. This is something that is determined when you run `perl Makefile.PL` when building DBD::mysql. You should verify at that stage that you are compiling with the 4.1 client library and header files. Since you need to make sure that your webservers still use the old DBD::mysql that was compiled against 4.0.2 for your production code, your best bet is probably to install the compiled-with-4.1-client-libraries DBD::mysql in some special location, and then your test scripts will `use lib '/path/to/secret/dir';` When you are ready to move to production with 4.1, you'll just reinstall the 4.1 DBD::mysql on top of the old one, and everything will work without a hitch! :) -- edan	[reply]