|
php.net | support | documentation | report a bug | advanced search | search howto | statistics | random bug | login |
[2014-10-08 08:50 UTC] 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
PatchesPull RequestsHistoryAllCommentsChangesGit/SVN commits
|
|||||||||||||||||||||||||||||||||||||
Copyright © 2001-2025 The PHP GroupAll rights reserved. |
Last updated: Mon Oct 27 01:00:02 2025 UTC |
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(....) }