java.lang.IllegalArgumentException:MySQL只支持单个生成值。jooq r2dbc

Question

我遇到了这个问题java.lang.IllegalArgumentException:MySQL只支持单个生成的值。在我所有的insert查询中，不仅仅是这个表。我使用的是jooq 3.15和r2dbc。可能是什么问题

public Mono<ResetCodesRecord> generateCode(String phone,int code) {
        return Mono.from(dsl.insertInto(RESET_CODES,RESET_CODES.PHONE, RESET_CODES.RESET_CODE)
            .values(phone,code)
            .returning()).map(record -> record.into(ResetCodesRecord.class));

    }

堆栈跟踪

java.lang.IllegalArgumentException: MySQL only supports single generated value
    at dev.miku.r2dbc.mysql.MySqlStatementSupport.returnGeneratedValues(MySqlStatementSupport.java:47) ~[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 ⇢ Handler com.bustapi.controllers.UserController#generateResetCode(String) [DispatcherHandler]
    *__checkpoint ⇢ org.springframework.security.web.server.authorization.AuthorizationWebFilter [DefaultWebFilterChain]
    *__checkpoint ⇢ org.springframework.security.web.server.authorization.ExceptionTranslationWebFilter [DefaultWebFilterChain]
    *__checkpoint ⇢ org.springframework.security.web.server.authentication.logout.LogoutWebFilter [DefaultWebFilterChain]
    *__checkpoint ⇢ org.springframework.security.web.server.savedrequest.ServerRequestCacheWebFilter [DefaultWebFilterChain]
    *__checkpoint ⇢ org.springframework.security.web.server.context.SecurityContextServerWebExchangeWebFilter [DefaultWebFilterChain]
    *__checkpoint ⇢ org.springframework.security.web.server.context.ReactorContextWebFilter [DefaultWebFilterChain]
    *__checkpoint ⇢ org.springframework.web.cors.reactive.CorsWebFilter [DefaultWebFilterChain]
    *__checkpoint ⇢ org.springframework.security.web.server.header.HttpHeaderWriterWebFilter [DefaultWebFilterChain]
    *__checkpoint ⇢ org.springframework.security.config.web.server.ServerHttpSecurity$ServerWebExchangeReactorContextWebFilter [DefaultWebFilterChain]
    *__checkpoint ⇢ org.springframework.security.web.server.WebFilterChainProxy [DefaultWebFilterChain]
    *__checkpoint ⇢ HTTP GET "/users/generate-code/25411000000" [ExceptionHandlingWebHandler]
Original Stack Trace:
        at dev.miku.r2dbc.mysql.MySqlStatementSupport.returnGeneratedValues(MySqlStatementSupport.java:47) ~[r2dbc-mysql-0.8.2.RELEASE.jar:0.8.2.RELEASE]
        at dev.miku.r2dbc.mysql.MySqlStatementSupport.returnGeneratedValues(MySqlStatementSupport.java:28) ~[r2dbc-mysql-0.8.2.RELEASE.jar:0.8.2.RELEASE]
        at org.jooq.impl.R2DBC$QueryExecutionSubscriber.onNext0(R2DBC.java:423) ~[jooq-3.15.1.jar:na]
        at org.jooq.impl.R2DBC$ConnectionSubscriber.onNext(R2DBC.java:363) ~[jooq-3.15.1.jar:na]
        at org.jooq.impl.R2DBC$AbstractNonBlockingSubscription.lambda$request0$0(R2DBC.java:540) ~[jooq-3.15.1.jar:na]
        at org.jooq.impl.Internal$1.onNext(Internal.java:433) ~[jooq-3.15.1.jar:na]
        at reactor.core.publisher.StrictSubscriber.onNext(StrictSubscriber.java:89) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.FluxRetry$RetrySubscriber.onNext(FluxRetry.java:87) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.FluxPeekFuseable$PeekFuseableSubscriber.onNext(FluxPeekFuseable.java:210) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.Operators$MonoSubscriber.complete(Operators.java:1816) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.MonoFlatMap$FlatMapInner.onNext(MonoFlatMap.java:249) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onNext(FluxOnErrorResume.java:79) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.complete(MonoIgnoreThen.java:284) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.onNext(MonoIgnoreThen.java:187) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.subscribeNext(MonoIgnoreThen.java:232) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.onComplete(MonoIgnoreThen.java:203) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.MonoIgnoreElements$IgnoreElementsSubscriber.onComplete(MonoIgnoreElements.java:89) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.FluxHandleFuseable$HandleFuseableSubscriber.onNext(FluxHandleFuseable.java:201) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.Operators$MonoSubscriber.complete(Operators.java:1816) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.MonoSupplier.subscribe(MonoSupplier.java:62) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.Mono.subscribe(Mono.java:4400) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.subscribeNext(MonoIgnoreThen.java:255) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.MonoIgnoreThen.subscribe(MonoIgnoreThen.java:51) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.InternalMonoOperator.subscribe(InternalMonoOperator.java:64) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.MonoFlatMap$FlatMapMain.onNext(MonoFlatMap.java:157) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.FluxPeek$PeekSubscriber.onNext(FluxPeek.java:200) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.FluxPeek$PeekSubscriber.onNext(FluxPeek.java:200) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.pool.AbstractPool$Borrower.deliver(AbstractPool.java:469) ~[reactor-pool-0.2.7.jar:0.2.7]
        at reactor.pool.SimpleDequePool.lambda$drainLoop$9(SimpleDequePool.java:393) ~[reactor-pool-0.2.7.jar:0.2.7]
        at reactor.core.publisher.FluxDoOnEach$DoOnEachSubscriber.onNext(FluxDoOnEach.java:154) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.Operators$MonoSubscriber.complete(Operators.java:1816) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.MonoFlatMap$FlatMapInner.onNext(MonoFlatMap.java:249) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.FluxMapFuseable$MapFuseableSubscriber.onNext(FluxMapFuseable.java:127) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.Operators$MonoSubscriber.complete(Operators.java:1816) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.MonoTakeLastOne$TakeLastOneSubscriber.onComplete(MonoTakeLastOne.java:120) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.FluxFlatMap$FlatMapMain.checkTerminated(FluxFlatMap.java:846) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.FluxFlatMap$FlatMapMain.drainLoop(FluxFlatMap.java:608) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.FluxFlatMap$FlatMapMain.drain(FluxFlatMap.java:588) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.FluxFlatMap$FlatMapMain.onComplete(FluxFlatMap.java:465) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.FluxMap$MapSubscriber.onComplete(FluxMap.java:142) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.FluxWindowPredicate$WindowPredicateMain.checkTerminated(FluxWindowPredicate.java:538) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.FluxWindowPredicate$WindowPredicateMain.drainLoop(FluxWindowPredicate.java:486) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.FluxWindowPredicate$WindowPredicateMain.drain(FluxWindowPredicate.java:430) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.FluxWindowPredicate$WindowPredicateMain.onComplete(FluxWindowPredicate.java:310) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.FluxHandleFuseable$HandleFuseableSubscriber.onComplete(FluxHandleFuseable.java:229) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.FluxContextWrite$ContextWriteSubscriber.onComplete(FluxContextWrite.java:126) ~[reactor-core-3.4.12.jar:3.4.12]
        at dev.miku.r2dbc.mysql.util.DiscardOnCancelSubscriber.onComplete(DiscardOnCancelSubscriber.java:104) ~[r2dbc-mysql-0.8.2.RELEASE.jar:0.8.2.RELEASE]
        at reactor.core.publisher.FluxPeekFuseable$PeekConditionalSubscriber.onComplete(FluxPeekFuseable.java:940) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.MonoFlatMapMany$FlatMapManyInner.onComplete(MonoFlatMapMany.java:260) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.FluxPeek$PeekSubscriber.onComplete(FluxPeek.java:260) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.FluxHandle$HandleSubscriber.onComplete(FluxHandle.java:213) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.FluxHandle$HandleSubscriber.onNext(FluxHandle.java:134) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.FluxPeekFuseable$PeekConditionalSubscriber.onNext(FluxPeekFuseable.java:854) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.EmitterProcessor.drain(EmitterProcessor.java:491) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.EmitterProcessor.tryEmitNext(EmitterProcessor.java:299) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.InternalManySink.emitNext(InternalManySink.java:27) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.core.publisher.EmitterProcessor.onNext(EmitterProcessor.java:265) ~[reactor-core-3.4.12.jar:3.4.12]
        at dev.miku.r2dbc.mysql.client.ReactorNettyClient$ResponseSink.next(ReactorNettyClient.java:340) ~[r2dbc-mysql-0.8.2.RELEASE.jar:0.8.2.RELEASE]
        at dev.miku.r2dbc.mysql.client.ReactorNettyClient.lambda$new$0(ReactorNettyClient.java:103) ~[r2dbc-mysql-0.8.2.RELEASE.jar:0.8.2.RELEASE]
        at reactor.core.publisher.FluxPeek$PeekSubscriber.onNext(FluxPeek.java:185) ~[reactor-core-3.4.12.jar:3.4.12]
        at reactor.netty.channel.FluxReceive.drainReceiver(FluxReceive.java:279) ~[reactor-netty-core-1.0.13.jar:1.0.13]
        at reactor.netty.channel.FluxReceive.onInboundNext(FluxReceive.java:388) ~[reactor-netty-core-1.0.13.jar:1.0.13]
        at reactor.netty.channel.ChannelOperations.onInboundNext(ChannelOperations.java:404) ~[reactor-netty-core-1.0.13.jar:1.0.13]
        at reactor.netty.channel.ChannelOperationsHandler.channelRead(ChannelOperationsHandler.java:93) ~[reactor-netty-core-1.0.13.jar:1.0.13]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) ~[netty-transport-4.1.70.Final.jar:4.1.70.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365) ~[netty-transport-4.1.70.Final.jar:4.1.70.Final]
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357) ~[netty-transport-4.1.70.Final.jar:4.1.70.Final]
        at dev.miku.r2dbc.mysql.client.MessageDuplexCodec.handleDecoded(MessageDuplexCodec.java:187) ~[r2dbc-mysql-0.8.2.RELEASE.jar:0.8.2.RELEASE]
        at dev.miku.r2dbc.mysql.client.MessageDuplexCodec.channelRead(MessageDuplexCodec.java:95) ~[r2dbc-mysql-0.8.2.RELEASE.jar:0.8.2.RELEASE]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) ~[netty-transport-4.1.70.Final.jar:4.1.70.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365) ~[netty-transport-4.1.70.Final.jar:4.1.70.Final]
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357) ~[netty-transport-4.1.70.Final.jar:4.1.70.Final]
        at io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:324) ~[netty-codec-4.1.70.Final.jar:4.1.70.Final]
        at io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:296) ~[netty-codec-4.1.70.Final.jar:4.1.70.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) ~[netty-transport-4.1.70.Final.jar:4.1.70.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365) ~[netty-transport-4.1.70.Final.jar:4.1.70.Final]
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357) ~[netty-transport-4.1.70.Final.jar:4.1.70.Final]
        at io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1410) ~[netty-transport-4.1.70.Final.jar:4.1.70.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) ~[netty-transport-4.1.70.Final.jar:4.1.70.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365) ~[netty-transport-4.1.70.Final.jar:4.1.70.Final]
        at io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:919) ~[netty-transport-4.1.70.Final.jar:4.1.70.Final]
        at io.netty.channel.epoll.AbstractEpollStreamChannel$EpollStreamUnsafe.epollInReady(AbstractEpollStreamChannel.java:795) ~[netty-transport-classes-epoll-4.1.70.Final.jar:4.1.70.Final]
        at io.netty.channel.epoll.EpollEventLoop.processReady(EpollEventLoop.java:480) ~[netty-transport-classes-epoll-4.1.70.Final.jar:4.1.70.Final]
        at io.netty.channel.epoll.EpollEventLoop.run(EpollEventLoop.java:378) ~[netty-transport-classes-epoll-4.1.70.Final.jar:4.1.70.Final]
        at io.netty.util.concurrent.SingleThreadEventExecutor$4.run(SingleThreadEventExecutor.java:986) ~[netty-common-4.1.70.Final.jar:4.1.70.Final]
        at io.netty.util.internal.ThreadExecutorMap$2.run(ThreadExecutorMap.java:74) ~[netty-common-4.1.70.Final.jar:4.1.70.Final]
        at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30) ~[netty-common-4.1.70.Final.jar:4.1.70.Final]
        at java.base/java.lang.Thread.run(Thread.java:833) ~[na:na]

生成的sql对我来说似乎没有问题，我甚至尝试在workbench上成功地插入它

System.out.println(dsl.insertInto(RESET_CODES,RESET_CODES.PHONE, RESET_CODES.RESET_CODE) .values(phone,code).returning().getSQL());
insert into `myschema`.`reset_codes` (`phone`, `reset_code`) values (?, ?)

表ddl看起来是这样的，它相当简单

CREATE TABLE `reset_codes` (
  `reset_codes_id` int(11) NOT NULL AUTO_INCREMENT,
  `reset_code` int(11) DEFAULT NULL,
  `phone` varchar(45) DEFAULT NULL,
  `created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`reset_codes_id`),
  KEY `phone_idx` (`phone`,`reset_code`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Answer 1

所以经过一些研究，我发现如果r2dbc中没有返回列，则会返回异常。问题是插入没有发生，或者jooq无法返回生成的键。事实证明是后者，这是一个已知的jooq问题，它在这里记录得很好https://github.com/jOOQ/jOOQ/issues/2943但是有一个解决方法来返回生成的值，这是我首先想要的。下面是重构的代码和它现在的工作。

public Mono<Integer> generateCode(String phone,int code) {
        return Mono.from(dsl.insertInto(RESET_CODES).columns(RESET_CODES.PHONE, RESET_CODES.RESET_CODE)
            .values(phone,code)
            .returningResult(field("reset_codes_id", INTEGER.identity(true)))).map(record -> record.get(RESET_CODES.RESET_CODES_ID));

    }