Problem connecting with SSL to mySQL database

[ibm1620]

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?

[clueless newbie]

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.

[ibm1620]

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.

[dorko]

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

[dorko]

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

[ibm1620]

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.

[dorko]

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

[clueless newbie]

Consider enabling DBI_TRACE. Then running each script to its own log and compare the logs;

[ffrost]

Try mysql_ssl_verify_server_cert=0 or get a certificate that really matches the hostname you are connecting to.