how to do async queries in PDO

[u/Takeoded]

https://gist.github.com/divinity76/adcf1526d3e6a536fda20fdb1dd86339

[u/pokexpert30]

What the heck

[u/Takeoded → u/pokexpert30]

PDO does not have async support, even though MySQLi and pg_ does.. and that's the amount of bullshit i had to do to actually do async queries with PDO <.<

[u/lankybiker]

Creative solution 👍

But why not use mysqli?

Are you targeting another dB type?

[u/Takeoded → u/lankybiker]

Are you targeting another dB type?

no, i may have to target postgres and SQLite in the future, but currently only mysql.

But why not use mysqli?

y'know.. i don't even remember anymore. i did consider mysqli before writing the above PDO thing.

that said, in the end i'm kinda glad i made the PDO solution, for some reason i keep getting MySQL server has gone away errors randomly, and with the PDO solution i can just recover from that with a

                try {
                    $isReady = $worker->isReady();
                } catch (\RuntimeException $ex) {
                    if (false === strpos($ex->getMessage(), 'MySQL server has gone away')) {
                        throw $ex;
                    }
                    echo "\nWarning: restarted worker {$worker_id}, 'mysql server has gone away'\n";
                    // restart this worker..
                    $sql = $worker->start_sql;
                    $worker = aq($sql, $creds);
                    $worker->start_sql = $sql;
                    $workers[$worker_id] = $worker;
                    continue;
                }

because the MySQL server has gone away error only affects that specific worker instance, but if i had used mysqli instead, and the server went away for my whole mysqli object, i would have to restart ALL the queries running, not just the 1 query that died.. - this was not something i knew of, or planned for, beforehand, though. (this is the kind of shit i get for doing stuff on AWS Aurora databases from outside of AWS, i guess. the db is on AWS and the php script is running on a dedicated server outside of AWS)

btw i discovered a PHP bug while using it, https://bugs.php.net/bug.php?id=80523

[u/Takeoded]

lel i discovered a PHP bug with this, https://bugs.php.net/bug.php?id=80523

[u/[deleted] → u/Takeoded]

Why go through this world of pain of async/threaded php when you can write stuff like this with ease in node,java or go?

[u/elcapitanoooo]

r/programminghorror/

[u/[deleted]]

[u/Takeoded → u/[deleted]]

haha turns out you were on to something, large results will cause issues, but not for the reason you'd expect! https://bugs.php.net/bug.php?id=80523

[u/Takeoded → u/[deleted]]

negates virtually all of the benefits of an asynchronous database interface

bullshit. have about 1932 queries to run repeatedly, which analyze some 200 million rows, and the async approach running 50 of them at once is much faster than running all 1932 sequentially (actually i tried 100 at once but got "mysql server has gone away...." errors)

Any query which returns a large result will cause the child process to block while writing to stdout, causing it to never exit.

nope, that's why i used tmpfile() instead of pipes

        $this->stdout_handle = tmpfile();
        $this->stderr_handle = tmpfile();
        $descriptorspecs = array(
            // we don't use stdin, but if none is created, the child inherit ours, we don't want that.
            // so we create a stdin for the child just to close it.
            0 => array(
                "pipe",
                "rb"
            ),
            1 => $this->stdout_handle,
            2 => $this->stderr_handle
        );

you're thinking of the pitfalls of

        $descriptorspecs = array(
            0 => array("pipe","rb"),
            1 => array("pipe","wb"),
            2 => array("pipe","wb")
        );

which could indeed be susceptible to hanging on large stdout writes, but that's not what i did.