php-fpm process: SQLSTATE[HY000] [2002] Connection timed out with database

thanhtc-kozocom commented 1 month ago

Description

I have a problem with upgrading a server from Centos to Ubuntu (both use Nginx, php, mysql, laravel application)

OS version:

Centos: CentOS Linux release 7.9.2009 (Core)
Ubuntu: Ubuntu 22.04.4 LTS

Nginx version:

Centos: nginx/1.20.1
Ubuntu: nginx/1.18.0 (Ubuntu)

PHP version:

Centos: PHP 8.1.29 (cli)
Ubuntu: PHP 8.1.29 (cli)

mysql connection is the same for both servers

php-fpm config is the same for both servers, command check `php-fpm8.1 -tt`

pm = dynamic
pm.max_children = 500
pm.start_servers = 50
pm.min_spare_servers = 50
pm.max_spare_servers = 350
pm.max_spawn_rate = 32
pm.process_idle_timeout = 10
pm.max_requests = 0

When the number of requests is large, the Centos server will operate normally, but the Ubuntu server will have some requests with connection timeout errors as follows:

SQLSTATE[HY000] [2002] Connection timed out {"exception":"[object] (PDOException(code: 2002): SQLSTATE[HY000] [2002] Connection timed out at /vendor/laravel/framework/src/Illuminate/Database /Connectors/Connector.php:70)

Could you please share more knowledge about this issue?

Thank you 🙇

PHP Version

PHP 8.1.29

Operating System

Ubuntu 22.04.4 LTS

SakiTakamachi commented 1 month ago

Could you try with PHP version 8.2 or higher? Bug fixes for 8.1 have been ended.

thanhtc-kozocom commented 1 month ago

Hi @SakiTakamachi I have tried with PHP 8.2.21 on Ubuntu OS, but some requests got the same error:

SQLSTATE[HY000] [2002] Connection timed out {"exception":"[object] (PDOException(code: 2002): SQLSTATE[HY000] [2002] Connection timed out at /vendor/laravel/framework/src/Illuminate/Database/Connectors/Connector.php:70

SakiTakamachi commented 1 month ago

I'm sorry for replying late. What is the state of the PDO attribute value options? In particular, I'd like to know if you're using persistent connections.

thanhtc-kozocom commented 1 month ago

Hi @SakiTakamachi This is my config in php.ini

[Pdo_mysql]

pdo_mysql.cache_size = 2000
pdo_mysql.default_socket=

[ODBC]

odbc.allow_persistent = On
odbc.check_persistent = On
odbc.max_persistent = -1
odbc.max_links = -1
odbc.defaultlrl = 4096
odbc.defaultbinmode = 1

[MySQLi]

mysqli.max_persistent = -1
mysqli.allow_persistent = On
mysqli.max_links = -1
mysqli.cache_size = 2000
mysqli.default_socket =
mysqli.reconnect = Off

SakiTakamachi commented 1 month ago

@thanhtc-kozocom Thank you. Is the timeout only an issue with PDO? Also, are you using PDO::ATTR_PERSISTENT?

thanhtc-kozocom commented 1 month ago

@SakiTakamachi

Also, are you using PDO::ATTR_PERSISTENT?

No, I am not using PDO::ATTR_PERSISTENT

This is my PDO options:

PDO::ATTR_CASE => PDO::CASE_NATURAL,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_ORACLE_NULLS => PDO::NULL_NATURAL,
PDO::ATTR_STRINGIFY_FETCHES => false,
PDO::ATTR_EMULATE_PREPARES => false,

SakiTakamachi commented 1 month ago

@thanhtc-kozocom Thanks, if this is the case, I think it's probably not a php problem. You wrote that you had no problems with your previous servers, how many accesses does each server actually have?

bukka commented 1 month ago

Also check the the MySQL server configuration if there are any difference in options. It might potentially have some lower limits. You might also check its logs to see if there is anything suspicious.

php / php-src