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: 2020-11-26 11:35 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: nikic (profile)
Status: Closed Package: PDO MySQL
PHP Version: 5.5.17 OS: Linux
Private report: No CVE-ID: None
View Add Comment Developer Edit
Anyone can comment on a bug. Have a simpler test case? Does it work for you on a different platform? Let us know!
Just going to say 'Me too!'? Don't clutter the database with that please !
Your email address:
MUST BE VALID
Solve the problem:
28 + 42 = ?
Subscribe to this entry?

 
 [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
 [2020-11-26 11:35 UTC] nikic@php.net
This warning has been removed in PHP 7.4 by https://github.com/php/php-src/commit/2856afc70e50b85424b2bd2d6653020679160a0b.
 [2020-11-26 11:35 UTC] nikic@php.net
-Status: Open +Status: Closed -Assigned To: +Assigned To: nikic
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Thu Apr 18 17:01:28 2024 UTC