Exploration of ShardingSphere's base support for R2DBC SPI 1.0.0.RELEASE

[linghengqian]

Feature Request

For English only, other languages will not accept.

Please pay attention on issues you submitted, because we maybe need more details.
If no response anymore and we cannot make decision by current information, we will close it.

Please answer these questions before submitting your issue. Thanks!

Is your feature request related to a problem?

Yes, with #8527 , #10837 , #8073 related.

Describe the feature you would like.

• The Reactive Relational Database Connectivity (R2DBC) project brings reactive programming APIs to relational databases.

• Thanks to https://r2dbc.io/2022/04/25/r2dbc-1.0-goes-ga, I'm currently exploring possible support for R2DBC SPI 1.0.0.RELEASE on the master branch of ShardingSphere.

• I first propose a possible treatment idea.

• We need an R2DBC driver implementation that is functionally compliant with shardingsphere-jdbc-core functionality, the ultimate goal of this step is to provide something like jdbc:shardingsphere:classpath:config.yaml , introduced in https://shardingsphere.apache.org/document/current/en/user-manual/shardingsphere-jdbc/jdbc-driver/ , should be similar in form to the ShardingSphere JDBC driver and share the same configuration file format. That is r2dbc:shardingsphere:classpath:config.yaml.

• In this step, we need to consider how to write a converter, which needs to deal with a large number of JDBC-specific classes like javax.sql.DataSource in the shardingsphere-infra module, and assist JDBC-independent information to fall into io.r2dbc.spi.ConnectionFactory on such classes.

• Such a shardingsphere-r2dbc module needs to shield distributed transactions, allowing startups to not perform everything related to XA transactions. This is because all open source R2DBC drivers currently do not handle XA transactions, refer to https://gitter.im/R2DBC/r2dbc?at=62c7ea03fe909e3ec76de28d . This means that reactive Atomikos are unlikely to be written. This doesn't mean that r2dbc doesn't have transactions, just that it doesn't currently handle distributed transactions well. As of R2DBC SPI 0.9.1.RELEASE, it provides good support for transaction scalability. A reasonable example, Spring R2DBC's ReactiveTransactionManager is upstream for single-source operations. The SAGA pattern implemented by choreography or orchestration is a possible target. This piece may be difficult to rely on Seata, because the corresponding Any plan to support R2DBC incubator-seata#3061 of Seata is in a dead state, and the issue does not provide an example that is convenient for me to debug.

• For responsive distributed transactions, we may be able to start with the flexible transaction of MQ, and its java client should be based on reactor-rabbitmq or reactor-kafka. Because in the reactive library, only Reactor and Smallrye Mutiny have the concept of Context. This concept does not exist in the Reactive Stream 1.0.4 specification. So it is unlikely to contain parts of RxJava. Context is regarded as a good substitute for ThreadLocal in JTA, and is used by Spring Framework and Quarkus respectively. Provides operations for R2DBC transactions.

• We also need to consider the fact that there is no R2DBC driver for MySQL that maintains state. Currently on R2DBC 0.9.1, there is only mariadb-r2dbc driver for connecting to MySQL. Once https://jira.mariadb.org/projects/R2DBC/issues/R2DBC-60?filter=allopenissues is resolved, a production-ready driver capable of connecting to MySQL will exist on R2DBC 1.0.0.RELEASE. An experimental MySQL X DevAPI based R2DBC driver is available at https://github.com/linux-china/r2dbc-mysqlx . If enough people are willing to maintain the MySQL R2DBC driver in a community, it will help us improve MySQL related tests. Related requests include but are not limited to https://forums.mysql.com/read.php?39,704940,704940#msg-704940 .

• shardingsphere-r2dbc related unit tests should focus on postgresql and h2database . Since h2database's R2DBC 1.0.0.RELEASE-based driver only supports H2database 2.x, we need to consider how to solve ShardingSphere incorrectly handles table INFORMATION_SCHEMA for H2Database 2.X #15327 .

• The information in the ShardingSphere Agent will be updated, but my knowledge does not determine the final shape of this piece. An idea related to R2DBC Pool's javaagent is at Idea for an option plugin that provides R2DBC Driver metrics collection skywalking#9229, but I haven't gotten around to it yet. For elastic-apm-java-agent, its processing is deadlocked. Refer to support r2dbc elastic/apm-agent-java#2069 .

• shardingsphere-r2dbc-core should work well with Spring Framework 6.0.0-M5 and Spring Boot 3.0.0-M4, the corresponding updates are at Upgrade client support to R2DBC 1.0 spring-projects/spring-framework#28787.

• For the Async Cache problem, it is not a big problem in theory, because both Caffeine and Cache2k provide Async Cache, which solves a long-standing problem with JSR107. The interface of cache2k is actually also very suitable for publishing new specifications. Jakarta EE's PMC is trying to keep an eye on JCache migrations or updates, but new specs aren't coming this fast. Refer to Handle jakarta namespace? jsr107/jsr107spec#415 and https://www.eclipse.org/lists/jakarta.ee-community/threads.html#02901 .

• Issues like [FEATURE] Add Calcite JDBC executor as new frontend in shardingsphere-proxy to allow federated querying of any datasources via MySQL/PG clients #17020 show how closely calcite is related to jdbc, the Calcite JDBC Executor is not an area I am familiar with, and I am not sure how closely it is related to R2DBC.

• The coupling between shardingsphere-proxy and shardingsphere-jdbc-core is relatively large, so I will not consider it for now. But I still have to point out that the ShardingSphere Proxy part uses vertx-sql-client, and its API is not related to R2DBC. Some related discussion happened at Proposal to expose PgClient functionality through R2DBC API eclipse-vertx/vertx-sql-client#249.

• I listed this issue for discussion because I can't guarantee or know for certain that at some point in time, a prototype PR will arrive on the master branch. I'm just giving some updates to friends who want to follow the R2DBC integration, i.e. I'm working on this issue and I'm trying to verify the achievability of this issue.

• Any possible hints will help me move this forward.👨‍💻

[linghengqian]

CC @mygitdds , @Jiar , @sgc109 .

[linghengqian]

• While referring to some external documentation, I found it difficult to view Calcite's historical documentation. Considering that the master branch of ShardingSphere is using an old version of calcite, and the protobuf-java version that this version depends on has a CVE, I will try to submit a PR to update the calcite version.

• At the same time, updating the Calcite version may help resolve Support Quarkus extension #12594 problem about graalvm encountered, while [CALCITE-4882] Introduce new Lambda-based Metadata framework calcite#2603 got into a deadlock.

• By the way, I was a bit confused by the info from https://groups.google.com/g/r2dbc/c/Lkkp5cUP8CA and https://groups.google.com/g/druid-user/c/qBFXIZUuf1k, Such discussions happen outside of Calcite's mailing list. However I found an early discussion https://www.mail-archive.com/dev@calcite.apache.org/msg16856.html.

• I'm not sure where the key entry point is, but I'm guessing that there should be a direct http client for calcite that I can cut directly to improve. Open the master branch of calcite, I haven't used nearly half of the databases, and I must point out I don't know what I can do with the so-called "R2DBC interface on Avatica's JDBC driver". While calcite Avatica is in another git.

• This is just a secondary tracking point.

[linghengqian]

Related PR: #19737

[linghengqian]

Although the changes to ShardingSphere Proxy do not need to be considered immediately, neither Curator nor Jetcd intersects with the Reactive Stream 1.0.4 Spec. Maybe some other repository issues will need to be re-tracked.

[linghengqian]

For the H2Database 2.x issue, I personally think it will be resolved in the associated #19806.

[linghengqian]

New PR review comments mean that compatibility with H2Database 1.x is likely to be dropped. Reference #15327.

[linghengqian]

Will be resolved at #19878.

[linghengqian]

The above PR involved misoperation and was finally resolved at #19964.

[linghengqian]

There is a PR from six months ago about Junit4 related issues, this thread is at #19845. Part of it affects the writing of unit tests, waiting for the issue to be closed to confirm the final calling class.

[linghengqian]

Considering https://www.jetbrains.com/help/idea/reactor.html , IntelliJ IDEA provides a richer integration for debugging Reactor, which is not reflected in Smallrye Mutiny. From a maintenance perspective, since RxJava does not have a transaction-related Context, so I think only Reactor should be used for reactive libraries. For the reactive API inside Helidon, it is not considered because it is based on JDK 9 Flow.

[linghengqian]

This may be a misunderstanding, I didn't notice https://smallrye.io/smallrye-reactive-messaging/smallrye-reactive-messaging/3.1/index.html before. But since I actually use Reactor the most, I tend to use Reactor's API.

[linghengqian]

apache/incubator-seata#4926 looks great, some of the extra burden surrounding this discussion may be about to be removed.

[linghengqian]

• Based on the early investigation of sharding-proxy performance #8527, I will start using JDK 19 to unify the handling of synchronous and asynchronous calls in the future. This involves early research on Helidon 4.x and Ehcache Jakarta 4.x.

[linghengqian]

• https://central.sonatype.com/artifact/io.r2dbc/r2dbc-h2 , which is used to replace the h2database jdbc driver, has not had a new version for more than a year. From the perspective of 2024, proposals like shardingsphere r2dbc are not as good as proposals like shardingsphere adbc.
• Issues like Transaction rollback does not seem to work when using r2dbc and spring jOOQ/jOOQ#16109 provide insight into the complexity of how R2DBC interacts with transactions. I don’t think R2DBC should be discussed at this point in time. The core reason is that R2DBC does not limit specific reactive API libraries, and draft issues such as Subscriber payload or contextualize request reactive-streams/reactive-streams-jvm#542 have not been closed, resulting in the shardingsphere possibly needing to adapt to Project Reactor, RxJava, and Smallrye Mutiny at the same time. This is too painful.
• I will not be working on the current issue any further, so I am closing it.