PHP Warning: PDOStatement::execute(): Error reading result

[DeltaVetal26]

Hi!

I'm using RedBean for my WebSocket server and often see this warning when I run queries against the database:

PHP Warning:  PDOStatement::execute(): MySQL server has gone away in /home/vendo                                                                                                                                                             r/libs/rb.php on line 747

Warning: PDOStatement::execute(): MySQL server has gone away in /home/vendor/lib                                                                                                                                                             s/rb.php on line 747
PHP Warning:  PDOStatement::execute(): Error reading result set's header in /hom                                                                                                                                                             e/vendor/libs/rb.php on line 747

Warning: PDOStatement::execute(): Error reading result set's header in /home/ven                                                                                                                                                             dor/libs/rb.php on line 747
[HY000] - SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
trace: #0 /home/vendor/libs/rb.php(1035): RedBeanPHP\Driver\RPDO->runQuery('SELE                                                                                                                                                             CT mode FRO...', Array)
#1 /home/vendor/libs/rb.php(1072): RedBeanPHP\Driver\RPDO->GetAll('SELECT mode F                                                                                                                                                             RO...', Array)
#2 /home/vendor/libs/rb.php(4246): RedBeanPHP\Driver\RPDO->GetOne('SELECT mode F                                                                                                                                                             RO...', Array)
#3 /home/vendor/libs/rb.php(11526): RedBeanPHP\Adapter\DBAdapter->getCell('SELEC                                                                                                                                                             T mode FRO...', Array)
#4 /home/vendor/libs/rb.php(12442): RedBeanPHP\Facade::query('getCell', 'SELECT                                                                                                                                                              mode FRO...', Array)
#5 /home/vendor/wstest.php(27): RedBeanPHP\Facade::getCell('SELECT mode FRO...',                                                                                                                                                              Array)
#6 [internal function]: {closure}(Object(Workerman\Connection\TcpConnection), 'H                                                                                                                                                             ello World!')
#7 /home/vendor/workerman/workerman/Connection/TcpConnection.php(645): call_user                                                                                                                                                             _func(Object(Closure), Object(Workerman\Connection\TcpConnection), 'Hello World!                                                                                                                                                             ')
#8 [internal function]: Workerman\Connection\TcpConnection->baseRead(Resource id                                                                                                                                                              #30)
#9 /home/vendor/workerman/workerman/Events/Select.php(289): call_user_func_array                                                                                                                                                             (Array, Array)
#10 /home/vendor/workerman/workerman/Worker.php(2231): Workerman\Events\Select->                                                                                                                                                             loop()
#11 /home/vendor/workerman/workerman/Worker.php(1356): Workerman\Worker->run()
#12 /home/vendor/workerman/workerman/Worker.php(1188): Workerman\Worker::forkOne                                                                                                                                                             WorkerForLinux(Object(Workerman\Worker))
#13 /home/vendor/workerman/workerman/Worker.php(1162): Workerman\Worker::forkWor                                                                                                                                                             kersForLinux()
#14 /home/vendor/workerman/workerman/Worker.php(478): Workerman\Worker::forkWork                                                                                                                                                             ers()
#15 /home/vendor/wstest.php(51): Workerman\Worker::runAll()
#16 {main}
Worker[11000] process terminated
worker[none:11000] exit with status 64000

This does not always happen, only after a few requests. How to fix it?

I`m using:

• PHP 7.0.30-0ubuntu0.16.04.1 (cli) ( NTS )
• mysql Ver 14.14 Distrib 5.7.23, for Linux (x86_64) using EditLine wrapper
• RedBean - I can not figure out where to find the version. Downloaded in the spring.

[Lynesth]

Hey there,

It looks like the mysql server is timing out. I don't know how your script is supposed to work but mysql will close the connection if it doesn't receive any request for some time.

I guess you could increase the wait_timeout or set PDO::ATTR_PERSISTENT to true using R::getDatabaseAdapter()->getDatabase()->getPDO()->setAttribute().

You could also (and that's how I'd do it I think) check if the connection is still active and reconnect if needed before making a request.

Note: I may be wrong and the issue may lie elsewhere.

Lyn

[DeltaVetal26]

@Lynesth

Thanks for the advice! I increased wait_timeout from 10 to 30 seconds, after 7 requests I saw this warning again. How can I make sure that the connection was active? The request was successful even when a warning was displayed.

[Lynesth]

Hmmm, just to make sure we're hunting the right issue, could you call those before executing your requests, which should force closing and reopening the connection:

R::getDatabaseAdapter()->getDatabase()->close();
R::getDatabaseAdapter()->getDatabase()->connect();

Let me know if this fixes your problem.

[DeltaVetal26]

@Lynesth

Now everything works without warnings) Is this the final solution to the problem or disguise? I'm curious what caused this problem

[Lynesth]

Ok so there currently isn't a way to do that directly using RedBean but here are different approaches:

1) Use a very high timeout 2) Use the previous code I gave you, disconnecting from the DB and reconnecting each time 3) Make a dummy query to check if you need to reconnect. Something like this:

// Just to prevent repeating this every time assign it to a variable before the loop if possible
$dbHandler = R::getDatabaseAdapter()->getDatabase();

// Then before making your queries, do something like
try {
    $dbHandler->getPDO()->query( 'SELECT 1' );
} catch (PDOException $e) {
    $dbHandler->close();
    $dbHandler->connect();
}

// Your queries

Not sure which would be the most efficient.

@gabordemooij I think we need a real testConnection() functions that actually tests the connection ;)

[DeltaVetal26]

@Lynesth

I will use the previous code. Thank you very much!

[Lynesth]

@DeltaVetal26 No problem :)

I think the 3rd code might be the most efficient one (if it works, everything is untested) because making a simple SELECT statement is very light on data transfer and server load compared to a reconnect. So my guess would be that, unless you expect to have exceeded the timeout almost everytime, the 3rd code should be better.

[DeltaVetal26]

@Lynesth

$dbHandler = R::getDatabaseAdapter()->getDatabase();
 // Then before making your queries, do something like
 try {
    $dbHandler->getPDO()->query( 'SELECT owner FROM session WHERE evid = 001' );
  } catch (PDOException $e) {
    $dbHandler->close();
    $dbHandler->connect();
  }

$state = R::getCell( 'SELECT mode FROM evsmode WHERE prta = ? ', array($key));

$sess_db = R::dispense('session');
$sess_db->evid = $numb;
$sess_db->evcid = $stationid;
R::store($sess_db);

$statuscheck = R::findOne('session', evid = ?', array($numb));

The warning appears again. I did something wrong?

[Lynesth]

Ok so, my bad I forgot the @. But you really just have to do a SELECT 1 to test the connection, it'll be very fast. Tell me if this works and removes the errors.

$dbHandler = R::getDatabaseAdapter()->getDatabase();
 // Then before making your queries, do something like
 try {
    @$dbHandler->getPDO()->query( 'SELECT 1' );
  } catch (PDOException $e) {
    $dbHandler->close();
    $dbHandler->connect();
  }

$state = R::getCell( 'SELECT mode FROM evsmode WHERE prta = ? ', array($key));

$sess_db = R::dispense('session');
$sess_db->evid = $numb;
$sess_db->evcid = $stationid;
R::store($sess_db);

$statuscheck = R::findOne('session', evid = ?', array($numb));

[DeltaVetal26]

The problem was the absence of @. Now all is well. Thank you! :)

[Lynesth]

No problem ;)

Don't close the issue yet though, I'd like to have @gabordemooij's opinion on this.

[gabordemooij]

We could extend the testConnection function to test a specific SQL scenario maybe?

However, the MySQL server should not behave this way. You have to figure out why the connection is lost. Have you checked your max allowed packet setting?

[mysqld]
max_allowed_packet=16M

[DeltaVetal26]

@gabordemooij

Yes

[DeltaVetal26]

This problem occurs in different places. First everything is in order, after several requests a warning may appear. @Lynesth helped me solve the problem by checking the connection before queries to the database

[Lynesth]

If I guessed correctly, you are using https://github.com/walkor/Workerman which is some script that runs in an endless loop waiting for requests. You first start a database connection then use it when needed but if the time between 2 requests to that script is too long, the MySQL connection will be closed, hence the error.

You could try using Workerman's Timer to "ping" the database every X seconds with a simple SELECT 1 (or even DO 1 to keep it even shorter). This should prevent the issue you're encountering. You could even reset the timer if a real request has been made so that you don't make too many of those.

Note: I don't know how to use Workerman, so not sure of the feasibility of the above ideas.

[DeltaVetal26]

@Lynesth

Yes. This is a WebSocket server through which I manage devices. Every time I connect to this server, authorization with requests to the database is performed. It uses a loop.

Your code fixed the problem, and now I do not see any warnings.