When executing a regular SQL query through ODBC, it requires an abnormal amount of memory. How to fix?

A

Arbane2017-11-10 16:06:35

PHP

Arbane, 2017-11-10 16:06:35

There are two servers. On CentOS 6.8 and on Debian Jessie (8.8). Each has apache, php, Firebird client libraries and a package for ODBC installed (unixODBC both there and there).
Detailed configurations:
CentOS 6.8, 64bit, PHP 5.3.3 (cli) (built: Aug 11 2016 20:33:53), apache2, php-odbc-5.3.3-48.el6_8.x86_64
Debian Jessie 8.8, 64bit, PHP 5.6.30-0+deb8u1 (cli) (built: Feb 8 2017 08:50:21), apache2, php5-odbc, unixodbc (oldstable, now 2.3.1-3 amd64 [installed])
There is a test file:

#!/usr/bin/php
<?php
$connect = odbc_connect("ProgrDB","USERUSER","pwrdpwrd");

if(odbc_error($connect)) {
  $err = odbc_errormsg($connect);
  print $err;
}

  if ($connect <= 0) {
    echo 'c ' . odbc_error($connect), ' ', odbc_errormsg($connect);
  }
  else {
    $query = "select coalesce(title, '') from SITE_POSITIONS_LIST(0) ";
    $res = odbc_exec($connect, $query);
    if( $res <= 0 ) {
      echo 'e ' . odbc_error($res), ' ', odbc_errormsg($res);
    }
    else
    {
      echo 'All OK!';
    }
  }
?>

The target server with a DB (one more machine) on Windows, there Firebird 3.0.2. There are no complaints about the work of everything connected with it.
So:
When running on CentOS, we get 'All OK!'.
When executed on Debian
PHP Fatal error: Out of memory (allocated 262144) (tried to allocate 140307991625985 bytes) in /var/www/html/site/odbctest.php on line 15
The connection itself is established both there and there. It also works to call the specified function directly from isql on Debain.
What I did to solve the problem:
I found out what this error can mean: 99.8% of cases on the Internet relate to the banal php memory limit. But hardly 95 petabytes, that's what php needs to run odbc_exec which shows 5 rows (from a table of 5 rows) like this:
Row1
Line2
Line3 Line4
Line5

About 95 petabytes

tried to allocate выдает немного разные значения, это один из результатов, в сообщении выше результат другой

The remaining 0.1% are odbc driver errors, such as NULL problems due to the fact that someone switched from 32bit to 64bit architecture, and the driver did it poorly. The question is the location of the bit that is responsible for NULL. Therefore, I added coalesce to the code, even though my procedure does not return null values anyway.
And the rest - my case. Other users (I counted exactly 3 lucky people on the stackoverflow, whose questions, of course, remained unanswered) require other values, say 13 exabytes of memory...
More about the settings:
ProgrDB is configured in odbc.ini:

[ProgrDB]
Description = Firebird connection to DB for site
Driver = Firebird64
Dbname = 192.168.0.20:E:\BASE\PRG.fdb
User = USERUSER
Password = pwrdpwrd
Role =
CharacterSet =
ReadOnly = No
NoWait = No

odbcinst.ini:

[Firebird64]
Description = InterBase/Firebird ODBC Driver
Driver = /usr/lib/libOdbcFb.so
Setup = /usr/lib/libOdbcFbS.so
Threading = 1
FileUsage = 1
CPTimeout =
CPReuse =

It differs only in that the paths are slightly different on CentOS: /usr/lib/odbc/
I tried to understand what my mistake was, I looked for differences:
libOdbcFb.so are the same, libfbclient.so.3.0.2 was taken on Debian with CentOS, it was there in the /usr/lib64 folder, there is no separate folder in usr, according to the instructions they are also placed in /usr/lib.
I checked the work with strace. But this is a bit complicated for me: the only piece that I noted is this:

mmap(NULL, 139629387190272, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = -1 ENOMEM (Cannot allocate memory)
mmap(NULL, 134217728, PROT_NONE, MAP_PRIVATE|MAP_ANONYMOUS|MAP_NORESERVE, -1, 0) = 0x7efe8c000000
munmap(0x7efe90000000, 67108864)        = 0
mprotect(0x7efe8c000000, 135168, PROT_READ|PROT_WRITE) = 0
mmap(NULL, 139629387059200, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = -1 ENOMEM (Cannot allocate memory)
write(2, "PHP Fatal error:  Out of memory "..., 146PHP Fatal error:  Out of memory (allocated 262144) (tried to allocate 139629386793217 bytes) in /var/www/html/site/odbctest.php on line 15
) = 146
close(2)                                = 0

I don't know what to do. Please tell me!
UPD 11/10/2017: PHP 7.0 was installed, the situation has not changed. I think on 1) odbc, 2) Firebird odbc driver, 3) php-odbc link.
UPD2:

unixodbc trace log

Ошибки не вижу в логе, хотя выполнение скрипта завершилось ошибкой как обычно

[ODBC][7660][1510327527.078817][__handles.c][460]
                Exit:[SQL_SUCCESS]
                        Environment = 0x560bc180d060
[ODBC][7660][1510327527.078879][SQLAllocHandle.c][375]
                Entry:
                        Handle Type = 2
                        Input Handle = 0x560bc180d060
[ODBC][7660][1510327527.078901][SQLAllocHandle.c][493]
                Exit:[SQL_SUCCESS]
                        Output Handle = 0x560bc1848c10
[ODBC][7660][1510327527.078921][SQLConnect.c][3703]
                Entry:
                        Connection = 0x560bc1848c10
                        Server Name = [ProgrDB][length = 7 (SQL_NTS)]
                        User Name = [USERUSER][length = 8 (SQL_NTS)]
                        Authentication = [********][length = 8 (SQL_NTS)]
                UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE'

[ODBC][7660][1510327527.166194][SQLConnect.c][4276]
                Exit:[SQL_SUCCESS_WITH_INFO]
[ODBC][7660][1510327527.166282][SQLAllocHandle.c][540]
                Entry:
                        Handle Type = 3
                        Input Handle = 0x560bc1848c10
[ODBC][7660][1510327527.166347][SQLAllocHandle.c][1081]
                Exit:[SQL_SUCCESS]
                        Output Handle = 0x560bc1878f20
[ODBC][7660][1510327527.166377][SQLGetInfo.c][554]
                Entry:
                        Connection = 0x560bc1848c10
                        Info Type = SQL_FETCH_DIRECTION (8)
                        Info Value = 0x7fff16a35a54
                        Buffer Length = 4
                        StrLen = (nil)
[ODBC][7660][1510327527.166402][SQLGetInfo.c][617]
                Exit:[SQL_SUCCESS]
[ODBC][7660][1510327527.166420][SQLSetStmtOption.c][197]
                Entry:
                        Statement = 0x560bc1878f20
                        Option = SQL_ATTR_CURSOR_TYPE
                        Value = 3
[ODBC][7660][1510327527.166441][SQLSetStmtOption.c][474]
                Exit:[SQL_SUCCESS]
[ODBC][7660][1510327527.166460][SQLExecDirect.c][240]
                Entry:
                        Statement = 0x560bc1878f20
                        SQL = [select coalesce(title, '') from SITE_POSITIONS_LIST(0) ][length = 55 (SQL_NTS)]
[ODBC][7660][1510327527.171816][SQLExecDirect.c][503]
                Exit:[SQL_SUCCESS]
[ODBC][7660][1510327527.171845][SQLNumResultCols.c][156]
                Entry:
                        Statement = 0x560bc1878f20
                        Column Count = 0x7f915bc551d0
[ODBC][7660][1510327527.171877][SQLNumResultCols.c][248]
                Exit:[SQL_SUCCESS]
                        Count = 0x7f915bc551d0 -> 1
[ODBC][7660][1510327527.171905][SQLColAttribute.c][293]
                Entry:
                        Statement = 0x560bc1878f20
                        Column Number = 1
                        Field Identifier = SQL_DESC_NAME
                        Character Attr = 0x7f915bc5b280
                        Buffer Length = 256
                        String Length = 0x7fff16a35962
                        Numeric Attribute = (nil)
[ODBC][7660][1510327527.171933][SQLColAttribute.c][664]
                Exit:[SQL_SUCCESS]
[ODBC][7660][1510327527.171952][SQLColAttribute.c][293]
                Entry:
                        Statement = 0x560bc1878f20
                        Column Number = 1
                        Field Identifier = SQL_DESC_CONCISE_TYPE
                        Character Attr = (nil)
                        Buffer Length = 0
                        String Length = (nil)
                        Numeric Attribute = 0x7f915bc5b390
[ODBC][7660][1510327527.171970][SQLColAttribute.c][664]
                Exit:[SQL_SUCCESS]
[ODBC][7660][1510327527.171987][SQLColAttribute.c][293]
                Entry:
                        Statement = 0x560bc1878f20
                        Column Number = 1
                        Field Identifier = SQL_DESC_OCTET_LENGTH
                        Character Attr = (nil)
                        Buffer Length = 0
                        String Length = (nil)
                        Numeric Attribute = 0x7fff16a35968
[ODBC][7660][1510327527.172004][SQLColAttribute.c][664]
                Exit:[SQL_SUCCESS]
[ODBC][7660][1510327527.172565][SQLDisconnect.c][208]
                Entry:
                        Connection = 0x560bc1848c10
[ODBC][7660][1510327527.182080][SQLDisconnect.c][364]
                Exit:[SQL_SUCCESS]
[ODBC][7660][1510327527.182117][SQLFreeHandle.c][284]
                Entry:
                        Handle Type = 2
                        Input Handle = 0x560bc1848c10
[ODBC][7660][1510327527.182136][SQLFreeHandle.c][333]
                Exit:[SQL_SUCCESS]
[ODBC][7660][1510327527.182152][SQLFreeHandle.c][219]
                Entry:
                        Handle Type = 1
                        Input Handle = 0x560bc180d060

Есть подозрение на версию библиотеки: стоит последняя, стоит попробовать 2.2.12, пока не понятно как