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

1
2
3
4
5
6
7
8
9
10
11
12
13
<?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

1
2
3
4
5
6
7
8
9
10
11
$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

1
2
3
4
5
6
7
8
9
10
11
12
$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

1
2
3
4
5
6
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"
}

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

1
2
3
4
5
6
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
<?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!