1

On my new brand AWS server I have a big problem with PHP and Firebird DB.

Current configuration:

  • Debian 10 (buster)
  • PHP 7.3.11-1~deb10u1 (cli) (built: Oct 26 2019 14:14:18) ( NTS )
  • LI-V3.0.5.3310 Firebird 3.0 Super

This is not real code from my application, but after a days I think this is the problem:

$dbh = ibase_connect('localhost:/*****/temp.fdb', 'SYSDBA', '*****');
$stmt = 'SELECT field_id FROM A0000';
$sth = ibase_query($dbh, $stmt);
while ($row = ibase_fetch_object($sth)) {

    $stmt0 = "SELECT * FROM A0000 WHERE field_id=?";
    $pstm0 = ibase_prepare($dbh, $stmt0);

    $sth0 = ibase_execute($pstm0, $row->field_id);
    $row0 = ibase_fetch_object($sth0);

    echo $row0->field_id . "\n";

    ibase_free_query($pstm0);
    ibase_free_result($sth0);

    $sth0 = null;
    $pstm0 = null;

}
ibase_free_result($sth);
ibase_close($dbh);

Obviously the ibase_prepare goes outside the loop for real performance.

This simple code makes Firebird goes in error with "too many open handles to database" (around the 64000th iterations, and Firebird with 16GB of RAM.

The same code on our old server (PHP 5.3/Firebird 2.5) works fine.

With this code instead all works perfectly (even on new server):

$dbh = ibase_connect('localhost:/*****/temp.fdb', 'SYSDBA', '*****');
$stmt = 'SELECT field_id FROM A0000';
$sth = ibase_query($dbh, $stmt);
while ($row = ibase_fetch_object($sth)) {

    $stmt0 = "SELECT * FROM A0000 WHERE field_id=?";

    $sth0 = ibase_query($dbh, $stmt0, $row->field_id);
    $row0 = ibase_fetch_object($sth0);
    echo $row0->field_id . "\n";

    ibase_free_result($sth0);

    $sth0 = null;
}
ibase_free_result($sth);
ibase_close($dbh);

Seems the proble was the ibase_prepare that is never released.

Is my code at fault?

  • Each connection can have (almost) 65535 'handles' (statements, blobs, transactions, etc) associated with it. The error suggests that those handles aren't closed. Does the error still occur if you move the `ibase_prepare` outside the loop? If so, that would suggest that `ibase_free_query` isn't closing the handle, but possibly only unpreparing. – Mark Rotteveel Nov 06 '19 at 12:09
  • See also [FirebirdSql.Data.FirebirdClient.FbException: too many open handles to database](https://stackoverflow.com/questions/39148222/firebirdsql-data-firebirdclient-fbexception-too-many-open-handles-to-database) You may also want to consider asking on the firebird-php list. – Mark Rotteveel Nov 06 '19 at 16:47
  • Thanx Mark. With the ibase_prepared outside all works fine. The problem is when the ibase_prepare is inside the cycle (the code is only an example: my application is more complex). What's strange is that the same code works on php5.3 and firebird 2.5 :( – Leonardo Cosmai Nov 06 '19 at 16:54
  • I don't use PHP myself, so I'm not sure, I know things were heavily changed recently. However, I just took a look at the sources in https://github.com/FirebirdSQL/php-firebird, and the problem might be the order of freeing resources. You are trying to free the statement before the results, it looks like doing that will not deallocate the handle (see https://github.com/FirebirdSQL/php-firebird/blob/master/ibase_query.c#L165). Try switching `ibase_free_query($pstm0);` and `ibase_free_result($sth0);`, so you release the result before releasing the statement. – Mark Rotteveel Nov 06 '19 at 17:02
  • Ignore my previous comment, I hadn't looked at what `ibase_free_result` does, that will free the statement handle. – Mark Rotteveel Nov 06 '19 at 17:06
  • i wonder if Trace API (might be used by text-config `fbtrace` tool or via GUI of https://sourceforge.net/projects/fbprofiler/ ) will show if the unprepare requests were sent to server or not – Arioch 'The Nov 06 '19 at 21:31
  • Thanx all... no way: ibase_free_query($pstm0) seems to do nothing: try a SELECT COUNT(1) FROM MON$STATEMENTS while running the code returns ~60000 records, instead the other code returns only 2/3 records. – Leonardo Cosmai Nov 07 '19 at 06:44
  • what if you swap lines with ibase_free_query and ibase_free_result ? Also try using Trace API, to see if server sees Free requests or not. At least you would have more data for bug reports towards PHP or FB maintainers – Arioch 'The Nov 07 '19 at 09:47
  • Hi Arioch, already tried it: swapping lines makes no difference. – Leonardo Cosmai Nov 07 '19 at 14:39
  • Well, I did not hoped much, but it was easy to try. Then make Trace API log of a test case - see if server thinks he was asked to free query or not. With such a log and sources of test case you then might address to both PHP and Firebird teams. – Arioch 'The Nov 08 '19 at 13:40

0 Answers0