MYSQLi bind_result allocates too much memory

问题:

I'm trying to fetch multiple rows from MYSQL, but when binding variables to the result, MYSQLi runs out of memory to allocate as it tries to fetch all rows at once and buffers the full LONGBLOB size even when not necessary.

The error is also discussed here. One poster seems to have solved the problem using mysqli_stmt_store_result, but does not elaborate as to exactly how (and mysqli_stmt_store_result is a procedural (not OO), method.

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 4294967296 bytes)

Ideally, I'd prefer to be using fetch_object() anyway, but I can't figure out how to get it to function with my prepared statement.

  public function display() {
    $page_offset = ($this->get_page_number()- 1)
                      * $this->notes_per_page;
    if ($page_offset < 0) {
      $page_offset = 0;
    }
    $sql = "SELECT title, date_posted, text, url
              FROM notes ORDER BY date_posted DESC
              LIMIT ?, ?";
    $results = $this->query($sql, "ii", $page_offset, $this->notes_per_page);
    $results->bind_result(&$title, &$date_posted, &$text, &$url);
    //while ($row = $result->fetch_object()) { //store_result()) {
      //echo 'success';
      //var_dump($row);
    //}
    //$this->write($results);
  }

  // Here is the query function that $this->db->query() above refers to.
  public function query() {
    $args = func_get_args();
    $statement = $this->db->prepare($args[0]);
    $args = array_slice($args, 1);
    call_user_func_array(array($statement, 'bind_param'), &$args);
    $statement->execute();
    return $statement;
  }

Thanks for any and all help!

回答1:

that's not MEDIUM but LONG TEXT size.
check your table definition

回答2:

I've solved this by using the following code. There is still a problem as some returned data appears mangled, but I believe that deserves its own question. What was tricky for me was that store_result() needs to be called on the mysqli object, while fetch_object() needs to be called on the statement.

  public function display() {
    $page_offset = ($this->get_page_number()- 1)
                      * $this->notes_per_page;
    if ($page_offset < 0) {
      $page_offset = 0;
    }
    $sql = "SELECT title, date_posted, text, url
              FROM notes ORDER BY date_posted DESC
              LIMIT ?, ?";
    $results = $this->query($sql, "ii", $page_offset, $this->notes_per_page);
    $results = $this->db->store_result();
    while ($row = $results->fetch_object()) {
      var_dump($row);
    }
    //$this->write($results);
  }

回答3:

mysqli_stmt_store_result has an OO form as well.