Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"

DBI with 2nd mysql server, nonstandard port

by Kozz (Friar)
on Feb 26, 2005 at 21:58 UTC ( #434824=perlquestion: print w/replies, xml ) Need Help??

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

Most wise monks:

I've got a bit of a dilemma on a server which I do not administer, and the actual administrator is very hesitant to upgrade perl and DBI to accomodate my needs. Here's the issue...

I requested an upgrade of MySQL, which he did: MySQL 4.1.9. However, he left the remaining MySQL server installed and running, for safety purposes while considering moving the entire server from the old to the new. He configured the new compile such that the binary is named "mysql4" instead of "mysql", it runs on port 3310 (instead of 3306), and socket file is /var/mysql4.1.9/mysql.sock.

Here's more version information:
Perl v5.6.0
$DBI::VERSION = "1.20"; $VERSION = '2.0415'; # DBD::mysql

I'm trying to get a perl script using DBI to connect to this OTHER server on port 3310 (not the mysql 3.x on 3306). Simply specifying "port=3310" in the $dsn doesn't seem to do anything -- it connects to the "old" mysql server. Only when specifying $ENV{'MYSQL_UNIX_PORT'} ='/var/mysql4.1.9/mysql.sock';
does the script attempt to connect to the "new" mysql installation, but at which point I get the following with DBI->trace(1):

DBI 1.20-nothread dispatch trace level set to 1 -> DBI->connect(DBI:mysql:database=dbname;host=localhost;port=3310, db +username, ****, HASH(0x80f8930)) -> DBI->install_driver(mysql) for linux perl=5.006 pid=18363 ruid=1005 + euid=1005 install_driver: DBD::mysql version 2.0415 loaded from /usr/lib/perl5/s +ite_perl/5.6.0/i386-linux/DBD/ <- install_driver= DBI::dr=HASH(0x817205c) <- DESTROY= undef at line 133 !! ERROR: 1251 'Client does not support authentication protocol reques +ted by server; consider upgrading MySQL client' <- connect= undef at line 426 <- errstr= 'Client does not support authentication protocol requested +by server; consider upgrading MySQL client' at line 427 DBI->connect(database=dbname;host=localhost;port=3310) failed: Client +does not support authentication protocol requested by server; conside +r upgrading MySQL client at ./ line 23 <- disconnect_all= '' at line 468 <- DESTROY= undef during global destruction

Is there any way to get this script with these versions of DBI, DBD::mysql to play nice with this new version of MySQL? My guess is that DBD::mysql is some sort of direct interface with the "mysql" binary, but I need it to use the binary named "mysql4". Can I hack things so it works properly?

All monks input is welcome and appreciated!

Replies are listed 'Best First'.
Re: DBI with 2nd mysql server, nonstandard port
by dbwiz (Curate) on Feb 26, 2005 at 22:17 UTC

    There are two issues here:

    • For MySQL, connecting to "localhost" means using a Unix socket (or a named pipe in Windows). That's why you needed to set $ENV{'MYSQL_UNIX_PORT'}. Instead of doing that, you could have used either of these statements:
      • my $dbh=DBI->connect('dbi:mysql:dbname' . ';host=' . ';port=3310', 'user', 'password');
      • my $dbh=DBI->connect('dbi:mysql:dbname' . ';host=localhost' . ';mysql_socket=/var/mysql4.1.9/mysql.sock', 'user', 'password');
    • As for the client issue, with MySQL 4.1 there is a new communication protocol to identify clients to the server. Also here, you have two choices:
      • Reinstall DBD::mysql, using the new client libraries from the newly installed 4.1.9 package.
      • Ask the administrator to change your password using the OLD_PASSWORD function, or do it yourself: just connect to the server using the command line client, and issue this command:
        SET PASSWORD=OLD_PASSWORD('yoursecretpassword');


Re: DBI with 2nd mysql server, nonstandard port
by esskar (Deacon) on Feb 26, 2005 at 22:09 UTC
Thanks - Re: DBI with 2nd mysql server, nonstandard port
by Kozz (Friar) on Feb 27, 2005 at 20:02 UTC
    Thank you to both monks who provided invaluable advice. It hadn't occurred to me that MySQL had changed so much as to create issues with connections & authentication via DBI. A big ++ to both of you.

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://434824]
Approved by Arunbear
Front-paged by davidj
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (2)
As of 2022-01-19 04:52 GMT
Find Nodes?
    Voting Booth?
    In 2022, my preferred method to securely store passwords is:

    Results (55 votes). Check out past polls.