MssqlNonTransientException: Procedure or function has too many arguments specified.

[gdinant]

Bug Report

Versions

• Driver: r2dbc-mssql 1.0.2.RELEASE
• Database: azure mssql
• springboot: 3.1.2
• spring-boot-starter-data-r2dbc
• Java: 17

Current Behavior

Bumped r2dbc-mssql from 1.0.0.RELEASE to 1.0.2.RELEASE (via springboot 3.1.2) generates the following exception at runtime:

i.r.m.ExceptionFactory$MssqlNonTransientException: Procedure or function  has too many arguments specified.
    at i.r.m.ExceptionFactory.createException(ExceptionFactory.java:154)
    at i.r.m.DefaultMssqlResult.lambda$doMap$5(DefaultMssqlResult.java:229)
    ... 2 frames excluded
    ... 71 common frames omitted
Wrapped by: o.s.r.UncategorizedR2dbcException: executeMany; SQL [SELECT MESSAGES.id, MESSAGES.external_id, MESSAGES.mgo_id, MESSAGES.creation_date, MESSAGES.modification_date, MESSAGES.sending_date, MESSAGES.status FROM MESSAGES WHERE MESSAGES.status = @P0_status AND (MESSAGES.sending_date < @P1_sendingdate)]; Procedure or function  has too many arguments specified.
    at o.s.r.c.ConnectionFactoryUtils.convertR2dbcException(ConnectionFactoryUtils.java:246)
    at o.s.r.c.DefaultDatabaseClient.lambda$inConnectionMany$8(DefaultDatabaseClient.java:151)
    ... 3 frames excluded

Implementation unchanged where both parameters are never null.

@Repository
public interface MessageDao extends ReactiveCrudRepository<MessageEntity, Long> {

    Flux<MessageEntity> findAllByStatusAndSendingDateBefore(MessageEntity.Status status, LocalDateTime dateTime);

}

Expected behavior/code

Running as 1.0.0.RELEASE (no exceptions)

Possible Solution

Additional context

Looks related to https://github.com/r2dbc/r2dbc-mssql/issues/271

[aironi]

For me, the error is the following (real parameter names renamed):

[2023-11-22T17:54:19.789Z] Exception: ExceptionFactory.MssqlNonTransientException: The parameterized query '(@P0_actualId nvarchar(4000),@P1_anotherId nvarchar(4000))SELECT ' expects the parameter '@P1_anotherId', which was not supplied.
...

[2023-11-22T17:54:19.790Z] Caused by: org.springframework.r2dbc.UncategorizedR2dbcException: executeMany; SQL [SELECT TOP(1) * FROM TABLE WITH(NOLOCK) WHERE ActualId = (:actualId)]; The parameterized query '(@P0_actualId nvarchar(4000),@P1_anotherId nvarchar(4000))SELECT ' expects the parameter '@P1_anotherId', which was not supplied.
[2023-11-22T17:54:19.790Z]  at org.springframework.r2dbc.connection.ConnectionFactoryUtils.convertR2dbcException(ConnectionFactoryUtils.java:245)
[2023-11-22T17:54:19.790Z]  Suppressed: The stacktrace has been enhanced by Reactor, refer to additional information below: 
[2023-11-22T17:54:19.791Z] Assembly trace from producer [reactor.core.publisher.FluxOnErrorResume] :
[2023-11-22T17:54:19.791Z]  reactor.core.publisher.Flux.onErrorMap
[2023-11-22T17:54:19.791Z]  org.springframework.r2dbc.core.DefaultDatabaseClient.inConnectionMany(DefaultDatabaseClient.java:151)
[2023-11-22T17:54:19.791Z] Error has been observed at the following site(s):
[2023-11-22T17:54:19.791Z]  *_____Flux.onErrorMap ⇢ at org.springframework.r2dbc.core.DefaultDatabaseClient.inConnectionMany(DefaultDatabaseClient.java:151)
[2023-11-22T17:54:19.791Z]  |_                    ⇢ at org.springframework.r2dbc.core.DefaultFetchSpec.all(DefaultFetchSpec.java:83)
[2023-11-22T17:54:19.791Z]  |_        Flux.buffer ⇢ at org.springframework.r2dbc.core.DefaultFetchSpec.one(DefaultFetchSpec.java:62)
[2023-11-22T17:54:19.791Z]  |_       Flux.flatMap ⇢ at org.springframework.r2dbc.core.DefaultFetchSpec.one(DefaultFetchSpec.java:63)
[2023-11-22T17:54:19.792Z]  |_          Flux.next ⇢ at org.springframework.r2dbc.core.DefaultFetchSpec.one(DefaultFetchSpec.java:73)
[2023-11-22T17:54:19.792Z]  |_          Mono.name ⇢ at com.mycode.MyRepositoryImpl.findXyzSql(MyRepositoryImpl.java:123)

BUT, the query in question does not even specify the anotherId in SQL.

[sebastian-alfers]

I can confirm this and it must have be introduced between 1.0.0 and 1.0.1: https://github.com/r2dbc/r2dbc-mssql/compare/v1.0.0.RELEASE...v1.0.1.RELEASE

[aironi]

Hi! I attempted to investigate the code diffs and my eyes looked at this diff for some reason:

https://github.com/r2dbc/r2dbc-mssql/compare/v1.0.0.RELEASE...v1.0.1.RELEASE#diff-95cb58e9345adad83a7b76a49fe8dd132f50df8715459168a59721733f81d9d4L42

Commit: https://github.com/r2dbc/r2dbc-mssql/commit/4781ad7dbd3266768e1fa3eb75bbbc4e2d4a331e

The default constructor was removed. I am not sure if this was used in some scenario that I couldn't find?

Also, the io.r2dbc.mssql.IndefinitePreparedStatementCache looks interesting. I could not find good references in code that would explain the issue at hand but my hunch is that it might have something to do with the bug. I could also be completely off the track :laughing:

Hope these help.

[LabziziKader]

Have you any fix for this issue please ?

[adamgongca]

I have located this issue, from 1.0.1.RELEASE, in line 357 of MssqlConnectionConfiguration.java:

private Predicate preferCursoredExecution = sql -> false;

has been changed:

private Predicate preferCursoredExecution = DefaultCursorPreference.INSTANCE;

it results that ParametrizedMssqlStatement run to line 255 of RpcQueryMessageFlow:

emit = handleSpCursorReturnValue(statementCache, codecs, query, binding, state, needsPrepare, returnValue);

then get this error.

I saw in main branch has fixed some similar issues, it added some codes for retry mechanism, unfortunately it only retry errorNumber == 8179 || errorNumber == 586 , but I encountered error number 8144 and 8178,

this bug report should the error number is "8144 | 16 | No | Procedure or function %.*ls has too many arguments specified."

My suggestion is no matter what error number encounter, all retry one more time, it would solve most errors, no worried about infinite loop, because retryReprepare.compareAndSet(true, false) only retry one more time.

so my suggestion is to remove "isPreparedStatementNotFound(((ErrorToken) message).getNumber())" from line 265 or add "|| errorNumber == 8144 || errorNumber == 8178" to line 307 of RpcQueryMessageFlow.java, the current bug will be gone.

[adamgongca]

agree kantharajnr's comment follow #273. this is my current solution as well.

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

[adamgongca]

If you really want to use the 1.0.1.RELEASE or 1.0.2.RELEASE version instead of waiting for a new version to be released.

The following solutions can fix this problem (skip this pit):

1) set up a function which only throw an error to option PRE_RELEASE, force the pool manager handled the error and make connections of the pool works good.

@Configuration @EnableR2dbcRepositories public class DatabaseConfig extends AbstractR2dbcConfiguration {

@Autowired
R2dbcProperties r2dbcProperties;

@Override
@Bean
public ConnectionFactory connectionFactory() {
    ConnectionFactoryOptions connectionFactoryOptions = ConnectionFactoryOptions.parse(r2dbcProperties.getUrl());
    ConnectionFactory pooledConnectionFactory =  ConnectionFactories.get(connectionFactoryOptions.mutate()
        .option(ConnectionFactoryOptions.USER, r2dbcProperties.getUsername())
        .option(ConnectionFactoryOptions.PASSWORD, r2dbcProperties.getPassword())
        .option(PoolingConnectionFactoryProvider.PRE_RELEASE  , conn ->
           Mono.error(new Exception("Force Connection release.")))
        .build()) ;
    return pooledConnectionFactory;
}

}

2) set up FALSE to option PREFER_CURSORED_EXECUTION, make sure the ParametrizedMssqlStatement do not use cursored execution.

@Configuration @EnableR2dbcRepositories public class DatabaseConfig extends AbstractR2dbcConfiguration {

@Autowired
R2dbcProperties r2dbcProperties;

@Override
@Bean
public ConnectionFactory connectionFactory() {
    ConnectionFactoryOptions connectionFactoryOptions = ConnectionFactoryOptions.parse(r2dbcProperties.getUrl());
    ConnectionFactory pooledConnectionFactory =  ConnectionFactories.get(connectionFactoryOptions.mutate()
        .option(ConnectionFactoryOptions.USER, r2dbcProperties.getUsername())
        .option(ConnectionFactoryOptions.PASSWORD, r2dbcProperties.getPassword())
        .option(MssqlConnectionFactoryProvider.PREFER_CURSORED_EXECUTION, false)
        .build()) ;
    return pooledConnectionFactory;
}

}

3) Another stupid but effective way is to use Begin/End to wrap your SQL statements. It looks like:

BEGIN select * from your_table END

[LabziziKader]

Reverting r2dbc-pool to 1.0.0.RELEASE did not address the original issue.

While downgrading r2dbc-mssql to 1.0.0.RELEASE resolves the problem in read mode, this issue surfaced in write mode https://github.com/r2dbc/r2dbc-mssql/issues/264.

"Transaction names must contain only characters and numbers and must not exceed 32 characters."

[adamgongca]

@LabziziKader I also encountered this issue, but it was gone when I downgraded the spring-boot-starter-parent to version 3.1.0. and all of r2dbc-mssql / r2dbc-pool/ r2dbc-spi to 1.0.0.RELEASE.

If your problem persists, I'll try to find out why.

[LabziziKader]

Thanks @adamgongca for proposition. Issue persist even when downgrading spring-boot-starter-parent to version 3.1.0 with r2dbc-mssql / r2dbc-pool/ r2dbc-spi to 1.0.0.RELEASE.

[sebastian-alfers]

@adamgongca Thanks for your response here, I seems to work well when using 1.0.2.RELEASE with .option(MssqlConnectionFactoryProvider.PREFER_CURSORED_EXECUTION, false).

[aironi]

I ran into #79 when using 1.0.2.RELEASE and preferCursoredExecution set to false.

I think 1.0.3.RELEASE should be made once this bug and #79 and #273 are fixed, since all of these prevent 1.0.3.RELEASE to my understanding.