When upgrading to Spring Cloud version 2021.0.5 from 2021.0.4 Cloud Config Server with JDBC backend (Oracle) stops working. The following error message is thrown:
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT "KEY", "VALUE" from PROPERTIES where APPLICATION=? and PROFILE is null and LABEL=?]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00904: "KEY": invalid identifier
The only change made is changing the version number in the pom.xml <spring-cloud.version>2021.0.5</spring-cloud.version>.
The application.yaml looks like this:
spring:
application:
name: configserver
profiles:
active: jdbc
datasource:
url: jdbc:oracle:thin:@connString
username: user
password: password
driver-class-name: oracle.jdbc.OracleDriver
type: oracle.ucp.jdbc.PoolDataSource
oracleucp:
connection-factory-class-name: oracle.jdbc.pool.OracleDataSource
sql-for-validate-connection: select * from dual
connection-pool-name: cserverConnectionPool
initial-pool-size: 15
min-pool-size: 10
max-pool-size: 30
cloud:
config:
server:
jdbc:
sql: SELECT PROP_KEY, VALUE from PROPERTIES where APPLICATION=? and PROFILE=? and LABEL=?
order: 1
# management:
# endpoints:
# web:
# exposure:
# include:
# - refresh
server:
port: 8080
The DDL for the properties table looks like this:
CREATE TABLE CONFIGSERVER.PROPERTIES (
ID NUMBER GENERATED ALWAYS AS IDENTITY ( START WITH 1 CACHE 20 ) ,
APPLICATION VARCHAR2 (4000) ,
PROFILE VARCHAR2 (4000) ,
LABEL VARCHAR2 (4000) ,
PROP_KEY VARCHAR2 (4000) NOT NULL,
VALUE CLOB NOT NULL,
CREATED_ON TIMESTAMP DEFAULT SYSDATE NOT NULL,
CREATED_BY VARCHAR2 (100) DEFAULT COALESCE(
REGEXP_SUBSTR(SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER'),'^[^:]*'),
SYS_CONTEXT('USERENV','SESSION_USER')) NOT NULL,
UPDATED_ON TIMESTAMP ,
UPDATED_BY VARCHAR2 (100)
) LOGGING;
ALTER TABLE CONFIGSERVER.PROPERTIES
ADD CONSTRAINT PROPERTIES_PK PRIMARY KEY ( ID )
USING INDEX LOGGING ;
The pom.xml file looks like this:
```<?xml version="1.0" encoding="UTF-8"?>
<dependency>
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ojdbc8</artifactId>
<version>${oracle.jdbc.version}</version>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ucp</artifactId>
<version>${oracle.jdbc.version}</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.security</groupId>
<artifactId>spring-security-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<dependencyManagement>
<dependencies>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-dependencies</artifactId>
<version>${spring-cloud.version}</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
**Comment From: victor-0807**
Align Jdbc Repository's behavior with Git Repository (https://github.com/spring-cloud/spring-cloud-config/pull/2171)
https://github.com/spring-cloud/spring-cloud-config/commit/7627b230541a3426345c500efa770545cc81c7ab
It looks like this submission caused it
**Comment From: ryanjbaxter**
@tianwen01 @andytael could either of you provide a complete sample that uses a basic H2 database to reproduce the error?
**Comment From: andytael**
@ryanjbaxter Here is an example using an Oracle DB that breaks when changing the Spring Cloud Version to `2021.0.5`. I don't know how to use H2 and it makes no sense creating a test case with other components? CC @tianwen01
https://github.com/andytael/ConfigServer
**Comment From: ryanjbaxter**
I don't have access to an oracle DB. Can we use a mysql db? Why wouldn't you be able to reproduce this with an in memory h2 database?
**Comment From: andytael**
I don't know how to use a H2 database and reproducing with a different DB makes no sense as the problem occurs with an Oracle DB unless I'm missing something?
There is a free version of the Oracle DB that you can install? https://www.oracle.com/database/technologies/appdev/xe.html
**Comment From: sravaniv-3cortex**
I got the same issue when upgrading from `2021.0.1` to `2021.0.5`. From reading the code, the config server uses two queries for querying - `spring.cloud.config.server.jdbc.sql` - with a profile available and `spring.cloud.config.server.jdbc.sql-without-profile` - without an available profile. In our case, we have only configured the former as we use a different table name and configured the query for the latter with the `profile is NULL` condition in the query to fix the issue.
Classes are `JdbcEnvironmentProperties` and `JdbcEnvironmentRepository`
**Comment From: 2019-05-10**
Issue is caused by putting the column names in quotes:
private static final String DEFAULT_SQL = "SELECT \"KEY\", \"VALUE\" from PROPERTIES" + " where APPLICATION=? and PROFILE=? and LABEL=?"; private static final String DEFAULT_SQL_WITHOUT_PROFILE = "SELECT \"KEY\", \"VALUE\" from PROPERTIES" + " where APPLICATION=? and PROFILE is null and LABEL=?";
Using a DB system distinguishing between upper case and lower case, and using lower case column names ("key", "value"), you get exactly that error.
Just change the query back to unquoted column names:
private static final String DEFAULT_SQL = "SELECT KEY, VALUE from PROPERTIES" + " where APPLICATION=? and PROFILE=? and LABEL=?"; private static final String DEFAULT_SQL_WITHOUT_PROFILE = "SELECT KEY, VALUE from PROPERTIES" + " where APPLICATION=? and PROFILE is null and LABEL=?";
**Comment From: 2019-05-10**
For the time being work around it by setting
spring.cloud.config.server.jdbc.sql-without-profile= SELECT key, value FROM properties WHERE application=? AND profile IS NULL AND label=?
in your `application.properties`
**Comment From: 2019-05-10**
Addendum: That new `DEFAULT_SQL_WITHOUT_PROFILE` breaks the previous behaviour regarding default values -- have a `profile` value called `default` to provide values not set for named profiles.
So, I changed that query to
spring.cloud.config.server.jdbc.sql-without-profile= SELECT key, value FROM properties WHERE application=? AND profile='default' AND label=? ```
Comment From: ryanjbaxter
Can you try either 2022.0.0 or 2021.0.6-SNAPSHOT?