Reading host from defaults file with DBD::mysql

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

Brethren,

I am using DBD::mysql and reading connection parameters from MySQL defaults files, and it is MOSTLY working. I've been doing it for years, with code similar to the following:

sub open_db
{
    my ($dsn, $dbh, $defaults);
    
    $defaults = (defined $opts{defaults} && -f $opts{defaults})
              ? $opts{defaults}
              : (-e $ENV{HOME}.'/.my.cnf')
              ? $ENV{HOME}.'/.my.cnf'
              : (-e '/root/.my.cnf')
              ? '/root/.my.cnf'
              : (-e './.my.cnf')
              ? './.my.cnf'
              : 'NOTFOUND';

    die "No valid defaults file found!" if ($defaults eq 'NOTFOUND' &&
+ !defined $opts{host});
    
    $dsn = defined $opts{host} && defined $opts{port}
         ? sprintf("DBI:mysql:mysql:host=%s;port=%d;mysql_read_default
+_file=%s;mysql_read_default_group=mysql;",
                   $opts{host},
                   $opts{port},
                   $defaults)
         : defined $opts{host}
         ? sprintf("DBI:mysql:mysql:host=%s;mysql_read_default_file=%s
+;mysql_read_default_group=mysql;",
                   $opts{host},
                   $defaults)
         : defined $opts{socket} && -e $opts{socket}
         ? sprintf("DBI:mysql:mysql;mysql_read_default_file=%s;mysql_r
+ead_default_group=mysql;mysql_socket=%s;",
                   $defaults,
                   $opts{socket})
         : sprintf("DBI:mysql:mysql;mysql_read_default_file=%s;mysql_r
+ead_default_group=mysql;",
                   $defaults);

    $dbh = DBI->connect($dsn, undef, undef, {RaiseError => 1, AutoComm
+it => 0});
    $dbh->do(sprintf('SET SESSION wait_timeout = %d',
                     $opts{interval} > 3500 ? $opts{interval} + 100 : 
+3600)) || die "Could not set session wait_timeout";

    return ($dbh);
}
[download]

Now, the one respect in which this is not working is that it ignores a host specified in the defaults file. Username is picked up correctly, password is picked up and used, host is ignored.

Can anyone tell me why this should be?

Comment on Reading host from defaults file with DBD::mysql Download Code

Replies are listed 'Best First'.
Re: Reading host from defaults file with DBD::mysql by NetWallah (Canon) on Jan 30, 2019 at 04:05 UTC
It's hard to tell without input data . Your best bet is to run this under the perl debugger: `perl -d <your code file>` [download] and step through (s command in debugger), to see what path it takes, and examine variables as it steps through (x command in the debugger). As a computer, I find your faith in technology amusing.	[reply] [d/l]
Re^2: Reading host from defaults file with DBD::mysql by Llew_Llaw_Gyffes (Scribe) on Jan 30, 2019 at 13:44 UTC
Oh, I've traced through my code and I know that it's doing what it should and reading the correct section of the defaults file. The problem is that DBD::mysql does not appear to be passing along the host specification along with the username and password, and I'm wondering whether I have missed something in my usage. The documentation leads me to believe that it SHOULD be working, but it isn't.	[reply]
Re^3: Reading host from defaults file with DBD::mysql by pryrt (Abbot) on Jan 30, 2019 at 14:57 UTC
Have you gone through Basic debugging checklist? Sounds like you've now done part (using the debugger), but maybe not everything: Does your $opts{defaults} really contain what you think it does when it enters the sub you showed? If not, then the problem lies outside of what you've shown. If it does contain what you think it should, then give us a security-redacted version of $opt{defaults}, because we cannot debug your logic in that sub without knowing what's there. See also SSCCE, How to ask better questions using Test::More and sample data, and How do I change/delete my post? edit: add "everything": I had intended the "but maybe not: ..." to be one logical thought, separate from "you've now done part"... but when re-reading, it looked more like I was saying OP hadn't done even the running-the-debugger, which was not what I meant. sorry. /edit	[reply]
Re^4: Reading host from defaults file with DBD::mysql by Llew_Llaw_Gyffes (Scribe) on Jan 31, 2019 at 19:01 UTC
Re^5: Reading host from defaults file with DBD::mysql by pryrt (Abbot) on Feb 01, 2019 at 15:28 UTC
Some notes below your chosen depth have not been shown here
Re^5: Reading host from defaults file with DBD::mysql by Anonymous Monk on Feb 01, 2019 at 05:05 UTC
Re: Reading host from defaults file with DBD::mysql by Danny (Chaplain) on Jul 25, 2024 at 18:56 UTC
I ran into this issue a few years back and was never able to resolve it. Since then I've just always specified the host in the dsn string. Below is an example script where the first case without specifying host=$host fails, but the second case with host=$host succeeds. Any pointers appreciated. It wasn't obvious to me where in DBD/mysql.pm it was reading the config file. I did step through the debugger forever, until it failed to connect, but never saw where it was reading the config file. #!/bin/env perl use warnings; use strict; use DBI; my $db = "**"; my $host = ""; my $cfg_file = "/tmp/my.cnf"; my ($dsn, $dbh); print STDERR "Without host=\$host\n"; $dsn = "dbi:mysql:mysql_ssl=1;database=$db;mysql_read_default_file=$cf +g_file;mysql_read_default_group=test_group"; $dbh = DBI->connect($dsn); print STDERR "\nWith host=\$host\n"; $dsn = "dbi:mysql:mysql_ssl=1;database=$db;mysql_read_default_file=$cf +g_file;mysql_read_default_group=test_group;host=$host"; $dbh = DBI->connect($dsn); print "\$dbh = $dbh\n"; [download] outputs: `Without host=$host DBI connect('mysql_ssl=1;database=;mysql_read_default_file=/tmp/my +.cnf;mysql_read_default_group=test_group','',...) failed: Can't conne +ct to local MySQL server through socket '/var/run/mysql.sock' (2) at +/tmp/test_db.pl line 12. With host=$host $dbh = DBI::db=HASH(0xa00318a18)` [download] The /tmp/my.cnf looks like: `[test_group] host= port=3306 database= user= password=** no-auto-rehash ssl=1` [download] Update: This is on a cygwin system with the latest cygwin based perl and associated modules. I just tried it on a linux system and it works without specifying host=$host	[reply] [d/l] [select]