Page 1 of 2

[solved-ish] DB results not being freed

Posted: Thu Aug 24, 2006 10:01 am
by pickle
Hi everyone,

I'm having a heck of a time with a seemingly random memory problem. I just migrated to a new server, and now some pages are giving me this error:
php wrote:Warning:(null)(): 1 result set(s) not freed. Use mysql_free_result to free result sets which were requested using mysql_query() in Unknown on line 0
The manual says "all associated result memory is automatically freed at the end of the script's execution". So....why isn't it?

I should mention that all my pages use a common, standardized database access file - so I don't think that's a factor.

Posted: Thu Aug 24, 2006 10:22 am
by feyd
php frees what it controls, those are not entirely in php's control though.

Posted: Thu Aug 24, 2006 10:30 am
by pickle
Do you know if I can do something to MySQL to get it to give up the memory? The only help I could find was to make the 'mode' not 'strict. I've restarted mysql with an explicit argument of unsetting all modes - that had no effect.

We're running MySQL 5.1 FYI.

Posted: Thu Aug 24, 2006 10:31 am
by feyd
Why not call mysql_free_result()?

Posted: Thu Aug 24, 2006 10:36 am
by pickle
Because I don't want to have to put that after every database call in every file on my webapp server. I certainly could do that for the particular files that are currently having the problem, but since I can see no rhyme or reason why it's happening, I'll have to do that to every new page I create, just to be sure.

And sorry, we're running 5.0.22, not 5.1

Posted: Thu Aug 24, 2006 10:41 am
by Benjamin
Are you using mysql_pconnect()?

Try setting mysql.trace_mode to off in php.ini.

Posted: Thu Aug 24, 2006 10:44 am
by pickle
astions wrote:Are you using mysql_pconnect()?
Nope. Here's the code for the db layer:

Code: Select all

<?php

class DB
{
  var $db_name;
  var $db_username;
  var $db_password;
  var $db_host;
  var $link;
  var $result;

  // constructor
  function DB($p_db_name = -1,$p_db_username = -1,$p_db_password = -1,$p_db_host = "127.0.0.1")
    {
      //if they passed values, use those
      if($p_db_name != -1 &&
	 $p_db_username != -1 &&
	 $p_db_password != -1)
      {
	$this->db_name = $p_db_name;
	$this->db_username = $p_db_username;
	$this->db_password = $p_db_password;
	$this->db_host = $p_db_host;
      }
      //otherwise, if the globals aren't set, default to this webapps defaults
      elseif(!defined('DB_NAME') && defined('CALLING_WEBAPP_NAME'))
      {
	global $SESSION_GLOBALS;
	$db_info = $SESSION_GLOBALS[CALLING_WEBAPP_NAME];

	$this->db_name = $db_info[db];
	$this->db_username = $db_info[user];
	$this->db_password = $db_info[password];
	$this->db_host = ($db_info[host] != '') ? $db_info[host] : $p_db_host;
      }
      //otherwise, default to globals
      else
      {
	$this->db_name = DB_NAME;
	$this->db_username = DB_USERNAME;
	$this->db_password = DB_PASSWORD;
	$this->db_host = $p_db_host;
      }
      // connect to database
      

      $this->link = mysql_connect($this->db_host, 
				  $this->db_username, 
				  $this->db_password)
	or die("Connection failed: " . mysql_error());

      // select our table
      mysql_select_db($this->db_name, $this->link)
	or die("Database selection failed [$this->db_name]: " . mysql_error());
    } // DB
      
  // abstraction function for the mysql_query command
  function sql_query($sql, $task_desc, $dont_die=0)
    {
      // if the query is not a select, author can indicate to return the error
      // code instead of the result set and not to die on error
      if ($dont_die)
	{
	  mysql_query($sql, $this->link);
	  $this->result = mysql_error();
	}
      else
	{
	  $this->result = mysql_query($sql, $this->link)
	 	    or die("Query failed while " . $task_desc . ": " . mysql_error());
	}
      return $this->result;
    } // sql_query

  // abstraction function for mysql_fetch_assoc
  function get_data($result)
    {
      return mysql_fetch_assoc($result);
    }

  // abstraction function for mysql_num_rows
  function count_rows($result)
    {
      return mysql_num_rows($result);
    }
  //frees up memory associated with $result
  //only necessary with queries that return data
  function free_result($result)
  {
    return(mysql_free_result($result));
  }

  // reset the result set internal pointer to the first record
  function seek_top($result)
  {
    if ($this->count_rows($result))
    {
      mysql_data_seek($result, 0);
    }
  }
} // class DB

Posted: Thu Aug 24, 2006 10:51 am
by onion2k
You wrote a method to free results and then you didn't use it. Doh!

Posted: Thu Aug 24, 2006 11:05 am
by pickle
I didn't write this class - I actually just discovered that function existed yesterday & tried it. I registered it as a shutdown function as you suggested yesterday, but then other pages started having problems with the results being prematurely freed.

Edit: Would the fact that I'm connecting to 127.0.0.1 rather than localhost have anything to do with it?

Posted: Thu Aug 24, 2006 11:06 am
by Jenk
technically and pedantically.. you should free up your result resources after you have finished with it, much like you should always close a file resource, or a persistant connection when finished etc.

Posted: Thu Aug 24, 2006 11:07 am
by pickle
Why, when they're supposed to be closed automatically at the end of script execution?

Posted: Thu Aug 24, 2006 11:09 am
by feyd
  1. To release resources as early as possible for other processes to use.
  2. It's good programming practice
  3. the library involved may have a memory leak if you don't (GD did for quite some time, it may still, I wouldn't know, I always call imagedestroy())

Posted: Thu Aug 24, 2006 11:15 am
by pickle
Ok, ok - fair enough. I'll adapt my practices for the future. Nonetheless I am still faced with this problem which technically, I think, shouldn't be happening. Any further ideas?

Would there be some way for me to do a conditional call - check it the result is freed & free it if not?

Posted: Thu Aug 24, 2006 11:20 am
by Jenk

Code: Select all

if (is_resource($result) && (get_resource_type($result) == 'mysql link')) mysql_free_result($result);
should work.

Posted: Thu Aug 24, 2006 11:24 am
by pickle
Thanks, I'll use that if I have to. I'd much rather find a configuration-type solution so I don't have to go through & modify a bunch of scripts.

The jist of my beef is that these scripts were working fine on two other boxes - should there not be some way to get this box to work like the others?