We help IT Professionals succeed at work.

Mysqli bind result causes memory crash

jimdgar2
jimdgar2 asked
on
I've inherited an app built entirely with mysql and am trying to convert to mysqli with bound parameters. Bind_param works just fine but bind_result almost always returns a memory crash:

Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 50331646 bytes) in
/home/.../emp_functions.php on line 44

Old mysql code which has been successfully running for years:

                  $query = "SELECT * FROM Employees WHERE Code = '$co'";
                  if (!$result = mysql_query ($query))
                    die ('Error: ' . mysql_error());
                  while ($row = mysql_fetch_array ($result))
                    // do things with $row[''] data

Open in new window


New mysqli code which causes the crash:

           $sql = "SELECT * FROM Employees WHERE Code = ?'";
           $in_params = array('s', $co);
           $stmt = $sqli_db->prepare($sql)
               or die ('Error on mysqli prepare: ' . mysqli_error());

           call_user_func_array (array($stmt, 'bind_param'), $in_params);

           $stmt->execute();

           if ($!get_rows) {
               $result = $sqli_db->affected_rows;
           } else {
               $meta = $stmt->result_metadata(); 
               while ($field = $meta->fetch_field())
                    $params[] = &$row[$field->name]; 
   
               call_user_func_array(array($stmt, 'bind_result'), $params);    // line 44 ***

               while ($stmt->fetch()) { 
                   foreach($row as $key => $val) 
                          $c[$key] = $val; 
                   $result[] = $c; 
                }
                $stmt->close();
                return $result;

Open in new window


I've tried various twists on this code but always with the same crash. I originally passed in actual parameters to the bind_result (skip the 3 lines of $meta code) but with the same crash.

Any hints? I would be glad to skip the bind_result step if necessary.

Comment
Watch Question

greetings jimdgar2, , I have read your code and I am not sure what you are trying to accomplish with the code line of -
call_user_func_array(array($stmt, 'bind_result'), $params);

which I take from your statement uses too much memory, and PHP abandons that script.

if I use a select SQL statement with mysqli prepare, I NEVER use "SELECT * FROM aTable", I ALWAYS list the coloms I need like - "SELECT items1, prices2, sizes3 FROM aTable"
and then use something like -
$stmt->bind_result($items1, $prices2, $sizes3);

I would think that somehow your array building of $params[] is not correct, and that when you try and run through the call_user_func_array( ) , , but I would not even try to use code like that for a web app, as I would think all kinds of errors might pop up that could be from several different things for instance -
$meta = $stmt->result_metadata();

or
while ($field = $meta->fetch_field()) $params[] = &$row[$field->name];


you may can try something like -
$co = 'XOR';
 $sql = "SELECT first1, second2, third3 FROM Employees WHERE Code = ?'";
$stmt = $sqli_db->prepare($sql) or die ('Error on mysqli prepare: ' . mysqli_error());
$stmt->bind_param("s", $co);
$stmt->execute();
$stmt->bind_result($first1, $second2. $third3);
$rowCount = 1;
while ($stmt->fetch()) {
// Here There are NO ROWS as $row, just the three bound variables $first1, $second2. $third3
echo 'Row Number is '.$rowCount.' first is '.$first1.' second is '.$second2.' third is '.$third3.<br />';
++$rowCount;
}

the above code is UNTESTED and may contain syntax errors, but should give you some idea on how to get a "Basic" mysqli to work

I did look at the many user note on the mysqli stmt bind_result page at
http://www.php.net/manual/en/mysqli-stmt.bind-result.php

there are many that have a
call_user_func_array(array($result, 'bind_result'), $variables);

but I do not see these as a straight forward way to do mysqli , and may be useful for special things

Author

Commented:
The reason for using call_user_func_array is to attempt to create a function to which I can pass a variable number of variables (is that a pun?). Your example code looks good but it's about 12 lines of code which I'd have to write literally 100's of times. I had this idea, maybe misplaced, that I could move from sql statements to sqli + bound parameters within a function or class to save a lot of time & code. So far I'm completely stumped on how to bind results and pass a reference to many rows of data back from a function.
as I said, the problem is in the lines -

$meta = $stmt->result_metadata();
while ($field = $meta->fetch_field() $params[] = &$row[$field->name];
   
call_user_func_array(array($stmt, 'bind_result'), $params);

since it is a Memory problem, you are calling this 100's of times in a function I guess

try and release memory in this function at the end of the function
unset($meta);
unset($params);
$stmt->close();
unset($stmt);

you might also try and run this function just once to see if it messes up memory in just one call, I would think you would have to call it at least 200 times before memory was corrupted

Author

Commented:
Thanks Slick812.

Finally figured out another method. The techniques I was trying were just way over my head, so I simplified. FYI, here's what I'm now doing for SQL calls which need to return rows of data.

function SQLout ($query, $in_params, $out_params) {
  global $sqli_db;

  $stmt = $sqli_db->prepare($query)
    or die ('Error on mysqli prepare: ' . mysqli_error($sqli_db));

  $stmt->bind_param ($in_params[0],$in_params[1])     // actual statements vary based on # of in_params
            or die ('Error on mysqli bind_param: ' . mysqli_error($sqli_db));

  $stmt->execute()
    or die ('Error on mysqli execute: ' . mysqli_error($sqli_db));

  $stmt->store_result();

  $stmt->bind_result ($out_params[0],$out_params[1])    // actual statements vary based on # of out_params
            or die ('Error on mysqli bind_result: ' . mysqli_error($sqli_db));
 
  return ($stmt);
}

  $stmt = SQLout ("SELECT a,b,c from MyTable WHERE name = ?",
                               array('s', $name), array(&$row['a'], &$row['b'], &$row['c']));
  while ($stmt->fetch()) {
// do things based on $row[]
  }
  $stmt->close();

Open in new window