0

Embarrassed to have to ask this of you Openshift guys, but Perl won't connect to MySQL, though PHP does just fine.

Following code works just fine on the client (I added OPENSHIFT env variables on my local machine) but fails on the Openshift app:

my $db_name = 'campaignmotor';
my $db_host = $ENV{'OPENSHIFT_MYSQL_DB_HOST'};
my $db_port = $ENV{'OPENSHIFT_MYSQL_DB_PORT'};
my $db_pw = $ENV{'OPENSHIFT_MYSQL_DB_PASSWORD'};
my $db_user = $ENV{'OPENSHIFT_MYSQL_DB_USERNAME'};

sub db_connect {
    my $dsn = "DBI:mysql:database=$db_name;host=$db_host;port=$db_port";
    my $dbh = DBI->connect($dsn, $db_user, $db_pw);
    return $dbh;
}

I get unending messages like: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

Following PHP config on my web app works just fine:

$db['default']['hostname'] = getenv('OPENSHIFT_MYSQL_DB_HOST') . ':'
                             . getenv('OPENSHIFT_MYSQL_DB_PORT');
$db['default']['username'] = getenv('OPENSHIFT_MYSQL_DB_USERNAME');
$db['default']['password'] = getenv('OPENSHIFT_MYSQL_DB_PASSWORD');
$db['default']['database'] = 'campaignmotor';

mysql, mysqldump etc. all work on the command line.

This is an HAProxy PHP application

Odd thing is I have no OPENSHIFT_MYSQL_DB_SOCKET variable. Most of the helper threads on this topic reference that.

Thanks in advance!

Chankey Pathak
  • 21,187
  • 12
  • 85
  • 133
user3550402
  • 59
  • 9
  • Are you connecting to "localhost" or "127.0.0.1" ? I noticed that when you connect to "localhost" the socket connector is used, but when you connect to "127.0.0.1" the TCP/IP connector is used. You could try using "127.0.0.1" if the socket connector is not enabled/working. Also make sure `mysql.sock` exists in `/var/lib/mysql/` with the code `[client] socket=/var/lib/mysql/mysql.sock` – Chankey Pathak Jun 07 '14 at 18:33
  • Is this a perl script that you are running on one of the PHP gears then? If you are running a scaled HAProxy application, then you can't use the mysql socket (since your db is on it's own gear), and the host should be a big long string that is an FQDN. Can you post the sanitized output of "env | grep MYSQL" from the gear that runs the Perl script? (remove usernames & passwords) –  Jun 07 '14 at 18:50
  • @Chakey - the DB server is on another host on OpenShift, ergo always have to reference OPENSHIFT_MYSQL_DB_HOST. – user3550402 Jun 07 '14 at 20:35
  • @developercorey - yes its Perl (trying to) run on a PHP gear/app. Understood that I can't use the socket - and from the Perl code above you can see I'm not trying to (I don't think). But I will review the DBI documentation some more to see if maybe Perl is trying to construct a socket anyway. Thanks!! – user3550402 Jun 07 '14 at 20:36
  • @developercorey Output you requested: `OPENSHIFT_MYSQL_DB_PORT=54341 OPENSHIFT_MYSQL_DB_HOST=53920f04e0b8cde61a0006d4-softwaresure.rhcloud.com OPENSHIFT_MYSQL_DB_PASSWORD=NOWAY OPENSHIFT_MYSQL_DB_GEAR_UUID=53920f04e0b8cde61a0006d4 OPENSHIFT_MYSQL_DB_USERNAME=adminAqZhUFS OPENSHIFT_MYSQL_DB_URL=mysql://adminAqZhUFS:NOWAY@53920f04e0b8cde61a0006d4-softwaresure.rhcloud.com:54341/ OPENSHIFT_MYSQL_LD_LIBRARY_PATH_ELEMENT=/opt/rh/mysql55/root/usr/lib64 OPENSHIFT_MYSQL_DB_GEAR_DNS=53920f04e0b8cde61a0006d4-softwaresure.rhcloud.com` – user3550402 Jun 07 '14 at 20:39
  • P.s. confirmed that DBD::mysql defaults to using socket, but only if host is omitted from the db_connect method. So, the hunt continues... – user3550402 Jun 07 '14 at 20:53
  • @user3550402: Try all the answers given [here](http://stackoverflow.com/questions/4448467/cant-connect-to-local-mysql-server-through-socket-var-lib-mysql-mysql-sock) – Chankey Pathak Jun 08 '14 at 03:24
  • Thanks Chankey, but those are all "socket-related", and running an OpenShift in high-availability mode with HAProxy changes the environment - no MySQL sockets are possible. My issue is that the Perl script appears to want to use a socket to connect, even though I'm specifying TCP parameters, etc. So strange! – user3550402 Jun 08 '14 at 13:48
  • Ok - I narrowed it down. The socket error is thrown not by the Perl code above (works fine apparently) but by the following code, which is a shell command run from within the Perl script: `code` `mysql -u $dbcreds->{db_user} -p$dbcreds->{db_pw} $dbcreds->{db_name} < $SQL_PATH`; Its the shell command that's failing. So weird that Perl wants to use the socket in this case. I keep exploring...Perhaps trying running my script using DBI only – user3550402 Jun 08 '14 at 15:00
  • In your shell command above, specify the -h option for the host, or else yes, it would try to use the socket. –  Jun 08 '14 at 17:08

1 Answers1

0

Corey - thanks!

In your shell command above, specify the -h option for the host, or else yes, it would try to use the socket.

And I'll add you need to specify the port as well, since in HAProxy mode MySQL runs on a non-standard port number. I added both -h with host and -P with port, and it works!

I might have stumbled on this except that the command worked as expected when run direct on the shell, even without Port number and host specified. Weird!

Thanks again!

user3550402
  • 59
  • 9