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;

I dont see any issue with the Entity. It works fine thru JPA entity but not with the query. Getting the same error for any update query. Looks like a bug in Spring data r2dbc. — user1578872, Mar 01 '21 at 15:25
R2DBC doesn't support JPA annotations, so the entity has to look very different. — Jens Schauder, Mar 01 '21 at 16:43
Can you get the SQL string _after_ the values have been filled in? — Rick James, Mar 01 '21 at 21:02
We can print SQL. But there is no way get the SQL with the values. — user1578872, Mar 01 '21 at 21:04
Update thru JPA object works but not thru the query. But, select thru the query works. — user1578872, Mar 01 '21 at 21:05
Please, be aware of the second information tip in the Spring Data R2DBC documentation when they explain [Query Methods](https://docs.spring.io/spring-data/r2dbc/docs/1.0.x/reference/html/#r2dbc.repositories.queries): `R2DBC repositories internally bind parameters to placeholders with Statement.bind(…) by index.`. Please, try to define the method as `Mono updateAppt(String newNotes, long apptId, int orgId);` - note the change in the order of params - with the provided query and see if it works. MySQL is clearly complaining about the use of `'Test create Appointment...'` as numeric. — jccampanero, Mar 03 '21 at 18:28
Do you mean that with the proposed change in the method signature everything works ok? — jccampanero, Mar 03 '21 at 18:31
That is great @user1578872, I am happy to hear that. Do you mind if I post the answer with this solution? Is it fine for you? — jccampanero, Mar 03 '21 at 18:34
Thank you very much @user1578872. I am very sorry for not being able to help you before. — jccampanero, Mar 03 '21 at 18:40

score 1 · Accepted Answer · answered Mar 03 '21 at 18:39

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.

Spring data r2dbc error - Truncated incorrect DOUBLE value

1 Answers1