Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister

Problem connecting with SSL to mySQL database

by ibm1620 (Friar)
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=;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=;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=;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 (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.

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



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



    -- Yeah, I'm a Delt.

      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:

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



        -- Yeah, I'm a Delt.
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;
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.

Log In?

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

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (6)
As of 2023-06-08 14:38 GMT
Find Nodes?
    Voting Booth?
    How often do you go to conferences?

    Results (32 votes). Check out past polls.