Php script hangs at "new PDO" object creation with mysqlrouter

Question

I refer to the questions: Php script hangs at "new PDO" object creation Long time this question had been asked, but I have exactly the same issue, with mysqlrouter.

PDO connection hang without error/log

A very simple connection program:

<?php

$dsn = "mysql:host=127.0.0.1;port=6446;dbname=XXXXXX;charset:utf8;";
$user = "nonrootaccount";
$password = "xxxxxxxxxxxxx";

//$user = 'root';
//$password = 'xxxxxxxxxx';

$options = [
    \PDO::ATTR_ERRMODE            => \PDO::ERRMODE_EXCEPTION,
    \PDO::MYSQL_ATTR_COMPRESS => false,
];

$pdo = new PDO($dsn, (string) $user, (string) $password, (array) $options);

The root account can connect (if I uncomment the lines...) The non root account cannot. If password or rights are bad, I have an error message, but as soon as the login parameters are good, the program hang on the new PDO.

Environmnent:

• Php7.4
• MySQL Router Ver 8.0.34-0ubuntu0.20.04.1
• Mysql Server version: 8.0.27 MySQL Community Server

The problem doesn't appear with php8.2 but I can't upgrade just now...

I try to suppress options.

An strace on process show the connection to DB and stop on the poll syscall, program is waiting on input:

connect(3, {sa_family=AF_INET, sin_port=htons(6446), sin_addr=inet_addr("127.0.0.1")}, 16) = -1 EINPROGRESS (Operation now in progress)
poll([{fd=3, events=POLLIN|POLLOUT|POLLERR|POLLHUP}], 1, 10000) = 1 ([{fd=3, revents=POLLOUT}])
getsockopt(3, SOL_SOCKET, SO_ERROR, [0], [4]) = 0
fcntl(3, F_SETFL, O_RDWR)               = 0
setsockopt(3, SOL_TCP, TCP_NODELAY, [1], 4) = 0
setsockopt(3, SOL_SOCKET, SO_KEEPALIVE, [1], 4) = 0
poll([{fd=3, events=POLLIN|POLLERR|POLLHUP}], 1, 86400000) = 1 ([{fd=3, revents=POLLIN}])
recvfrom(3, "J\0\0\0\n8.0.27\0\24\266u\2;%p#\36H5\17\0\337\377\377\2\0\377\323"..., 32768, MSG_DONTWAIT, NULL, NULL) = 78
sendto(3, "\230\0\0\1\215\242\33\0\0\0\0\300\377\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 156, MSG_DONTWAIT, NULL, 0) = 156
poll([{fd=3, events=POLLIN|POLLERR|POLLHUP}], 1, 86400000) = 1 ([{fd=3, revents=POLLIN}])
recvfrom(3, "\7\0\0\2\0\0\0\2\0\0\0", 32768, MSG_DONTWAIT, NULL, NULL) = 11
poll([{fd=3, events=POLLIN|POLLERR|POLLHUP}], 1, 86400000

On the mysqlrouter side, same thing, some dialogue and wait for input

[pid 1693032] recvmsg(62,  <unfinished ...>
[pid 1693032] <... recvmsg resumed>{msg_name=NULL, msg_namelen=0, msg_iov=[{iov_base="\2\0\0\2\1\3\7\0\0\3\0\0\0\2\0\0\0", iov_len=16384}], msg_iovlen=1, msg_controllen=0, msg_flags=0}, 0) = 17
[pid 1693032] sendmsg(34, {msg_name=NULL, msg_namelen=0, msg_iov=[{iov_base="\7\0\0\2\0\0\0\2\0\0\0", iov_len=11}], msg_iovlen=1, msg_controllen=0, msg_flags=0}, 0 <unfinished ...>
[pid 1693032] <... sendmsg resumed>)    = 11
[pid 1693032] epoll_ctl(8, EPOLL_CTL_ADD, 34, {EPOLLIN|EPOLLONESHOT|EPOLLET, {u32=34, u64=34}} <unfinished ...>
[pid 1693032] <... epoll_ctl resumed>)  = 0
[pid 1693032] epoll_ctl(8, EPOLL_CTL_ADD, 62, {EPOLLIN|EPOLLONESHOT|EPOLLET, {u32=62, u64=62}} <unfinished ...>
[pid 1693032] <... epoll_ctl resumed>)  = 0
[pid 1693032] epoll_wait(8,  <unfinished ...>

The new PDO doesn't return

Answer 1

As say: php - new PDO() and new mysqli() hanging, but MySQL shows connection

It's a problem of auth plugin in mysql : Using mysql_native_password in place of caching_sha2_password solved the problem...