Php update (ratchet+symfony) PDOException : MySQL server has gone away

[Leonardo Pucci]

Hello Friends, 

This is just a head´s up about an error that i am facing after a php update. 

When I added symfony  to my ratchet, i had to use the PDO object inside it. 

So this is the code that i have added to my ratchet server:

$pdo = new PDO('mysql:host=localhost;dbname=reacher', 'root', 'xxxxxxxxxxxxxxx',array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$dbOptions = array(

    'db_table'      => 'Base_Sessoes_Symfony',

    'db_id_col'     => 'session_id',

    'db_data_col'   => 'session_value',

    'db_time_col'   => 'session_time',);

$session = new SessionProvider( new Chat() ,new Handler\PdoSessionHandler($pdo,$dbOptions));

My new version of php is:

PHP 5.5.14 (cli) (built: Jun 27 2014 11:23:47)

Copyright (c) 1997-2014 The PHP Group

Zend Engine v2.5.0, Copyright (c) 1998-2014 Zend Technologies

    with Zend OPcache v7.0.4-dev, Copyright (c) 1999-2014, by Zend Technologies

So what is happening: the server is online. After some time, the websocket freezes and there is no answer on the browser. 

The browser keeps trying to connect and the script generates this error:

PDOException was thrown when trying to read the session data: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

So after some research, this seems to be related to persistent connection. 

(http://dev.mysql.com/doc/refman/5.0/en/gone-away.html read the comments)

So trying to solve this problem, i am adding this: PDO::ATTR_PERSISTENT => false

$pdo = new PDO('mysql:host=localhost;dbname=reacher', 'root', 'xxxxxxxxxxxxxxxxxxx',array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8",PDO::ATTR_PERSISTENT => false));

I was used to run my ratchet on a vps with 256mb of memory. 

After this upgrade, my server kept consuming memory. Today I am at a 1gb server and the problem still ocurrs. 

I will try to fix this, then i will try to downgrade to a less expensive server again. 

If the problem fixes itself with this mod, i will let you know.

Pucci

[Leonardo Pucci]

An Update, 

It is a PDO long connection problem. 

the error is thrown here:

public function onError(ConnectionInterface $conn, \Exception $e) {

Seems that the problem is that the connection times out. 

The script keep running and them it drops because the time is up. 

On my.cnf (mysql conf file) :

wait_timeout variable is 28800 seconds, which is 8 hours.

On my case this happens at night. Because the system become idle and there is no connections. So the connection is dropped. 

I will duplicate this value to see if solves the problem.

On /etc/my.cnf:

[mysqld]

wait_timeout=57600

If you don´t have the wait_timeout field in my.cnf, this is normal. It is using default (28800s)

you can check on mysql client:

mysql> show global variables like '%timeout%';

+----------------------------+----------+

| Variable_name              | Value    |

+----------------------------+----------+

| connect_timeout            | 10       |

| delayed_insert_timeout     | 300      |

| innodb_lock_wait_timeout   | 50       |

| innodb_rollback_on_timeout | OFF      |

| interactive_timeout        | 28800    |

| lock_wait_timeout          | 31536000 |

| net_read_timeout           | 30       |

| net_write_timeout          | 60       |

| slave_net_timeout          | 3600     |

| wait_timeout               | 57600    |

+----------------------------+----------+

=)

[attozk]

I ran into similar issue not with PDO but reactphp-mysql (for non-sessions related stuff).

I solved mine by creating a query() wrapper function, which would catch disconnect exception and reconnect.

[Leonardo Pucci]

Like this one?

 try {
            $this->connection()->query("SHOW STATUS;")->execute();
        } catch(\PDOException $e) {
            if($e->getCode() != 'HY000' || !stristr($e->getMessage(), 'server has gone away')) {
                throw $e;

            }//Only reconnect if mysql error is gone away 
   $this->reconnect();
        }
 
  public function reconnect()
    {
        $this->pdo = null;
        return $this->connect();
    }

 public function connect()
    {
        $this->pdo = new PDO($this->dsn, $this->username, $this->password, (array) $this->driver_options); 
        $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        return $this->pdo;
    }
            $this->reconnect();

[Leonardo Pucci]

4 days after my modification, the problem still occurs. 

But now the behavior is a little different. 

The Mysql message now does not shows up, no error is throwed, but the server cannot send a request to the browser.

I know that is still related to PDO, because I have two types of users: admin and client. And my logs shows admin as a client. It cannot get session data from the mysql

 public function onMessage(ConnectionInterface $from, $msg) {

//Synfony2 Stuff two detect if it is admin:
$Admin_Id = $conn->Session->get('admin_id');
 if(isset($Admin_Id) && !empty($Admin_Id)){
echo "New ADMIN conection  ({$conn->resourceId})\n";
//Admin stuff here
}else{
echo "New CLIENT conection  ({$conn->resourceId})\n";

$this->WebSid_Conexao[$conn->resourceId]->send($json);

}

And what my log shows:

New CLIENT connection  (195)

[developm...@gmail.com]

I had the same issue. I solved it by switching to Memcached session handler (instead of PDO session handler).

[Leonardo Pucci]

I think now it is solved:

I have replaced the php version, so the php.ini file lost the cookie domain path.

My site runs at www.site.com.br

Ratchet runs under subdomain.site.com.br

So this has to be added: 

session.cookie_domain = ".site.com.br"

Let´s see how many days it goes!