[FIXED] MySQL results not getting freed

Submitted by Anonymous on Fri, 03/25/2005 - 12:38
Written by
Herodes

There is an issue of an E_WARNING with my current installation.

It returns the annoying warning message concerning the mysql_free_result()...

I found a way to get past it but it would change the way the results are used inside the code.

This is the changed part of the db_mysql.php

	//
// Fetch query results
//
function fetch_result($query) {

if ($result = $this->query($query)) {

$data = array();

// copy the results to an array
while ( $row = mysql_fetch_assoc($result) ) {
$data[] = $row;
}
// free the result resource
mysql_free_result($result);
return $data;
}
return FALSE;
}

Grabing the results of a query like this is much more straight-forward and makes the annoying E_WARNING go away ...

What is the exact error you are receiving? I can only think of you are having far too less memory available to MySQL or PHP...

My machine runs :

Apache : 2.0.52,
PHP : 5.0.2
MySQL server : 4.1.7-nt
MySQL client : 5.0.0

From the php errorlog.

[26-Mar-2005 13:24:14] PHP Warning:  Unknown: 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

In PHP Settings, I have changed the default memory limit to 10MB from the default value of 8MB
MySQL Settings to default nothing has been touched.. P3 600Mhz, 512MB

[*edit*] If i set the mysql.trace_mode = off in php.ini I dont get the error .. but thats ignoring the warning, not fixing it... :)

You normally don't have to free any result set as everything is freed anyway when the script ends. The same goes for disconnecting a MySQL link which is also done automatically at the script end.

Setting mysql.trace_mode = off is the right solution. I bet 90% of PHP applications including UseBB wouldn't work if this is set on.

PC_Freak

Setting mysql.trace_mode = off is the right solution. I bet 90% of PHP applications including UseBB wouldn't work if this is set on.

you are right about the 90% (or smth like that), but I see no reason for just hitting warnings for fun .. the same proc time required to generate the error message could be taken to stop it from appearing in the first place..

It is a warning, not a critical error. application doesnt stop execution..

The warning occures if you set a special configuration value for MySQL which is as far as I know always disabled. It's not a regular PHP warning message.

it is not a regular but it is ..
shouldnt the package work in as much as possible configurations smoothly ? Doesnt that define the "software usability" in your term definition ?

It's nearly impossible to get the software working on all combinations of configuration values.

I will consider fixing this in a later version.

PC_Freak

I will consider fixing this in a later version.

okie, my personal view on this is "squash them while they're young" :)

Most bugs are fixed within a week after being reported but this one is an exception, because it only occurs on very rare configurations.

I think this has been fixed in CVS. I submitted code which automatically frees the result sets after saving the rows in an array. I couldn't reproduce the errors you were getting so I can't test it if it really fixes the problem. But since it calls mysql_free_result() I suppose it does.

Please enable auto_free_sql_results in config.php and test this on your configuration.

PC_Freak

I think this has been fixed in CVS. I submitted code which automatically frees the result sets after saving the rows in an array. I couldn't reproduce the errors you were getting so I can't test it if it really fixes the problem. But since it calls mysql_free_result() I suppose it does.

Please enable auto_free_sql_results in config.php and test this on your configuration.

Thanks for taking care of it .. I am pretty sure the way you did it should fix it. I'll be back with the results on my test machine as soon as I have a chance to test it ;)

In the mean time, I fixed a small issue causing num_rows() not to work.

thx for getting there first ;)

Also it is tiny and I don't know how much it helps or not but :

mysql_fetch_array( $result, MYSQL_ASSOC )

is the same as

mysql_fetch_assoc( $result )

Thanks for that, but there is a difference. Without the constant PHP will also return the fields with a numeric key in the array, so you get in fact twice the results (with numeric keys + with the real field names). Just try running a print_r() above a results array.

Well, about this bug: I've found how you can disable trace mode for MySQL, and it seemed that it didn't fix anything at all. I implemented something really simple now (free all MySQL results before doing the database disconnect) and I think it also fixes it (however, not a real fix, after all not the right fix for what PHP was complaining). But there seem to occur a LOT of other warnings with trace mode, about inefficient queries. I can't fix all those now, it would require a total database redesign and much code rewriting.

I put in an ini_set call which tries to disable trace mode if it is turned on. Let's hope UseBB 2.0 or something will not suffer from this.

yep you are very right, that function does return a more obfuscated resutl...

PC_Freak

I put in an ini_set call which tries to disable trace mode if it is turned on. Let's hope UseBB 2.0 or something will not suffer from this.

my eyes stretched open when I read the 'UseBB 2.0' part,.. but I hope it will be fixed eventually ;)

2.0 will probably be a rewrite of big parts of the code, if not all. At this time it's too late to change the whole underlying system.