Invalid data provided by SELECT when using connection pooling

[jeff1985]

Driver version

ODBC v17 (libmsodbcsql-17.1.so.0.1) and php_sqlsrv 5.2.0

SQL Server version

Azure SQL Database

Client operating system

Ubuntu 16.04

PHP version

php 7.1.16

Table schema

see https://gist.github.com/jeff1985/a064486a4a10c23bdc10d6d48419a332#file-msodbcsqlbug-db-structure-sql

Problem description

We have identified a problem within php_sqlsrv, which leads to incomplete/wrong data appearing in our php application when using connection pooling with the latest stable drivers. We have seen that problem multiple times occuring while using a php application hosted on Azure Linux Webapps with php running in a docker container.
I'm able to reproduce the problem in a development environment by using an unstable mobile internet connection. In my repro script I've selected one row from the database and logged the result into a file. As you can see, different(sometimes wrong) values are returned by the driver while no changes on the data occured:

2018-04-22 17:37:56.672436 got result(1): id='306' totalLines='8273111' state='finished'
2018-04-22 17:38:11.176791 got result(1): id='306' totalLines='8273111' state=NULL
2018-04-22 17:38:44.856734 Exception: SQLSTATE[HYT00]: [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired

Expected behavior and actual behavior

The expected behaviour is:
either
a) the result of the select query is transfered fully without any errors and in that case is available to php application
b) an error/timeout occurs during data transmission and in that case an exception is raised on php side providing the application with a readable error message to be able to react to the error accordingly

The actual behaviour is:
in case the driver does not transfer the data completely due to network issues, sometimes we see NULL values in the same data item, sometimes it has the correct value.

The result is that the application can not trust the data from the database, which is a clear breach of ACID properties expected from any business level SQL database nowadays.

Repro code

https://gist.github.com/jeff1985/a064486a4a10c23bdc10d6d48419a332#file-test_incomplete_data-php

Logs

All relevant logs are available here:
https://gist.github.com/jeff1985/a064486a4a10c23bdc10d6d48419a332
Note: I removed db credentials from logs and code.

In the logs you will find three application calls captured:

1. successful call => logged correct data as in db
2. problematic call => logged wrong data in field status, no exception occured!
3. timeout exception raised on php side (as example of expected error handling)

[yitam]

hi @jeff1985 I'm afraid this is related to your other issue #756 , as unixODBC was known to have issues with connection pooling in 2.3.1 (default of Ubuntu 16). Please upgrade to 2.3.6 and check if the problems persist. Thank you!

[jeff1985]

hi @yitam
I tried upgrading to unixODBC 2.3.6, but the error still persists, it even got worse. Now I see same behavior on a stable network connection.

[yitam]

Please read my reply in #756

[jeff1985]

Hi @yitam ,

thanks for provided upgraded instruction in #756 . I can confirm that the bug reported here is fixed by compiling unixODBC 2.3.6 and installing from source. Thank you for your support!

Also here I see a big need in updating the official documentation to clearly state the danger of running connection pooling with any unixODBC <2.3.6

The error that I have seen here makes your db non ACID-compliant which is like walking barefoot on glass..

[yitam]

Yes, @jeff1985 , thanks for the update. Your suggestion is valid, and we will update our documentation accordingly.

[yitam]

Hi @jeff1985 , we do plan the update documentation re unixODBC, so please stay tuned. Thanks!

[yitam]

This is to inform you that we are closing this issue, @jeff1985.

The unixODBC source comes with INSTALL instructions which explain how to build it. We realize those instructions are focused towards C developers and assumes a certain level of knowledge of prerequisites on the part of the reader. However, rather than document how to compile unixODBC on all platforms supported by the Microsoft ODBC Driver for SQL Server, the ODBC driver team’s plan is to build and maintain compiled unixODBC packages themselves.