Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
PerlMonks  

Problem connecting with SSL to mySQL database

by ibm1620 (Monk)
on Apr 10, 2019 at 17:06 UTC ( #1232413=perlquestion: print w/replies, xml ) Need Help??

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);

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);

(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 ...
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 (Deacon) 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.

      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.
        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);

        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.
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.
      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.

        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);
        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.
Re: Problem connecting with SSL to mySQL database
by clueless newbie (Deacon) on Apr 11, 2019 at 16:33 UTC
    Consider enabling DBI_TRACE. Then running each script to its own log and compare the logs;
Re: Problem connecting with SSL to mySQL database
by ffrost (Novice) 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.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1232413]
Approved by haukex
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (5)
As of 2021-01-22 09:57 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Notices?