[wp-trac] [WordPress Trac] #28155: mysqli flush issues

[WordPress Trac]

#28155: mysqli flush issues
--------------------------+-----------------------------
 Reporter:  soulseekah    |      Owner:
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  Database      |    Version:  3.9
 Severity:  normal        |   Keywords:
  Focuses:                |
--------------------------+-----------------------------
 The `flush` method in `wpdb` is not correct for mysqli usage.
 First of all `is_resource` does not work with `mysqli_result` instances.

 Next, I'm getting a `"Commands out of sync; you can't run this command
 now"` error when using resultsets in stored procedures. This is a known
 issue with MySQLi and seems to occurr when trying to initiate a new query
 when the old resultset has not been freed. So obviously I was expecting
 `flush` to take care of the quirk.

 Here's the test procedure;

 {{{
 -- Test procedure for out of sync mysqli commands

 DROP PROCEDURE IF EXISTS `mysqli_sync_procedure`;
 DELIMITER ;;

 CREATE PROCEDURE `mysqli_sync_procedure`()
 BEGIN
     SELECT * FROM `wp_posts` LIMIT 1;
 END;;
 DELIMITER ;
 }}}

 Next, try running the following code:

 {{{
 global $wpdb;
 $wpdb->get_results( 'CALL mysqli_sync_procedure' );
 $wpdb->get_results( 'SELECT * FROM wp_posts LIMIT 1' );
 }}}

 You should get a bunch of `WordPress database error: [Commands out of
 sync; you can't run this command now]` from other queries being executed.

 This is because stored procedures have a second resultset which contains
 the status: 'OK' or 'ERR'.

 `flush` doesn't work as expected, first of all because `is_resource`
 returns `false` for mysqli results, checking should probably be made using
 `$this->result instanceof mysqli_result` instead.

 Regardless, you'll find yourself against a new error `"Couldn't fetch
 mysqli_result"` when trying to free any of the results. This happens even
 if you don't use the procedure, but just call `flush()` between two
 queries. This may have to do with how the results are never stored or used
 to begin with (`mysqli_use_result`, `mysqli_store_result`).

 A primitive fix in this case would be to use `while (
 $this->dbh->more_results() ) $this->dbh->next_result();`, so that all
 results are fetched, leaving no results to linger about.

 A better fix would be sort out the correct usage of `use`/`store` and
 `free` with MySQLi. See `resultmode` in
 http://ru2.php.net/manual/en/mysqli.query.php where by default you can't
 even call `free` on the result, because it's always STORED vs. USED. ("If
 you use MYSQLI_USE_RESULT all subsequent calls will return error Commands
 out of sync unless you call mysqli_free_result()").

 So, to summarize, we have three issues with mysqli:

 1. `is_resource` is not the correct way to identify mysqli results
 2. stored procedures are not flushed correctly because of a second query
 3. `free` cannot be called on results that were fetched with
 `MYSQLI_STORE_RESULT` mode

 Thoughts, ideas? I'm attaching a patch that flushes mysqli results by
 fetching any lingering rows.

--
Ticket URL: <https://core.trac.wordpress.org/ticket/28155>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform