linghengqian commented on issue #19655:
URL:
https://github.com/apache/shardingsphere/issues/19655#issuecomment-1202244237
- An embarrassing topic is, `CREATE TABLE repository(id varchar(36) PRIMARY
KEY, key TEXT, value TEXT, parent TEXT);` can only be executed in H2Database
1.x, if the user uses H2Databse 2.x, when using ShardingSphere Standalone mode
will throw an exception and cause the project to fail to start. I think
additional factors should be considered in the processing of this SQL.
```shell
Error starting ApplicationContext. To display the conditions report re-run
your application with 'debug' enabled.
2022-08-02 17:17:08.280 ERROR 46368 --- [ main]
o.s.boot.SpringApplication : Application run failed
org.springframework.beans.factory.BeanCreationException: Error creating bean
with name 'myDataSourceConfiguration': Injection of resource dependencies
failed; nested exception is
org.springframework.beans.factory.BeanCreationException: Error creating bean
with name 'shardingSphereDataSource' defined in class path resource
[org/apache/shardingsphere/spring/boot/ShardingSphereAutoConfiguration.class]:
Bean instantiation via factory method failed; nested exception is
org.springframework.beans.BeanInstantiationException: Failed to instantiate
[javax.sql.DataSource]: Factory method 'shardingSphereDataSource' threw
exception; nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax
error in SQL statement "CREATE TABLE repository(id varchar(36) PRIMARY KEY,
[*]key TEXT, value TEXT, parent TEXT)"; expected "identifier"; SQL statement:
CREATE TABLE repository(id varchar(36) PRIMARY KEY, key TEXT, value TEXT,
parent TEXT) [42001-210]
at
org.springframework.context.annotation.CommonAnnotationBeanPostProcessor.postProcessProperties(CommonAnnotationBeanPostProcessor.java:332)
~[spring-context-5.3.20.jar:5.3.20]
at
org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1431)
~[spring-beans-5.3.20.jar:5.3.20]
at
org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:619)
~[spring-beans-5.3.20.jar:5.3.20]
at
org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:542)
~[spring-beans-5.3.20.jar:5.3.20]
at
org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335)
~[spring-beans-5.3.20.jar:5.3.20]
at
org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234)
~[spring-beans-5.3.20.jar:5.3.20]
at
org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333)
~[spring-beans-5.3.20.jar:5.3.20]
at
org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208)
~[spring-beans-5.3.20.jar:5.3.20]
at
org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:953)
~[spring-beans-5.3.20.jar:5.3.20]
at
org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:918)
~[spring-context-5.3.20.jar:5.3.20]
at
org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:583)
~[spring-context-5.3.20.jar:5.3.20]
at
org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:147)
~[spring-boot-2.7.0.jar:2.7.0]
at
org.springframework.boot.SpringApplication.refresh(SpringApplication.java:734)
~[spring-boot-2.7.0.jar:2.7.0]
at
org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:408)
~[spring-boot-2.7.0.jar:2.7.0]
at
org.springframework.boot.SpringApplication.run(SpringApplication.java:308)
~[spring-boot-2.7.0.jar:2.7.0]
at
org.springframework.boot.SpringApplication.run(SpringApplication.java:1306)
~[spring-boot-2.7.0.jar:2.7.0]
at
org.springframework.boot.SpringApplication.run(SpringApplication.java:1295)
~[spring-boot-2.7.0.jar:2.7.0]
at
com.baomidou.samples.shardingSphere.jdbc.v5.spring.ShardingSphereApplication.main(ShardingSphereApplication.java:27)
~[classes/:na]
Caused by: org.springframework.beans.factory.BeanCreationException: Error
creating bean with name 'shardingSphereDataSource' defined in class path
resource
[org/apache/shardingsphere/spring/boot/ShardingSphereAutoConfiguration.class]:
Bean instantiation via factory method failed; nested exception is
org.springframework.beans.BeanInstantiationException: Failed to instantiate
[javax.sql.DataSource]: Factory method 'shardingSphereDataSource' threw
exception; nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax
error in SQL statement "CREATE TABLE repository(id varchar(36) PRIMARY KEY,
[*]key TEXT, value TEXT, parent TEXT)"; expected "identifier"; SQL statement:
CREATE TABLE repository(id varchar(36) PRIMARY KEY, key TEXT, value TEXT,
parent TEXT) [42001-210]
at
org.springframework.beans.factory.support.ConstructorResolver.instantiate(ConstructorResolver.java:658)
~[spring-beans-5.3.20.jar:5.3.20]
at
org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:638)
~[spring-beans-5.3.20.jar:5.3.20]
at
org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateUsingFactoryMethod(AbstractAutowireCapableBeanFactory.java:1352)
~[spring-beans-5.3.20.jar:5.3.20]
at
org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1195)
~[spring-beans-5.3.20.jar:5.3.20]
at
org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:582)
~[spring-beans-5.3.20.jar:5.3.20]
at
org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:542)
~[spring-beans-5.3.20.jar:5.3.20]
at
org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335)
~[spring-beans-5.3.20.jar:5.3.20]
at
org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234)
~[spring-beans-5.3.20.jar:5.3.20]
at
org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333)
~[spring-beans-5.3.20.jar:5.3.20]
at
org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:213)
~[spring-beans-5.3.20.jar:5.3.20]
at
org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.resolveBeanByName(AbstractAutowireCapableBeanFactory.java:479)
~[spring-beans-5.3.20.jar:5.3.20]
at
org.springframework.context.annotation.CommonAnnotationBeanPostProcessor.autowireResource(CommonAnnotationBeanPostProcessor.java:550)
~[spring-context-5.3.20.jar:5.3.20]
at
org.springframework.context.annotation.CommonAnnotationBeanPostProcessor.getResource(CommonAnnotationBeanPostProcessor.java:520)
~[spring-context-5.3.20.jar:5.3.20]
at
org.springframework.context.annotation.CommonAnnotationBeanPostProcessor$ResourceElement.getResourceToInject(CommonAnnotationBeanPostProcessor.java:673)
~[spring-context-5.3.20.jar:5.3.20]
at
org.springframework.beans.factory.annotation.InjectionMetadata$InjectedElement.inject(InjectionMetadata.java:228)
~[spring-beans-5.3.20.jar:5.3.20]
at
org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:119)
~[spring-beans-5.3.20.jar:5.3.20]
at
org.springframework.context.annotation.CommonAnnotationBeanPostProcessor.postProcessProperties(CommonAnnotationBeanPostProcessor.java:329)
~[spring-context-5.3.20.jar:5.3.20]
... 17 common frames omitted
Caused by: org.springframework.beans.BeanInstantiationException: Failed to
instantiate [javax.sql.DataSource]: Factory method 'shardingSphereDataSource'
threw exception; nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException:
Syntax error in SQL statement "CREATE TABLE repository(id varchar(36) PRIMARY
KEY, [*]key TEXT, value TEXT, parent TEXT)"; expected "identifier"; SQL
statement:
CREATE TABLE repository(id varchar(36) PRIMARY KEY, key TEXT, value TEXT,
parent TEXT) [42001-210]
at
org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:185)
~[spring-beans-5.3.20.jar:5.3.20]
at
org.springframework.beans.factory.support.ConstructorResolver.instantiate(ConstructorResolver.java:653)
~[spring-beans-5.3.20.jar:5.3.20]
... 33 common frames omitted
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL
statement "CREATE TABLE repository(id varchar(36) PRIMARY KEY, [*]key TEXT,
value TEXT, parent TEXT)"; expected "identifier"; SQL statement:
CREATE TABLE repository(id varchar(36) PRIMARY KEY, key TEXT, value TEXT,
parent TEXT) [42001-210]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:521)
~[h2-2.1.210.jar:2.1.210]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:496)
~[h2-2.1.210.jar:2.1.210]
at org.h2.message.DbException.getSyntaxError(DbException.java:265)
~[h2-2.1.210.jar:2.1.210]
at org.h2.command.Parser.readIdentifier(Parser.java:5759)
~[h2-2.1.210.jar:2.1.210]
at org.h2.command.Parser.parseTableColumnDefinition(Parser.java:9355)
~[h2-2.1.210.jar:2.1.210]
at org.h2.command.Parser.parseCreateTable(Parser.java:9298)
~[h2-2.1.210.jar:2.1.210]
at org.h2.command.Parser.parseCreate(Parser.java:6779)
~[h2-2.1.210.jar:2.1.210]
at org.h2.command.Parser.parsePrepared(Parser.java:746)
~[h2-2.1.210.jar:2.1.210]
at org.h2.command.Parser.parse(Parser.java:674)
~[h2-2.1.210.jar:2.1.210]
at org.h2.command.Parser.parse(Parser.java:644)
~[h2-2.1.210.jar:2.1.210]
at org.h2.command.Parser.prepareCommand(Parser.java:551)
~[h2-2.1.210.jar:2.1.210]
at org.h2.engine.SessionLocal.prepareLocal(SessionLocal.java:615)
~[h2-2.1.210.jar:2.1.210]
at org.h2.engine.SessionLocal.prepareCommand(SessionLocal.java:553)
~[h2-2.1.210.jar:2.1.210]
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1116)
~[h2-2.1.210.jar:2.1.210]
at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:237)
~[h2-2.1.210.jar:2.1.210]
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:223)
~[h2-2.1.210.jar:2.1.210]
at
org.apache.shardingsphere.mode.repository.standalone.jdbc.JDBCRepository.initTable(JDBCRepository.java:53)
~[shardingsphere-standalone-mode-repository-jdbc-core-5.1.3-SNAPSHOT.jar:5.1.3-SNAPSHOT]
at
org.apache.shardingsphere.mode.repository.standalone.h2.H2Repository.init(H2Repository.java:47)
~[shardingsphere-standalone-mode-repository-jdbc-h2-5.1.3-SNAPSHOT.jar:5.1.3-SNAPSHOT]
at
org.apache.shardingsphere.spi.type.required.RequiredSPIRegistry.getRegisteredService(RequiredSPIRegistry.java:45)
~[shardingsphere-spi-5.1.3-SNAPSHOT.jar:5.1.3-SNAPSHOT]
at
org.apache.shardingsphere.mode.repository.standalone.StandalonePersistRepositoryFactory.getInstance(StandalonePersistRepositoryFactory.java:44)
~[shardingsphere-standalone-mode-repository-api-5.1.3-SNAPSHOT.jar:5.1.3-SNAPSHOT]
at
org.apache.shardingsphere.mode.manager.standalone.StandaloneContextManagerBuilder.build(StandaloneContextManagerBuilder.java:46)
~[shardingsphere-standalone-mode-core-5.1.3-SNAPSHOT.jar:5.1.3-SNAPSHOT]
at
org.apache.shardingsphere.driver.jdbc.core.datasource.ShardingSphereDataSource.createContextManager(ShardingSphereDataSource.java:76)
~[shardingsphere-jdbc-core-5.1.3-SNAPSHOT.jar:5.1.3-SNAPSHOT]
at
org.apache.shardingsphere.driver.jdbc.core.datasource.ShardingSphereDataSource.<init>(ShardingSphereDataSource.java:64)
~[shardingsphere-jdbc-core-5.1.3-SNAPSHOT.jar:5.1.3-SNAPSHOT]
at
org.apache.shardingsphere.driver.api.ShardingSphereDataSourceFactory.createDataSource(ShardingSphereDataSourceFactory.java:93)
~[shardingsphere-jdbc-core-5.1.3-SNAPSHOT.jar:5.1.3-SNAPSHOT]
at
org.apache.shardingsphere.spring.boot.ShardingSphereAutoConfiguration.shardingSphereDataSource(ShardingSphereAutoConfiguration.java:91)
~[shardingsphere-jdbc-core-spring-boot-starter-5.1.3-SNAPSHOT.jar:5.1.3-SNAPSHOT]
at
org.apache.shardingsphere.spring.boot.ShardingSphereAutoConfiguration$$EnhancerBySpringCGLIB$$9e4994a0.CGLIB$shardingSphereDataSource$0(<generated>)
~[shardingsphere-jdbc-core-spring-boot-starter-5.1.3-SNAPSHOT.jar:5.1.3-SNAPSHOT]
at
org.apache.shardingsphere.spring.boot.ShardingSphereAutoConfiguration$$EnhancerBySpringCGLIB$$9e4994a0$$FastClassBySpringCGLIB$$b7ed0820.invoke(<generated>)
~[shardingsphere-jdbc-core-spring-boot-starter-5.1.3-SNAPSHOT.jar:5.1.3-SNAPSHOT]
at
org.springframework.cglib.proxy.MethodProxy.invokeSuper(MethodProxy.java:244)
~[spring-core-5.3.20.jar:5.3.20]
at
org.springframework.context.annotation.ConfigurationClassEnhancer$BeanMethodInterceptor.intercept(ConfigurationClassEnhancer.java:331)
~[spring-context-5.3.20.jar:5.3.20]
at
org.apache.shardingsphere.spring.boot.ShardingSphereAutoConfiguration$$EnhancerBySpringCGLIB$$9e4994a0.shardingSphereDataSource(<generated>)
~[shardingsphere-jdbc-core-spring-boot-starter-5.1.3-SNAPSHOT.jar:5.1.3-SNAPSHOT]
at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
~[na:na]
at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
~[na:na]
at
java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
at
org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:154)
~[spring-beans-5.3.20.jar:5.3.20]
... 34 common frames omitted
```
- This at least affects
https://github.com/apache/shardingsphere/issues/15327 .
- I think the SQL change proposed by @huangxinjian at
https://github.com/apache/shardingsphere/issues/19766#issue-1325180718 is
acceptable.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
