Running APM 10.7 with old DB (PostgreSQL 9.4.x instead of 9.6.x).

Products

• CA Application Performance Management (APM / Wily / Introscope)

Issue/Introduction

How to get APM 10.7.x to work with PostgreSQL 9.4.

Launching the EM causes the following error to show up:

3/26/21 02:20:00.192 PM CET [INFO] [Thread-17] [Manager.ATC.DAO] Inserted 1 Initial records.
3/26/21 02:20:00.240 PM CET [ERROR] [Thread-17] [Manager.AppMap]
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [  ALTER TABLE appmap_states_20210319 DROP CONSTRAINT IF EXISTS  appmap_states_20210319_fk CASCADE;
  ALTER TABLE appmap_states_20210319 DROP CONSTRAINT IF EXISTS  appmap_states_20210319_fk2 CASCADE;
  ALTER TABLE appmap_states_20210319 DROP CONSTRAINT IF EXISTS  appmap_states_20210319_fk3 CASCADE;
  CREATE INDEX IF NOT EXISTS appmap_states_202103193_idx ON appmap_states_20210319(vertex_id, fork, end_time, start_time);
  CREATE INDEX IF NOT EXISTS appmap_states_202103194_idx ON appmap_states_20210319(alert_id, fork, end_time, start_time);]; nested exception is org.postgresql.util.PSQLException: ERROR: syntax error at or near "NOT"
  Position: 20
        at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:419)
        at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:544)
        at com.wily.introscope.appmap.em.model.dao.impl.gmt.GMTJdbcTemplate.update(GMTJdbcTemplate.java:91)
        at com.wily.introscope.appmap.em.model.dao.impl.CollectorOperationsDaoImpl.removeStatusFKs(CollectorOperationsDaoImpl.java:1597)
        at com.wily.introscope.appmap.em.model.dao.impl.CollectorOperationsDaoImpl.postConstruct(CollectorOperationsDaoImpl.java:160)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
        at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
        at com.sun.proxy.$Proxy140.postConstruct(Unknown Source)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
        at com.sun.proxy.$Proxy141.postConstruct(Unknown Source)
        at com.wily.introscope.appmap.config.AppMapPostConstructExecutor.initializeAll(AppMapPostConstructExecutor.java:196)
        at com.wily.introscope.appmap.config.AppMapPostConstructExecutor.access$1(AppMapPostConstructExecutor.java:183)
        at com.wily.introscope.appmap.config.AppMapPostConstructExecutor$1.run(AppMapPostConstructExecutor.java:308)
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "NOT"
  Position: 20
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:331)
        at com.zaxxer.hikari.pool.ProxyStatement.executeUpdate(ProxyStatement.java:123)
        at com.zaxxer.hikari.pool.HikariProxyStatement.executeUpdate(HikariProxyStatement.java)
        at org.springframework.jdbc.core.JdbcTemplate$1UpdateStatementCallback.doInStatement(JdbcTemplate.java:532)
        at org.springframework.jdbc.core.JdbcTemplate$1UpdateStatementCallback.doInStatement(JdbcTemplate.java:529)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:408)
        ... 27 more

3/26/21 02:20:00.241 PM CET [INFO] [Thread-17] [Manager.AppMap] DB is not available. Next attempt to retry to connect in 15 seconds.

Resolution

HF25 developer comment showed some information about a workaround for using older Databases to be used:

introscope.apmserver.atc.legacy.postgre=true

so this is usable from HF25 upwards.

Note: tests have shown that it is indeed "postgre=true", postgre without an "s".