5.10.0 regression #1371

marcinkleczek · 2022-02-07T20:33:45Z

Hi

I have regression. On 5.9.0 simple query returns all 2503 rows, on 5.10.0 it returns only part of data (for * 1136, for id column only 2503, for some columns [id, name, code] 745).

PHP version
First problem occured on 8.1.2 (when I wanted to migrate to 8.1). Then I downgrade to 8.0.15 and on 5.9.0 everything works perfectly. On 5.10.0 problems occurs.

PHP SQLSRV or PDO_SQLSRV version
5.10.0

SQL Server version
Microsoft SQL Server 2016 (SP1-GDR) (KB4505219) - 13.0.4259.0 (X64)
Jun 15 2019 19:20:12
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows Server 2016 Standard 6.3 (Build 14393: ) (Hypervisor)

Client operating system
debian 10.11

Table schema
don't have.

Problem description
On 5.9.0 simple query returns all 2503 rows, on 5.10.0 it returns only part of data (for * 1136, for id column only 2503, for some columns [id, name, code] 745).

Expected behavior and actual behavior
The same amount of data.

absci · 2022-02-08T21:46:57Z

Hi, I tested with PDO_SQLSRV driver and couldn't reproduce it. Which driver are you currently using, PDO_SQLSRV or SQLSRV?

marcinkleczek · 2022-02-08T21:48:46Z

I was using sqlsrv. How can I provide some additional info (I can't export tables/data cause it's my client production data).

absci · 2022-02-08T21:59:01Z

I was using sqlsrv. How can I provide some additional info (I can't export tables/data cause it's my client production data).

Ok thanks, I'll test sqlsrv to see if there's any issue.

marcinkleczek · 2022-02-08T22:02:49Z

php 8.0
PHP 8.0.15 (cli) (built: Jan 29 2022 07:39:13) ( NTS )
Copyright (c) The PHP Group
Zend Engine v4.0.15, Copyright (c) Zend Technologies
with Zend OPcache v8.0.15, Copyright (c), by Zend Technologies

sqlsrv

sqlsrv support => enabled
ExtensionVer => 5.9.0

Directive => Local Value => Master Value
sqlsrv.ClientBufferMaxKBSize => 10240 => 10240
sqlsrv.LogSeverity => 0 => 0
sqlsrv.LogSubsystems => 0 => 0
sqlsrv.SetLocaleInfo => 2 => 2
sqlsrv.WarningsReturnAsErrors => On => On

php 8.1
PHP 8.1.2 (cli) (built: Jan 27 2022 12:20:56) (NTS)
Copyright (c) The PHP Group
Zend Engine v4.1.2, Copyright (c) Zend Technologies
with Zend OPcache v8.1.2, Copyright (c), by Zend Technologies

sqlsrv

sqlsrv support => enabled
ExtensionVer => 5.10.0

Directive => Local Value => Master Value
sqlsrv.ClientBufferMaxKBSize => 10240 => 10240
sqlsrv.LogSeverity => 0 => 0
sqlsrv.LogSubsystems => 0 => 0
sqlsrv.SetLocaleInfo => 2 => 2
sqlsrv.WarningsReturnAsErrors => On => On

The same command:
root@PHP8:/home/leanmate/dev# php8.0 bin/console *:daily-plan-save
int(2532)
root@PHP8:/home/leanmate/dev# php8.1 bin/console *:daily-plan-save
int(1136)

Doctrine dbal:

        $query = "SELECT * FROM HM.TW";
        $retval = $this->dbal->fetchAllAssociative($query, []);

raw functions returns the same.

marcinkleczek · 2022-02-08T22:03:30Z

I can't compile 5.9.0 on 8.1 and I can't compile 5.10.0 on 8.0.

absci · 2022-02-09T00:12:07Z

I still couldn't reproduce it with SQLSRV, could you try a simple query without using dbal? like this example.

marcinkleczek · 2022-02-09T08:35:50Z

No I've got errors on both 5.9.0 and 5.10.0.
Are there any silent memory limits?

$tsql = "SELECT id FROM DBNAME.HM.TW";
$stmt = sqlsrv_query( $conn, $tsql);
$num = 0;
/* Retrieve each row as an associative array and display the results.*/
while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC))
{
$num++;
}
var_dump($num);
int(2533)

For: SELECT *
int(1136)

marcinkleczek · 2022-02-09T09:14:37Z

OK, after investigation I have an error at one of records, but different for 5.9 and 5.10:
root@PHP8:/home/leanmate/dev/public# php8.1 simple.php
sqlsrv_errors: entering
array(1) {
[0]=>
array(6) {
[0]=>
string(5) "HY090"
["SQLSTATE"]=>
string(5) "HY090"
[1]=>
int(0)
["code"]=>
int(0)
[2]=>
string(57) "[unixODBC][Driver Manager]Invalid string or buffer length"
["message"]=>
string(57) "[unixODBC][Driver Manager]Invalid string or buffer length"
}
}

php8.0 simple.php
sqlsrv_errors: entering
array(1) {
[0]=>
array(6) {
[0]=>
string(5) "01004"
["SQLSTATE"]=>
string(5) "01004"
[1]=>
int(0)
["code"]=>
int(0)
[2]=>
string(71) "[Microsoft][ODBC Driver 17 for SQL Server]String data, right truncation"
["message"]=>
string(71) "[Microsoft][ODBC Driver 17 for SQL Server]String data, right truncation"
}
}

marcinkleczek · 2022-02-09T09:16:03Z

Setting:
sqlsrv_configure('WarningsReturnAsErrors', 0);

Works for 5.9.0 but on 5.10.0 i still get an error (and that's why I haven't get enought rows).

absci · 2022-02-09T19:52:15Z

Could you run odbcinst -j to get unixODBC version, and is it possible to provide an ODBC trace?

marcinkleczek · 2022-02-09T20:35:08Z

unixODBC 2.3.7
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/marcin/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

root@PHP8:/etc# cat odbcinst.ini
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.8.so.1.1
UsageCount=1

Rest of files are empty.

How can I dump ODBC trace?

absci · 2022-02-09T23:17:39Z

unixODBC 2.3.7 DRIVERS............: /etc/odbcinst.ini SYSTEM DATA SOURCES: /etc/odbc.ini FILE DATA SOURCES..: /etc/ODBCDataSources USER DATA SOURCES..: /home/marcin/.odbc.ini SQLULEN Size.......: 8 SQLLEN Size........: 8 SQLSETPOSIROW Size.: 8

root@PHP8:/etc# cat odbcinst.ini [ODBC Driver 17 for SQL Server] Description=Microsoft ODBC Driver 17 for SQL Server Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.8.so.1.1 UsageCount=1

Rest of files are empty.

How can I dump ODBC trace?

You could follow this doc.

marcinkleczek · 2022-02-16T08:35:33Z

I hadn't time, but I have odbctrace.log now. But it's huge. It's the few last lines. If you need whole file, please ping me.

            Entry:
                    Statement = 0x559c8964ac00
                    Column Number = 9
                    Target Type = 4 SQL_INTEGER
                    Buffer Length = 8
                    Target Value = 0x7f5ce32bfa28
                    StrLen Or Ind = 0x7ffce60cc458

[ODBC][10621][1645000358.854242][SQLGetData.c][545]
Exit:[SQL_SUCCESS]
Buffer = [0]
Strlen Or Ind = 0x7ffce60cc458 -> 4
[ODBC][10621][1645000358.854249][SQLGetData.c][237]
Entry:
Statement = 0x559c8964ac00
Column Number = 10
Target Type = 1 SQL_CHAR
Buffer Length = 41
Target Value = 0x7f5ce2b4fc00
StrLen Or Ind = 0x7ffce60cc368
[ODBC][10621][1645000358.854258][SQLGetData.c][545]
Exit:[SQL_SUCCESS_WITH_INFO]
Buffer = [U. środki trwałe nieruchmość Łuczyc]
Strlen Or Ind = 0x7ffce60cc368 -> 41
[ODBC][10621][1645000358.854266][SQLGetDiagField.c][958]
Entry:
Statement = 0x559c8964ac00
Rec Number = 1
Diag Ident = 4
Diag Info Ptr = 0x7ffce60cc380
Buffer Length = 6
String Len Ptr = 0x7ffce60cc366
DIAG [01004] [Microsoft][ODBC Driver 17 for SQL Server]String data, right truncation

[ODBC][10621][1645000358.854294][SQLGetDiagField.c][990]
Exit:[SQL_SUCCESS]
[ODBC][10621][1645000358.854301][SQLGetData.c][237]
Entry:
Statement = 0x559c8964ac00
Column Number = 10
Target Type = 1 SQL_CHAR
Buffer Length = -1957
Target Value = 0x7f5ce29f6bc0
StrLen Or Ind = 0x7ffce60cc390
[ODBC][10621][1645000358.854307][SQLGetData.c][371]Error: HY090
[ODBC][10621][1645000358.854321][SQLGetDiagRecW.c][535]
Entry:
Statement = 0x559c8964ac00
Rec Number = 1
SQLState = 0x7ffce60cb014
Native = 0x7ffce60cafec
Message Text = 0x7ffce60cb020
Buffer Length = 1025
Text Len Ptr = 0x7ffce60cafea
[ODBC][10621][1645000358.854331][SQLGetDiagRecW.c][596]
Exit:[SQL_SUCCESS]
SQLState = [HY090]
Native = 0x7ffce60cafec -> 0
Message Text = [[unixODBC][Driver Manager]Invalid string or buffer length]
[ODBC][10621][1645000358.854343][SQLGetDiagRecW.c][535]
Entry:
Statement = 0x559c8964ac00
Rec Number = 2
SQLState = 0x7ffce60cb014
Native = 0x7ffce60cafec
Message Text = 0x7ffce60cb020
Buffer Length = 1025
Text Len Ptr = 0x7ffce60cafea
[ODBC][10621][1645000358.854350][SQLGetDiagRecW.c][596]
Exit:[SQL_NO_DATA]
[ODBC][10621][1645000358.854446][SQLFreeHandle.c][387]
Entry:
Handle Type = 3
Input Handle = 0x559c8964ac00
[ODBC][10621][1645000358.854931][SQLFreeHandle.c][490]
Exit:[SQL_SUCCESS]
[ODBC][10621][1645000358.855627][SQLEndTran.c][417]
Entry:
Connection = 0x559c896552d0
Completion Type = 1
[ODBC][10621][1645000358.855642][SQLGetInfo.c][236]
Entry:
Connection = 0x559c896552d0
Info Type = SQL_CURSOR_COMMIT_BEHAVIOR (23)
Info Value = 0x559c89656748
Buffer Length = 2
StrLen = 0x7ffce60cd9fe
[ODBC][10621][1645000358.855652][SQLGetInfo.c][236]
Entry:
Connection = 0x559c896552d0
Info Type = SQL_CURSOR_ROLLBACK_BEHAVIOR (24)
Info Value = 0x559c8965674a
Buffer Length = 2
StrLen = 0x7ffce60cd9fe
[ODBC][10621][1645000358.855658][SQLEndTran.c][566]
Exit:[SQL_SUCCESS]
[ODBC][10621][1645000358.855665][SQLDisconnect.c][208]
Entry:
Connection = 0x559c896552d0
[ODBC][10621][1645000358.855715][SQLDisconnect.c][379]
Exit:[SQL_SUCCESS]
[ODBC][10621][1645000358.855724][SQLFreeHandle.c][290]
Entry:
Handle Type = 2
Input Handle = 0x559c896552d0
[ODBC][10621][1645000358.855732][SQLFreeHandle.c][339]
Exit:[SQL_SUCCESS]
[ODBC][10621][1645000358.857098][SQLFreeHandle.c][220]
Entry:
Handle Type = 1
Input Handle = 0x559c895a0200
[ODBC][10621][1645000358.857133][SQLFreeHandle.c][220]
Entry:
Handle Type = 1
Input Handle = 0x559c895a27a0

absci · 2022-02-16T21:15:23Z

Thanks, could you also give the output of locale command?

marcinkleczek · 2022-02-16T21:30:24Z

LANG=en_US.UTF-8
LANGUAGE=
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=

thsmrtone1 · 2022-02-22T16:13:22Z

I believe we are being affected by the same or similar issue.

Specs:

Symfony/Doctrine project
Using Docker with php:fpm-8.0 as base image
installing msodbcsql17 as instructed from official docs
installing sqlsrv php extension via pecl

When flushing several records to the database, we will consistently get the SQL error "SQLSTATE [22001, 0]: [Microsoft][ODBC Driver 18 for SQL Server]String data, right truncation" when inserting one specific record to the database. For example, if we are flushing 5 records to the same table, the 4th record will consistently fail.

The work around in our case is to force pecl to install v5.9.0 instead of v5.10.0

Unfortunately, I'm unable to provide an example file to reproduce, but wanted to provide our scenario in case it helps shed light on the issue.

marcinkleczek · 2022-02-22T19:39:19Z

But 5.9.0 doesn't work with php 8.1.

absci · 2022-03-08T01:09:38Z

But 5.9.0 doesn't work with php 8.1.

In you ODBC trace, there's a line Buffer Length = -1957, the negative value for buffer length doesn't look right, I have compiled a sqlsrv driver with some added print statement when it calls SQLGetData. Just need to figure out why the buffer_length somehow becomes negative.

sqlsrv.zip

marcinkleczek · 2022-03-08T08:07:05Z

How can I change this value?

absci · 2022-03-08T19:54:44Z

How can I change this value?

This value is calculated by the driver, so the client can't change this specific buffer size. There's a variable ClientBufferMaxKBSize you can play with. https://docs.microsoft.com/en-us/sql/connect/php/cursor-types-sqlsrv-driver?view=sql-server-ver15#client-side-cursors-and-the-sqlsrv-driver. Also, if you could use the custom driver I uploaded(sqlsrv.zip), it will print out some more info which may help to debug.

absci · 2022-04-29T23:54:03Z

@marcinkleczek Have you made any progress on this? We're still unable to reproduce, we understand that you can't provide actual data but a simple repro app would be helpful.

marcinkleczek · 2022-05-04T16:50:15Z

No I don't. But for small amount of data it works as in 8.0. Maybe we should wait till 5.11 and try after update?

jstolp · 2022-07-15T14:37:30Z

it might be the same error as i was able to reproduce in #1391

absci · 2022-09-14T14:06:55Z

This issue is fixed in PR #1408, it will be included in the next release.

absci closed this as completed Sep 14, 2022

absci mentioned this issue Sep 14, 2022

Wrongly implicit conversion of unicode nvarchar(MAX) values #1391

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

5.10.0 regression #1371

5.10.0 regression #1371

marcinkleczek commented Feb 7, 2022

absci commented Feb 8, 2022 •

edited

Loading

marcinkleczek commented Feb 8, 2022

absci commented Feb 8, 2022

marcinkleczek commented Feb 8, 2022

marcinkleczek commented Feb 8, 2022

absci commented Feb 9, 2022

marcinkleczek commented Feb 9, 2022

marcinkleczek commented Feb 9, 2022

marcinkleczek commented Feb 9, 2022

absci commented Feb 9, 2022

marcinkleczek commented Feb 9, 2022

absci commented Feb 9, 2022

marcinkleczek commented Feb 16, 2022

absci commented Feb 16, 2022

marcinkleczek commented Feb 16, 2022

thsmrtone1 commented Feb 22, 2022

marcinkleczek commented Feb 22, 2022

absci commented Mar 8, 2022

marcinkleczek commented Mar 8, 2022

absci commented Mar 8, 2022

absci commented Apr 29, 2022

marcinkleczek commented May 4, 2022

jstolp commented Jul 15, 2022

absci commented Sep 14, 2022

5.10.0 regression #1371

5.10.0 regression #1371

Comments

marcinkleczek commented Feb 7, 2022

absci commented Feb 8, 2022 • edited Loading

marcinkleczek commented Feb 8, 2022

absci commented Feb 8, 2022

marcinkleczek commented Feb 8, 2022

marcinkleczek commented Feb 8, 2022

absci commented Feb 9, 2022

marcinkleczek commented Feb 9, 2022

marcinkleczek commented Feb 9, 2022

marcinkleczek commented Feb 9, 2022

absci commented Feb 9, 2022

marcinkleczek commented Feb 9, 2022

absci commented Feb 9, 2022

marcinkleczek commented Feb 16, 2022

absci commented Feb 16, 2022

marcinkleczek commented Feb 16, 2022

thsmrtone1 commented Feb 22, 2022

marcinkleczek commented Feb 22, 2022

absci commented Mar 8, 2022

marcinkleczek commented Mar 8, 2022

absci commented Mar 8, 2022

absci commented Apr 29, 2022

marcinkleczek commented May 4, 2022

jstolp commented Jul 15, 2022

absci commented Sep 14, 2022

absci commented Feb 8, 2022 •

edited

Loading