Perl not connecting to MySQL [Solved]

[MacPorts]

On 3/11/19 12:10 AM, MacPorts wrote:
>
>> I found this thread:
>> http://mac-os-forge.2317878.n4.nabble.com/bp-seqfeature-load-pl-module-cannot-connect-to-mysql-td140155.html
>> which recommends that you create a link from /var/mysql/mysql.sock to
>> /opt/local/var/run/mysql5.
>>    ( you may need to sudo mkdir -v /opt/local/var/run/mysql5 first)
>>    $ sudo ln -s /var/mysql/mysql.sock /opt/local/var/run/mysql5
>>
>> I make the link, but Perl still can't connect to local MySQL server
>> through socket '/opt/local/var/run/mysql57/mysqld.sock'.
>>
>
I found several comments on the InterNET that suggested creating a link
from the socket to the directory where Perl expects the port to be. I
tried to do this in several different ways. None of them worked.

I'm going to detail here what I did so if someone has this same question
they will know what I tried that didn't work.

First I tried
   $ sudo ln -s /var/mysql/mysql.sock /opt/local/var/run/mysql57/mysqld.sock
this created a link in .../mysql57 owned by root:admin with permissions
lrwxr-xr-x

I assumed it wasn't working because the socket needed to be owned by
_mysql:wheel so I
   $ sudo chown _mysql:wheel mysql57/mysqld.sock
the ownership did not change.
I also tried 'sudo chown mysql:wheel mysql57/mysqld.sock' and the
ownership didn't change.

Since the sticky bit is set on the socket, and was not set on the link,
i tried
   $ sudo chmod +t mysql57/mysqld.sock
Perl still could not connect to the socket.

I tried
   $ sudo ln -s /var/mysql /opt/local/var /opt/local/var/run/mysql57
this created a link from the directory the socket resides in to the
directory where Perl expects to find the socked. Then I created a link
in /var/mysql from mysql.sock to mysqld.sock
   $ cd /var/mysql
   $ sudo ln -s mysql.sock mysqld.sock
This created sockets called mysql.sock, and mysqld.sock in .../mysql57.
The socket called mysqld.sock was owned by root:admin with permissions
lrwxr-xr-x and still gave an error when trying to connect Perl to the
Database.

Because of the problems with ownership/permissions, I removed
mysqld.sock and created a hard link
   $ sudo ln mysql.sock mysqld.sock
This created a socket in /opt/local/var/run/mysql57 with the correct
name (mysqld.sock), the proper ownership (_mysql:wheel), and the proper
permissions (srwxrwxrwt (notice the 'socket bit', and the sticky bit are
both set)).
Perl still could not connect.

All of these give the error:
   Can't connect to local MySQL server through socket
'/opt/local/var/run/mysql57/mysqld.sock' (2)
Notice the error code ENOENT(2), which means 'No such file or
directory'. I don't understand why Perl thinks the socket (or the path
to it) is missing.

After creating the links, I tried
   $ nc -U /opt/local/var/run/mysql57/mysqld.sock
This created output showing that MySQL was asking for a password. I also
tried this command on the socket itself, and the link to the socket in
/var/mysql. All of them showed MySQL requesting a password.


It would have been nice if creating a link to the socket had worked
because I could run Perl on this machine without having to modify the
programs when copying them to other machines. However, links on macOS do
work differently than on Linux.
   $ echo 'file1' > file1
   $ ln -s file1 link1
   $ cat link1
      file1
   $ mv file1 file2
   $ echo 'fileb' > file1
   $ cat link1
      fileb

This will also work if you remove the first file1, then create a new
file1. This is what most text editors (vi, vim, emacs) will do. They
move the original file to a temporary file, name the edited file the
original name, and remove the temporary file. This is how symbolic links
work on macOS. On Linux, this will break symbolic links.

This difference between the way Linux and macOS handle links may be why
some people only need to create a link to the socket for Perl to find
it, but it doesn't work on macOS. If someone knows more about this
please let me know.


What did work to connect Perl to MySQL was to put
'mysql_socket=/var/mysql/mysql.sock' into the $dsn string for
   $dbh = DBI->connect($dsn, $db_user_id, $db_password)
      Note: The $dsn is a semi-colon (;) separated string of database
name; hostname; socket, etc. Look at DBD::mysql on
                metacpan.org for more information about the content of
$dsn strings.

The problem with this solution is that it means changing the connection
code for every computer where the socket is stored in a different place,
a real possibility when moving from macOS, to different flavors of Linux.

The other solution, as suggested by Stephen Butler, is to set
'mysql_read_default_file=/etc/my.cnf' in the $dsn string. It still means
that you will need to change the code if the MySQL configuration file is
in /etc/mysql/my.cnf, or somewhere else, but it allows for more
flexibility than hard coding the path to the socket. I'm using this
method in my code.



Carl.



-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macports.org/pipermail/macports-users/attachments/20190312/a2662cf6/attachment.html>