Spring data r2dbc error - Truncated incorrect DOUBLE value

Question

I am using Spring data r2dbc and facing a strange issue.

implementation 'org.springframework.boot:spring-boot-starter-data-r2dbc'
runtimeOnly 'dev.miku:r2dbc-mysql'
runtimeOnly 'mysql:mysql-connector-java'

Getting error with update query.

@Modifying
@Query("UPDATE appt SET notes = :newNotes WHERE appt_id = :apptId AND org_id = :orgId")
Mono<Integer> updateAppt(long apptId, int orgId, String newNotes);

Error :-

Caused by: io.r2dbc.spi.R2dbcNonTransientResourceException: Truncated incorrect DOUBLE value: 'Test create Appointment - Updated'
    at dev.miku.r2dbc.mysql.ExceptionFactory.mappingSqlState(ExceptionFactory.java:115) ~[r2dbc-mysql-0.8.2.RELEASE.jar:0.8.2.RELEASE]
    Suppressed: reactor.core.publisher.FluxOnAssembly$OnAssemblyException: 
Error has been observed at the following site(s):
    |_ checkpoint ⇢ SQL "UPDATE appt SET notes = :newNotes WHERE appt_id = :apptId AND org_id = :orgId" [DatabaseClient]
Stack trace:
        at dev.miku.r2dbc.mysql.ExceptionFactory.mappingSqlState(ExceptionFactory.java:115) ~[r2dbc-mysql-0.8.2.RELEASE.jar:0.8.2.RELEASE]
        at dev.miku.r2dbc.mysql.ExceptionFactory.createException(ExceptionFactory.java:102) ~[r2dbc-mysql-0.8.2.RELEASE.jar:0.8.2.RELEASE]
        at dev.miku.r2dbc.mysql.TextQueryHandler.accept(QueryFlow.java:317) ~[r2dbc-mysql-0.8.2.RELEASE.jar:0.8.2.RELEASE]
        at dev.miku.r2dbc.mysql.TextQueryHandler.accept(QueryFlow.java:292) ~[r2dbc-mysql-0.8.2.RELEASE.jar:0.8.2.RELEASE]
        at reactor.core.publisher.FluxHandleFuseable$HandleFuseableSubscriber.onNext(FluxHandleFuseable.java:169) ~[reactor-core-3.4.2.jar:3.4.2]
        at org.springframework.cloud.sleuth.instrument.reactor.ScopePassingSpanSubscriber.onNext(ScopePassingSpanSubscriber.java:88) ~[spring-cloud-sleuth-instrumentation-3.0.2-SNAPSHOT.jar:3.0.2-SNAPSHOT]
        at reactor.core.publisher.FluxContextWrite$ContextWriteSubscriber.onNext(FluxContextWrite.java:107) ~[reactor-core-3.4.2.jar:3.4.2]
        at dev.miku.r2dbc.mysql.util.DiscardOnCancelSubscriber.onNext(DiscardOnCancelSubscriber.java:70) ~[r2dbc-mysql-0.8.2.RELEASE.jar:0.8.2.RELEASE]
        at org.springframework.cloud.sleuth.instrument.reactor.ScopePassingSpanSubscriber.onNext(ScopePassingSpanSubscriber.java:88) ~[spring-cloud-sleuth-instrumentation-3.0.2-SNAPSHOT.jar:3.0.2-SNAPSHOT]
        at reactor.core.publisher.FluxPeek$PeekSubscriber.onNext(FluxPeek.java:199) ~[reactor-core-3.4.2.jar:3.4.2]
        at org.springframework.cloud.sleuth.instrument.reactor.ScopePassingSpanSubscriber.onNext(ScopePassingSpanSubscriber.java:88) ~[spring-cloud-sleuth-instrumentation-3.0.2-SNAPSHOT.jar:3.0.2-SNAPSHOT]
        at reactor.core.publisher.MonoFlatMapMany$FlatMapManyInner.onNext(MonoFlatMapMany.java:250) ~[reactor-core-3.4.2.jar:3.4.2]
        at org.springframework.cloud.sleuth.instrument.reactor.ScopePassingSpanSubscriber.onNext(ScopePassingSpanSubscriber.java:88) ~[spring-cloud-sleuth-instrumentation-3.0.2-SNAPSHOT.jar:3.0.2-SNAPSHOT]
        at reactor.core.publisher.FluxPeek$PeekSubscriber.onNext(FluxPeek.java:199) ~[reactor-core-3.4.2.jar:3.4.2]
        at org.springframework.cloud.sleuth.instrument.reactor.ScopePassingSpanSubscriber.onNext(ScopePassingSpanSubscriber.java:88) ~[spring-cloud-sleuth-instrumentation-3.0.2-SNAPSHOT.jar:3.0.2-SNAPSHOT]
        at reactor.core.publisher.FluxHandle$HandleSubscriber.onNext(FluxHandle.java:118) ~[reactor-core-3.4.2.jar:3.4.2]
        at org.springframework.cloud.sleuth.instrument.reactor.ScopePassingSpanSubscriber.onNext(ScopePassingSpanSubscriber.java:88) ~[spring-cloud-sleuth-instrumentation-3.0.2-SNAPSHOT.jar:3.0.2-SNAPSHOT]
        at reactor.core.publisher.FluxPeek$PeekSubscriber.onNext(FluxPeek.java:199) ~[reactor-core-3.4.2.jar:3.4.2]

Query thru SQL bench works fine.

UPDATE appt SET notes = 'Test create Appointment - Updated' WHERE appt_id = 1 AND org_id = 2;

Answer 1

Please, be aware of the second information tip in the Spring Data R2DBC documentation when they explain Query Methods:

R2DBC repositories internally bind parameters to placeholders with Statement.bind(…) by index.

Please, try to define your method as:

@Modifying
@Query("UPDATE appt SET notes = :newNotes WHERE appt_id = :apptId AND org_id = :orgId")
Mono<Integer> updateAppt(String newNotes, long apptId, int orgId);

Please, note the change in the order of the parameters.

MySQL is clearly complaining about the use of the value 'Test create Appointment - Updated' as numeric.