prepareMethod=prepare causes DB error code 8179

[otbutz]

Driver version

11.1.1.jre17-preview

SQL Server version

Microsoft SQL Server 2019 (RTM-CU16-GDR) (KB5014353) - 15.0.4236.7 (X64)

Client Operating System

Ubuntu 22.04

JAVA/JVM version

17

Stacktrace (EclipseLink)

Internal Exception: java.sql.BatchUpdateException: Could not find prepared statement with handle 0.
Error Code: 8179
	at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333) ~[?:?]
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeJDK12BatchStatement(DatabaseAccessor.java:939) ~[?:?]
	at org.eclipse.persistence.internal.databaseaccess.ParameterizedSQLBatchWritingMechanism.executeBatch(ParameterizedSQLBatchWritingMechanism.java:181) ~[?:?]
	at org.eclipse.persistence.internal.databaseaccess.ParameterizedSQLBatchWritingMechanism.executeBatchedStatements(ParameterizedSQLBatchWritingMechanism.java:136) ~[?:?]
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.writesCompleted(DatabaseAccessor.java:1885) ~[?:?]
	at org.eclipse.persistence.internal.sessions.AbstractSession.writesCompleted(AbstractSession.java:4446) ~[?:?]
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.writesCompleted(UnitOfWorkImpl.java:5774) ~[?:?]
	at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.writeChanges(RepeatableWriteUnitOfWork.java:458) ~[?:?]
	at org.eclipse.persistence.internal.jpa.EntityManagerImpl.flush(EntityManagerImpl.java:975) ~[?:?]
	... 65 more

Full connection URI: jdbc:sqlserver://mssql-db;databaseName=xxxxx;port=1433;encrypt=true;trustServerCertificate=true;prepareMethod=prepare

Support for prepareMethod was discussed in #1196 and introduced in #1719

Removing the option reverts back to sp_prepexc based execution which seems to fix the issue.

Maybe the prepared statement needs to be recreated at some point?

[tkyc]

Maybe the prepared statement needs to be recreated at some point?

Hi @otbutz , that possibly might be the case. I'll need to look over the sp_prepare code. In the mean time, could you provide the table schema and query used that caused the error (in case it might help with debugging)?

[tkyc]

Looking over the code, there is retry logic to recreate the prepared statement handle if the error Error Code: 8179 is thrown. However, it looks like the retry logic is guarded behind a check that requires statement pooling to be enabled (I'll need reevaluate the approach to this or investigate the reason for this). But in the mean time, could you enable statement pooling and see if it is a viable workaround?

Try adding the following to the connection string: disableStatementPooling=false;statementPoolingCacheSize=10;
Again, let me know if it does get around the problem.

If possible, could you provide a repro app that reproduces the issue? Again, or provide the table schema, queries and describe the scenario setup that reproduces the issue as I'm still not able to repro this myself.

Also, is there more to the stack trace? With traces directly from the driver? In case it helps debugging. Thanks

[otbutz]

Try adding the following to the connection string: disableStatementPooling=false;statementPoolingCacheSize=10;
Again, let me know if it does get around the problem.

That seems to do the job 👍

If possible, could you provide a repro app that reproduces the issue? Again, or provide the table schema, queries and describe the scenario setup that reproduces the issue as I'm still not able to repro this myself.

Unfortunately, I cannot provide you with our database schema or a reproducible example. The error is thrown in our application by the execution of a data export job that touches many different tables until finally this exception is thrown.

Maybe dynamically creating statements with different sized IN-clauses is enough to force this kind of statement overflow?

[otbutz]

Maybe i was a bit ealry calling it a workaround.

With enabled assertions:

java.lang.AssertionError: null
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.buildExecParams(SQLServerPreparedStatement.java:896) ~[mssql-jdbc-11.1.1.jre17-preview.jar:?]
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatementBatch(SQLServerPreparedStatement.java:2905) ~[mssql-jdbc-11.1.1.jre17-preview.jar:?]
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtBatchExecCmd.doExecute(SQLServerPreparedStatement.java:2763) ~[mssql-jdbc-11.1.1.jre17-preview.jar:?]
        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7601) ~[mssql-jdbc-11.1.1.jre17-preview.jar:?]
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3885) ~[mssql-jdbc-11.1.1.jre17-preview.jar:?]
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:268) ~[mssql-jdbc-11.1.1.jre17-preview.jar:?]
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:242) ~[mssql-jdbc-11.1.1.jre17-preview.jar:?]
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(SQLServerPreparedStatement.java:2161) ~[mssql-jdbc-11.1.1.jre17-preview.jar:?]
        at org.eclipse.persistence.internal.databaseaccess.DatabasePlatform.executeBatch(DatabasePlatform.java:2470) ~[org.eclipse.persistence.core-2.7.10.1.jar:?]
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeJDK12BatchStatement(DatabaseAccessor.java:928) ~[org.eclipse.persistence.core-2.7.10.1.jar:?]
        at org.eclipse.persistence.internal.databaseaccess.ParameterizedSQLBatchWritingMechanism.executeBatch(ParameterizedSQLBatchWritingMechanism.java:181) ~[org.eclipse.persistence.core-2.7.10.1.jar:?]
        at org.eclipse.persistence.internal.databaseaccess.ParameterizedSQLBatchWritingMechanism.executeBatchedStatements(ParameterizedSQLBatchWritingMechanism.java:136) ~[org.eclipse.persistence.core-2.7.10.1.jar:?]
        at org.eclipse.persistence.internal.databaseaccess.ParameterizedSQLBatchWritingMechanism.appendCall(ParameterizedSQLBatchWritingMechanism.java:84) ~[org.eclipse.persistence.core-2.7.10.1.jar:?]
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:612) ~[org.eclipse.persistence.core-2.7.10.1.jar:?]
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:567) ~[org.eclipse.persistence.core-2.7.10.1.jar:?]
        at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2105) ~[org.eclipse.persistence.core-2.7.10.1.jar:?]
        at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:313) ~[org.eclipse.persistence.core-2.7.10.1.jar:?]
        at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:277) ~[org.eclipse.persistence.core-2.7.10.1.jar:?]
        at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:263) ~[org.eclipse.persistence.core-2.7.10.1.jar:?]
        at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.updateObject(DatasourceCallQueryMechanism.java:850) ~[org.eclipse.persistence.core-2.7.10.1.jar:?]
        at org.eclipse.persistence.internal.queries.StatementQueryMechanism.updateObject(StatementQueryMechanism.java:437) ~[org.eclipse.persistence.core-2.7.10.1.jar:?]
        at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.updateObjectForWriteWithChangeSet(DatabaseQueryMechanism.java:1093) ~[org.eclipse.persistence.core-2.7.10.1.jar:?]
        at org.eclipse.persistence.queries.UpdateObjectQuery.executeCommitWithChangeSet(UpdateObjectQuery.java:86) ~[org.eclipse.persistence.core-2.7.10.1.jar:?]
        at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.executeWriteWithChangeSet(DatabaseQueryMechanism.java:316) ~[org.eclipse.persistence.core-2.7.10.1.jar:?

[tkyc]

Thanks for the info @otbutz, I believe I managed to repro the issue as I'm seeing the same error as you now.

[tkyc]

The fix will be going in our GA release, which will sometime next week.

[otbutz]

Do you have nightly builds? I'd like to test the fix and provide feedback.

[tkyc]

Thanks, that'd be great. I've attached the jre17 version of the driver to this comment.
mssql-jdbc-11.2.0-SNAPSHOT.jre17-preview.zip