Zend TableGateway can't execute 2 consecutive query

[aditbisa]

Hi Guys,

Firtsly, I'm sorry if I miss placed the question. After weeks learning the framework with this skeleton, I just desperate facing this issue.

It seems I can't run 2 consecutive query using TableGateway. It's always produce (not just UPDATE):

"Statement couldn't be produced with sql: UPDATE `users` SET `last_access` = NOW() WHERE `id` = ?"

Here's the code inside ZendUserRepository:

...
    public function footprint(int $id): void {
        $data = ['last_access' => new Predicate\Expression('NOW()')];
        $where = ['id' => $id];
        $this->table->update($data, $where);
    }
    
    public function authenticate(string $username, string $password): bool {
        $where = [
            'username' => $username,
            new Predicate\IsNotNull('roles')
        ];
        $rowset = $this->table->select($where);
        if (null === $row = $rowset->current()) {
            return false;
        }
        $data = (array) $row;
        if(password_verify($password, $data['password'])) {
            $this->footprint($data['id']);
            return true;
        }
        return false;
    }
...

Please, kindly help this newbie here.

Best Regards,
Adit

[aditbisa]

Using PHPUnit test, I got the following result:

Zend\Db\Adapter\Exception\InvalidQueryException: Statement couldn't be produced with sql: UPDATE `users` SET `last_access` = NOW() WHERE `id` = ?

/vagrant/vendor/zendframework/zend-db/src/Adapter/Driver/Mysqli/Statement.php:208
/vagrant/vendor/zendframework/zend-db/src/Adapter/Driver/Mysqli/Statement.php:229
/vagrant/vendor/zendframework/zend-db/src/TableGateway/AbstractTableGateway.php:391
/vagrant/vendor/zendframework/zend-db/src/TableGateway/AbstractTableGateway.php:349
/vagrant/src/Infrastructure/ZendUserRepository.php:90
/vagrant/src/Infrastructure/ZendUserRepository.php:104
/vagrant/src/Application/User/UserAuthenticationHandler.php:19
/vagrant/tests/Application/User/UserAuthenticationHandlerTest.php:41

Caused by
Zend\Db\Adapter\Exception\ErrorException: Commands out of sync; you can't run this command now

And from google lead to https://stackoverflow.com/a/614741/3164944

You can't have two simultaneous queries because mysqli uses unbuffered queries by default (for prepared statements; it's the opposite for vanilla mysql_query). You can either fetch the first one into an array and loop through that, or tell mysqli to buffer the queries (using $stmt->store_result()).

[aditbisa]

Solved with additional configuration:

[
        "driver" => "Mysqli",
        "database" => getenv("DB_NAME"),
        "username" => getenv("DB_USER"),
        "password" => getenv("DB_PASSWORD"),
        "hostname" => getenv("DB_HOST"),
        "charset" => "utf8",
        'options' => ['buffer_results' => true],
]

Advice?

[tuupola]

Reading the docs suggest the following:

This means that query results are immediately transferred from the MySQL Server to PHP and then are kept in the memory of the PHP process. This allows additional operations like counting the number of rows, and moving (seeking) the current result pointer. It also allows issuing further queries on the same connection while working on the result set. The downside of the buffered mode is that larger result sets might require quite a lot memory.

So to me it seem setting buffer_results to true is a good idea. I will add it to default settings of the skeleton.

Thanks!