Weirdness with PHP-FPM and UDS sockets (and, possibly, ProxySQL)

Issues related to applications and software problems
Post Reply
sawozny
Posts: 40
Joined: 2019/07/13 22:19:14

Weirdness with PHP-FPM and UDS sockets (and, possibly, ProxySQL)

Post by sawozny » 2020/04/26 21:16:53

In my CentOS -> Apache -> PHP-FPM -> ProxySQL -> MariaDB stack I have this weird problem where when I have PHP code that talks to a local ProxySQL instance through PHP-FPM via 127.0.0.1:3306 the code works fine, but when I try to make contact through a UDS socket, the request doesn't work and my troubleshooting is turning up some very weird stuff. I've tried posting this to both the ProxySQL and the PHP-FPM forums, but haven't heard anything back. I thought I'd ask here in case anyone has any ideas or suggestions where I can make further inquiries. Since I CAN connect to the ProxySQL proxy via the mysql client, I have to assume the problem is on the PHP-FPM side, but I can't figure out how to contact those folks outside the forum that appears dead.

Note: While I say I'm using the Percona Repo's ProxySQL, due to a variety of reasons that make me unwilling to rely on anything they have been in contact with, I have replaced my ProxySQL instance with one from the ProxySQL author's repo. Otherwise everything else is the same.

----------------

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:

Code: Select all

<?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

User avatar
TrevorH
Forum Moderator
Posts: 29140
Joined: 2009/09/24 10:40:56
Location: Brighton, UK

Re: Weirdness with PHP-FPM and UDS sockets (and, possibly, ProxySQL)

Post by TrevorH » 2020/04/26 22:58:50

Perhaps your php-fpm has PrivateTmp=True in its systemd unit file?
CentOS 6 will die in November 2020 - migrate sooner rather than later!
Info for USB installs on http://wiki.centos.org/HowTos/InstallFromUSBkey
CentOS 5 is dead, do not use it.
Full time Geek, part time moderator. Use the FAQ Luke

User avatar
KernelOops
Posts: 278
Joined: 2013/12/18 15:04:03
Location: xfs file system

Re: Weirdness with PHP-FPM and UDS sockets (and, possibly, ProxySQL)

Post by KernelOops » 2020/04/27 06:26:27

I think centos by default requires sockets to exist under /run (previously /var/run) and SELinux contexts has some enforcement requirements for that. From your error it seems like php-fpm can't find the socket file.

As TrevorH mentioned, check the php-fpm service for any extra restrictions. Usually there is an override .conf file under:
/etc/systemd/system/php-fpm.service.d

Here is what I use with php-fpm:

Code: Select all

[Service]
PrivateTmp=true
ProtectHome=false
ProtectSystem=full
NoNewPrivileges=false
PrivateDevices=true
RestrictAddressFamilies=AF_INET AF_INET6 AF_UNIX
CapabilityBoundingSet=~ CAP_NET_ADMIN CAP_SYS_ADMIN CAP_SYS_BOOT CAP_SYS_MODULE
--
I love my computer - all my friends live there.
--

sawozny
Posts: 40
Joined: 2019/07/13 22:19:14

Re: Weirdness with PHP-FPM and UDS sockets (and, possibly, ProxySQL)

Post by sawozny » 2020/04/27 20:14:03

TrevorH wrote:
2020/04/26 22:58:50
Perhaps your php-fpm has PrivateTmp=True in its systemd unit file?
It does, indeed. Could you help me understand why that would be a problem? I would understand if ProxySQL had PrivateTmp enabled (making the /tmp socket file I'm having a problem getting to inaccessible to other processes) but I checked and it does not. It DOES have PrivateDevices enabled but my understanding of that directive is that it only affects the /dev area and should have no influence on a general file system operation outside that area. But ProxySQL isn't trying to connect to a /tmp resource controlled by PHP-FPM; it's the other way around. I guess if PrivateTmp was a bidirectional thing (no other processes can get to it's /tmp files AND it cannot access anything in the general /tmp area) then I could see it causing problems, but I can't see anything indicating that in the systemd documentation.

Is that what's actually happening here? If so, can you point me at documentation for that so I can figure out what my workaround is going to look like?

Thanks,

Scott

sawozny
Posts: 40
Joined: 2019/07/13 22:19:14

Re: Weirdness with PHP-FPM and UDS sockets (and, possibly, ProxySQL)

Post by sawozny » 2020/04/27 20:29:29

KernelOops wrote:
2020/04/27 06:26:27
I think centos by default requires sockets to exist under /run (previously /var/run) and SELinux contexts has some enforcement requirements for that. From your error it seems like php-fpm can't find the socket file.

As TrevorH mentioned, check the php-fpm service for any extra restrictions. Usually there is an override .conf file under:
/etc/systemd/system/php-fpm.service.d

Here is what I use with php-fpm:

Code: Select all

[Service]
PrivateTmp=true
ProtectHome=false
ProtectSystem=full
NoNewPrivileges=false
PrivateDevices=true
RestrictAddressFamilies=AF_INET AF_INET6 AF_UNIX
CapabilityBoundingSet=~ CAP_NET_ADMIN CAP_SYS_ADMIN CAP_SYS_BOOT CAP_SYS_MODULE
As ProxySQL's socket file in /tmp is accessible when I use the command line MySQL client, I can't see how it's presence in the /tmp folder could be conditionally offending the operating system when requests to it come through PHP-FPM, but I'll try relocating it to /run and see if that makes a difference.

I don't believe SELinux is the culprit here because I've looked through audit2allow output and see nothing for the httpd_t type label (the context under which PHP-FPM runs) nothing related in a sudo ausearch -m AVC at all and I've also tried this whole thing with SELinux running in permissive mode. Also, just from a DAC perspective the socket files is 777 so that shouldn't be getting in the way of the file being accessed either and when I run mysql with a socket connect as my regular user ID, it works fine.

Here are my .service files for PHP-FPM and ProxySQL if there's anything in there you think might be causing this issue.

/etc/systemd/system/multi-user.target.wants/php-fpm.service

Code: Select all

[Unit]
Description=The PHP FastCGI Process Manager
After=syslog.target network.target

[Service]
Type=notify
PIDFile=/run/php-fpm/php-fpm.pid
EnvironmentFile=/etc/sysconfig/php-fpm
ExecStart=/usr/sbin/php-fpm --nodaemonize
ExecReload=/bin/kill -USR2 $MAINPID
PrivateTmp=true

[Install]
WantedBy=multi-user.target
/etc/systemd/system/multi-user.target.wants/proxysql.service

Code: Select all

[Unit]
Description=High Performance Advanced Proxy for MySQL
After=network.target

[Service]
Type=forking
RuntimeDirectory=proxysql
#PermissionsStartOnly=true
#ExecStartPre=/usr/bin/mkdir -p /var/run/proxysql /var/run/proxysql
#ExecStartPre=/usr/bin/chown -R proxysql: /var/run/proxysql/
ExecStart=/usr/bin/proxysql -c /etc/proxysql.cnf
#PIDFile=/var/lib/proxysql/proxysql.pid
#StandardError=null  # all output is in stderr
SyslogIdentifier=proxysql
Restart=no
User=proxysql
Group=proxysql
PermissionsStartOnly=true
UMask=0007
LimitNOFILE=102400
LimitCORE=1073741824
ProtectHome=yes
NoNewPrivileges=true
CapabilityBoundingSet=CAP_SETGID CAP_SETUID CAP_SYS_RESOURCE
RestrictAddressFamilies=AF_INET AF_INET6 AF_UNIX AF_ALG
ProtectSystem=full
PrivateDevices=yes

[Install]
WantedBy=multi-user.target
In the meantime, I'll see what moving this socket file to /run will do.

Thanks,

Scott

sawozny
Posts: 40
Joined: 2019/07/13 22:19:14

Re: Weirdness with PHP-FPM and UDS sockets (and, possibly, ProxySQL)

Post by sawozny » 2020/04/27 21:45:08

Winner Winner! Chicken Dinner! :)

When I relocate the socket file to /run/proxysql/proxysql.sock PHP-FPM speaks to it just fine and retrieves the data, as requested.

Any idea why this is? I'm not really a stranger to troubleshooting and writing custom policies for SELinux and I couldn't see any complaints. Could it be that running PHP-FPM with PrivateTmp prevents it from accessing any resources in the /tmp directory even for other processes? The documentation doesn't seem to be written that way, and I'm open to the idea, conceptually, but I'd like to understand WHY, if I can.

Either way, I'll take the win, so thanks very much to those who responded! :)

Scott

sawozny
Posts: 40
Joined: 2019/07/13 22:19:14

Re: Weirdness with PHP-FPM and UDS sockets (and, possibly, ProxySQL)

Post by sawozny » 2020/04/27 21:49:43

Doh! Did another read through now that I'm less stressed out about this and I see this:
If true, sets up a new file system namespace for the executed processes and mounts private /tmp and /var/tmp directories inside it that is not shared by processes outside of the namespace. This is useful to secure access to temporary files of the process, but makes sharing between processes via /tmp or /var/tmp impossible.
So, the PHP-FPM process can't even SEE what's in regular /tmp (hence the "file does not exist" error). Duh! :)

Again, very much appreciate those who responded. Mystery solved. :)

Thanks,

Scott

Post Reply

Return to “CentOS 7 - Software Support”