Serious problem with the driver on Firebird 2.1 and 2.5 and Linux

[peacefuldev]

Hello,

We've ran into a serious issue with the driver that we can't seem to overcome.

It is working fine on Windows, but on Linux, it doesn't work neither with Firebird 2.1, nor with Firebird 2.5.

With Firebird 2.1, parametrized queries don't work.

The issue has been mentioned here before, and closed without a solution:
#7

Whenever executing a parametrized query, Firebird gives the following error:
Dynamic SQL Error SQL error code = -303 arithmetic exception, numeric overflow, or string truncation.

Note that somebody else has also ran into this issue, even when using PDO:
https://stackoverflow.com/questions/64873300/pdo-firebird-cant-execute-prepare-statement-with-integer-parameters

In order to overcome the problem, we've tried switching to FB2.5.

With FB2.5, we've ran into a different, but not less serious issue:

Here is some example code:

    private function db_connect()
    {
        if($this->db_handle !== null) {
            return;
        }

        $this->db_handle = ibase_connect(AppConfig::getDbHost(), AppConfig::getDbUsername(), AppConfig::getDbPassword());
        if (!$this->db_handle) { // Failure to connect
            throw new Exception('Failed to connect to database because: ' . ibase_errmsg(), ibase_errcode());
        }
    }
    private function db_transaction()
    {
        if($this->transaction_handle !== null) {
            return;
        }

        $transaction_args = 0
            + IBASE_COMMITTED + IBASE_REC_NO_VERSION // Read Committed
            + IBASE_WAIT                             // Wait if locked
            + IBASE_WRITE;                           // Read-Write tansaction
        $this->transaction_handle = ibase_trans($transaction_args, $this->db_handle);
        if (!$this->transaction_handle) {
            throw new Exception( 'Unable to create new transaction because: ' . ibase_errmsg(), ibase_errcode() );
        }
    }
    private function db_prepare(string $queryString)
    {
        $query = ibase_prepare($this->transaction_handle, $queryString);
        if (!$query) {
            throw new Exception('Unable to process query because: ' . ibase_errmsg() . '\r\nQuery: ' . $queryString, ibase_errcode());
        }
        return $query;
    }

            $this->db_connect();
            $this->db_transaction();
            $query = $this->db_prepare($queryString);

When executing:
$query = ibase_prepare($this->transaction_handle, $queryString);
FB2.5 restarts itself.

In Firebird.log, the following two lines are written:
/opt/firebird/bin/fbguard: /opt/firebird/bin/fbserver terminated abnormally (-1)
/opt/firebird/bin/fbguard: guardian starting /opt/firebird/bin/fbserver

If we execute the query a second time, after the restart, it works fine!

We've tried this on Debian 9 and 10 and Ubuntu 18. Also with PHP 7.2 and 7.3.
All of the environments produce the error.

On Windows, everything works fine, even with FB2.1.

Could you please look into this issue? It keeps us from releasing our software on Linux.

[MartinKoeditz]

Yes, we will check this. Do I understand this correct? The code above crashes Firebird 2.5? I don't use FB 2.1, but check on FB 2.5 should be possible.

Can you also provide an example $queryString and the underlying table structure (DDL)?

[AlexPeshkoff]

On 4/23/21 12:04 PM, peacefuldev wrote: It is working fine on Windows, but on Linux, it doesn't work neither with Firebird 2.1, nor with Firebird 2.5.

Have you tried with something fresh? FB3?

[peacefuldev]

@MartinKoeditz: Yes, it crashes on FB2.5. I'll post the $queryString and DDL later today.

@AlexPeshkoff: We couldn't switch to FB3.0 yet, because a desktop application is also going to use the same DB, and porting requires some work on that too. If we can't find a solution, we'll try with FB3.0.

[peacefuldev]

Upon trying to compile an example we've come to the conclusion that the problem is caused by an UDF library (we were confused, because the UDF call is embedded within a Stored Procedure). The UDF works fine under Firebird 2.1, but somehow one of the functions don't work on FB2.5. So the issue with 2.1 is still a problem, but regarding FB 2.5 it was a false alarm. Thank you!

[hoiihop]

I have same error with FB 2.5 when use in select query where statement on timestamp field.
SQLSTATE[HY000]: General error: -303 Dynamic SQL Error SQL error code = -303 arithmetic exception, numeric overflow, or string truncation

[MartinKoeditz]

@hoiihop Please provide an example code. What datatypes are use in the destination fields? Seems to be a compatibility problem.

[hoiihop]

@MartinKoeditz I had this problem when try binding value to query (in db field has timestamp type, tried bind from php date/datetime/string). PHP 7.4.27, Laravel Framework Lumen (8.2.3) (Laravel Components ^8.0), Firebird 2.5. Now working only this:

$selectDate = Carbon::now()->subDays($days);

return self::getDb()->select(
            DB::raw('SELECT
            WHS.id_departments,
            DPS.n_departments,
            WHS.id_warehouse,
            WHS.n_warehouse,
            RL.ID_GOODSDIR,
            G.KN_GOODS,
            sum((CASE
                WHEN r.id_receipt_gr = 1 THEN (CGP.amount_pay)
                WHEN r.id_receipt_gr = 2 THEN -1*(CGP.amount_pay)
              END)) as amount_pay,
            \''. $selectDate->format('Y-m-d') .'\' as created_at
          FROM AZS_RECEIPT R, AZS_RECEIPT_LINE RL, G_GOODSDIR G,
            AZS_CHANGES CH, AZS_CHANGE_GOODS_PAY CGP, PAYTYPE_VAR PV, cashdesks cds, warehouse whs, departments dps
          WHERE R.ID_RECEIPT=RL.ID_RECEIPT
            AND RL.ID_GOODSDIR=G.ID_GOODSDIR
            AND R.ID_CHANGES=CH.ID_CHANGES
            AND R.ID_RECEIPT_GR<>4
            AND R.ID_RECEIPT = CGP.ID_RECEIPT
            AND CGP.ID_PAYTYPE_VAR=PV.ID_PAYTYPE_VAR
            AND cds.id_cashdesks = ch.id_cashdesks
            AND whs.id_warehouse = cds.id_warehouse
            AND dps.id_departments = whs.id_departments
            AND (R.d_receipt between \'' . $selectDate->startOfDay()->toDateTimeString() . '\' and \'' . $selectDate->endOfDay()->toDateTimeString() . '\')
            AND (RL.id_goodsdir < 0)
        group by 1,2,3,4,5,6')
        );