View Issue Details

IDProjectCategoryView StatusLast Update
0002835SymmetricDSBugpublic2017-06-05 13:08
Reporterdaorti@gmail.com Assigned Tochenson  
Prioritynormal 
Status closedResolutionfixed 
Product Version3.8.4 
Target Version3.8.25Fixed in Version3.8.25 
Summary0002835: Cannot access audit tables when db is postgressql
DescriptionHello
I am trying to establish auditing on some tables that are on PosgresSQL 9.5

When I update the postgres table for the first time it will create the audit table, but when it tries to insert the record it will fail with the error:


Failed pk data was: "544553544553470000014D6BB4152A@bmw.de"
Failed row data was: "544553544553470000014D6BB4152A@bmw.de","0000000000TESTESG","2015-06-19 12:24:33.000000","2015-05-19 12:24:34.653000",,
Failed old data was: "544553544553470000014D6BB4152A@bmw.de","0000000000TESTESG","2015-05-19 12:24:33.584000","2015-05-19 12:24:34.653000",,

2016-09-29 16:34:58,145 ERROR [ENG_POSTGRES] [RouterService] [ENG_POSTGRES-job-8] Failed to route and batch data on 'CH_AUD_CSBE' channel StackTraceKey.init [SqlException:350904137]
org.jumpmind.db.sql.SqlException: ERROR: column "audit_id" does not exist
  Position: 12
    at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:283)
    

I activated logging at the postgressql side and the attempted query is:
select max(AUDIT_ID) from "public"."T_CSBE_EVENT_AUDIT"

notice that the row audit id is queried without the double quotes, when the following query is attempted it works

  alter table T_CSBE_EVENT_AUDIT rename "AUDIT_ID" to AUDIT_ID

It is specially known that Postgres gives special treatment to the double quotes and that any name within double quotes has to be referenced with them in the future

DDL:

CREATE TABLE "r6174"."T_CSBE_EVENT_AUDIT"(
    "AUDIT_ID" BIGINT NOT NULL,
    "AUDIT_TIME" TIMESTAMP,
    "AUDIT_EVENT" CHAR(1),
    "eventid" varchar(64),
    "vin" varchar(17),
    "starttime" timestamp,
    "terminationtime" timestamp,
    "lastuplink" timestamp,
    "linkedeventid" varchar(64),
    PRIMARY KEY ("AUDIT_ID")
);

Full stack trace
at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:274)
    at org.jumpmind.db.sql.JdbcSqlTemplate.execute(JdbcSqlTemplate.java:487)
    at org.jumpmind.db.sql.JdbcSqlTemplate.queryForObject(JdbcSqlTemplate.java:150)
    at org.jumpmind.db.sql.AbstractSqlTemplate.queryForLong(AbstractSqlTemplate.java:75)
    at org.jumpmind.symmetric.route.AuditTableDataRouter.routeToNodes(AuditTableDataRouter.java:99)
    at org.jumpmind.symmetric.service.impl.RouterService.routeData(RouterService.java:917)
    at org.jumpmind.symmetric.service.impl.RouterService.selectDataAndRoute(RouterService.java:835)
    at org.jumpmind.symmetric.service.impl.RouterService.routeDataForChannel(RouterService.java:634)
    at org.jumpmind.symmetric.service.impl.RouterService.routeDataForEachChannel(RouterService.java:427)
    at org.jumpmind.symmetric.service.impl.RouterService.routeData(RouterService.java:201)
    at org.jumpmind.symmetric.job.RouterJob.doJob(RouterJob.java:40)
    at org.jumpmind.symmetric.job.AbstractJob.invoke(AbstractJob.java:180)
    at org.jumpmind.symmetric.job.AbstractJob.run(AbstractJob.java:224)
    at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)
Caused by: org.postgresql.util.PSQLException: ERROR: column "audit_id" does not exist
  Position: 12
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2182)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1911)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:173)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:616)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:466)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:351)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
    at org.jumpmind.db.sql.JdbcSqlTemplate$1.execute(JdbcSqlTemplate.java:162)
    at org.jumpmind.db.sql.JdbcSqlTemplate.execute(JdbcSqlTemplate.java:485)
    ... 19 more
Steps To ReproduceActivate auditing on a table that is in a Postgresql database
Additional InformationWindows 7
Symmetric DS 3.8.4
PostgresSQL 9.5
TagsNo tags attached.

Activities

admin

2016-09-30 14:28

administrator   ~0000881

Re-opening. Was accidentally closed when performing the release.

Related Changesets

SymmetricDS: 3.8 abc8ed3f

2017-05-31 19:45:42

chenson42

Details Diff
0002835: Cannot access audit tables when db is postgressql Affected Issues
0002835
mod - symmetric-core/src/main/java/org/jumpmind/symmetric/route/AuditTableDataRouter.java Diff File

Issue History

Date Modified Username Field Change
2016-09-29 16:11 daorti@gmail.com New Issue
2016-09-30 01:01 admin Status new => closed
2016-09-30 14:28 admin Status closed => new
2016-09-30 14:28 admin Note Added: 0000881
2017-05-24 17:21 chenson Assigned To => maxwellpettit
2017-05-24 17:21 chenson Status new => assigned
2017-05-24 17:21 chenson Target Version => 3.8.25
2017-05-31 19:56 chenson Assigned To maxwellpettit => chenson
2017-05-31 23:47 chenson Status assigned => resolved
2017-05-31 23:47 chenson Fixed in Version => 3.8.25
2017-05-31 23:47 chenson Resolution open => fixed
2017-06-01 00:00 chenson42 Changeset attached => SymmetricDS 3.8 abc8ed3f
2017-06-05 13:08 chenson Status resolved => closed