Fixing PHP OPcache mysql_xdevapi Uncaught Error: Call to a member function limit()

Fixing PHP OPcache mysql_xdevapi Uncaught Error: Call to a member function limit()

I have Been playing with the X DevApi on MySQL 8. It's really cool idea and I think I will need to write up about it in the near future. Anyway check it out on PHP Docs, https://www.php.net/manual/en/book.mysql-xdevapi.php and on the MySQL site https://dev.mysql.com/doc/x-devapi-userguide/en/devapi-users-introduction.html.

This is a very good API which has fluent interfaces for CRUD operations on relational tables and with Document Stores.

So I come across a bug on mysql_xdevapi 8.0.17 this only happens when you have Zend OPcache running at the same time.

The Problem

So here's the problem. I am making a collection using the Document Store and then fetching all the records back for pagination, so I need to use a limit and offset clause.

So the code is

<?php

declare(strict_types=1);

use function mysql_xdevapi\getSession;

$session = getSession("mysqlzx://dbuser:!OneTwo3@localhost:33060");

$schema = $session->getSchema('dbname');
$collection = $schema->getCollection("posts");

$collection->addOrReplaceOne('02eb5585a3d74645883b200caa70852f', '{"author":"Shaun Freeman","title":"Title 1","content":"Content 1"}');
$collection->addOrReplaceOne('40089f7d6154426cb28300d05d1ace5c', '{"author":"Shaun Freeman","title":"Title 2","content":"Content 2"}');

So now I've made the collection called 'posts' with two documents in. Now we need to retrieve all the records so we use

$result = $collection->find('true')
    ->offset(0)
    ->limit(2)
    ->execute();

$result = $result->fetchAll();

print "<pre>";
foreach ($result as $row) {
    print_r($row);
}

So now the result we are expecting is

Array
(
    [_id] => 02eb5585a3d74645883b200caa70852f
    [title] => Title 1
    [author] => Shaun Freeman
    [content] => Content 1
)
Array
(
    [_id] => 40089f7d6154426cb28300d05d1ace5c
    [title] => Title 2
    [author] => Shaun Freeman
    [content] => Content 2
)

This works fine when Zend OPcache is not loaded but crashes when OPcache is  loaded and enabled it breaks with the error

Fatal error: Uncaught Error: Call to a member function limit() on bool in /home/projects/public/expressive-xdevapi-demo/public/xdevapi.php:21 Stack trace: #0 {main} thrown in /home/projects/public/expressive-xdevapi-demo/public/xdevapi.php on line 21

Now that's not what I want!

The Solution

So how to get around this from happening?

Well I could turn off OPcache but that's defeating the purpose as I want  my app as fast as possible, it's OK in development but not in production.

So while waiting for a fix I found a workaround by treating the collection as a relational table with the mysql_xdevapi\Schema::getCollectionAsTable(), so we can do the same call but we have to do all the work that find does for us automatically. So to acheive this we would do

$table = $schema->getCollectionAsTable('posts');
$result = $table->select('*')
    ->offset(0)
    ->limit(2)
    ->execute();

$result = $result->fetchAll();

print "<pre>";
foreach ($result as $row) {
    print_r($row);
}

This now returns

Array
(
    [doc] => {"_id": "02eb5585a3d74645883b200caa70852f", "title": "Title 1", "author": "Shaun Freeman", "content": "Content 1"}
    [_id] => 02eb5585a3d74645883b200caa70852f
)
Array
(
    [doc] => {"_id": "40089f7d6154426cb28300d05d1ace5c", "title": "Title 2", "author": "Shaun Freeman", "content": "Content 2"}
    [_id] => 40089f7d6154426cb28300d05d1ace5c
)

Not quite there yet as I want an associative array like the one earlier. As the getCollectionAsTable() function returns an array of rows with an '_id' key and an 'doc' key. it's the 'doc' key we only want so we can alter the foreach() loop to

foreach ($result as $row) {
    if (array_key_exists('doc', $row)) {
        $row = $row['doc'];
    }
    print_r($row);
}

Which gives us

{"_id": "02eb5585a3d74645883b200caa70852f", "title": "Title 1", "author": "Shaun Freeman", "content": "Content 1"}{"_id": "40089f7d6154426cb28300d05d1ace5c", "title": "Title 2", "author": "Shaun Freeman", "content": "Content 2"}

So we just get the raw JSON string so I need now to decode the JSON with json_decode() function, so now to rewrite the foreach() loop which look like

foreach ($result as $row) {
    if (array_key_exists('doc', $row)) {
        $row = json_decode($row['doc'], true);
    }
    print_r($row);
}

And this gives us the output of

Array
(
    [_id] => 02eb5585a3d74645883b200caa70852f
    [title] => Title 1
    [author] => Shaun Freeman
    [content] => Content 1
)
Array
(
    [_id] => 40089f7d6154426cb28300d05d1ace5c
    [title] => Title 2
    [author] => Shaun Freeman
    [content] => Content 2
)

This is what I want. So I've now got the desired output from the collection while keeping OPcache on. Time pat myself on the back and enjoy a nice cup of coffee!

Here's the full script with the work around

<?php

declare(strict_types=1);

use function mysql_xdevapi\getSession;

$session = getSession("mysqlzx://dbuser:!OneTwo3@localhost:33060");

$schema = $session->getSchema('dbname');
$collection = $schema->getCollection("posts");

$collection->addOrReplaceOne('02eb5585a3d74645883b200caa70852f', '{"author":"Shaun Freeman","title":"Title 1","content":"Content 1"}');
$collection->addOrReplaceOne('40089f7d6154426cb28300d05d1ace5c', '{"author":"Shaun Freeman","title":"Title 2","content":"Content 2"}');

try {
    $result = $collection->find('true')
        ->offset(0)
        ->limit(2)
        ->execute();
} catch (Throwable $throwable) {
    // Fatal error: Uncaught Error: Call to a member function limit() on bool
    $table = $schema->getCollectionAsTable('posts');
    $result = $table->select('*')
        ->offset(0)
        ->limit(2)
        ->execute();
}

$result = $result->fetchAll();

print "<pre>";
foreach ($result as $row) {
    if (array_key_exists('doc', $row)) {
        $row = json_decode($row['doc'], true);
        //$row = $row['doc'];
    }
    print_r($row);
}

I hope this was useful to you. I hope this gets fixed soon too.

This extension is still young and is a real game changer as I don't have to write SQL for simple a CRUD and I don't have to use a library like Doctrine or Zend\DB, keeping the code base and libraries to a minimum is always good!

What do you think, let me know and happy coding!


07/10/2019 12:48:00 Shaun Freeman Filed Under: PHP Linux, MySQL, PHP


Twitter Feed
Shaun Freeman @Zendmaster

Shaun Freeman @Zendmaster

Fixing PHP OPcache mysql_xdevapi Uncaught Error: Call to a member function limit() https://t.co/3xSyOxmlj1

Shaun Freeman @Zendmaster

I liked a @YouTube video https://t.co/iI9BZaslSm 8 super heroic Linux commands that you probably aren't using

Shaun Freeman @Zendmaster

I liked a @YouTube video https://t.co/SOOJGb32h0 How to Maximize Your Productivity (As a Software Developer or Learning Programming)

Shaun Freeman @Zendmaster

I liked a @YouTube video https://t.co/8nGfvmdsI2 From Carnivore Diet to Near-Death: Lessons Learned From Today's Most Popular Fad Diet

Shaun Freeman @Zendmaster

I liked a @YouTube video https://t.co/EFLh2y3I24 Carnivore Diet – Myths about Meat – Phil Escott – WHIS 2018