Allowed memory size of 134217728 bytes has been exhausted (tried to allocate 4294967296 bytes)

Question

My project uses the open source PHP library https://github.com/ajillion/PHP-MySQLi-Database-Class

But the project in the middle of the year reports: "Fatal error: the allowed memory size of 134217728 bytes has been exhausted (tried to allocate 4294967296 bytes) in / home 1 / flipalbu / public_html / kvisofttest / login-admin / Lib / class.MysqliDb.php on line 422" This mistake,

My server: linux x86_64

PHP Version 5.4.17

Mysql Version: 5.5.32

memory_limit = 128M

Line 422: call_user_func_array (array ($ stmt, 'bind_result'), $ parameters);

Request a piece of code:

  $ db = new MysqliDb ('LocalHost', 'root', 'PASSWD', 'DB'); $ wqdb = $ db-> query ("SELECT * FROM db_table"); foreach ($ wqdb as $ row) {    $ con. = $ row ['ID']; } echo $ con; 

Is there any way to solve it?

---

/** Error code **/

  protected function _dynamicBindResults(mysqli_stmt $stmt) { $parameters = array(); $results = array(); $meta = $stmt->result_metadata(); $row = array(); while ($field = $meta->fetch_field()) { $row[$field->name] = null; $parameters[] = & $row[$field->name]; } call_user_func_array(array($stmt, 'bind_result'), $parameters); while ($stmt->fetch()) { $x = array(); foreach ($row as $key => $val) { $x[$key] = $val; } array_push($results, $x); } return $results; } 

Answer 1

I read this error report here: https://bugs.php.net/bug.php?id=51386

Your problem arises because there are longblob or longtext in the table columns.

longtext / longblob have a maximum length of 4294967295 [4 GB], so mysqli is trying to allocate this memory for the buffer to be sure that nothing is lost. I would suggest that you use the maximum mediumtext length (16777215 [16 MB]), which should be enough for everything, as usual.

Update: Since this answer has seen some activity, I am adding this solution from Phil_1984 (see Comments)

I use mysqli and after reading this quote from php dev, adding $ Stmt-> store_result (); between execute and bind_result seems to fix issues for me

=> If you use $stmt->store_result() , you can use mysqli with longblob / longtext without getting an error.

-

Old answer: I suggest that you either change the column to another type (middle text) or use PDO (I think it does not have this problem). but if you want to keep the column long text you need to switch your mysql library

Quote from PHP Dev:

This is a known ext / mysqli limitation when using libmysql (always in 5.2 and previous) and when libmysql is enabled since 5.3. the reason is that the server does not send very specific metadata about the column. This long text has a maximum length of 4G and ext / mysqli tries to bind with a maximum length so that there is no data loss (data does not fit into the binding buffer at the C level). However, this means 4G for longtext / longblob. ext / mysqli has been modified to have a way around this. You need to call mysqli_stmt_store_result (), which will store data locally, which means, of course, higher memory usage for PHP. However, since you are using libmysql, this will not hit the PHP memory limit, of course. During store_result max_length, each column will be calculated, and then, when bind_result is executed, only a buffer with a 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

Answer 2

If you are trying to read the entire table at a time, and there are many rows and columns in this table, then running out of memory is inevitable. You can disable it by increasing the memory limit in php.ini, but the problem will be repeated only when adding several thousand lines.

You need to rewrite your script in order to be more reasonable about what it gets. If you only need specific records, then pulling out the entire table and looking for the rows you want in the result set is terribly inefficient. Use the WHERE clause to indicate what you really want to receive. Rule of thumb with PHP / SQL applications: "Use SQL wherever possible to indicate what you want, and then do what you need to do with it in PHP."

Of course, maybe there is a legitimate reason why you need to process the entire table in PHP. In this case, you should get the data in pieces (for example, 100 rows at a time) using LIMIT and OFFSET, process these rows, get the next fragment, process these and so on until you go through the whole table. This will be much less memory than trying to load the entire table immediately.

Answer 3

Doesn't seem like a huge table! It seems like an endless loop! He is trying to allocate about 4 GB, I don’t think you have such a large table ....

make sure you do not create a loop here:

 call_user_func_array (array ($ stmt, 'bind_result'), $ parameters); 

Perhaps you should post the code that is around this line.

Answer 4

You have exceeded the maximum available memory. Yow has two options:

• Increase the maximum allowed memory for each PHP script, either by configuration ( memory_limit directive in php.ini ) or at runtime using ini_set('memory_limit', '200M')

• Improve the code to process only the necessary information.