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
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: peter0komar at gmail dot com
New email:
PHP Version: OS:

 

 [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: Tue Oct 23 15:01:26 2018 UTC