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
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
- To release resources as early as possible for other processes to use.
- It's good programming practice
- 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?