Mixed up statements when pool is enabled

[stephanpelikan]

Bug Report

On activating the connection pool

spring:
  r2dbc:
    ....
    pool:
      enabled: true
      max-idle-time: PT9M
      max-life-time: PT1H
      initial-size: 1

parameters of statements are mixed up! I get errors regarding parameter binding which belong to other statements.

Versions

• Driver: current SNAPSHOT build from main (point currently to https://github.com/r2dbc/r2dbc-mssql/commit/7d5b08b765d3a15754ed08f8f1374e018b98b558)
• Database: 15.00.2101
• Java: 17
• OS: Docker image azul/azul-openjdk-alpine:17-latest

Current Behavior

There is one statement having 3 parameters documentTypes, lowerBound and upperBound which gives me the error

...
WHERE document.LastModified BETWEEN :lowerBound AND :upperBound
]; Procedure or function  has too many arguments specified.

And there is another statement having 2 parameters documentPk and projectPk which gives me this error:

...
]; The parameterized query '(@P0_documentTypes nvarchar(4000),@P1_documentTypes nvarchar(400' expects the parameter '@P0_documentTypes', which was not supplied.
        at org.springframework.r2dbc.connection.ConnectionFactoryUtils.convertR2dbcException(ConnectionFactoryUtils.java:246) ~[spring-r2dbc-6.0.8.jar!/:6.0.8]

On disabling the pool everything is fine.

This only happens in our test environment (Azure-Cloud). On my local Linux client everthing was fine also with using the pool.

Steps to reproduce

Unfortunately, don't know. This only happens in our Azure AKS environment.

Expected behavior/code

Same behavior as without activated pool ;-)

[kschlesselmann]

@mp911de Looks very much like my issue in https://github.com/r2dbc/r2dbc-mssql/issues/271#issuecomment-1608931156

I just tried 1.0.2.RELEASE with the same results. After reading this here I disabled the pool and all tests are fine.

Here we use JSON data in columns and it seems that not only parameters are used for the wrong statements but results are sent back to the wrong ReadingConverters (we use Spring Data R2DBC) as well since some errors tell me that they cannot decode JSON which obviously belongs to another entity.

[mp911de]

This issue seems to benefit from a state that builds up over time. Without pool usage, you get to use a fresh connection that doesn't have the state necessary to reproduce the problem. I'm happy to investigate the issue if you can provide a reproducer.

[kschlesselmann]

@mp911de I just tried to create a sample that reproduces this behaviour but I don't know how :-( Sadly I cannot share the original code.

@stephanpelikan Can you provide a sample?

[kucharzyk]

I am facing the same issue. After upgrade my tests are failing randomly saying that one of the parameters was not supplied.

This bug was introduced somewhere after 1.0.0.RELEASE and it occurs even when connection pool is disabled.

[kucharzyk]

@mp911de I've tried to create reproducer but it's really hard to recreate this issue.

org.springframework.r2dbc.UncategorizedR2dbcException: executeMany; SQL [SELECT myschema.entity_data.id, myschema.entity_data.entity_id, myschema.entity_data.entity_data, myschema.entity_data.created_at FROM myschema.entity_data WHERE myschema.entity_data.entity_id = @P0_entityid]; The parameterized query '(@P0_id bigint)SELECT myschema.entities.*, ROW_NUMB' expects the parameter '@P0_id', which was not supplied.
    at org.springframework.r2dbc.connection.ConnectionFactoryUtils.convertR2dbcException(ConnectionFactoryUtils.java:246)

If you read this error message carefully you will notice two different queries.

One of those queries was re-prepared - which may connect it with one of the latest changes

2023-08-11 11:15:08,421 DEBUG [reactor-tcp-nio-6] reactor.util.Loggers$Slf4JLogger: Attempting to re-prepare statement: SELECT myschema.entities.*, ROW_NUMBER() over (ORDER BY (SELECT 1)) AS __relational_row_number__ FROM myschema.entities WHERE myschema.entities.id = @P0_id ORDER BY __relational_row_number__ OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY

[kucharzyk]

It looks like this issue is related: https://github.com/r2dbc/r2dbc-mssql/issues/276

[kucharzyk]

@mp911de Any thought regarding this issue - what recent change could cause mixing up different queries?

[LabziziKader]

I have the problem with version 1.0.2.RELEASE (Spring Boot version 3.1.4) even in local env with MSSQL Server dockerised.

I can give whole stack trace if needed.

[stephanpelikan]

Due to this problem we had to switch back to classic JDBC. So unfortunately, I cannot provide a sample reproducing the effect.

[LabziziKader]

It's frustrating to see no solution when it's the only connection pool supported by R2DBC.

[LabziziKader]

Hello, @mp911de I can provide more informations on how to reproduce if needed. Currently, no version upgrade is possible with MSSQL Server driver.

[kucharzyk]

@LabziziKader It would be great if you could create simple reproducer and publish on GitHub. I’ve tried that but it’s not so easy to reproduce this issue in simple project

[anurag-mishra1]

This issue has become blocker for us . We have spring boot 3.1.2 and r2dbc mssql 1.0.2.RELEASE version. I am not able to execute query for mssql server for any mssql version, geting the same error like ExceptionFactory$MssqlNonTransientException: The parameterized query '(@P0_id bigint)SELECT test_case.*, ROW_NUMBER() over (ORDER BY (' expects the parameter '@P0_id', which was not supplied . Please do let me know when it is going to be fixed . I have tested with latest snapshot version , but still issue exists.

[mp911de]

Please do let me know when it is going to be fixed .

Unless we have a way to reproduce the problem, we cannot find what is broken.

[anurag-mishra1]

all required infromation as spring boot version, driver version is provided. Write simple queries using spring r2dbc and execute one after other.

[kantharajnr]

Downgrading r2dbc-pool to 1.0.0.RELEASE resolved error related MssqlNonTransientException: The parameterized query has been resolved.

[adamgongca]

Same problem with #276 , please see my comment follows #276 .

[jopousa84]

This issue seems to benefit from a state that builds up over time. Without pool usage, you get to use a fresh connection that doesn't have the state necessary to reproduce the problem. I'm happy to investigate the issue if you can provide a reproducer.

In our case, it only happens when we are not using @Query. We get the cross-table query error, and everything stops working.

Example, this one produce the error if we execute several queries in an small amout of time: Mono< Boolean > existsBySourceId(String sourceId);

reactor.core.Exceptions$ErrorCallbackNotImplemented: org.springframework.r2dbc.UncategorizedR2dbcException: executeMany; SQL [SELECT conversation.id, ROW_NUMBER() over (ORDER BY (SELECT 1)) AS relational_row_number FROM conversation WHERE conversation.source_id = @P0_sourceid ORDER BY relational_row_number OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY]; The parameterized query '(@P0_type nvarchar(4000))SELECT count(1) FROM channel WHERE type' expects the parameter '@P0_type', which was not supplied. Caused by: org.springframework.r2dbc.UncategorizedR2dbcException: executeMany; SQL [SELECT conversation.id, ROW_NUMBER() over (ORDER BY (SELECT 1)) AS relational_row_number FROM conversation WHERE conversation.source_id = @P0_sourceid ORDER BY relational_row_number OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY]; The parameterized query '(@P0_type nvarchar(4000))SELECT count(1) FROM channel WHERE type' expects the parameter '@P0_type', which was not supplied.

We tried @adamgongca 1st solution (v1.0.2) and makes the code keep going. Although we get a warning about a closed connection, everything seems to work.

But, if we use: @Query("SELECT count(1) FROM conversation WHERE source_id = :sourceId") Mono< Integer > existsBySourceId(String sourceId);

Everything seems to work Fine.