Hi Mark @mp911de,
Is there any way to customize the column selection in the named queries?
@Query(
"select a, b
from author a, book b
where b.author_id = a.id and a.id in (:authorIds)"
)
Flux<DummyProjection> findByAuthorIdIn(List<Integer> authorIds);
Even though Projection classes help if I know the column names during the design time, lets take GraphQL for example where the client specify the fields at run time.
So it would be nice, if we could support as shown here.
@Query(
"select a, b
from author a, book b
where b.author_id = a.id and a.id in (:authorIds)"
)
Flux<DummyProjection> findByAuthorIdIn(List<Integer> authorIds, Collection<String> columns);
Hi All,
Trying to insert a string in a column which is enum inside postrgresql table. .bind("address_type", Parameter.fromOrEmpty(address.getHomeAddress().toString(), String.class))
Getting io.r2dbc.postgresql.ExceptionFactory$PostgresqlBadGrammarException: column "address_type" is of type addresstype but expression is of type character varying
HomeAddress is a enum here {HOME, OFFICE, OTHERS}
Hey! I am trying to save an entity in a postgresdb with spring data that has an OffsetDateTime timestamp. It works fine in my dev environment but when I run it inside a docker container I get this exception:
Caused by: java.lang.ClassCastException: class java.time.OffsetDateTime cannot be cast to class java.time.LocalTime (java.time.OffsetDateTime and java.time.LocalTime are in module java.base of loader 'bootstrap')
at io.r2dbc.postgresql.codec.BuiltinCodecSupport.encodeToText(BuiltinCodecSupport.java:83)
Suppressed: reactor.core.publisher.FluxOnAssembly$OnAssemblyException:
Assembly trace from producer [reactor.core.publisher.MonoSupplier] :
reactor.core.publisher.Mono.fromSupplier
io.r2dbc.postgresql.codec.AbstractCodec.create(AbstractCodec.java:150)
Error has been observed at the following site(s):
I am not sure why as r2dbc postgres driver should have support for this datatype. Anyone have any suggestion to solve this issue?
INSERT INTO TABLE (enum_column_name) VALUES (CAST(:value as ENUM_type));
:)
.doOnSuccess(
o -> {
// this method
this.logEventToDatabase(Event.builder()
.message("Updated contact information.")
.build())
.subscribe();
}
);
private Mono<Event> logEventToDatabase(final Event event) {
return this.eventRepository.save(event).publishOn(Schedulers.boundedElastic());
}
Hi everyone. I have recently upgraded r2dbc-postgresql to 0.9.1.RELEASE from 0.8.x. I have started getting Bound parameter count does not match parameters in SQL statement
which was not the case with the previous version. below is how I am passing the parameters. Requesting team to help on this
@Transactional
public Flux<Integer> unblockInventory(InventoryRequest request, boolean orderFulfilled) {
return this.databaseClient.inConnectionMany(connection -> {
var statement = connection.createStatement(UNBLOCK_INVENTORY_QUERY);
for (var item : request.getOrderItems()) {
statement
.bind(0, request.getSource() == UpdateSource.AMAZON ? item.getQuantity() : 0)
.bind(1, request.getSource() == UpdateSource.APOLLO247 ? item.getQuantity() : 0)
.bind(2, orderFulfilled ? 1 : 0)
.bind(3, Utility.getKey(item.getSku(), request.getStoreId()))
.bind(4, request.getAxdcCode())
.add();
}
return Flux
.from(statement.execute())
.flatMap(Result::getRowsUpdated)
.map(e -> {
if (e == 0) {
throw new InventoryNotUnblockedException(request.toString());
}
return e;
})
.doOnError(ex -> LOGGER.error(() -> MessageUtils.errorMessage(Event.UNBLOCK_INVENTORY_FAILED,
ex.getMessage(), ex, false)));
});
}
below is the query
private static final String UNBLOCK_INVENTORY_QUERY = """
UPDATE item_inventory AS iv
SET
amazon_reserved = CASE
WHEN (iv.amazon_reserved - $1) < 0 THEN 0 ELSE iv.amazon_reserved - $1
END,
apollo_reserved = CASE
WHEN (iv.apollo_reserved - $2) < 0 THEN 0 ELSE iv.apollo_reserved - $2
END,
quantity = CASE
WHEN $3 = 1 THEN iv.quantity - $1 - $2 ELSE iv.quantity
END,
version = iv.version + 1,
updated_at = NOW()
WHERE id = $4 AND iv.axdc_code = $5
""";
This is after I have updated to
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>r2dbc-postgresql</artifactId>
<version>0.9.1.RELEASE</version>
</dependency>
Database calls are getting stuck. not sure whether they are making to DB host or not, but seeing below log statement when i enable TRACE logs on io.r2dbc package after that nothing
491156 --- [or-http-epoll-5] io.r2dbc.mssql.QUERY [ 250] : Executing query: select column_1, column_2 from table where column_3 = 123
Using Springboot 2.7.2 with following
r2dbc-mssql:0.8.8.RELEASE
r2dbc-pool:0.8.8.RELEASE
r2dbc-spi:0.8.6.RELEASE
''return databaseClient.sql("select column_1, column_2 from table where column_3 = :value)
.bind("value", "123")
.fetch()
.first().flatmap(res -> return Mono.just(res));''
did any one face/saw similar issue? what could be the problem
@inpeace_gitlab This is solve there is a conflict between connect_timeout and idleTimeOut. idleTimeOut <= connect_timeout.
org.springframework.dao.DataAccessResourceFailureException: Failed to obtain R2DBC Connection; nested exception is io.r2dbc.spi.R2dbcNonTransientResourceException: [1129] Host '<myip>' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts')
minIdle
behavior on the r2dbc-pool
. I have set this option on my project and my expectation was it will behave same like HikariCP where the idle size will be maintained at the configured size no matter how long the app run. But it is not the case here. First try, the connection count dropped to zero after 30 minutes. We found that it's because of maxIdleTime
was default to 30 mins. We set it to -1 to remove eviction behavior. It was looking good at the surface because the connection count stayed at the configured size. But when we tried to access the DB from our app, it seems the connection is hanging: it stays at the configured size, but seems to lose the connection to the db because the process got timed out. It is known issue or my expectation was wrong about the minIdle
behavior?
Hi All. Getting below error when trying to use reactor-test
13:36:18.316 [Test worker] DEBUG reactor.core.publisher.Operators - Duplicate Subscription has been detected
java.lang.IllegalStateException: Spec. Rule 2.12 - Subscriber.onSubscribe MUST NOT be called more than once (based on object equality)
StepVerifier.create(promotionMono.log())
.expectSubscription()
.expectNextMatches(validatePromotion(promotion))
.verifyComplete(); --- error comes in this line
I am using smae thread pool in different classes as @Qualifier
Sorry, I have a problem, please help.
I have a problem in below,
[1;31m[ERROR][m[36m[2022-09-07T19:20:19,078][m[34m[reactor-tcp-epoll-2][m[35m[][m[BaseExceptionHandler] causeMsg: Request queue is full
AWS RDS MySQL 8
Spring Boot: 2.6.10
POM.xml
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-r2dbc</artifactId>
</dependency>
<dependency>
<groupId>dev.miku</groupId>
<artifactId>r2dbc-mysql</artifactId>
<version>0.8.2.RELEASE</version>
</dependency>
<dependency>
<groupId>io.r2dbc</groupId>
<artifactId>r2dbc-spi</artifactId>
<version>1.0.0.RELEASE</version>
</dependency>
<dependency>
<groupId>io.netty</groupId>
<artifactId>netty-all</artifactId>
<version>4.1.79.Final</version>
</dependency>
acquire-retry
param to r2dbc pool config from my yaml but i was not able to. I tried adding programatically referring the documentation and all looks good but my initial size doesn't reflect on my postgres query to check number of connections.
public ConnectionFactory connectionFactory() {
ConnectionFactory connectionFactory = ConnectionFactories.get(ConnectionFactoryOptions.builder()
.option(DRIVER, "pool")
.option(PROTOCOL, "postgresql")
.option(HOST, host)
.option(PORT, port)
.option(USER, userName)
.option(PASSWORD, password)
.option(DATABASE, dbName)
.build());
ConnectionPoolConfiguration configuration = ConnectionPoolConfiguration.builder(connectionFactory)
.initialSize(initialSize)
.maxSize(maxSize)
.minIdle(minIdle)
.metricsRecorder(new R2DBCPoolMetrics())
.maxValidationTime(Duration.ofMillis(maxValidationTime))
.maxAcquireTime(Duration.ofMillis(maxAcquireTime))
.acquireRetry(acquireRetry)
.build();
ConnectionPool connectionPool= new ConnectionPool(configuration);
return connectionPool;
}
This looks good but if i check num of connections in postgres it doesn't reflect initialSize i pass here
Hi, did someone stumble upon this error in r2dbc?
executeMany; SQL [SELECT <skipped...>]; Connection unexpectedly closed; nested exception is io.r2dbc.postgresql.client.ReactorNettyClient$PostgresConnectionClosedException: Connection unexpectedly closed
It happens several times a day. After re-try query returns valid dat. I use pooled connection created as
@Bean
override fun connectionFactory(): ConnectionFactory {
val factory = PostgresqlConnectionFactory(
PostgresqlConnectionConfiguration.builder()
.host(host)
.port(port)
.database(database)
.username(username)
.password(password)
.sslMode(SSLMode.VERIFY_FULL)
.codecRegistrar(
EnumCodec.builder()
.withEnum("allowed_status", Status::class.java)
.build()
)
.build()
)
return connectionPool(factory)
}
fun connectionPool(
connectionFactory: ConnectionFactory
): ConnectionPool {
val builder = ConnectionPoolConfiguration.builder(connectionFactory)
builder.maxSize(poolMaxSize)
builder.initialSize(poolInitialSize)
builder.maxLifeTime(Duration.ofMillis(-1))
return ConnectionPool(builder.build())
}
I'm a bit suspicious that this behavior can be caused by some sort of ttl timeout. Can't prove it though.
@Transactional
i am using .as(TransactionalOperator::transactional)
.