php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #68184 mysqlnd drive generates: MySQL server has gone away
Submitted: 2014-10-08 08:50 UTC Modified: 2017-04-02 14:49 UTC
Votes:11
Avg. Score:4.3 ± 1.1
Reproduced:9 of 9 (100.0%)
Same Version:2 (22.2%)
Same OS:7 (77.8%)
From: peter0komar at gmail dot com Assigned:
Status: Open Package: PDO MySQL
PHP Version: 5.5.17 OS: Linux
Private report: No CVE-ID: None
Have you experienced this issue?
Rate the importance of this bug to you:

 [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

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2014-10-08 08:52 UTC] peter0komar at gmail dot com
-Summary: mysqlnd drive generated: MySQL server has gone away +Summary: mysqlnd drive generates: MySQL server has gone away
 [2014-10-08 08:52 UTC] peter0komar at gmail dot com
none
 [2015-02-20 10:59 UTC] 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(....)

}
 [2017-04-02 14:49 UTC] tpunt@php.net
-Package: mysql +Package: PDO MySQL
 
PHP Copyright © 2001-2018 The PHP Group
All rights reserved.
Last updated: Wed Oct 17 11:01:25 2018 UTC