PHP-FPM UDS connection errors

26 views
Skip to first unread message

Scott Wozny

unread,
Apr 9, 2020, 7:39:53 PM4/9/20
to highload-php-en

On CentOS 7.6 I am running Apache 2.4.6, PHP-FPM 5.4.16 and ProxySQL 2.0.7 (via the Percona repository). I have a simple PHP test I first made sure ran against my off system DB server (Percona XtraDB Cluster 5.7) and then against an off-system ProxySQL server connected to my Percona cluster, both of which were successful. On the local install of ProxySQL I am using a mysql interfaces setting of “127.0.0.1:3306;/tmp/proxysql.sock”. My real intention is to use only the socket for both security and performance reasons, but to start with, I set up for both.


The PHP is pretty basic:


<?php

$servername = "127.0.0.1";

$username = "appusername";

$password = "apppassword";

$dbname = "wwwdb";


// Create connection

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}


$dbname = $conn->host_info;

echo "<p>DB Server: ".$dbname."</p>";


$sql = "SELECT * FROM testtable";

$result = $conn->query($sql);


if ($result->num_rows > 0) {

echo "<table><tr><th>ID</th><th>Username</th><th>Password</th><th>Email</th></tr>";

// output data of each row

while($row = $result->fetch_assoc()) {

echo "<tr><td>".$row["user_id"]."</td><td>".$row["username"]."</td><td>".$row["password"]."</td><td>".$row["email"]."</td></tr>";

}

echo "</table>";

} else {

echo "0 results";

}

$conn->close();

?>


I also set mysqli.default_socket = /tmp/proxysql.sock in /etc/php.ini so I wouldn’t need to set the socket manually (requiring 2 different constructors) so I could switch back and forth with localhost as the servername for testing TCP vs UDS.


When I test this with a server name of 127.0.0.1 (forcing TCP) I get a “127.0.0.1 via TCP/IP” connection result string and then the data I expected from the DB server. When I test it with localhost (forcing UDS) I get “Connection failed: Can't connect to local MySQL server through socket '/tmp/proxysql.sock' (2)” from the PHP connection result and “PHP Warning: mysqli::mysqli(): (HY000/2002): Can't connect to local MySQL server through socket '/tmp/proxysql.sock' (2) in /var/www/html/select.php on line 12” from the PHP-FPM error log.


Concerned the socket couldn’t be accessed by PHP, I checked the DAC rights for the file and it was 777 so any process that could get to it should be able to read and write and I also ran a sudo audit2allow -a to make sure SELinux wasn’t getting involved and it wasn’t which should not surprise me as ProxySQL runs as an unconstrained daemon.


My next check was that the socket ProxySQL had created was actually open and working properly so I tried connecting to it from the locally installed MySQL client (also from the Percona repo) with “mysql -u appusername -p --socket=/tmp/proxysql.sock” and it connected to the socket file and I was able to run my select statement which ProxySQL dutifully sent on to the DB servers and returned the data to the console.


So now I’m running out of places to look. The PHP and the UDS socket both work, just not together. I did a pair of straces on the PHP PIDs and the mysql client command to see if there was any indication why there was a connection problem on the PHP connecting to the UDS socket. Unfortunately, what I saw was not illuminating.


Through PHP talking to the socket, this was my result:


[pid 8966] socket(AF_LOCAL, SOCK_STREAM, 0) = 4

[pid 8966] fcntl(4, F_SETFL, O_RDONLY) = 0

[pid 8966] fcntl(4, F_GETFL) = 0x2 (flags O_RDWR)

[pid 8966] connect(4, {sa_family=AF_LOCAL, sun_path="/tmp/proxysql.sock"}, 110) = -1 ENOENT (No such file or directory)

[pid 8966] shutdown(4, SHUT_RDWR) = 0

[pid 8966] close(4) = 0


Through mysql client talking to the socket, this was what I got:


socket(AF_LOCAL, SOCK_STREAM, 0) = 3

connect(3, {sa_family=AF_LOCAL, sun_path="/tmp/proxysql.sock"}, 110) = 0

setsockopt(3, SOL_TCP, TCP_NODELAY, [1], 4) = -1 EOPNOTSUPP (Operation not supported)

setsockopt(3, SOL_SOCKET, SO_KEEPALIVE, [1], 4) = 0

recvfrom(3, "J\0\0\0\n5.5.30\0\6\0\0\0Teo\0356\4\26 \0/\362!\2\0\217\200"..., 16384, 0, NULL, NULL) = 78

sendto(3, "W\0\0\1\205\246\377\1\0\0\0\1!\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 91, 0, NULL, 0) = 91

recvfrom(3, "\7\0\0\2\0\0\0\0\0\0\0", 16384, 0, NULL, NULL) = 11

rt_sigaction(SIGINT, {0x409990, [INT], SA_RESTORER|SA_RESTART, 0x7fa4252f2340}, {SIG_DFL, [], 0}, 8) = 0

rt_sigaction(SIGQUIT, {0x40acb0, [QUIT], SA_RESTORER|SA_RESTART, 0x7fa4252f2340}, {SIG_DFL, [], 0}, 8) = 0

rt_sigaction(SIGHUP, {0x40b080, [HUP], SA_RESTORER|SA_RESTART, 0x7fa4252f2340}, {SIG_DFL, [], 0}, 8) = 0

fstat(1, {st_dev=makedev(0, 12), st_ino=4, st_mode=S_IFCHR|0620, st_nlink=1, st_uid=1000, st_gid=5, st_blksize=1024, st_blocks=0, st_rdev=makedev(136, 1), st_atime=2019/12/02-21:49:12.966431495, st_mtime=2019/12/02-21:49:20.966431495, st_ctime=2019/12/02-21:24:51.969431519}) = 0

mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7fa427670000

write(1, "Welcome to the MySQL monitor. C"..., 58Welcome to the MySQL monitor. Commands end with ; or \g.

) = 58


And so on as it talks to the ProxySQL socket...


So I’m not sure why PHP wanted to do those extra fcntl operations on the socket before the attempt to connect, but why would it get a “No such file or directory” return when it’s running the same connect operation with the same parameters as the mysql client which worked properly?


Does anyone have any suggestions why these 2 socket based comms are behaving differently? I can’t find any errors in the proxysql.log file so ProxySQL doesn’t seem to have any issues and journalctl -e offers up no complaints so the operating system doesn’t seem to be upset, either. PHP logs the failure to connect, but with only the most generic connection error code so all THAT tells me is “can’t connect” which I already know.


I can stick with the 127.0.0.1 TCP connection to ProxySQL, but I’d prefer to use a socket for better speed and security, if I can but I can’t tell what’s tripping me up. The statement that /tmp/proxysql.sock doesn’t exists is demonstrably incorrect, but I can’t tell if it’s PHP, the OS or ProxySQL that’s in the wrong.  Is there a way to turn on debug logging (or increase the log level in any way) in PHP-FPM?


Any suggestions would be greatly appreciated.


Thanks,


Scott

Scott Wozny

unread,
Apr 27, 2020, 6:42:21 PM4/27/20
to highload-php-en
So, it turns out that the ProxySQL socket file being in /tmp was the problem as the PHP-FPM service uses PrivateTmp=true in its systemd service file.  As a result, from PHP-FPM’s running perspective, the “real” /tmp folder is inaccessible since systemd remounts /tmp /var/tmp for the process perspective to the private tmp folder.  Moving the socket file to /run/proxysql/proxysql.sock makes it accessible to PHP-FPM and my problem goes away.

Just wanted to close the loop on this in case someone runs into this issue in the future.  :)

Scott
Reply all
Reply to author
Forward
0 new messages