"The TDS protocol stream is not valid" exception when streaming larger content as blob from varbinary(max) column #567

ericpias · 2017-11-29T19:27:55Z

Driver version or jar name

6.2.2.jre8. Also happens in 6.3.5 and 6.2.0.jre8. It does NOT happen with 6.1.0.jre8 so it seems like a regression.

SQL Server version

2014

Client operating system

Mac and Windows. Does not matter.

Java/JVM version

1.8.0_45-b14

Table schema

create table [contentdata] (
[id] varchar(255) not null,
[dtype] varchar(255) not null,
[name] varchar(255),
[type] varchar(255),
[size] numeric(19,0),
[data] varbinary(max),
primary key ([id])
);

The data column is the important one.

Problem description

This seems to be an issue with 6.2 and later driver.

Attempting to read from the binary stream of a blob for a varbinary column with more than 40-60 kb of content throws SQL exception: "The TDS protocol stream is not valid". Trying blob.length() first also throws the same exception.

Expected behavior and actual behavior

Should be able to read from the binary stream. Worked ok in v 6.1.

Repro code

This is the simplest plain vanilla jdbc code that illustrates the issue. The main point is that the blob's stream is no longer valid after closing the resultset when using v6.2 and later of the mssql jdbc driver whereas in v6.1 the stream is still valid and can be read from after resultset/statement close. From my understanding, the "A Blob object is valid for the duration of the transaction in which is was created".

Code:

package com.gauss.forms.web;

import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class JdbcBlobTester {

public static void main(String[] args) {
	// 
	Connection connection = null;  
    Statement statement = null;   
    ResultSet rs = null;  
	try {
		Class cls = Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		
		Driver driver = (Driver)cls.newInstance();
		System.out.println("Driver version: " + driver.getMajorVersion() + "." + driver.getMinorVersion());
		
		String connectionString = "some connection string;";
		
		connection = DriverManager.getConnection(connectionString, "user", "password");  
		connection.setAutoCommit(false);
		
		String selectSql = "SELECT ...";  
        statement = connection.createStatement();  
        rs = statement.executeQuery(selectSql);  

        // Print results from select statement  
        rs.next();  

        //System.out.println(rs.getString(1));
        Blob b = rs.getBlob("data");
        
        InputStream is = b.getBinaryStream();
        System.out.println("First byte: "+is.read());
        
        rs.close();
        rs = null;
        System.out.println("rs closed");
        statement.close();
        statement = null;
        System.out.println("Statement closed");
        System.out.println("Second byte: "+is.read());  // for driver v 6.2 and later, exception here
            
	}
	catch (ClassNotFoundException cnfe) {
		System.out.println("ClassNotFoundException");
	}
	catch (Exception e) {  
        e.printStackTrace();  
    }  
    finally {  
        // Close the connections after the data has been handled.  
        if (rs != null) try { rs.close(); } catch(Exception e) {}  
        if (statement != null) try { statement.close(); } catch(Exception e) {}  
        if (connection != null) try { connection.close(); } catch(Exception e) {}  
    } 	}

}

This is the output when running using driver v6.2.2.jre8:

Driver version: 6.2
First byte: 37
rs closed
Statement closed
Nov 30, 2017 9:07:13 AM com.microsoft.sqlserver.jdbc.TDSReader throwInvalidTDS
SEVERE: ConnectionID:1 ClientConnectionId: fb9d035c-e266-4fd2-b108-8547683257ce got unexpected value in TDS response at offset:3808
java.io.IOException: The TDS protocol stream is not valid.
at com.microsoft.sqlserver.jdbc.PLPInputStream.readBytes(PLPInputStream.java:304)
at com.microsoft.sqlserver.jdbc.PLPInputStream.read(PLPInputStream.java:223)
at com.gauss.forms.web.JdbcBlobTester.main(JdbcBlobTester.java:48)

When running using driver v6.1.0.jre8:

Driver version: 6.1
First byte: 37
rs closed
Statement closed
Second byte: 80

The text was updated successfully, but these errors were encountered:

ericpias · 2017-11-29T19:35:57Z

Here is a snippet of the relevant stack trace against 6.2.2.jre8.

Caused by: java.io.IOException: The TDS protocol stream is not valid.
at com.microsoft.sqlserver.jdbc.PLPInputStream.readBytes(PLPInputStream.java:304)
at com.microsoft.sqlserver.jdbc.PLPInputStream.read(PLPInputStream.java:244)
at com.gauss.util.FileUtil.copy(FileUtil.java:118)

peterbae · 2017-11-30T00:56:08Z

Hi @ericpias, I created a similar table to yours and put a ~10mb blob in the column, but I couldn't reproduce the error (tried with both 6.2.2 jar and the latest driver, with SQL Server 2014). Do you have any idea why the problem is not reproducible from my end?

ericpias · 2017-11-30T01:17:24Z

Thanks for trying it. It looks like I will have to try and narrow it down to the simplest possible scenario. There is obviously somthing specific in my environment that has been left out of my report. I will investigate and get back to you.

…

On Nov 29, 2017, at 7:56 PM, Peter Bae ***@***.***> wrote: Hi @ericpias <https://github.com/ericpias>, I created a similar table to yours and put a ~10mb blob in the column, but I couldn't reproduce the error (tried with both 6.2.2 jar and the latest driver, with SQL Server 2014). Do you have any idea why the problem is not reproducible from my end? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#567 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/APqqNFuGuC4xsuU_4qAKIuHAQiZlkWlIks5s7f0qgaJpZM4Qvf5W>.

ericpias · 2017-11-30T14:34:24Z

I updated the ticket with further details on how to replicate.

…

On Nov 29, 2017, at 8:17 PM, Eric Pias ***@***.***> wrote: Thanks for trying it. It looks like I will have to try and narrow it down to the simplest possible scenario. There is obviously somthing specific in my environment that has been left out of my report. I will investigate and get back to you. __________________________________________________________ Eric Pias | frevvo Inc. | 500 E. Main St, Branford, CT 06405 | http://www.frevvo.com <http://www.frevvo.com/> m: 860.754.8480 | ***@***.*** ***@***.***> | Skype: ericpias > On Nov 29, 2017, at 7:56 PM, Peter Bae ***@***.*** ***@***.***>> wrote: > > Hi @ericpias <https://github.com/ericpias>, I created a similar table to yours and put a ~10mb blob in the column, but I couldn't reproduce the error (tried with both 6.2.2 jar and the latest driver, with SQL Server 2014). Do you have any idea why the problem is not reproducible from my end? > > — > You are receiving this because you were mentioned. > Reply to this email directly, view it on GitHub <#567 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/APqqNFuGuC4xsuU_4qAKIuHAQiZlkWlIks5s7f0qgaJpZM4Qvf5W>. >

peterbae · 2017-12-11T22:17:11Z

@ericpias Thanks, I can reproduce the error now. We will start looking into the issue.

peterbae · 2017-12-13T22:38:03Z

Looks like this issue came from #16. While this change was needed to avoid loading the entire blob object into heap space, it also meant the blob object cannot be streamed after the resultset was closed (this issue is only reproducible if the resultset is closed). I will make a PR once I think of an adequate solution for this problem.

rene-ye · 2018-01-03T00:30:29Z

Hello @ericpias, the use of an inputstream after it has been closed is not supported. However, you are correct that the driver should not be throwing a TDS protocol error, it should instead be throwing a stream is closed error. The JDBC spec also states that the user should have access to the blob even after the statement has been closed, and the driver's current behavior does not reflect that.

The proposed solution is to give the user the option to receive a populated byte[] by calling Blob.getBinaryStream() again after the Result Set or Statement is closed. This change will align the driver with JDBC specs, but will not revert the driver to its pre-6.1.0 behavior where the user can continue from before the stream closed. The user can mark the stream or keep track of their progress themselves if that is their intention. Before changes are finalized, we would like your opinion on this matter.

ericpias · 2018-01-03T02:01:36Z

Hello @rene-ye. Your proposed solution looks good to me. I looked again at my actual code (not my simplified example) and in fact I do not use an open stream across an rs.close() call. However, that illustrated well a change in behavior between the driver versions in my sample code. What my actual code does is just what you describe will be supported by the fix, i.e. getBinaryStream() is called on a blob after the close. So, I think it will work for me at least. I will be glad to test any pre-release version if you would like.

Fills the contents of a blob and makes it availible for use after the RS or statement has been closed. Addresses the TDS issue from microsoft#567.

rene-ye · 2018-01-03T19:50:07Z

Hi @ericpias, the changes are currently under review and may take some time. Meanwhile, you can fork the branch from here to test your code.

rene-ye · 2018-01-31T17:53:11Z

Hi @ericpias, I was wondering if you've had time to test the branch. If there are no problems, we will close this issue in the near future.

cnsgithub · 2018-03-02T07:14:05Z

Still happens in 6.4 final. This is a serious bug and should be handled with higher priority.

Any version above 6.1 causes the problem

cnsgithub · 2018-03-02T07:49:57Z

@rene-ye Tested your changes and can confirm, that it works now. I've tested on SQL Server 2016 and 2017. Good work, thanks!

rene-ye · 2018-03-06T20:31:04Z

PR #595 has been merged and can be expected in 6.5.0. Closing issue.

TarasTielkes · 2018-06-09T06:46:41Z

Will this fix be provided in an upcoming 6.4.x release as well?
We are resolving the last remaining Java 8 compatibility issues in our dependencies, and to "jre7" builds seem to be available for the 6.5.x series of the driver.

microsoft/mssql-jdbc#567

rene-ye added a commit to rene-ye/mssql-jdbc that referenced this issue Jan 3, 2018

Blob fix

4758485

Fills the contents of a blob and makes it availible for use after the RS or statement has been closed. Addresses the TDS issue from microsoft#567.

rene-ye mentioned this issue Jan 3, 2018

Blob fix #595

Merged

cheenamalhotra added Waiting for Response Waiting for a reply from the original poster, or affiliated party PR Under Review labels Jan 9, 2018

Kinchkun mentioned this issue Feb 22, 2018

Hibernate and BlobStreams causing "The TDS protocol stream is not valid" exception #633

Closed

cheenamalhotra removed the Waiting for Response Waiting for a reply from the original poster, or affiliated party label Mar 5, 2018

rene-ye closed this as completed Mar 6, 2018

rene-ye removed the PR Under Review label Mar 6, 2018

mheiss mentioned this issue Apr 11, 2018

Reading data from a clob column not possible AFTER the result set has been closed. ("The TDS protocol stream is not valid") #673

Closed

CheriePun mentioned this issue May 15, 2018

Should InputStream be open after resultset is closed? #697

Closed

JingYenLoh added a commit to csit-opva/opva-ms-pv that referenced this issue Jan 24, 2019

Use newer version of jdbc

533d8f4

microsoft/mssql-jdbc#567

karinazhou mentioned this issue May 20, 2021

[QUESTION] Suppressed IOException/SQLServerException when using ResultSet.getClob(...) #1561

Closed

hareesh-kp mentioned this issue Aug 25, 2024

Reading data from XML column is not possible after closing the ResultSet #2479

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

"The TDS protocol stream is not valid" exception when streaming larger content as blob from varbinary(max) column #567

"The TDS protocol stream is not valid" exception when streaming larger content as blob from varbinary(max) column #567

ericpias commented Nov 29, 2017 •

edited

Loading

ericpias commented Nov 29, 2017

peterbae commented Nov 30, 2017

ericpias commented Nov 30, 2017 via email

ericpias commented Nov 30, 2017 via email

peterbae commented Dec 11, 2017

peterbae commented Dec 13, 2017

rene-ye commented Jan 3, 2018 •

edited

Loading

ericpias commented Jan 3, 2018

rene-ye commented Jan 3, 2018

rene-ye commented Jan 31, 2018

cnsgithub commented Mar 2, 2018

cnsgithub commented Mar 2, 2018

rene-ye commented Mar 6, 2018

TarasTielkes commented Jun 9, 2018

"The TDS protocol stream is not valid" exception when streaming larger content as blob from varbinary(max) column #567

"The TDS protocol stream is not valid" exception when streaming larger content as blob from varbinary(max) column #567

Comments

ericpias commented Nov 29, 2017 • edited Loading

Driver version or jar name

SQL Server version

Client operating system

Java/JVM version

Table schema

Problem description

Expected behavior and actual behavior

Repro code

ericpias commented Nov 29, 2017

peterbae commented Nov 30, 2017

ericpias commented Nov 30, 2017 via email

ericpias commented Nov 30, 2017 via email

peterbae commented Dec 11, 2017

peterbae commented Dec 13, 2017

rene-ye commented Jan 3, 2018 • edited Loading

ericpias commented Jan 3, 2018

rene-ye commented Jan 3, 2018

rene-ye commented Jan 31, 2018

cnsgithub commented Mar 2, 2018

cnsgithub commented Mar 2, 2018

rene-ye commented Mar 6, 2018

TarasTielkes commented Jun 9, 2018

ericpias commented Nov 29, 2017 •

edited

Loading

rene-ye commented Jan 3, 2018 •

edited

Loading