http://qs321.pair.com?node_id=1232413

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

Dear Monks,

Having trouble connecting to a mySQL database using SSL. I've checked with the DBAs and all the certs are in place. The problem appears to be the version of Perl (or the CPAN modules). I maintain my own Perl installation using perlbrew, so that I can freely add CPAN modules without involving the systems people.

The system Perl works: 

#!/usr/bin/perl
use warnings;
use strict;
use DBI;
my $user = 'charrison';
my $pass = '********';
my $conn = 'DBI:mysql:database=dwcontact;host=192.168.200.39;port=3402
+;mysql_ssl=1';
my $dbh  = DBI->connect($conn, $user, $pass);

[download]

Mine doesn't: 

#!/usr/bin/env perl
use warnings;
use strict;
use DBI;
my $user = 'charrison';
my $pass = '********';
my $conn = 'DBI:mysql:database=dwcontact;host=192.168.200.39;port=3402
+;mysql_ssl=1';
my $dbh  = DBI->connect($conn, $user, $pass);

[download]

(Only the first line is different.)

System has perl v5.16.3. My installation runs perl v5.22.4. The error I get is: 

DBI connect('database=dwcontact;host=192.168.200.39;port=3402;mysql_ss
+l=1','charrison',...) failed: SSL connection error: self signed certi
+ficate in certificate chain at ...

[download]
Any thoughts on what's missing from my installation?

Replies are listed 'Best First'.
Re: Problem connecting with SSL to mySQL database
by clueless newbie (Curate) on Apr 10, 2019 at 23:46 UTC
    DBD::mysql says:

    When enabling mysql_SSL there are other ssl option that should also be enabled at least mysql_ssl_ca_file or mysql_ssl_ca_path.

[reply]
      Yes, I've tried unsuccessfully to supply them. I'm told they're only needed when something (the OS? Perl?) "doesn't know where the certs are located". But I think the key thing is, the connection string, minus those additional SSL options, works fine when I connect using our /usr/bin/perl installation - just not when I connect using my personal v22 installation.
[reply]
        I think clueless newbie isn't as clueless s/he contends. Let's see if there is something enabled by default. Let's compare the two $conn data structures. Try something like this (untested):

        #!/shebang_goes_here
use warnings;
use strict;
use Data::Dumper;
use DBI;
my $user = 'charrison';
my $pass = '********';
my $conn = 'DBI:mysql:database=dwcontact;host=192.168.200.39;port=3402
+;mysql_ssl=1';
print Dumper ($conn);
my $dbh  = DBI->connect($conn, $user, $pass);

        [download]

        Perhaps mysql_ssl_ca_file or mysql_ssl_ca_path are getting defined in a way we don't understand.

        Cheers,

        Brent

        -- Yeah, I'm a Delt.
[reply]
[d/l]
[select]
Re: Problem connecting with SSL to mySQL database
by dorko (Prior) on Apr 10, 2019 at 22:04 UTC
    Hello ibm1620,

    I'm taking a stab in the dark here, just trying to be helpful.

    Stating the obvious, the 5.16 version doesn't mind that you've got some self-signed certs. 5.22 doesn't like the self-signed certs.

    Perhaps 5.16 is configured to disregard SSL errors?

    Lastly, I don't know anything about invoking with /usr/bin/env perl, but perhaps it's adjusting the environment (variables) such that the two Perls behave differently? (Running the programs under two different user accounts could have a similar effect.)

    Good luck.

    EDIT: What happens when you use an absolute path to the 5.22 installation in the shebang instead of using env?

    Cheers,

    Brent

    -- Yeah, I'm a Delt.
[reply]
[d/l]
[select]
      Brent,

      Thanks for your feedback. I didn't get any different results using absolute paths in the shebang line. And I'm running under the same account in all cases (the same account whose credentials I'm providing to mysql).

      I don't know what a "self-signed SSL certification" is so I don't know if that's what we have, or whether that's a misleading error message. I think we've established that the only thing different is the version (or location?) of Perl and its library of modules.

      I'm going to try to figure out if this is a matter of missing modules, or of the way the 5.16 version was configured when it was built.

[reply]
        hmm... I'm really out of decent ideas.

        I haven't used DBI in quite some time. Perhaps you can try enabling some debugging information by using DBI->trace() ?

        Something like:

        DBI->trace($trace_setting);

        [download]
        where $trace_setting is number from 1 and 15. Start with 1 and work your way up until you see something useful.

        Cheers,

        Brent

        -- Yeah, I'm a Delt.
[reply]
[d/l]
[select]
Re: Problem connecting with SSL to mySQL database
by clueless newbie (Curate) on Apr 11, 2019 at 16:33 UTC
    Consider enabling DBI_TRACE. Then running each script to its own log and compare the logs;
[reply]
Re: Problem connecting with SSL to mySQL database
by ffrost (Acolyte) on Apr 11, 2019 at 15:26 UTC
    Try mysql_ssl_verify_server_cert=0 or get a certificate that really matches the hostname you are connecting to.
[reply]

Back to Seekers of Perl Wisdom