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();
?>
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
