search

microsoft / msphpsql

Microsoft Drivers for PHP for SQL Server
MIT License
1.8k stars 371 forks source link

PHP hangs on PDOStatement::fetch for large SELECT query #1207

Closed zorino closed 3 years ago

zorino commented 3 years ago

.+Please check the FAQ (frequently-asked questions) first. If you have other questions or something to report, please address the following:

+## PHP Driver version or file name

error_reporting(E_ALL);

try { $conn = new PDO('sqlsrv:Server=xxx.xxx.xxx.xxx;Database=Accpac', 'user', 'pass');

$sql = 'SELECT FIELD1, FIELD2, FIELDX FROM TABLE';

$stmt = $conn->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$stmt->execute();

$number_row = 0;

while ( $row = $stmt->fetch() ){

    $number_row++;

    if (($number_row % 1000) == 0) {
        echo $number_row . PHP_EOL;
    }
    if (($number_row > 48000)) {
        echo $number_row . PHP_EOL;
    }
}

echo "END";

print $stmt->rowCount();

} catch ( PDOException $e ) { print $e->getMessage(); die(); }

yitam commented 3 years ago

Hi @zorino

What's your table schema? Please add these lines to your while loop to help us investigate:

  1. $usage = memory_get_usage();
  2. unset ($row)
yitam commented 3 years ago

Any update @zorino ?

zorino commented 3 years ago

Hi @yitam

The memory is steady around 419700.

I have the same issue with different tables.

See the complete schema of one table that has the issue : https://gist.github.com/zorino/31ee79aa9bd0097c87608a892edb1f5c

zorino commented 3 years ago

@yitam

If it could help we succeeded to run the same queries with the FreeTDS driver

$conn = new PDO('odbc:Driver=FreeTDS; Server=xxx.xxx.xxx.xxx; Database=Accpac; TDS_Version=4.2; ClientCharset=UTF-8', 'user', 'pass');

yitam commented 3 years ago

@zorino I could not reproduce this in neither Ubuntu 18.04 nor Debian 9 with a table of 70154 rows and 10 columns of various sizes of varchar, including 2 varchar(max) columns. I did a select * from testtable and was able to finish fetching all rows.

Have you tried testing in a clean Debian 9?

It will help if you can provide more details, like the particular columns you're fetching. The example you've provided has many columns, so was your query a select *?

zorino commented 3 years ago

@yitam well it depends of the resultset size...

In your case you might need more rows or columns .. I had other queries that would hang after fetching over 100,000 records.

The Debian 9 in question run in a clean Docker container.

yitam commented 3 years ago

@zorino I tested more than 84000 rows for 10 columns yesterday. Before I got your reply I also tried testing in a clean Debian 9 Docker container without any issues.

Have you tried not using Docker container or do you always use Docker? What's your host, memory, disk size, etc.? How many columns did it start failing?

To help us investigate this further, the more details you can provide the better.

zorino commented 3 years ago

@yitam couldn't it be related to the SQL server version ? are you also using SQL server standard (64 bit) Version: 11.0.3128.0 ?

It's always related to the result set size if I change the order for instance it could yield more or less rows but will systematically hang at the same row... for the same query ... so it's probably related to a buffer or a timeout issue somewhere.

yitam commented 3 years ago

Likely not related to the version, @zorino , because I tested with SQL Server 2019 as well as SQL Server 2012 SP1: Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) Dec 28 2012 20:23:12 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.2

Yesterday I had no problem fetching all data (10 columns with 126007 rows in total) running Debian 9 in a Docker container (Ubuntu 18.04 host).

Are you using ODBC Driver 17 and unixODBC 2.3.7? To check, do odbcinst -j and then cat /etc/odbcinst.ini

You may want to provide ODBC trace (or even wireshark). For example, you can do this

printf "[odbc]\ntrace = yes\nTraceFile = /tmp/testtrace.log\n" >> /etc/odbcinst.ini

Then modify your pdo_sqlsrv script by adding TraceOn=1;, like this:

$conn = new PDO('sqlsrv:Server=xxx.xxx.xxx.xxx;TraceOn=1;Database=Accpac', 'user', 'pass');

v-chojas commented 3 years ago

Be careful with ODBC trace, it will use huge amount of disk space. Especially if you want to fetch so much data.

When it hang, attach a gdb, backtrace, see where it stuck

zorino commented 3 years ago

The output is indeed very huge .. 843M I've printed the first and last 100 lines below. I have obfuscated the results in connection strings and Buffer lines by changing characters to 'x'.

root@f827b1723760:/code# odbcinst -j
unixODBC 2.3.7
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

root@f827b1723760:/code# cat /etc/odbcinst.ini
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.6.so.1.1
UsageCount=1
trace = yes
TraceFile = /tmp/testtrace.log
[ODBC][408][1604073543.536046][SQLDriverConnectW.c][290]
        Entry:
            Connection = 0x55ffd3a2d7e0
            Window Hdl = (nil)
            Str In = [Server={xxx.xxx.xxx.xxx};UID={xxxx};PWD={xxxx};Database={Accpac};MARS_Connection={Yes};Driver={ODBC Driver 17 for SQL Ser...][length = 132]
            Str Out = (nil)
            Str Out Max = 0
            Str Out Ptr = 0x7ffc8483089a
            Completion = 0
        UNICODE Using encoding ASCII 'ANSI_X3.4-1968' and UNICODE 'UTF-16LE'

[ODBC][408][1604073543.666829][SQLGetEnvAttr.c][157]
        Entry:
            Environment = 0x55ffd3a4b250
            Attribute = 65002
            Value = 0x7ffc8482d8b0
            Buffer Len = 128
            StrLen = 0x7ffc8482d84c
[ODBC][408][1604073543.666853][SQLGetEnvAttr.c][273]
        Exit:[SQL_SUCCESS]
[ODBC][408][1604073543.666861][SQLFreeHandle.c][220]
        Entry:
            Handle Type = 1
            Input Handle = 0x55ffd3a4b250
[ODBC][408][1604073543.666882][SQLDriverConnectW.c][914]
        Exit:[SQL_SUCCESS_WITH_INFO]                    
            Connection Out [[NULL]]
[ODBC][408][1604073543.666892][SQLGetDiagRecW.c][535]
        Entry:
            Connection = 0x55ffd3a2d7e0
            Rec Number = 1
            SQLState = 0x7ffc8482ff44
            Native = 0x7f09a546d0b8
            Message Text = 0x7ffc8482ff50
            Buffer Length = 1025
            Text Len Ptr = 0x7ffc8482ff2c
[ODBC][408][1604073543.666902][SQLGetDiagRecW.c][596]
        Exit:[SQL_SUCCESS]
            SQLState = [01000]
            Native = 0x7f09a546d0b8 -> 5701
            Message Text = [[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Changed database context to 'Accpac'.]
[ODBC][408][1604073543.666925][SQLGetInfo.c][236]
        Entry:
            Connection = 0x55ffd3a2d7e0
            Info Type = SQL_DBMS_VER (18)
            Info Value = 0x7ffc84830a20
            Buffer Length = 256
            StrLen = 0x7ffc8483091e
[ODBC][408][1604073543.666946][SQLAllocHandle.c][540]
        Entry:
            Handle Type = 3
            Input Handle = 0x55ffd3a2d7e0
[ODBC][408][1604073543.666967][SQLAllocHandle.c][1085]
        Exit:[SQL_SUCCESS]
            Output Handle = 0x55ffd3a4b250
[ODBC][408][1604073543.666975][SQLSetStmtAttr.c][265]
        Entry:
            Statement = 0x55ffd3a4b250
            Attribute = SQL_ATTR_CURSOR_TYPE
            Value = (nil)
            StrLen = -5
[ODBC][408][1604073543.666982][SQLSetStmtAttr.c][928]
        Exit:[SQL_SUCCESS]
[ODBC][408][1604073543.666999][SQLPrepareW.c][165]
        Entry:
            Statement = 0x55ffd3a4b250
            SQL = [SELECT CONCAT(OEINVD.INVUNIQ,'_', OEINVD.LINENUM) AS ID,
       OEINVD.INVUNIQ,
       OEINVH.INVDATE,
       LTRIM(RTRIM(COALE...][length = 1397]
[ODBC][408][1604073543.667025][SQLPrepareW.c][346]
        Exit:[SQL_SUCCESS]
[ODBC][408][1604073543.667035][SQLExecute.c][187]
        Entry:
            Statement = 0x55ffd3a4b250
[ODBC][408][1604073543.930860][SQLExecute.c][357]
        Exit:[SQL_SUCCESS]
[ODBC][408][1604073543.930896][SQLNumResultCols.c][156]
        Entry:
            Statement = 0x55ffd3a4b250
            Column Count = 0x7ffc84830d48
[ODBC][408][1604073543.930916][SQLNumResultCols.c][251]
        Exit:[SQL_SUCCESS]
            Count = 0x7ffc84830d48 -> 40
[ODBC][408][1604073543.930923][SQLRowCount.c][173]
        Entry:
            Statement = 0x55ffd3a4b250
            Row Count = 0x7ffc84830d48
[ODBC][408][1604073543.930930][SQLRowCount.c][247]
        Exit:[SQL_SUCCESS]
            Row Count = 0x7ffc84830d48 -> -1
[ODBC][408][1604073543.930940][SQLDescribeColW.c][209]
        Entry:
            Statement = 0x55ffd3a4b250
            Column Number = 1
            Column Name = 0x7f09a545d8c0
            Buffer Length = 129
            Name Length = 0x7ffc84830c7c
            Data Type = 0x7f09a546221a
            Column Size = 0x7f09a5462220

...

            Target Type = 1 SQL_CHAR
            Buffer Length = 22
            Target Value = 0x7f09a546d2b8
            StrLen Or Ind = 0x7ffc84830be8
[ODBC][408][1604073612.596092][SQLGetData.c][545]
        Exit:[SQL_SUCCESS]                
            Buffer = [8.0000]                
            Strlen Or Ind = 0x7ffc84830be8 -> 6
[ODBC][408][1604073612.596100][SQLGetData.c][237]
        Entry:
            Statement = 0x55ffd3a4b250
            Column Number = 30
            Target Type = -8 SQL_WCHAR
            Buffer Length = 46
            Target Value = 0x7f09a54564d0
            StrLen Or Ind = 0x7ffc84830be8
[ODBC][408][1604073612.596107][SQLGetData.c][545]
        Exit:[SQL_SUCCESS]                
            Buffer = [xxxxxx                ](unicode)                
            Strlen Or Ind = 0x7ffc84830be8 -> 44
[ODBC][408][1604073612.596113][SQLGetData.c][237]
        Entry:
            Statement = 0x55ffd3a4b250
            Column Number = 31
            Target Type = -8 SQL_WCHAR
            Buffer Length = 26
            Target Value = 0x7f09a54553e0
            StrLen Or Ind = 0x7ffc84830be8
[ODBC][408][1604073612.596119][SQLGetData.c][545]
        Exit:[SQL_SUCCESS]                
            Buffer = [xxxx        ](unicode)                
            Strlen Or Ind = 0x7ffc84830be8 -> 24
[ODBC][408][1604073612.596125][SQLGetData.c][237]
        Entry:
            Statement = 0x55ffd3a4b250
            Column Number = 32
            Target Type = -8 SQL_WCHAR
            Buffer Length = 122
            Target Value = 0x7f09a548e080
            StrLen Or Ind = 0x7ffc84830be8
[ODBC][408][1604073612.596134][SQLGetData.c][545]
        Exit:[SQL_SUCCESS]                
            Buffer = [xxxxxxx xxxx xxxx xxx xxx.                             ](unicode)                
            Strlen Or Ind = 0x7ffc84830be8 -> 120
[ODBC][408][1604073612.596141][SQLGetData.c][237]
        Entry:
            Statement = 0x55ffd3a4b250
            Column Number = 33
            Target Type = -8 SQL_WCHAR
            Buffer Length = 122
            Target Value = 0x7f09a548e080
            StrLen Or Ind = 0x7ffc84830be8
[ODBC][408][1604073612.596149][SQLGetData.c][545]
        Exit:[SQL_SUCCESS]                
            Buffer = [xxxx xxxx. xxxxx xxxxx xxx xxx                          ](unicode)                
            Strlen Or Ind = 0x7ffc84830be8 -> 120
[ODBC][408][1604073612.596156][SQLGetData.c][237]
        Entry:
            Statement = 0x55ffd3a4b250
            Column Number = 34
            Target Type = -8 SQL_WCHAR
            Buffer Length = 62
            Target Value = 0x7f09a54741e0
            StrLen Or Ind = 0x7ffc84830be8
[ODBC][408][1604073612.596163][SQLGetData.c][545]
        Exit:[SQL_SUCCESS]                
            Buffer = [xxxxxx xxxxxx              ](unicode)                
            Strlen Or Ind = 0x7ffc84830be8 -> 60
[ODBC][408][1604073612.596169][SQLGetData.c][237]
        Entry:
            Statement = 0x55ffd3a4b250
            Column Number = 35
            Target Type = -8 SQL_WCHAR
            Buffer Length = 62
            Target Value = 0x7f09a54741e0
            StrLen Or Ind = 0x7ffc84830be8
[ODBC][408][1604073612.596176][SQLGetData.c][545]
        Exit:[SQL_SUCCESS]                
            Buffer = [xx                            ](unicode)                
            Strlen Or Ind = 0x7ffc84830be8 -> 60
[ODBC][408][1604073612.596182][SQLGetData.c][237]
        Entry:
            Statement = 0x55ffd3a4b250
            Column Number = 36
            Target Type = -8 SQL_WCHAR
            Buffer Length = 42
            Target Value = 0x7f09a5462990
            StrLen Or Ind = 0x7ffc84830be8
[ODBC][408][1604073612.596188][SQLGetData.c][545]
        Exit:[SQL_SUCCESS]                
            Buffer = [xxx xxx             ](unicode)                
            Strlen Or Ind = 0x7ffc84830be8 -> 40
[ODBC][408][1604073612.596194][SQLGetData.c][237]
        Entry:
            Statement = 0x55ffd3a4b250
            Column Number = 37
            Target Type = -8 SQL_WCHAR
            Buffer Length = 62
            Target Value = 0x7f09a54741e0
            StrLen Or Ind = 0x7ffc84830be8
yitam commented 3 years ago

Thanks @zorino for the efforts. I can see that you have at least 35+ columns in your query. How many columns do you have? What is the majority of the data types? Are they varchar, nvarchar, decimal, etc.?

I'd appreciate some answers from you re my questions earlier. Also, please provide an extract of your Dockerfile or anything else you think can help us investigate.

Have you tried not using Docker container or do you always use Docker? What's your host, memory, disk size, etc.?

As @v-chojas mentioned, using a debugger (gdb) will provide more insights. Although I can't reproduce the problem you've reported, I did try using gdb myself. Please take the following steps:

  1. Disable ODBC tracing (modify odbcinst.ini and your pdo script)
  2. Run your Docker like this docker run --privileged -it --name mytest <docker image name> (without priviledged gdb won't work)
  3. Run docker exec -it mytest bash
  4. Install ODBC, php and pdo_sqlsrv, etc.
  5. Install the debugger apt-get install gdb
  6. Run your php script
  7. In a new terminal, run docker exec -it mytest bash
  8. Type ps -ld | grep php (find the PID)
  9. Type gdb
  10. Inside gdb, type attach <PID> (from step 8)

When your php script is hanging, in the terminal where gdb is running, type break and then bt

Please send us the output of the stack trace.

zorino commented 3 years ago

@yitam the majority of the columns in the tested query are char with different length, and some decimal and smallint.

The gdb stack trace

(gdb) bt
#0  0x00007f3658726819 in poll () from target:/lib/x86_64-linux-gnu/libc.so.6
#1  0x00007f3651970993 in ?? () from target:/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.6.so.1.1
#2  0x00007f3651971061 in ?? () from target:/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.6.so.1.1
#3  0x00007f365194697f in ?? () from target:/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.6.so.1.1
#4  0x00007f3651948cb8 in ?? () from target:/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.6.so.1.1
#5  0x00007f365194cea8 in ?? () from target:/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.6.so.1.1
#6  0x00007f3651915fc4 in ?? () from target:/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.6.so.1.1
#7  0x00007f36518ffdef in ?? () from target:/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.6.so.1.1
#8  0x00007f36519002d4 in ?? () from target:/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.6.so.1.1
#9  0x00007f3651909308 in ?? () from target:/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.6.so.1.1
#10 0x00007f365182f773 in ?? () from target:/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.6.so.1.1
#11 0x00007f3651891bb6 in ?? () from target:/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.6.so.1.1
#12 0x00007f36553492c2 in SQLGetData (statement_handle=0x5625119358d0, column_number=<optimized out>, target_type=<optimized out>, target_value=0x7f3630e549b0, buffer_length=62, 
    strlen_or_ind=0x7fffa8cfbee8) at SQLGetData.c:474
#13 0x00007f36555c2c99 in sqlsrv_odbc_result_set::get_data(unsigned short, short, void*, long, long*, bool) ()
   from target:/usr/local/lib/php/extensions/no-debug-non-zts-20190902/pdo_sqlsrv.so
#14 0x00007f36555cdc08 in core_sqlsrv_get_field(sqlsrv_stmt*, unsigned short, sqlsrv_phptype, bool, void*&, long*, bool, SQLSRV_PHPTYPE*) ()
   from target:/usr/local/lib/php/extensions/no-debug-non-zts-20190902/pdo_sqlsrv.so
#15 0x00007f36555bdfd3 in pdo_sqlsrv_stmt_get_col_data(_pdo_stmt_t*, int, char**, unsigned long*, int*) ()
   from target:/usr/local/lib/php/extensions/no-debug-non-zts-20190902/pdo_sqlsrv.so
#16 0x000056250f5e2f85 in ?? ()
#17 0x000056250f5e575f in ?? ()
#18 0x000056250f5e6892 in ?? ()
#19 0x000056250f7e554e in execute_ex ()
#20 0x000056250f78a952 in zend_generator_resume ()
#21 0x000056250f7d58e1 in ?? ()
#22 0x000056250f7de5a1 in execute_ex ()
#23 0x000056250f78a952 in zend_generator_resume ()
#24 0x000056250f7d58e1 in ?? ()
#25 0x000056250f7de5a1 in execute_ex ()
#26 0x000056250f7e6033 in zend_execute ()
#27 0x000056250f7604a3 in zend_execute_scripts ()
#28 0x000056250f7001f0 in php_execute_script ()
#29 0x000056250f7e8116 in ?? ()
#30 0x000056250f49cb0b in ?? ()
#31 0x00007f365865c09b in __libc_start_main () from target:/lib/x86_64-linux-gnu/libc.so.6
#32 0x000056250f49d24a in _start ()
zorino commented 3 years ago

I also try on a arch linux host or within docker on it (also on a debian 10 server with similiar spec) with 16GB of RAM with Intel(R) Core(TM) i7-10710U CPU @ 1.10GHz and NVMe SSD with 131GB left.

ARG PHP_VERSION=7.4
FROM php:${PHP_VERSION}-cli

RUN apt-get update;
ENV DEBIAN_FRONTEND=noninteractive

##############################
# Adding Microsoft Repo
###################################################

RUN . /etc/os-release; \
    curl https://packages.microsoft.com/config/${ID}/${VERSION_ID}/packages-microsoft-prod.deb  > /tmp/packages-microsoft-prod.deb; \
    dpkg -i /tmp/packages-microsoft-prod.deb; \
    apt-get update;

##############################
# Installing REDIS php clients
###################################################

RUN pecl install redis; \
    docker-php-ext-enable redis

##############################
# Installing MSSQL php clients (downgraded security requirements to TLS1.0 for sqlserver 2012)
###################################################

RUN export ACCEPT_EULA=Y; apt-get install -yq msodbcsql17 unixodbc-dev openssl; \
    sed -i 's,^\(MinProtocol[ ]*=\).*,\1'TLSv1.0',g' /etc/ssl/openssl.cnf; \
    sed -i 's,^\(CipherString[ ]*=\).*,\1'DEFAULT@SECLEVEL=1',g' /etc/ssl/openssl.cnf; \
    pecl install sqlsrv; \
    pecl install -f pdo_sqlsrv; \
    docker-php-ext-enable sqlsrv pdo_sqlsrv;
v-chojas commented 3 years ago

Thanks for the information. To help track this down, since you seem to be able to consistently reproduce it...

It's always related to the result set size if I change the order for instance it could yield more or less rows but will systematically hang at the same row... for the same query ... so it's probably related to a buffer or a timeout issue somewhere.

...what is the smallest number of rows that you've seen to cause this hang? Could you share that schema and data? You can replace all the cell data with random values as long as they are the same length, and it will still cause a hang if this hypothesis about it being caused by the combination of data lengths in a row (and all the preceding rows, as well as possibly following ones) is true.

yitam commented 3 years ago

Thanks @zorino for the stack trace and docker file. Our php drivers enable MARS by default, and the ODBC team wants to isolate the source of this issue. Please try disabling MARS.

Since you're using pdo_sqlsrv, this is an example. Once you have disabled MARS, try re-running your repro script and see if that makes a difference.

Ideally, please provide fake data from your side, as @v-chojas has mentioned. In the meantime, we will try to reproduce this by creating random data of 40+ columns.

zorino commented 3 years ago

@yitam @v-chojas indeed I was able to fetch the complete result set by disabling MARS !!

Thank you very much for your help.

Should I close the ticket or let it open to further investigate the issue ?

v-chojas commented 3 years ago

Thanks for the confirmation, this helps to narrow down the cause. Now we know that MARS is definitely necessary to reproduce it. If you can provide the row that hangs the driver, it does not need to be exact data but as long as the lengths are the same and it will still cause it to hang, that would be even more helpful.

You can leave it open for now, because there is definitely a Bug present.

yitam commented 3 years ago

Thanks @zorino for your contributions so far. You mentioned this before,

It's always related to the result set size if I change the order for instance it could yield more or less rows but will systematically hang at the same row... for the same query ... so it's probably related to a buffer or a timeout issue somewhere.

So could you please elaborate? What did you mean by "change the order"? Did you mean the order of the columns in the SELECT clause?

On our side, we have tried reproducing the problem with random huge datasets but to no avail. Perhaps you can provide the schema and the data for the problematic row, replacing all characters by 'x' or numbers by any random numbers so long as it can still reproduce the issue. This would certainly help us track down the bug. Any input or extra info from you is much appreciated.

zorino commented 3 years ago

I was meaning the clause ORDER BY either ASC or DESC. See below the complete schema. I will track down the exact rows once I get a chance.

Column Name # Type Length Scale Precision Not Null Identity Default Description
INVUNIQ 1 decimal 9 [NULL] 19 TRUE FALSE [NULL] [NULL]
LINENUM 2 smallint 2 [NULL] 5 TRUE FALSE [NULL] [NULL]
AUDTDATE 3 decimal 5 [NULL] 9 TRUE FALSE [NULL] [NULL]
AUDTTIME 4 decimal 5 [NULL] 9 TRUE FALSE [NULL] [NULL]
AUDTUSER 5 char 8 [NULL] [NULL] TRUE FALSE [NULL] [NULL]
AUDTORG 6 char 6 [NULL] [NULL] TRUE FALSE [NULL] [NULL]
LINETYPE 7 smallint 2 [NULL] 5 TRUE FALSE [NULL] [NULL]
ITEM 8 char 24 [NULL] [NULL] TRUE FALSE [NULL] [NULL]
MISCCHARGE 9 char 6 [NULL] [NULL] TRUE FALSE [NULL] [NULL]
DESC 10 char 60 [NULL] [NULL] TRUE FALSE [NULL] [NULL]
ACCTSET 11 char 6 [NULL] [NULL] TRUE FALSE [NULL] [NULL]
USERCOSTMD 12 smallint 2 [NULL] 5 TRUE FALSE [NULL] [NULL]
PRICELIST 13 char 6 [NULL] [NULL] TRUE FALSE [NULL] [NULL]
CATEGORY 14 char 6 [NULL] [NULL] TRUE FALSE [NULL] [NULL]
LOCATION 15 char 6 [NULL] [NULL] TRUE FALSE [NULL] [NULL]
PICKSEQ 16 char 10 [NULL] [NULL] TRUE FALSE [NULL] [NULL]
EXPDATE 17 decimal 5 [NULL] 9 TRUE FALSE [NULL] [NULL]
STOCKITEM 18 smallint 2 [NULL] 5 TRUE FALSE [NULL] [NULL]
QTYORDERED 19 decimal 9 4 19 TRUE FALSE [NULL] [NULL]
QTYSHIPPED 20 decimal 9 4 19 TRUE FALSE [NULL] [NULL]
QTYBACKORD 21 decimal 9 4 19 TRUE FALSE [NULL] [NULL]
INVUNIT 22 char 10 [NULL] [NULL] TRUE FALSE [NULL] [NULL]
UNITCONV 23 decimal 9 6 19 TRUE FALSE [NULL] [NULL]
UNITPRICE 24 decimal 9 6 19 TRUE FALSE [NULL] [NULL]
PRICEOVER 25 smallint 2 [NULL] 5 TRUE FALSE [NULL] [NULL]
UNITCOST 26 decimal 9 6 19 TRUE FALSE [NULL] [NULL]
MOSTREC 27 decimal 9 6 19 TRUE FALSE [NULL] [NULL]
STDCOST 28 decimal 9 6 19 TRUE FALSE [NULL] [NULL]
COST1 29 decimal 9 6 19 TRUE FALSE [NULL] [NULL]
COST2 30 decimal 9 6 19 TRUE FALSE [NULL] [NULL]
UNITPRCDEC 31 smallint 2 [NULL] 5 TRUE FALSE [NULL] [NULL]
PRICEUNIT 32 char 10 [NULL] [NULL] TRUE FALSE [NULL] [NULL]
PRIUNTPRC 33 decimal 9 6 19 TRUE FALSE [NULL] [NULL]
PRIUNTCONV 34 decimal 9 6 19 TRUE FALSE [NULL] [NULL]
PRIPERCENT 35 decimal 5 5 9 TRUE FALSE [NULL] [NULL]
PRIAMOUNT 36 decimal 9 3 19 TRUE FALSE [NULL] [NULL]
BASEUNIT 37 char 10 [NULL] [NULL] TRUE FALSE [NULL] [NULL]
PRIBASPRC 38 decimal 9 6 19 TRUE FALSE [NULL] [NULL]
PRIBASCONV 39 decimal 9 6 19 TRUE FALSE [NULL] [NULL]
COSTUNIT 40 char 10 [NULL] [NULL] TRUE FALSE [NULL] [NULL]
COSUNTCST 41 decimal 9 6 19 TRUE FALSE [NULL] [NULL]
COSUNTCONV 42 decimal 9 6 19 TRUE FALSE [NULL] [NULL]
EXTICOST 43 decimal 9 3 19 TRUE FALSE [NULL] [NULL]
EXTINVMISC 44 decimal 9 3 19 TRUE FALSE [NULL] [NULL]
INVDISC 45 decimal 9 3 19 TRUE FALSE [NULL] [NULL]
EXTOVER 46 smallint 2 [NULL] 5 TRUE FALSE [NULL] [NULL]
UNITWEIGHT 47 decimal 9 4 19 TRUE FALSE [NULL] [NULL]
EXTWEIGHT 48 decimal 9 4 19 TRUE FALSE [NULL] [NULL]
TAUTH1 49 char 12 [NULL] [NULL] TRUE FALSE [NULL] [NULL]
TAUTH2 50 char 12 [NULL] [NULL] TRUE FALSE [NULL] [NULL]
TAUTH3 51 char 12 [NULL] [NULL] TRUE FALSE [NULL] [NULL]
TAUTH4 52 char 12 [NULL] [NULL] TRUE FALSE [NULL] [NULL]
TAUTH5 53 char 12 [NULL] [NULL] TRUE FALSE [NULL] [NULL]
TCLASS1 54 smallint 2 [NULL] 5 TRUE FALSE [NULL] [NULL]
TCLASS2 55 smallint 2 [NULL] 5 TRUE FALSE [NULL] [NULL]
TCLASS3 56 smallint 2 [NULL] 5 TRUE FALSE [NULL] [NULL]
TCLASS4 57 smallint 2 [NULL] 5 TRUE FALSE [NULL] [NULL]
TCLASS5 58 smallint 2 [NULL] 5 TRUE FALSE [NULL] [NULL]
TINCLUDED1 59 smallint 2 [NULL] 5 TRUE FALSE [NULL] [NULL]
TINCLUDED2 60 smallint 2 [NULL] 5 TRUE FALSE [NULL] [NULL]
TINCLUDED3 61 smallint 2 [NULL] 5 TRUE FALSE [NULL] [NULL]
TINCLUDED4 62 smallint 2 [NULL] 5 TRUE FALSE [NULL] [NULL]
TINCLUDED5 63 smallint 2 [NULL] 5 TRUE FALSE [NULL] [NULL]
TBASE1 64 decimal 9 3 19 TRUE FALSE [NULL] [NULL]
TBASE2 65 decimal 9 3 19 TRUE FALSE [NULL] [NULL]
TBASE3 66 decimal 9 3 19 TRUE FALSE [NULL] [NULL]
TBASE4 67 decimal 9 3 19 TRUE FALSE [NULL] [NULL]
TBASE5 68 decimal 9 3 19 TRUE FALSE [NULL] [NULL]
TAMOUNT1 69 decimal 9 3 19 TRUE FALSE [NULL] [NULL]
TAMOUNT2 70 decimal 9 3 19 TRUE FALSE [NULL] [NULL]
TAMOUNT3 71 decimal 9 3 19 TRUE FALSE [NULL] [NULL]
TAMOUNT4 72 decimal 9 3 19 TRUE FALSE [NULL] [NULL]
TAMOUNT5 73 decimal 9 3 19 TRUE FALSE [NULL] [NULL]
TRATE1 74 decimal 9 5 15 TRUE FALSE [NULL] [NULL]
TRATE2 75 decimal 9 5 15 TRUE FALSE [NULL] [NULL]
TRATE3 76 decimal 9 5 15 TRUE FALSE [NULL] [NULL]
TRATE4 77 decimal 9 5 15 TRUE FALSE [NULL] [NULL]
TRATE5 78 decimal 9 5 15 TRUE FALSE [NULL] [NULL]
DETAILNUM 79 smallint 2 [NULL] 5 TRUE FALSE [NULL] [NULL]
COMMINST 81 smallint 2 [NULL] 5 TRUE FALSE [NULL] [NULL]
GLNONSTKCR 82 char 45 [NULL] [NULL] TRUE FALSE [NULL] [NULL]
AVGCOST 83 decimal 9 6 19 TRUE FALSE [NULL] [NULL]
LASTCOST 84 decimal 9 6 19 TRUE FALSE [NULL] [NULL]
SHINUMBER 85 char 22 [NULL] [NULL] TRUE FALSE [NULL] [NULL]
SHIDTLNUM 86 smallint 2 [NULL] 5 TRUE FALSE [NULL] [NULL]
SHIPTRACK 87 char 36 [NULL] [NULL] TRUE FALSE [NULL] [NULL]
SHIPVIA 88 char 6 [NULL] [NULL] TRUE FALSE [NULL] [NULL]
VIADESC 89 char 60 [NULL] [NULL] TRUE FALSE [NULL] [NULL]
DISCPER 90 decimal 5 5 9 TRUE FALSE [NULL] [NULL]
ORDQTYORD 91 decimal 9 4 19 TRUE FALSE [NULL] [NULL]
ORDQTYBKOR 92 decimal 9 4 19 TRUE FALSE [NULL] [NULL]
ORDQTYCOMM 93 decimal 9 4 19 TRUE FALSE [NULL] [NULL]
ORDQTYTCOM 94 decimal 9 4 19 TRUE FALSE [NULL] [NULL]
ORDQTYSTD 95 decimal 9 4 19 TRUE FALSE [NULL] [NULL]
ORDUNIT 96 char 10 [NULL] [NULL] TRUE FALSE [NULL] [NULL]
ORDUNITCON 97 decimal 9 6 19 TRUE FALSE [NULL] [NULL]
MANITEMNO 98 char 24 [NULL] [NULL] TRUE FALSE [NULL] [NULL]
CUSTITEMNO 99 char 24 [NULL] [NULL] TRUE FALSE [NULL] [NULL]
QTYCOMMIT 100 decimal 9 4 19 TRUE FALSE [NULL] [NULL]
QTYTRUECOM 101 decimal 9 4 19 TRUE FALSE [NULL] [NULL]
ORDNUMBER 102 char 22 [NULL] [NULL] TRUE FALSE [NULL] [NULL]
ORDDTLNUM 103 smallint 2 [NULL] 5 TRUE FALSE [NULL] [NULL]
REFRESH 104 smallint 2 [NULL] 5 TRUE FALSE [NULL] [NULL]
ORIGQTYSHP 105 decimal 9 4 19 TRUE FALSE [NULL] [NULL]
VALUES 106 int 4 [NULL] 10 TRUE FALSE [NULL] [NULL]
DDTLTYPE 107 smallint 2 [NULL] 5 TRUE FALSE [NULL] [NULL]
DDTLNO 108 char 6 [NULL] [NULL] TRUE FALSE [NULL] [NULL]
BUILDQTY 109 decimal 9 4 19 TRUE FALSE [NULL] [NULL]
BUILDUNIT 110 char 10 [NULL] [NULL] TRUE FALSE [NULL] [NULL]
BLDUNTCONV 111 decimal 9 6 19 TRUE FALSE [NULL] [NULL]
EPOSPROMID 112 smallint 2 [NULL] 5 TRUE FALSE [NULL] [NULL]
TERMDISCBL 113 smallint 2 [NULL] 5 TRUE FALSE [NULL] [NULL]
BASEWUNIT 114 char 10 [NULL] [NULL] TRUE FALSE [NULL] [NULL]
WEIGHTUNIT 115 char 10 [NULL] [NULL] TRUE FALSE [NULL] [NULL]
WEIGHTCONV 116 decimal 9 6 19 TRUE FALSE [NULL] [NULL]
PRWGHTUNIT 117 char 10 [NULL] [NULL] TRUE FALSE [NULL] [NULL]
PRWGHTCONV 118 decimal 9 6 19 TRUE FALSE [NULL] [NULL]
PRIBASWCNV 119 decimal 9 6 19 TRUE FALSE [NULL] [NULL]
DEFUWEIGHT 120 decimal 9 4 19 TRUE FALSE [NULL] [NULL]
DEFEXTWGHT 121 decimal 9 4 19 TRUE FALSE [NULL] [NULL]
PRPRICEBY 122 smallint 2 [NULL] 5 TRUE FALSE [NULL] [NULL]
NEEDPCHECK 123 smallint 2 [NULL] 5 TRUE FALSE [NULL] [NULL]
CAPPROVEBY 124 char 8 [NULL] [NULL] TRUE FALSE [NULL] [NULL]
HDRDISC 125 decimal 9 3 19 TRUE FALSE [NULL] [NULL]
ITRAMOUNT1 126 decimal 9 3 19 TRUE FALSE [NULL] [NULL]
ITRAMOUNT2 127 decimal 9 3 19 TRUE FALSE [NULL] [NULL]
ITRAMOUNT3 128 decimal 9 3 19 TRUE FALSE [NULL] [NULL]
ITRAMOUNT4 129 decimal 9 3 19 TRUE FALSE [NULL] [NULL]
ITRAMOUNT5 130 decimal 9 3 19 TRUE FALSE [NULL] [NULL]
COG 131 decimal 9 3 19 TRUE FALSE [NULL] [NULL]
COSTED 132 smallint 2 [NULL] 5 TRUE FALSE [NULL] [NULL]
JOBRELATED 133 smallint 2 [NULL] 5 TRUE FALSE [NULL] [NULL]
CONTRACT 134 char 16 [NULL] [NULL] TRUE FALSE [NULL] [NULL]
PROJECT 135 char 16 [NULL] [NULL] TRUE FALSE [NULL] [NULL]
CCATEGORY 136 char 16 [NULL] [NULL] TRUE FALSE [NULL] [NULL]
COSTCLASS 137 smallint 2 [NULL] 5 TRUE FALSE [NULL] [NULL]
PROJSTYLE 138 smallint 2 [NULL] 5 TRUE FALSE [NULL] [NULL]
PROJTYPE 139 smallint 2 [NULL] 5 TRUE FALSE [NULL] [NULL]
REVREC 140 smallint 2 [NULL] 5 TRUE FALSE [NULL] [NULL]
BILLTYPE 141 smallint 2 [NULL] 5 TRUE FALSE [NULL] [NULL]
REVBILL 142 char 45 [NULL] [NULL] TRUE FALSE [NULL] [NULL]
COGSWIP 143 char 45 [NULL] [NULL] TRUE FALSE [NULL] [NULL]
RTGAMOUNT 144 decimal 9 3 19 TRUE FALSE [NULL] [NULL]
RTGPERCENT 145 decimal 5 5 9 TRUE FALSE [NULL] [NULL]
RTGDAYS 146 smallint 2 [NULL] 5 TRUE FALSE [NULL] [NULL]
RTGDATEDUE 147 decimal 5 [NULL] 9 TRUE FALSE [NULL] [NULL]
RTGDDTOVR 148 smallint 2 [NULL] 5 TRUE FALSE [NULL] [NULL]
RTGAMTOVR 149 smallint 2 [NULL] 5 TRUE FALSE [NULL] [NULL]
RTGTXBASE1 150 decimal 9 3 19 TRUE FALSE [NULL] [NULL]
RTGTXBASE2 151 decimal 9 3 19 TRUE FALSE [NULL] [NULL]
RTGTXBASE3 152 decimal 9 3 19 TRUE FALSE [NULL] [NULL]
RTGTXBASE4 153 decimal 9 3 19 TRUE FALSE [NULL] [NULL]
RTGTXBASE5 154 decimal 9 3 19 TRUE FALSE [NULL] [NULL]
RTGTXAMT1 155 decimal 9 3 19 TRUE FALSE [NULL] [NULL]
RTGTXAMT2 156 decimal 9 3 19 TRUE FALSE [NULL] [NULL]
RTGTXAMT3 157 decimal 9 3 19 TRUE FALSE [NULL] [NULL]
RTGTXAMT4 158 decimal 9 3 19 TRUE FALSE [NULL] [NULL]
RTGTXAMT5 159 decimal 9 3 19 TRUE FALSE [NULL] [NULL]
PRICEOPT 160 smallint 2 [NULL] 5 TRUE FALSE [NULL] [NULL]
PAYMNTDIST 161 decimal 9 3 19 TRUE FALSE [NULL] [NULL]
SERIALQTY 162 int 4 [NULL] 10 TRUE FALSE [NULL] [NULL]
LOTQTY 163 decimal 9 4 19 TRUE FALSE [NULL] [NULL]
SLITEM 164 smallint 2 [NULL] 5 TRUE FALSE [NULL] [NULL]
COMPANYID 165 int 4 [NULL] 10 TRUE FALSE [NULL] [NULL]
OPPOID 166 int 4 [NULL] 10 TRUE FALSE [NULL] [NULL]
v-chojas commented 3 years ago

Thanks for the schema, but it does not seem to match the trace you posted earlier -- could you give a corresponding trace, which shows in particular which column it hung at? Since you mention it is consistently reproducible we would like to be able to repro too, so that the bug can be found and fixed.

yitam commented 3 years ago

Thanks again @zorino for the schema. Yet, as @v-chojas mentioned, we appreciate any additional data or trace for the particular column(s) and/or row(s) from you so that we can try reproducing the problem on our side.

zorino commented 3 years ago

My comment was the full schema, however the query was selecting just a certain number of columns in that table.

Which kind of additional data or trace do you want me to provide ?

v-chojas commented 3 years ago

however the query was selecting just a certain number of columns in that table.

That is more important since it's what gets sent through the network - to be more precise, we are interested in the schema of the resultset itself. If you can provide a repro with anonymised/fake data of a minimal number of rows, even better.

v-chojas commented 3 years ago

Any updates on getting a repro for this? It is a very interesting bug we would like to look at.

yitam commented 3 years ago

My comment was the full schema, however the query was selecting just a certain number of columns in that table.

Which kind of additional data or trace do you want me to provide ?

Hi @zorino, please provide the query or queries that show(s) which columns you're selecting and at which certain row(s) this problem will always occur, etc. Any additional data from you is much appreciated.

yitam commented 3 years ago

Closing this due to inactivity, @zorino. Please feel free to reopen anytime.