Msphpsql: HY010 [unixODBC][Driver Manager]Function sequence error when updating/deleting 0 rows
Driver version or file name
About pecl.php.net/pdo_sqlsrv-4.3.0
===================================
Release Type PECL-style PHP extension (source code)
Name pdo_sqlsrv
Channel pecl.php.net
Summary Microsoft Drivers for PHP for SQL Server
(PDO_SQLSRV)
Description The Microsoft Drivers for PHP for SQL Server are
PHP extensions that allow for the reading and
writing of SQL Server data from within PHP
scripts. The SQLSRV extension provides a
procedural interface while the PDO_SQLSRV
extension implements PDO for accessing data in
all editions of SQL Server 2008 R2 and later
(including Azure SQL DB). These drivers rely on
the Microsoft ODBC Driver for SQL Server to
handle the low-level communication with SQL
Server.
*This package contains only the PDO_SQLSRV
driver.*
Maintainers Meet Bhagdev <[email protected]> (lead)
Jay Kint <[email protected]> (lead,
inactive)
Marie Barwin <[email protected]> (lead)
Hadis Kakanejadi Fard <[email protected]>
(lead)
Release Date 2017-07-05 14:25:43
Release Version 4.3.0 (stable)
API Version 4.3.0 (stable)
License The MIT License (MIT)
(https://opensource.org/licenses/mit)
Release Notes [Added]
- Added Unicode Column name support (issue
#138).
- Support for Always On Availability groups via
Transparent Network IP Resolution (TNIR)
- Added support for sql_variant data type with
limitation (issue #51 and issue #127)
- Support drivers on Debian Jessie (tested on
Debian 8.7)
- Connection Resiliency support in Windows
- Connection pooling support for Linux and macOS
- Support for Mac(El Capitan and above)
- Azure Active Directory Authentication with
ActiveDirectoryPassword and SqlPassword
[Fixed]
- Fixed PECL installation errors when PHP was
installed from source (issue #213).
- Fixed segmentation fault with
PDOStatement::getColumnMeta() when the supplied
column index is out of range (issue #224).
- Fixed the assertion error (Linux) when
fetching data from a binary column using the
binary encoding (issue #226).
- Fixed issue output parameters bound to empty
string (issue #182).
- Fixed issue with
SQLSRV_ATTR_FETCHES_NUMERIC_TYPE when column
return type is set on statement (issue #173).
- Fixed a memory leak in closing connection
resources.
- Fixed load ordering issue in MacOS (issue
#417)
- Added a workaround for a bug in unixODBC 2.3.4
when connection pooling is enabled.
- Fixed the issue with driver loading order in
macOS
- Fixed null returned when an empty string is
set to an output parameter (issue #308).
- Fixed incorrectly binding of unicode parameter
when emulate prepare is on and the encoding is
set at the statement level (issue #92).
- Fixed binary column binding when emulate
prepare is on (issue #140).
- Improved performance by implementing a cache
to store column SQL types and display sizes
(issue #189).
- Fixed issue with the unsupported attribute
PDO::ATTR_PERSISTENT in connection (issue #65).
- Fixed the issue with executing DELETE
operation on a non-existent value (issue #336).
- Fixed incorrectly binding of unicode parameter
when emulate prepare is on and the encoding is
set at the statement level (issue #92).
- Fixed wrong value returned when fetching
varbinary value on Linux (issue #270).
- Fixed binary data not returned when the column
is bound by name (issue #35).
- Fixed exception thrown on closeCursor() when
the statement has not been executed (issue
#267).
[Limitation]
- Limited support for inout / output params when
using sql_variant type
[Known Issues]
- When pooling is enabled in Linux or MAC
- unixODBC 2.3.4 or prior (Linux and MAC)
might not return proper diagnostics information,
such as error messages, warnings and informative
messages
- due to this unixODBC bug, fetch large data
(such as xml, binary) as streams as a
workaround. See the examples
(https://github.com/Microsoft/msphpsql/wiki/Connection-Pooling-on-Linux-and-Mac)
Required Dependencies PHP version 7.0.0
PEAR installer version 1.4.0b1 or newer
OS/Arch matching pattern '//'
package.xml version 2.0
Last Modified 2018-03-03 14:08
Previous Installed - None -
Version
SQL Server version
SQL Server 10.50.6220 (2008r2)
Client operating system
Debian 8篓
PHP version
7.0.27-1~dotdeb+8.1
Problem description
It seems, that issue #336 has not been fixed, because I can reproduce the same behavior in the 4.3.0 version. When executing UPDATE or DELETE through PDO with WHERE condition filtering out 0 rows (thus not updating anything), HY010 [unixODBC][Driver Manager]Function sequence error is raised.
Expected behavior and actual behavior
Assume this table with only one row:
Table test1
+----+------+
| id | name |
+----+------+
| 1 | test |
+----+------+
When running UPDATE test1 SET name = 'updated' WHERE id = 5;
HY010 [unixODBC][Driver Manager]Function sequence error is raised. No error should be raised.
Same error is raised, when trying to delete non existent row.
Repro code
SQL
CREATE TABLE [dbo].[test1](
[id] [int] NOT NULL,
[name] [nvarchar](50) NOT NULL
);
PHP:
$pdo = new \PDO('sqlsrv:server=<server addr>;database=<db name>', '<username>', '<password>);
$result = $pdo->exec("UPDATE test1 SET name = 'updated' WHERE id = 5");
var_dump($result); // bool(false)
var_dump($pdo->errorCode()); // string(5) "HY010"
var_dump($pdo->errorInfo()); /*
array(3) {
聽 | [0]=>
聽 | string(5) "HY010"
聽 | [1]=>
聽 | int(0)
聽 | [2]=>
聽 | string(49) "[unixODBC][Driver Manager]Function sequence error"
聽 | }
*/
hubipe
All 18 comments
Hi @hubipe,
Unfortunately I can not reproduce this issue as shown in your repro. I tested with both v4.3.0 and our latest v5.2.0RC. Please double check your client info?
$conn = new PDO( "sqlsrv:server=$server", $uid, $pwd);
print_r($conn->getAttribute( PDO::ATTR_CLIENT_VERSION ));
yitam
on 5 Mar 2018
Hello @yitam,
the result of your code is as follows:
(
[DriverName] => libmsodbcsql-13.1.so.9.2
[DriverODBCVer] => 03.52
[DriverVer] => 13.01.0009
[ExtensionVer] => 4.0.8.9215
)
I guess that problem is in installation, where even though there is version 4.3.0 installed, the webserver still uses version 4.0.8.9215. Thank you for pointing me the correct direction.
hubipe
on 6 Mar 2018
Glad to hear that @hubipe!
Please let us know if you still have questions.
yitam
on 6 Mar 2018
Closing issue due to inactivity. Please feel free to reopen.
yitam
on 5 Apr 2018
Hi,
I'm facing the same issue with 5.2.0 under Docker.
Do you have any idea ?
younessadmi
on 19 Feb 2019
@younessadmi please provide more details to help us investigate, like what platform, SQL Server, etc.
yitam
on 19 Feb 2019
Docker 3.6
PHP Version : 7.2.14
Distribution : Debian 9
SQLServer PHP extension : 5.2.0
SQLServer : 10.50 (2K12 R2)
Apache 2.4
I'm facing the same issue than @hubipe. I already checked what extension is used by the Apache and it's the 5.2.0
It works like a charm on Windows 7 & PHP 5.6.
Nevertheless, if I check first the existence of the rows that I want to delete, it will work... but I do not want to add a query to check the rows to delete :/
younessadmi
on 19 Feb 2019
@younessadmi please also provide details for the following questions (copied from a new issue template):
+## Microsoft ODBC Driver version
+
+## Table schema
+
+## Problem description
+
+## Expected behavior and actual behavior
+
+## Repro code or steps to reproduce
yitam
on 19 Feb 2019
Driver version or file name
$ pecl info sqlsrv
ABOUT PECL.PHP.NET/SQLSRV-5.2.0
===============================
Release Type PECL-style PHP extension (source code)
Name sqlsrv
Channel pecl.php.net
Summary Microsoft Drivers for PHP for SQL Server
(SQLSRV)
Description The Microsoft Drivers for PHP for SQL Server are
PHP extensions that allow for the reading and
writing of SQL Server data from within PHP
scripts. The SQLSRV extension provides a
procedural interface while the PDO_SQLSRV
extension implements PDO for accessing data in
all editions of SQL Server 2008 R2 and later
(including Azure SQL DB). These drivers rely on
the Microsoft ODBC Driver for SQL Server to
handle the low-level communication with SQL
Server.
*This package contains only the SQLSRV driver.*
Maintainers Meet Bhagdev <[email protected]> (lead)
Jay Kint <[email protected]> (lead,
inactive)
Marie Barwin <[email protected]> (lead)
Hadis Kakanejadi Fard <[email protected]>
(lead)
Release Date 2018-03-23 12:55:42
Release Version 5.2.0 (stable)
API Version 5.2.0 (stable)
License The MIT License (MIT)
(https://opensource.org/licenses/mit)
Release Notes [Added]
- Added support for Always Encrypted with basic
CRUD functionalities (see here [here]
(https://github.com/Microsoft/msphpsql/wiki/Features#aebindparam))
- Support for Windows Certificate Store (use
connection keyword ColumnEncryption)
- Support for inserting into and modifying an
encrypted column
- Support for fetching from an encrypted
column
- Added support for PHP 7.2
- Added support for MSODBC 17
- Added support for Ubuntu 17 (requires MSODBC
17)
- Added support for Debian 9 (requires MSODBC
17)
- Added support for SUSE 12
- Added Driver option to set the ODBC driver,
Added "Driver" option, valid values are ODBC
Driver 17 for SQL Server, ODBC Driver 13 for SQL
Server, and ODBC Driver 11 for SQL Server
- The default driver is ODBC Driver 17 for SQL
Server
[Fixed]
- Issue #555 - Hebrew strings truncation
(requires MSODBC 17)
- Adjusted precisions for numeric/decimal inputs
with Always Encrypted
- Support for non-UTF8 locales in Linux and
macOS
- Fixed crash caused by executing an invalid
query in a transaction (Issue #434)
- Added error handling for binding TEXT, NTEXT
or IMAGE as output parameter (Issue #231)
- Decimal types with no decimals are correctly
handled when AE is enabled (PR #544)
- BIGINT as an output param no longer results in
value out of range exception when the returned
value is larger than a maximum integer (PR #567)
[Removed]
- No longer support Ubuntu 15
[Limitations]
- In Linux and macOS, setlocale() only takes
effect if it is invoked before the first
connection. The subsequent locale setting will
not work
- Always Encrypted functionalities are only
supported using MSODBC 17
- Always Encrypted limitations [Always Encrypted
limitations](https://github.com/Microsoft/msphpsql/wiki/Features#aelimitation)
- When using sqlsrv_query with Always Encrypted
feature, SQL type has to be specified for each
input (see here
[here](https://github.com/Microsoft/msphpsql/wiki/Features#aebindparam))
- No support for inout / output params when
using sql_variant type
[Known Issues]
- Connection pooling on Linux doesn't work
properly when using the MSODBC 17 preview
- When pooling is enabled in Linux or macOS
- unixODBC <= 2.3.4 (Linux and macOS) might
not return proper diagnostics information, such
as error messages, warnings and informative
messages
- due to this unixODBC bug, fetch large data
(such as xml, binary) as streams as a
workaround. See the examples here
[here](https://github.com/Microsoft/msphpsql/wiki/Connection-Pooling-on-Linux-and-Mac)
- Calling stored procedure with XML parameter
does not work with Column Encryption enabled
(Issue #674)
- Connection with Connection Resiliency enabled
so not resume properly with Connection Pooling
(Issue #678)
- Cannot connect with both Connection Resiliency
enabled and ColumnEncryption enabled (Issue
#577)
Required Dependencies PHP version 7.0.0
PEAR installer version 1.4.0b1 or newer
OS/Arch matching pattern '//'
package.xml version 2.0
Last Modified 2019-02-11 15:03
Previous Installed - None -
Version
$ pecl info pdo_sqlsrv
ABOUT PECL.PHP.NET/PDO_SQLSRV-5.2.0
===================================
Release Type PECL-style PHP extension (source code)
Name pdo_sqlsrv
Channel pecl.php.net
Summary Microsoft Drivers for PHP for SQL Server
(PDO_SQLSRV)
Description The Microsoft Drivers for PHP for SQL Server are
PHP extensions that allow for the reading and
writing of SQL Server data from within PHP
scripts. The SQLSRV extension provides a
procedural interface while the PDO_SQLSRV
extension implements PDO for accessing data in
all editions of SQL Server 2008 R2 and later
(including Azure SQL DB). These drivers rely on
the Microsoft ODBC Driver for SQL Server to
handle the low-level communication with SQL
Server.
*This package contains only the PDO_SQLSRV
driver.*
Maintainers Meet Bhagdev <[email protected]> (lead)
Jay Kint <[email protected]> (lead,
inactive)
Marie Barwin <[email protected]> (lead)
Jenny Tam <[email protected]> (lead)
Release Date 2018-03-23 13:00:20
Release Version 5.2.0 (stable)
API Version 5.2.0 (stable)
License The MIT License (MIT)
(https://opensource.org/licenses/mit)
Release Notes [Added]
- Added support for Always Encrypted with basic
CRUD functionalities (see here [here]
(https://github.com/Microsoft/msphpsql/wiki/Features#aebindparam))
- Support for Windows Certificate Store (use
connection keyword ColumnEncryption)
- Support for inserting into and modifying an
encrypted column
- Support for fetching from an encrypted
column
- Added support for PHP 7.2
- Added support for MSODBC 17
- Added support for Ubuntu 17 (requires MSODBC
17)
- Added support for Debian 9 (requires MSODBC
17)
- Added support for SUSE 12
- Added Driver option to set the ODBC driver,
Added "Driver" option, valid values are ODBC
Driver 17 for SQL Server, ODBC Driver 13 for SQL
Server, and ODBC Driver 11 for SQL Server
- The default driver is ODBC Driver 17 for SQL
Server
[Changed]
- Implementation of PDO::lastInsertId($name) to
return the last inserted sequence number if the
sequence name is supplied to the function
(lastInsertId)
[Fixed]
- Issue #555 - Hebrew strings truncation
(requires MSODBC 17)
- Adjusted precisions for numeric/decimal inputs
with Always Encrypted
- Support for non-UTF8 locales in Linux and
macOS
- Fixed crash caused by executing an invalid
query in a transaction (Issue #434)
- Added error handling for using
PDO::SQLSRV_ATTR_DIRECT_QUERY or
PDO::ATTR_EMULATE_PREPARES in a Column
Encryption enabled connection
- Added error handling for binding TEXT, NTEXT
or IMAGE as output parameter (Issue #231)
- PDO::quote with string containing ASCII NUL
character (Issue #538)
- Decimal types with no decimals are correctly
handled when AE is enabled (PR #544)
- BIGINT as an output param no longer results in
value out of range exception when the returned
value is larger than a maximum integer (PR #567)
[Removed]
- No longer support Ubuntu 15
- Supplying tablename into
PDO::lastInsertId($name) no longer return the
last inserted row (lastInsertId
[lastInsertId](https://github.com/Microsoft/msphpsql/wiki/Features#aebindparam))
[Limitations]
- In Linux and macOS, setlocale() only takes
effect if it is invoked before the first
connection. The subsequent locale setting will
not work
- Always Encrypted functionalities are only
supported using MSODBC 17
- Always Encrypted limitations [Always Encrypted
limitations](https://github.com/Microsoft/msphpsql/wiki/Features#aelimitation)
- When using sqlsrv_query with Always Encrypted
feature, SQL type has to be specified for each
input (see here
[here](https://github.com/Microsoft/msphpsql/wiki/Features#aebindparam))
- No support for inout / output params when
using sql_variant type
[Known Issues]
- Connection pooling on Linux doesn't work
properly when using the MSODBC 17 preview
- When pooling is enabled in Linux or macOS
- unixODBC <= 2.3.4 (Linux and macOS) might
not return proper diagnostics information, such
as error messages, warnings and informative
messages
- due to this unixODBC bug, fetch large data
(such as xml, binary) as streams as a
workaround. See the examples here
[here](https://github.com/Microsoft/msphpsql/wiki/Connection-Pooling-on-Linux-and-Mac)
- Calling stored procedure with XML parameter
does not work with Column Encryption enabled
(Issue #674)
- Connection with Connection Resiliency enabled
so not resume properly with Connection Pooling
(Issue #678)
- Cannot connect with both Connection Resiliency
enabled and ColumnEncryption enabled (Issue
#577)
Required Dependencies PHP version 7.0.0
PEAR installer version 1.4.0b1 or newer
OS/Arch matching pattern '//'
package.xml version 2.0
Last Modified 2019-02-11 15:03
Previous Installed - None -
Version
SQL Server version
SQL Server 10.50 (2012r2)
Client operating system
Debian 9
PHP version
PHP 7.2.14
Problem description
When executing UPDATE or DELETE through PDO with WHERE condition filtering out 0 rows (thus not updating anything), HY010 [unixODBC][Driver Manager]Function sequence error is raised.
Expected behavior and actual behavior
Assume this table with only one row:
Table test1
+----+------+
| id | name |
+----+------+
| 1 | test |
+----+------+
When running UPDATE test1 SET name = 'updated' WHERE id = 5;
HY010 [unixODBC][Driver Manager]Function sequence error is raised. No error should be raised.
Same error is raised, when trying to delete non existent row.
Repro code
SQL
CREATE TABLE [dbo].[test1](
[id] [int] NOT NULL,
[name] [nvarchar](50) NOT NULL
);
PHP:
$pdo = new \PDO('sqlsrv:server=<server addr>;database=<db name>', '<username>', '<password>);
$result = $pdo->exec("UPDATE test1 SET name = 'updated' WHERE id = 5");
var_dump($result); // bool(false)
var_dump($pdo->errorCode()); // string(5) "HY010"
var_dump($pdo->errorInfo()); /*
array(3) {
聽 | [0]=>
聽 | string(5) "HY010"
聽 | [1]=>
聽 | int(0)
聽 | [2]=>
聽 | string(49) "[unixODBC][Driver Manager]Function sequence error"
聽 | }
*/
@younessadmi I could not seem to reproduce this using v5.2.0 and ODBC 17.2, trying both update and delete ops. This is what I did:
try{
$result = $conn->exec("DELETE FROM test1 WHERE id = 5");
//$result = $conn->exec("UPDATE test1 SET name = 'updated' WHERE id = 5");
echo "result: "; var_dump($result);
echo "errorCode: "; var_dump($conn->errorCode());
echo "errorInfo: "; var_dump($conn->errorInfo());
} catch (PDOException $e) {
var_dump($e);
}
The output I got was
result: int(0)
errorCode: string(5) "00000"
errorInfo: array(3) {
[0]=>
string(5) "00000"
[1]=>
NULL
[2]=>
NULL
}
@younessadmi please double check the driver version by running
$attr = $pdo->getAttribute(constant('PDO::ATTR_CLIENT_VERSION'));
$version = substr($attr['DriverVer'], 0, 2);
print_r($pdo->getAttribute( PDO::ATTR_CLIENT_VERSION ));
Thanks for answering.
Here's the executed code :
$attr = $pdo->getAttribute(constant('PDO::ATTR_CLIENT_VERSION'));
$version = substr($attr['DriverVer'], 0, 2);
var_dump($version);
var_dump($pdo->getAttribute( \PDO::ATTR_CLIENT_VERSION ));
Result :
/app/module/xxx/src/xxx/Controller/xxxController.php:49:string '17' (length=2)
/app/module/xxx/src/xxx/Controller/xxxController.php:50:
array (size=4)
'DriverName' => string 'libmsodbcsql-17.2.so.0.1' (length=24)
'DriverODBCVer' => string '03.52' (length=5)
'DriverVer' => string '17.02.0000' (length=10)
'ExtensionVer' => string '5.2.0' (length=5)
@younessadmi I just realized you were using SQL Server 10.50 2008 R2, not SQL Server 2012. However, I still could not reproduce the issue in SQL Server 2008 R2 (SP2).
What's the output of SELECT @@Version in your SQL Server? Can you try a different SQL Server?
Also, please enable ODBC trace when running your query / queries. Then send the trace for further investigation.
yitam
on 20 Feb 2019
@yitam , thanks for your answer.
I actually find the real issue.
Indeed, when I use the following code, it works like a charm :
$pdo = new \PDO('sqlsrv:Server=xx.xx.xx.xx;Database=xxxxx', 'xxxxx', 'xxxxx');
$attr = $pdo->getAttribute(constant('PDO::ATTR_CLIENT_VERSION'));
$version = substr($attr['DriverVer'], 0, 2);
var_dump($version);
var_dump($pdo->getAttribute( \PDO::ATTR_CLIENT_VERSION ));
$sql = '
DELETE FROM AddLinkLogInsert
WHERE Key = 9999999999
';
$pdo->query($sql);
But my problem comes when I use Zend Framework. It's actually the case when I try the following code :
$sm = $this->getServiceLocator();
/** @var Zend\Db\Adapter\Adapter $adapter */
$adapter = $sm->get('xxxxx');
$sql = '
DELETE FROM AddLinkLogInsert
WHERE ContenuKey = 9999999999
';
$adapter->query($sql);
I'ill try to resolve the issue then come back to you.
younessadmi
on 20 Feb 2019
👍1
@yitam actually, the framework uses the sqlsrv_query() function. The error occurs when the framework does the following code :
$returnValue = sqlsrv_query($this->resource, $sql, array(), $options);
if ($returnValue === false) {
var_dump($sql);
var_dump($options);
var_dump(sqlsrv_errors());
var_dump(sqlsrv_client_info($this->resource));
var_dump($this->resource);
die;
}
And got this :
/app/module/xxxxx/src/xxxxx/Service/Connection.php:59:string '
DELETE FROM AddLinkLogInsert
WHERE ContenuKey = '9999999999'
' (length=94)
/app/module/xxxxx/src/xxxxx/Service/Connection.php:60:
array (size=2)
'Scrollable' => string 'buffered' (length=8)
'QueryTimeout' => int 40
/app/module/xxxxx/src/xxxxx/Service/Connection.php:61:
array (size=1)
0 =>
array (size=6)
0 => string 'HY010' (length=5)
'SQLSTATE' => string 'HY010' (length=5)
1 => int 0
'code' => int 0
2 => string '[unixODBC][Driver Manager]Function sequence error' (length=49)
'message' => string '[unixODBC][Driver Manager]Function sequence error' (length=49)
/app/module/xxxxx/src/xxxxx/Service/Connection.php:62:
array (size=4)
'DriverName' => string 'libmsodbcsql-17.2.so.0.1' (length=24)
'DriverODBCVer' => string '03.52' (length=5)
'DriverVer' => string '17.02.0000' (length=10)
'ExtensionVer' => string '5.3.0' (length=5)
/app/module/xxxxx/src/xxxxx/Service/Connection.php:63:resource(23, SQL Server Connection)
Any idea ? I'd like to avoid using PDO right now, as it's a bit risky regarding our 14M visitor monthly :/
younessadmi
on 21 Feb 2019
Yes @younessadmi I could reproduce this with sqlsrv and unixODBC 2.3.4 in Debian 9, but pdo_sqlsrv has no problem. However, the issue seemingly lies with unixODBC because the problem with sqlsrv is gone with unixODBC 2.3.7.
yitam
on 21 Feb 2019
Thanks for answering. unixODBC 2.3.7 is not available on Debian 9 :/ So I'll try to build the package by myself.
Nevertheless, is the bug solved from 2.3.7 or before ?
younessadmi
on 22 Feb 2019
@younessadmi I did not spend time trying different unixODBC versions. However, fyi unixODBC 2.3.7 is available with ODBC 17.3.1.1.
yitam
on 26 Feb 2019
❤1
So great !!! Thank you, it works like a charm so far. The issue can be closed.
younessadmi
on 27 Feb 2019
👍1
Was this page helpful?
0 / 5 - 0 ratings
Related issues
adankert
路
4Comments
matfiz
路
6Comments
Agares
路
6Comments
Grglrglrg
路
4Comments
benyanke
路
6Comments