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

I am currently running 2 MySQL servers on one machine. The production version is 4.0.2, and the new version being tested is 4.1.3

I am having trouble connecting to the new 4.1.3 version via perl with the DBI / DBD. I have the 4.1.3 server listening on port 3307, so i tried connecting using:
DBI->connect("DBI:mysql:<dbname>;port=3307", user, pass);
But this is trying to connect to the 4.0.2 server. I know this because if i provide a user / pass combo that is valid for the 4.0.2 server, the connection succeeds, otherwise it fails.

Does the DBD read /etc/my.cnf, or does it figure things out another way, based on the port provided?

I dont know what to do here, because MySQL doesnt appear to properly read my.cnf files that are in datadir (of the mysql server variables).

Anyone else tried this before?

Replies are listed 'Best First'.
Re: DBD MySQL connect problem.
by dragonchild (Archbishop) on Aug 16, 2004 at 17:39 UTC
    Are you attempting to connect from the machine the databases are on? If so, you probably are attempting to use sockets. You might have to set a different socket in the my.cnf for the second server as well as in your connect statement. If you aren't, then you're missing the hostname parameter in your connect() statement.

    Also, I may be wrong, but the authentication methods changed between 4.0.? and 4.1.? - meaning you might have to recompile DBD::mysql to get it to work right. (I had a devil of a time getting this to work as I wanted, but I was new at the time.)

    ------
    We are the carpenters and bricklayers of the Information Age.

    Then there are Damian modules.... *sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon. - flyingmoose

    I shouldn't have to say this, but any code, unless otherwise stated, is untested

[reply]
      Update At End

      Yes, i am connecting from the machine that the MySQL servers are located on. I do have different sockets & pidfiles for the different servers.

      So i shouldnt have to set the host param in connect() - both connections are to the local machine.

      I've never seen anything about setting the socket in the DBI connect statement, is this something that can be done? What effects might it have?

      If i do recompile the DBD::mysql, then will it stop working for the 4.0.2 server? I have read a bit about the changes, but whether i can get both to work at the same time is another story. Perhaps i'll need both DBD::mysql's installed, and put the new one somewhere else, and use both from my script.

      Hope this helps clarify things. Any other questions, This is all im working on until its done!

      Thanks much!

      Update:
      Ok, i trield setting the socket explicitly with:
      mysql_socket=<...>
      And now i get a more meaningful error:
      ...failed: Client does not support authentication protocol requested by server; consider upgrading MySQL client at...
      So now with the socket specified, it appears that its a DBD version problem, which i should be able to take care of. (i hope)

[reply]
Re: DBD MySQL connect problem.
by tachyon (Chancellor) on Aug 16, 2004 at 22:56 UTC

    The problem is the way that the driver defaults. Locally you can connect using either UNIX or TCP sockets. For localhost the default is UNIX sockets (faster). At compile time the location of the default mysql UNIX socket is effectively compiled in so your driver is loosly coupled to the original MySQL 4.0.x

    The fix is easy - add the hostname to force use of TCP sockets, or tell the driver where the 4.1.x socket is. Any of these should work: 

    $dsn = "DBI:mysql:database=$database;host=$hostname;port=$port"; 

$dsn = "DBI:mysql:test;mysql_read_default_file=/home/foo/my.cnf";

$dsn = "DBI:mysql:test;mysql_socket=/dev/mysql"; 
  
$dbh = DBI->connect($dsn, $user, $password, {'RaiseError' => 1} );

    [download]

    cheers

    tachyon

[reply]
[d/l]

Back to Seekers of Perl Wisdom