Problem connecting with SSL to mySQL database

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?

Comment on Problem connecting with SSL to mySQL database Select or Download Code

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]
Re^2: Problem connecting with SSL to mySQL database by ibm1620 (Hermit) on Apr 11, 2019 at 00:05 UTC
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]
Re^3: Problem connecting with SSL to mySQL database by dorko (Prior) on Apr 11, 2019 at 00:30 UTC
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^4: Problem connecting with SSL to mySQL database by ibm1620 (Hermit) on Apr 11, 2019 at 13:39 UTC
Re^5: Problem connecting with SSL to mySQL database by dorko (Prior) on Apr 11, 2019 at 16:33 UTC
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]
Re^2: Problem connecting with SSL to mySQL database by ibm1620 (Hermit) on Apr 10, 2019 at 23:29 UTC
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]
Re^3: Problem connecting with SSL to mySQL database by dorko (Prior) on Apr 11, 2019 at 00:09 UTC
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]


Clear questions and runnable code get the best and fastest answer
	PerlMonks