msphpsql
msphpsql copied to clipboard
microsoft
5.10.0 regression
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 <X64> (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.
Hi, I tested with PDO_SQLSRV driver and couldn't reproduce it. Which driver are you currently using, PDO_SQLSRV or SQLSRV?
I was using sqlsrv. How can I provide some additional info (I can't export tables/data cause it's my client production data).
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.
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.
I still couldn't reproduce it with SQLSRV, could you try a simple query without using dbal? like this example.
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)
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" } }
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).
Could you run odbcinst -j to get unixODBC version, and is it possible to provide an ODBC trace?
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?
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?
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.
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
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=
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.
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.
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.
@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.
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?
