5

Is there a way to continue in a PHP7.2 script after using 

$sql = "INSERT INTO `table` SET `a` = 'aaa';
$result = mysqli_query($link,$sql,MYSQLI_ASYNC);
// $result is always 'true'

as a means of a delayed insert?

It now gives Commands out of sync; you can't run this command now when my script continues and the next query is executed.

I'm using innodb so an INSERT DELAYED doesn't work, although I'm looking for the same behavior from a PHP script perspective. (deploy query, don't wait for it being processed)

Do I need to use a separate script and message broker (e.g. RabbitMQ) to pull this off with InnoDB databases?

Sergeant
  • 131
  • 1
  • 11

2 Answers2

-2

Yes, MYSQLI_ASYNC will return immediately from mysqli_query().

But the MySQL/MariaDB session may still be busy when you run the next query, which then will give you a "Commands out of sync" error.

So, you have to care for

  • waiting for an (unknown) amount of time before running further queries
  • not calling mysqli_close() on that session until the async query has finished, otherwise the connection barfs
  • not even let PHP script end, as that calls mysqli_close() implicitly, as far as I know.

The conclusion is: don't use MYSQLI_ASYNC, unless you're in a very uncommon environment, which uses multiple parallel connections, never lets a connection end, and fires no further queries on the same session.

Anse
  • 1,573
  • 12
  • 27
-3

There is no good reason to run INSERT asynchronously; it is likely to be very fast.

Ditto for UPDATE or DELETE, unless there are millions of rows being updated or deleted.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • 1
    There is a good reason, when you have a replicated backend that has a master-master setup there is a small delay when inserting a row cause it makes sure all databases are in sync. I don't want my php script to wait for that, cause I'm not interested in the outcome of the query. – Sergeant May 17 '18 at 18:02
  • @Sergeant - Do you really mean just Master-Master? If so, that configuration is _not_ synchronous. "Semi-sync" may have an issue; Galera could have an issue if the nodes are geographically spread apart. – Rick James May 18 '18 at 00:00