Bug #51386 [Asn->Wfx]: mysql tries to allocate 4294967296 bytes when fetching longtext via mysqli

Edit report at http://bugs.php.net/bug.php?id=51386&edit=1 ID: 51386 Updated by: andrey@php.net Reported by: rellig at minad dot de Summary: mysql tries to allocate 4294967296 bytes when fetching longtext via mysqli -Status: Assigned +Status: Wont fix Type: Bug Package: MySQLi related Operating System: gentoo PHP Version: 5.2.13 Assigned To: mysql New Comment: This is a known limitation of ext/mysqli when using libmysql (always in 5.2 and previous) and when libmysql is enabled with 5.3 . The reason is that the server sends not too specific metadata about the column. This longtext has a max length of 4G and ext/mysqli tries to bind with the max length, to be sure no data loss occurs (data doesn't fit in the bind buffer on C level). However, that means 4G for a longtext/longblob column. ext/mysqli has been changed to have a way to work around that. You need to call mysqli_stmt_store_result() which will store the data locally, which means, of course a higher memory usage for PHP. However, because you use libmysql this won't hit the PHP's memory limit, for sure. During store_result the max_length of every column will be calculated and then when bind_result is executed only a buffer with size of max_length will be allocated, which will be definitely lower than 4G. In short, prepare execute store_result bind_result fetch...fetch...fetch Best, Andrey Previous Comments: ------------------------------------------------------------------------ [2010-04-07 20:37:41] andrey@php.net From the link: function news_read($limit = 0) { if($limit == 0) $qry = "SELECT * FROM news ORDER BY n_id DESC"; else $qry = "SELECT * FROM news ORDER BY n_id DESC LIMIT $limit"; echo $qry; $stmt = $this->db->prepare($qry); $stmt->execute(); $stmt->bind_result($n_id,$n_title,$n_note,$n_time,$n_author); while($stmt->fetch()) { $n_ids[] = $n_id; $n_titles[] = $n_title; $n_notes[] = $n_note; $n_times[] = $n_time; $n_authors[] = $n_author; $stmnt->free_result; } $stmt->close(); return array(ids => $n_ids, titles => $n_titles, notes => $n_notes, times => $n_times, authors => $n_authors); } ------------------------------------------------------------------------ [2010-03-25 08:19:55] rellig at minad dot de Description: ------------ I wanted to fetch some lines in a php script via mysqli-prepared-statement and got this error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 4294967296 bytes) I was confused because php reportet that this error occured at bind_result. After some time I figured out that it works when I turn one field with LONGTEXT type to VARCHAR, for example. here is a link to my php-script: http://paste.minad.de/?nid=216 you can look at http://bugs.mysql.com/bug.php?id=52234 too. ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/bug.php?id=51386&edit=1