Bug #68184 [Opn->Csd]: mysqlnd drive generates: MySQL server has gone away

Edit report at https://bugs.php.net/bug.php?id=68184&edit=1 ID: 68184 Updated by: nikic@php.net Reported by: peter0komar at gmail dot com Summary: mysqlnd drive generates: MySQL server has gone away -Status: Open +Status: Closed Type: Bug Package: PDO MySQL Operating System: Linux PHP Version: 5.5.17 -Assigned To: +Assigned To: nikic Block user comment: N Private report: N Previous Comments: ------------------------------------------------------------------------ [2020-11-26 11:35:24] nikic@php.net This warning has been removed in PHP 7.4 by https://github.com/php/php-src/commit/2856afc70e50b85424b2bd2d6653020679160a0b. ------------------------------------------------------------------------ [2015-02-20 10:59:24] sergey dot shilko at gmail dot com PHP 5.5.21-1+deb.sury.org~precise+2 (cli) (built: Jan 26 2015 20:02:42) Same here, the documentation does not say anything about mysqli_real_connect generating a E_WARNING: Error (E_WARNING): mysqli_real_connect(): MySQL server has gone away $handler = mysqli_init(); if ($handler && $handler instanceof mysqli && mysqli_options($handler, MYSQLI_OPT_CONNECT_TIMEOUT, 5)) { //notice init && options calls are both SUCCESSFULL //generated E_WARNING here with persistent connections $connected = mysqli_real_connect(....) } ------------------------------------------------------------------------ [2014-10-08 08:52:25] peter0komar at gmail dot com none ------------------------------------------------------------------------ [2014-10-08 08:50:04] peter0komar at gmail dot com Description: ------------ Hello. I have a problem with mysqlnd driver. I use PDO and persistent connection to MySQL and in error.log I have lot of warnings: Warning (2): PDO::__construct(): MySQL server has gone away. I investigated this issues and see that happened when connection time expired on mysql server side but PHP doesn't check status of this connection and tries to use it even this connection is has already expired, generates warning and creates new connection. But warnings is not good. It is possible to avoid this warnings when used persistent connections but without operator @ or handler warnings? I checked on PHP 5.3 without mysqlnd driver and this error not reproduced there. This issue reproduced on PHP 5.4, 5.5, 5.6. I checked different timeout settings for mysql and PHP but problem still reproduced. This is full version of test script: <?php date_default_timezone_set('Europe/Kiev'); function errors($errno, $errstr, $errfile, $errline) { echo ' ERROR|WARNING: ' . "[$errno] $errstr File: $errfile : $errline" . PHP_EOL; } function msg($name, $message) { echo "\t - " . date('Y-m-d H:i:s') .' ' .$name.': ' . $message . PHP_EOL; } set_error_handler('errors'); //Set timeout $timeout = 9; $timeWait = 12; $dsn = "mysql:host=127.0.0.1;port=3306;dbname=test2"; $flags = array( PDO::ATTR_PERSISTENT => true, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8', PDO::ATTR_TIMEOUT => $timeout, ); ini_set('connect_timeout', $timeout); ini_set('default_socket_timeout', $timeout); ini_set('mysql.connect_timeout', $timeout); ini_set('mysqlnd.net_read_timeout', $timeout); echo 'PHP params:' . PHP_EOL; msg('connect_timeout', ini_get('connect_timeout')); msg('default_socket_timeout', ini_get('default_socket_timeout')); msg('mysql.connect_timeout', ini_get('mysql.connect_timeout')); msg('mysqlnd.net_read_timeout', ini_get('mysqlnd.net_read_timeout')); $con = new PDO($dsn, 'root', '', $flags); echo 'PDO driver params:' . PHP_EOL; msg('Driver Name', $con->getAttribute(PDO::ATTR_DRIVER_NAME)); msg('Client Version', $con->getAttribute(PDO::ATTR_CLIENT_VERSION)); msg('Connection Status', $con->getAttribute(PDO::ATTR_CONNECTION_STATUS)); echo PHP_EOL; echo 'MySQL timeout variables' . PHP_EOL; echo '-------------------------------------------------------------------- ' . PHP_EOL; foreach ($con->query('SHOW SESSION VARIABLES LIKE "%wait%";') as $row) { msg($row['Variable_name'], $row['Value']); } echo '---------------------------------------------------------------------' . PHP_EOL; msg('Wait', $timeWait . ' secs'); sleep($timeWait); msg('Get Connection Status', $con->getAttribute(PDO::ATTR_CONNECTION_STATUS)); msg('Try to use previous connection ... ', ''); //Generate warning $con = new PDO($dsn, 'root', '', $flags); msg('Done', ''); Test script: --------------- <?php function errors($errno, $errstr, $errfile, $errline){ echo ' ERROR|WARNING: ' . "[$errno] $errstr File: $errfile : $errline" . PHP_EOL; } set_error_handler('errors'); //Set timeout $timeout = 9; $timeWait = 12; $dsn = "mysql:host=127.0.0.1;port=3306;dbname=test2"; $flags = array(PDO::ATTR_PERSISTENT => true, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8', PDO::ATTR_TIMEOUT => $timeout); ini_set('connect_timeout', $timeout); $con = new PDO($dsn, 'root', '', $flags); $con->query('SHOW SESSION VARIABLES LIKE "%wait%";'); sleep($timeWait); $con = new PDO($dsn, 'root', '', $flags); echo 'Done'; Expected result: ---------------- Done Actual result: -------------- ERROR|WARNING: [2] PDO::__construct(): MySQL server has gone away File: /home/peter/Documents/scripts/mysql/tets_bug.php : 18 Done ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=68184&edit=1