[solved-ish] DB results not being freed

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

[solved-ish] DB results not being freed

Post 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.
Last edited by pickle on Thu Aug 24, 2006 2:44 pm, edited 1 time in total.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

php frees what it controls, those are not entirely in php's control though.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Why not call mysql_free_result()?
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Are you using mysql_pconnect()?

Try setting mysql.trace_mode to off in php.ini.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

You wrote a method to free results and then you didn't use it. Doh!
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post 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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Why, when they're supposed to be closed automatically at the end of script execution?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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())
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

Code: Select all

if (is_resource($result) && (get_resource_type($result) == 'mysql link')) mysql_free_result($result);
should work.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply