View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0002835 | SymmetricDS | Bug | public | 2016-09-29 16:11 | 2017-06-05 13:08 |
| Reporter | daorti@gmail.com | Assigned To | chenson | ||
| Priority | normal | ||||
| Status | closed | Resolution | fixed | ||
| Product Version | 3.8.4 | ||||
| Target Version | 3.8.25 | Fixed in Version | 3.8.25 | ||
| Summary | 0002835: Cannot access audit tables when db is postgressql | ||||
| Description | Hello 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 Reproduce | Activate auditing on a table that is in a Postgresql database | ||||
| Additional Information | Windows 7 Symmetric DS 3.8.4 PostgresSQL 9.5 | ||||
| Tags | No tags attached. | ||||
| 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 |