Php resource id overflow error

Question

I have a PHP server application harvesting tweets and thus continuously running. This application starts failing after a couple of days. Refusing to return results for a mysql select statement. I found out this is due to php resource id overflowing (PHP warning):

-2147483648 is not a valid MySQL result resource

This means the application is running out of possible ids to assign to a resource variable (because of the very high amount of query statements that are being fired). I figured that the answer to this question was to use mysql_free_result underneath every mysql select statement so that every resource eventually is freed. This however did not solve my problem.

On some forums I read that PHP is unable to reuse released resource ids. This would mean the only plausible solution is to reconnect to the mysql database?

Some information on my PHP version:

PHP 5.5.10-1+deb.sury.org~precise+1 (cli) (built: Mar 27 2014 16:18:01) Copyright (c) 1997-2014 The PHP Group Zend Engine v2.5.0, Copyright (c) 1998-2014 Zend Technologies with Zend OPcache v7.0.3, Copyright (c) 1999-2014, by Zend Technologies

Does anyone have experience with this issue? Could another solution be to use the mysqli extension instead of the regular one?

Thank you!

Answer 1

The error does NOT necessarily mean "the application is running out of possible ids to assign to a resource variable". Actually, it could be caused by your (if improper) using mysql_free_result() because later attempting to do something with the cleared resources would produce an error like you see. Make sure you're not temporarily "caching" or keeping resource ID's in the session and reusing them.

However, if you are sure your app runs out of resource ids, using unset() or mysql_free_result() you can free resources, then the freed ID(s) is available again and PHP could reuse these resources again. In 64 bit systems, it is unlikely you can run out of resource ids though.

It would be BETTER to optimize the program itself not to waste the resources instead of exhausting them and then resetting. No way you can run out of abundant resources so quickly.

What happened when you called mysql_free_result()? BTW, if you use mysqli, you should use mysqli_result::free instead of mysql_free_result().