Problems with Shardingsphere-jdbc 5.4.1 working on springboot 2.3.7

[noroadzh]

version

shardingsphere-jdbc-core 5.4.1

config

The sharing-dev.yaml file is used for configuration.

When the springboot project is started, an error is reported.as below

Is shardingsphere 5.4.1 not supported by springboot 2.3.7 or is there something wrong with the config file?

I'm using dynamic-datasource dynamic-datasource-spring-boot-starter 3.5.2 don't know if it has any effect

[linghengqian]

• In fact, according to the CI at https://github.com/dynamic-datasource/dynamic-datasource-samples/tree/master/third-part-samples/shardingsphere-sample/shardingsphere-jdbc-5.x-core-sample , there is no conflict when combined with org.apache.shardingsphere:shardingsphere-jdbc-core:5.4.1, org.springframework.boot:spring-boot-starter-web:2.7.18 and com.baomidou:dynamic-datasource-spring-boot-starter:4.3.0 .
• Can you create a git with minimal unit tests using h2database?

[noroadzh]

• In fact, according to the CI at https://github.com/dynamic-datasource/dynamic-datasource-samples/tree/master/third-part-samples/shardingsphere-sample/shardingsphere-jdbc-5.x-core-sample , there is no conflict when combined with org.apache.shardingsphere:shardingsphere-jdbc-core:5.4.1, org.springframework.boot:spring-boot-starter-web:2.7.18 and com.baomidou:dynamic-datasource-spring-boot-starter:4.3.0 .
• Can you create a git with minimal unit tests using h2database?

I'm in the process of rebuilding it now, I'll try making a tweak to the example you gave and see if it works

[noroadzh]

• In fact, according to the CI at https://github.com/dynamic-datasource/dynamic-datasource-samples/tree/master/third-part-samples/shardingsphere-sample/shardingsphere-jdbc-5.x-core-sample , there is no conflict when combined with org.apache.shardingsphere:shardingsphere-jdbc-core:5.4.1, org.springframework.boot:spring-boot-starter-web:2.7.18 and com.baomidou:dynamic-datasource-spring-boot-starter:4.3.0 .
• Can you create a git with minimal unit tests using h2database?

Because it is the first time to use H2database, may be some do not understand the use, but the project inside the use of the driver, or the same error!

The error is as follows:
com.zaxxer.hikari.HikariConfig : Failed to load driver class org.apache.shardingsphere.driver.ShardingSphereDriver from HikariConfig class classloader sun.misc.Launcher$AppClassLoader@18b4aac2 2024-03-20 21:39:30.097 WARN 41347 --- [ main] ConfigServletWebServerApplicationContext : Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataMonitorServiceExecute': Injection of resource dependencies failed; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'dataMonitorInfoMapper' defined in file [/Users/noroadzh/test/java/testSharding5/target/classes/com/example/sharding5/dao/mapper/oracle/DataMonitorInfoMapper.class]: Unsatisfied dependency expressed through bean property 'sqlSessionFactory'; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'sqlSessionFactory' defined in class path resource [com/baomidou/mybatisplus/autoconfigure/MybatisPlusAutoConfiguration.class]: Unsatisfied dependency expressed through method 'sqlSessionFactory' parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSource' defined in class path resource [com/baomidou/dynamic/datasource/spring/boot/autoconfigure/DynamicDataSourceAutoConfiguration.class]: Invocation of init method failed; nested exception is java.lang.RuntimeException: Failed to load driver class org.apache.shardingsphere.driver.ShardingSphereDriver in either of HikariConfig class loader or Thread context classloader

project git address:
https://github.com/noroadzh/sharding5.git

[linghengqian]

• I saw the problem with the unit test you wrote at linghengqian-scratches/sharding5@c4366f5 .

1. You should not use snakeyaml 2.2, but should follow shardingsphere 5.4.1 and use snakeyaml 1.33. For using snakeyaml 2.2, this is on the milestone of shardingsphere 5.5.0. Reference Updates ElasticJob to 3.0.4 to block CVEs for SnakeYAML delivery #28805.
2. There is a problem with the shardingsphere configuration file you wrote. There are no properties for dataSources.names and rules[0].sharding-algorithms, and shardingsphere has never declared that sharding-algorithms is equivalent to shardingAlgorithms.
3. You are not using maven wrapper. I have no way of knowing the maven version you are using. I can only initialize the wrapper environment on site.
4. You don’t seem to realize that shardingsphere’s compatibility with h2database is only based on the mysql mode of h2database, and shardingsphere does not test the oracle mode of h2database. I think if you need to test for oracle database, you need to write unit tests using testcontainers-java. Refer to https://github.com/apache/shardingsphere/tree/a09574b0b0fa03ce58a838a376bf5a75d346d122/test/native/src/test/java/org/apache/shardingsphere/test/natived/jdbc/databases .
5. shardingsphere-jdbc-core is not a pom, it is a jar.

• Overall, there is a problem with the SQL you wrote. Either rewrite your SQL to mysql mode compatible SQL, or use testcontainers-java to create a docker instance of oracle database.

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "\000a\000aCREATE TABLE `data_interface_status_202402`\000a(\000a    `id` VARCHAR(64) PRIMARY KEY,\000a    `ADDUP_CYCLE` BIGINT[*](20),\000a    `TOTAL_COUNT` BIGINT(20),\000a    `SUCCESS_QUANTITY` BIGINT(20),\000a    `ACCESS_SERVICE_NAME` VARCHAR(255),\000a    `SERVICE_STATUS` VARCHAR(20) ,\000a    `SERVICE_STATUS_DESCRIPTION` VARCHAR(4000)\000a)"; expected "ARRAY, INVISIBLE, VISIBLE, NOT NULL, NULL, AS, DEFAULT, GENERATED, ON UPDATE, NOT NULL, NULL, AUTO_INCREMENT, DEFAULT ON NULL, NULL_TO_DEFAULT, SEQUENCE, SELECTIVITY, COMMENT, CONSTRAINT, COMMENT, PRIMARY KEY, UNIQUE, NOT NULL, NULL, CHECK, REFERENCES, AUTO_INCREMENT, ,, )"; SQL statement:


CREATE TABLE `data_interface_status_202402`
(
    `id` VARCHAR(64) PRIMARY KEY,
    `ADDUP_CYCLE` BIGINT(20),
    `TOTAL_COUNT` BIGINT(20),
    `SUCCESS_QUANTITY` BIGINT(20),
    `ACCESS_SERVICE_NAME` VARCHAR(255),
    `SERVICE_STATUS` VARCHAR(20) ,
    `SERVICE_STATUS_DESCRIPTION` VARCHAR(4000)
) [42001-214]

[noroadzh]

• I saw the problem with the unit test you wrote at linghengqian-scratches/sharding5@c4366f5 .

1. You should not use snakeyaml 2.2, but should follow shardingsphere 5.4.1 and use snakeyaml 1.33. For using snakeyaml 2.2, this is on the milestone of shardingsphere 5.5.0. Reference Updates ElasticJob to 3.0.4 to block CVEs for SnakeYAML delivery #28805.
2. There is a problem with the shardingsphere configuration file you wrote. There are no properties for dataSources.names and rules[0].sharding-algorithms, and shardingsphere has never declared that sharding-algorithms is equivalent to shardingAlgorithms.
3. You are not using maven wrapper. I have no way of knowing the maven version you are using. I can only initialize the wrapper environment on site.
4. You don’t seem to realize that shardingsphere’s compatibility with h2database is only based on the mysql mode of h2database, and shardingsphere does not test the oracle mode of h2database. I think if you need to test for oracle database, you need to write unit tests using testcontainers-java. Refer to https://github.com/apache/shardingsphere/tree/a09574b0b0fa03ce58a838a376bf5a75d346d122/test/native/src/test/java/org/apache/shardingsphere/test/natived/jdbc/databases .
5. shardingsphere-jdbc-core is not a pom, it is a jar.

• Overall, there is a problem with the SQL you wrote. Either rewrite your SQL to mysql mode compatible SQL, or use testcontainers-java to create a docker instance of oracle database.

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "\000a\000aCREATE TABLE `data_interface_status_202402`\000a(\000a    `id` VARCHAR(64) PRIMARY KEY,\000a    `ADDUP_CYCLE` BIGINT[*](20),\000a    `TOTAL_COUNT` BIGINT(20),\000a    `SUCCESS_QUANTITY` BIGINT(20),\000a    `ACCESS_SERVICE_NAME` VARCHAR(255),\000a    `SERVICE_STATUS` VARCHAR(20) ,\000a    `SERVICE_STATUS_DESCRIPTION` VARCHAR(4000)\000a)"; expected "ARRAY, INVISIBLE, VISIBLE, NOT NULL, NULL, AS, DEFAULT, GENERATED, ON UPDATE, NOT NULL, NULL, AUTO_INCREMENT, DEFAULT ON NULL, NULL_TO_DEFAULT, SEQUENCE, SELECTIVITY, COMMENT, CONSTRAINT, COMMENT, PRIMARY KEY, UNIQUE, NOT NULL, NULL, CHECK, REFERENCES, AUTO_INCREMENT, ,, )"; SQL statement:


CREATE TABLE `data_interface_status_202402`
(
    `id` VARCHAR(64) PRIMARY KEY,
    `ADDUP_CYCLE` BIGINT(20),
    `TOTAL_COUNT` BIGINT(20),
    `SUCCESS_QUANTITY` BIGINT(20),
    `ACCESS_SERVICE_NAME` VARCHAR(255),
    `SERVICE_STATUS` VARCHAR(20) ,
    `SERVICE_STATUS_DESCRIPTION` VARCHAR(4000)
) [42001-214]

Thanks for replying at this point in time, I realized that you've said a lot of things about my test project, I'll make the adjustments you've mentioned and try again when I'm done!

[noroadzh]

Thank you very much, has solved my problem perfectly and I've learned a lot from your replies and code, I'll close the thread for now!