Migration from spring boot 2.6.1 to 2.6.2+ on postgresql fails on "select from int_message_group"

[kripsadin]

Spring boot 2.6.2 - 2.6.3

Application fails to start
with postgresql select error:

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT COMPLETE, LAST_RELEASED_SEQUENCE, CREATED_DATE, UPDATED_DATE, "CONDITION" from INT_MESSAGE_GROUP where GROUP_KEY=? and REGION=?]; nested exception is org.postgresql.util.PSQLException: ОШИБКА: столбец "CONDITION" не существует
  Позиция: 70
	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79)
	at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1541)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:667)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:713)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:744)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:757)
	at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:886)
	at org.springframework.integration.jdbc.store.JdbcMessageStore.getGroupMetadata(JdbcMessageStore.java:435)
	at org.springframework.integration.jdbc.store.JdbcMessageStore.getMessageGroup(JdbcMessageStore.java:416)
	at org.springframework.integration.handler.DelayHandler.reschedulePersistedMessages(DelayHandler.java:584)
	at org.springframework.integration.handler.DelayHandler.onApplicationEvent(DelayHandler.java:616)
	at org.springframework.integration.handler.DelayHandler.onApplicationEvent(DelayHandler.java:90)
	at org.springframework.context.event.SimpleApplicationEventMulticaster.doInvokeListener(SimpleApplicationEventMulticaster.java:176)
	at org.springframework.context.event.SimpleApplicationEventMulticaster.invokeListener(SimpleApplicationEventMulticaster.java:169)
	at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:143)
	at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:421)
	at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:378)
	at org.springframework.context.support.AbstractApplicationContext.finishRefresh(AbstractApplicationContext.java:938)
	at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:586)
	at org.springframework.boot.web.reactive.context.ReactiveWebServerApplicationContext.refresh(ReactiveWebServerApplicationContext.java:64)
	at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:732)
	at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:414)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:302)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1303)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1292)
	at ru.bpc.otter.Application.main(Application.java:35)
Caused by: org.postgresql.util.PSQLException: ОШИБКА: столбец "CONDITION" не существует
  Позиция: 70
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2674)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2364)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:354)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:484)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:404)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:162)
	at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
	at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:722)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:651)
	... 23 common frames omitted

[artembilan]

See Migration Guide: https://github.com/spring-projects/spring-integration/wiki/Spring-Integration-5.4-to-5.5-Migration-Guide#messagegroupcondition-option.

It is not just get-n-go upgrade.
You also need to really add that column to you table.
The respective SQL script is here: https://github.com/spring-projects/spring-integration/blob/main/spring-integration-jdbc/src/main/resources/org/springframework/integration/jdbc/schema-postgresql.sql#L18

Also see "What's New" for more possible upgrade obstacles: https://docs.spring.io/spring-integration/docs/current/reference/html/whats-new.html#whats-new

Closed as Invalid since there are enough resources warning about such a change.

[kripsadin]

@artembilan

I saw this migration and successfully migrated to spring-boot 2.6.1 - everything works fine.

But in spring-boot 2.6.2+ select sql was changed - added quotes for mysql and after that application fails to start on postgresql

[artembilan]

The select is like this now:

SELECT COMPLETE, LAST_RELEASED_SEQUENCE, CREATED_DATE, UPDATED_DATE, "CONDITION" from INT_MESSAGE_GROUP where GROUP_KEY=? and REGION=?

A DDL for PostgreSQL is like this:

CREATE TABLE INT_MESSAGE_GROUP  (
	GROUP_KEY CHAR(36) NOT NULL,
	REGION VARCHAR(100) NOT NULL,
    "CONDITION" VARCHAR(255),
	COMPLETE BIGINT,
	LAST_RELEASED_SEQUENCE BIGINT,
	CREATED_DATE TIMESTAMP NOT NULL,
	UPDATED_DATE TIMESTAMP DEFAULT NULL,
	constraint INT_MESSAGE_GROUP_PK primary key (GROUP_KEY, REGION)
);

Are you sure that you have schema updated?
I mean that CONDITION column has to be wrapped into double quotes: https://www.postgresql.org/docs/8.1/sql-keywords-appendix.html

[kripsadin]

Thank you, @artembilan
Yes schema updated with liquibase

preConditions: - dbms: type: postgresql - onFail: MARK_RAN - sqlCheck: expectedResult: 0 sql: select count(*) from information_schema.columns where table_name = 'int_message_group' and column_name = 'condition'; changes: - sql: sql: ALTER TABLE INT_MESSAGE_GROUP ADD CONDITION VARCHAR(255);

And before the double quotes in select query everything worked fine

[artembilan]

I’m sorry: just to be sure that we are on the same page. Is condition column name wrapped into double quotes in the DB? According your screenshots it is not…

[artembilan]

Have just tested against PostgreSQL in Testcontainers the latest Spring Integration with SELECT COMPLETE, LAST_RELEASED_SEQUENCE, CREATED_DATE, UPDATED_DATE, "CONDITION" from INT_MESSAGE_GROUP where GROUP_KEY=? and REGION=?
and DDL as:

CREATE TABLE INT_MESSAGE_GROUP  (
	GROUP_KEY CHAR(36) NOT NULL,
	REGION VARCHAR(100) NOT NULL,
    "CONDITION" VARCHAR(255),
	COMPLETE BIGINT,
	LAST_RELEASED_SEQUENCE BIGINT,
	CREATED_DATE TIMESTAMP NOT NULL,
	UPDATED_DATE TIMESTAMP DEFAULT NULL,
	constraint INT_MESSAGE_GROUP_PK primary key (GROUP_KEY, REGION)
);

So, need to be sure that column name in the table is exactly like "CONDITION" - double quoted.

In the next 6.0 version we are going to rename this column to the GROUP_CONDITION, so no more confusions like this.

[kripsadin]

I’m sorry: just to be sure that we are on the same page. Is condition column name wrapped into double quotes in the DB? According your screenshots it is not…

Try to clarify :

The column was created as CONDITION, without quotes, while migrating to 2.5.5
ALTER TABLE INT_MESSAGE_GROUP ADD CONDITION VARCHAR(255)

And we have two apps looking to one database.
One app - spring-boot 2.5.5 and it works with CONDITION without double quotes.
Another app - spring-boot 2.6.1 and it also works with CONDITION without double quotes.

If we want to upgrade the second app to spring boot 2.6.2+ - we need to rename column to "CONDITION"

ALTER TABLE INT_MESSAGE_GROUP RENAME COLUMN condition TO "CONDITION"

But after this change app on spring-boot 2.5.5 fails to start with
bad SQL grammar [SELECT COMPLETE, LAST_RELEASED_SEQUENCE, CREATED_DATE, UPDATED_DATE, CONDITION from INT_MESSAGE_GROUP where GROUP_KEY=? and REGION=?]; nested exception is org.postgresql.util.PSQLException: ОШИБКА: столбец "condition" не существует

@artembilan , thank you for help) - while I'm trying to explain what issues we have - I found out how to fix them)

The solution seems very simple - we should have both condition and "CONDITION" columns in a table