A problem under PHP 8.0.10 using the Thread Safe DLL

[frafor]

https://github.com/FirebirdSQL/php-firebird/releases/download/1.1.2-pre/php-8.0.1-interbase-1.1.1-win-x64-ts.dll I am talking about that PHP InterBase dll.

The problem is the following:

select
    a.idc,
    a.corp,
    A.USR,
   sum(case
      when FHSALE is null then datediff (hour,FECHORA,current_timestamp)
      else datediff (hour,FECHORA,FHSALE)
    end) horas
from USRLOGS A where a.idu <> 1 and a.idc=126 and a.fechora > '2021-08-15'
group by 1,2,3

The above query executes well if you run it with IBExpert against a database.

If you run that SAME query against the SAME database using PHP 8, then this is what happens:

[Thu Sep 09 11:17:40.902634 2021] [mpm_winnt:notice] [pid 14992:tid 604] AH00428: Parent: child process 12468 exited with status 3221225477 -- Restarting.
[Thu Sep 09 11:17:40.976400 2021] [mpm_winnt:notice] [pid 14992:tid 604] AH00455: Apache/2.4.48 (Win64) OpenSSL/1.1.1l PHP/8.0.10 configured -- resuming normal operations
[Thu Sep 09 11:17:40.976400 2021] [mpm_winnt:notice] [pid 14992:tid 604] AH00456: Server built: Aug 25 2021 11:29:23
[Thu Sep 09 11:17:40.976400 2021] [core:notice] [pid 14992:tid 604] AH00094: Command line: 'C:\\program files\\apache24\\bin\\httpd.exe -d C:/Program Files/Apache24'
[Thu Sep 09 11:17:40.984393 2021] [mpm_winnt:notice] [pid 14992:tid 604] AH00418: Parent: Created child process 7992
[Thu Sep 09 11:17:41.696372 2021] [mpm_winnt:notice] [pid 7992:tid 1456] AH00354: Child: Starting 64 worker threads.

That is from the Apache24 log file.

All parts related to the problem have to do with the DLL specified above. I am using Firebird 4.0.0

The mentioned query has been part of a view for at least 5 years, passing from version to version of PHP / Interbase drivers used under Windows (my development environment) and Linux (my production servers).

The query works fine if the values are presented instead of the SUM( ... ) of the values grouped by 1,2,3.

It has been tested only under Windows 10 and PHP 8.0.10

[frafor]

AVG and SUM cause Apache to crash and restart.

COUNT seems to be ok.

This works if executed in IBExpert BUT crashes Apache24 when executed through PHP 8.0.10 and the Interbase 8.0.1 DLL:

select avg(tocars) from movens where id > 180500

select sum(tocars) from movens where id > 180500

Of course, we can not continue to make the shift 4.0 and PHP 8.0.10 unless that is working.

[AlexPeshkoff]

On 9/10/21 2:29 AM, Franz J Fortuny L de M wrote:

AVG and SUM cause Apache to crash and restart.

COUNT seems to be ok.

Try DatatypeCompatibility = 3.0 in firebird.conf.

[frafor]

Try DatatypeCompatibility = 3.0 in firebird.conf.

It did not work. I tried 2.5 and 3.0 and the same thing happened: Apache crashed.

[frafor]

I have finally been able to install it under a WSL 2 environment.

I am using PHP prior to 8. Firebird 4 is showing the same problem:

In this first picture, you can see what happens when you do the sum() operations using isql:

But look at the terrible result when you use PHP with the latest INTERBASE.SO version. This is NOT PHP 8.xxxx. It is the latest php version 7.xx prior to 8.xxx. But apparently it does the same in all php xxxxx.so modules.

[frafor]

I must inform to you that even though this is NOT the solution, I found that it works if I do this in the databases.conf file, but not in the firebird.conf file:

# Live Databases: # devel3 = /dbs/devel3.fdb { DataTypeCompatibility=3.0 }

[mrotteveel]

@frafor If it works when you modify databases.conf, but doesn't work when you modify firebird.conf, then that suggests that you modified it but did not restart Firebird (databases.conf is AFAIK re-read on each attach, while firebird.conf is read on server startup only), or you modified the wrong firebird.conf (one not read by Firebird).

[frafor]

I first tested it under Windows 10. There I tried it typing exactly what you suggested.

Could it be that I typed as per your suggestion DatatypeCompatibility instead of DataTypeCompatibility.

I came back to Windows 10 and made the changes and now it works.

However, the problem is still there, somewhere in the php dll that connects FirebirdSQL to PHP and Apache2. The behavior under IBExpert is different (using the WOW64 fbclient.dll supplied with the FirebirdSQL installation), it renders the aggregate results correctly. (Under Linux the problem is in the interbase.so file ...)

[mrotteveel]

Could it be that I typed as per your suggestion DatatypeCompatibility instead of DataTypeCompatibility.

The suggestion was Alex', not mine, but it is indeed DataTypeCompatibility, not DatatypeCompatibility.

As to the difference between PHP and IBExpert, I would guess that IBExpert was updated to handle the new data types, so it handles them appropriately. The problem is caused by the fact that the data type returned by aggregates was changed in Firebird 4, to make use of the new INT128, and NUMERIC/DECIMAL with precision of 38.