Hi. I've spend over two hours trying to figure this out. What seems to be happening is that mysql_read_default_file is not reading the config file. I've tried many things, rehashed this a bunch of times, and below is my final attempt along with a whole lotta comments(some of which are verbatim from pod). I do believe this is supposed to work.

Please go through the code, and read all the comments.

If you see any mistakes/misconclusions don't hesitate to tell me.

This could either be a bug in DBD::mysql, corrupted installation of DBD::mysql, misconfiguration of the server by jcwren (i contacted him, but won't hear till tomorrow, and honestly don't think this is it), or just me being extremely stupid/ignorant.

Like noted in the comments, stuff I read includes Re: Reusing DBI Statement Handle, (dkubb) Re: (2) Hiding passwords using DBI's DBI_DSN, http://www.mysql.com/articles/mysql_intro.html and http://www.mysql.com/doc/O/p/Option_files.html.

The first block of pod is my ~/.my.cnf file (/usr/home/crazyinsomniac/.my.cnf). After it is my test script (test.pl). Permission on .my.cnf is 0600 as pod suggests.

=pod

#client is supposed to be the default, so just in case
[client]
host     = localhost
user     = crazyinsomniac_r
username = crazyinsomniac_r
password = elpaseo_r
mysql_compression = 1
verbose = 1

[perlgroup]
host     = localhost
user     = crazyinsomniac_r
username = crazyinsomniac_r
# the above user* settings have no apparent effect, even though they s
+hould

password = elpaseo_r
mysql_compression = 1
verbose = 1

# http://www.mysql.com/articles/mysql_intro.html
# http://www.mysql.com/doc/O/p/Option_files.html
# the above says
# option=value is equivalent to mysql --option=value
# see myslq --help for options

=cut

#!/usr/bin/perl -w

use strict;
use DBI;                        # Fo' updating results to db

{
    undef %ENV;

    my $db_driver    = 'mysql';
    my $db_name      = 'crazyinsomniac';
    my $db_user      = 'crazyinsomniac_r';
    my $db_group     = 'perlgroup';
    my $mysqlconfile = "~/.my.cnf";
#       $mysqlconfile = "/home/crazyinsomniac/.my.cnf";

# specifying a path other than ~/.test.cnf for my custom config file
# yields in DBI returning a DBI::db=HASH(0x8103330) object to $dbh
#

    # this is proper syntaxt, read pod, search for ):
    my $dsn  = "DBI:$db_driver(RaiseError=>1,AutoCommit=>1,PrintError=
+>1):;"; #$db_name
       $dsn .= "mysql_read_default_file=$mysqlconfile;";
       $dsn .= "mysql_read_default_group=$db_group;";
# appears not to have any effect.  It could be possible this is disabl
+ed,
# but i've not found any info to indicate so
# if $db_group does not exist, mysql will read from either the global 
+or server options file
# and thus set username to crazyinsomniac (as opposed to crazyinsomnia
+c_r which i want)
# there will be no password set of course
# /etc/my.cnf /var/db/mysql/my.cnf ~/.my.cnf

#http://perlmonks.com/index.pl?node_id=59597&lastnode_id=3989
#http://perlmonks.com/index.pl?node_id=51901&lastnode_id=3989
    $ENV{DBI_USER} = $db_user;  # doesn't read user from .test.cnf, so
+ i have to set
    $ENV{DBI_DSN}  = $dsn;      # yes I know its usually set by mod_pe
+rl, but it's not so

    my $dbh = DBI->connect;

    print $dbh,"\n";

    my $sth = $dbh->execute("SELECT thething FROM thingsisay ORDER BY 
+RAND()
                             LIMIT 1;");

    print @{$sth->fetchrow_arrayref};

    $dbh->disconnect(); # don't ever forget
}

__END__

=head1 RESOURCES

http://www.mysql.com/articles/mysql_intro.html

=cut

view /usr/local/lib/perl5/site_perl/5.005/i386-freebsd/DBI.pm
version 1.15


mysql_read_default_file

mysql_read_default_group

These options can be used to read a config file like /etc/my.cnf or ~/
+.my.cnf. By default MySQL's C client library doesn't use any config f
+iles unlike the client programs (mysql, mysqladmin, ...) that do, but
+ outside of the C client library. Thus you need to explicitly request
+ reading a config file, as in 
    $dsn = "DBI:mysql:test;mysql_read_default_file=/home/joe/my.cnf";
    $dbh = DBI->connect($dsn, $user, $password)
The option mysql_read_default_group can be used to specify the default
+ group in the config file: Usually this is the client group, but see 
+the following example:

    [perl]
    host=perlhost
    [client]
    host=localhost
If you read this config file, then you'll be typically connected to lo
+calhost. However, by using

    $dsn = "DBI:mysql:test;mysql_read_default_group=perl;"
        . "mysql_read_default_file=/home/joe/my.cnf";
    $dbh = DBI->connect($dsn, $user, $password);
you'll be connected to perlhost. See the (missing :-) documentation of
+ the C function mysql_options() for details.

# so i went and looked and found only:
int mysql_options(MYSQL *mysql, enum mysql_option option, const char *
+arg) 
######################################################################
+##########
Between DBI 1.00 and DBI 1.09:

  Added $dbh->selectcol_arrayref($statement) method.
  Connect now allows you to specify attribute settings within the DSN.
  For example: 
        dbi:Driver(RaiseError=>1,Taint=>1,AutoCommit=>0):dbname"
  Added $h->{Taint}, $sth->{NAME_uc}, and $sth->{NAME_lc} attributes.
[download]

 
___crazyinsomniac_______________________________________
Disclaimer: Don't blame. It came from inside the void
perl -e "$q=$_;map({chr unpack qq;H*;,$_}split(q;;,q*H*));print;$q/$q;"