View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0002153 | SymmetricDS | Bug | public | 2015-01-22 21:46 | 2022-08-02 19:03 |
| Reporter | rotten | Assigned To | elong | ||
| Priority | normal | ||||
| Status | closed | Resolution | won't fix | ||
| Product Version | 3.7.2 | ||||
| Summary | 0002153: upgrade from 3.6.12 to 3.7.2 fails with ERROR on insert to new node_group_channel_wnd table | ||||
| Description | Upgrading SymmetricDS from 3.6.12 to 3.7.2 against a PostgreSQL 9.4 database. Encountered this exception on startup, it appears to be fatal. -- 2015-01-22 16:36:17,098 INFO [SymmetricDS] [PostgreSqlSymmetricDialect] [symmetric-engine-startup-0] DDL applied: CREATE TABLE "symmetricds"."sym_node_group_channel_wnd_"( "node_group_id" VARCHAR(50) NOT NULL, "channel_id" VARCHAR(128) NOT NULL, "start_time" TIME NOT NULL, "end_time" TIME NOT NULL, "enabled" SMALLINT DEFAULT 0 NOT NULL, PRIMARY KEY ("node_group_id", "channel_id", "start_time", "end_time") ) 2015-01-22 16:36:17,100 INFO [SymmetricDS] [PostgreSqlSymmetricDialect] [symmetric-engine-startup-0] DDL applied: INSERT INTO "symmetricds"."sym_node_group_channel_wnd_" ("node_group_id","channel_id","start_time","end_time","enabled") SELECT "node_group_id","channel_id","start_time","end_time","enabled" FROM "symmetricds"."sym_node_group_channel_wnd" 2015-01-22 16:36:17,107 INFO [SymmetricDS] [PostgreSqlSymmetricDialect] [symmetric-engine-startup-0] DDL applied: DROP TABLE "symmetricds"."sym_node_group_channel_wnd" CASCADE 2015-01-22 16:36:17,112 INFO [SymmetricDS] [PostgreSqlSymmetricDialect] [symmetric-engine-startup-0] DDL applied: CREATE TABLE "sym_node_group_channel_wnd"( "node_group_id" VARCHAR(50) NOT NULL, "channel_id" VARCHAR(128) NOT NULL, "start_time" TIMESTAMP NOT NULL, "end_time" TIMESTAMP NOT NULL, "enabled" SMALLINT DEFAULT 0 NOT NULL, PRIMARY KEY ("node_group_id", "channel_id", "start_time", "end_time") ) 2015-01-22 16:36:17,114 WARN [SymmetricDS] [PostgreSqlSymmetricDialect] [symmetric-engine-startup-0] DDL failed: INSERT INTO "sym_node_group_channel_wnd" ("node_group_id","channel_id","start_time","end_time","enabled") SELECT "node_group_id","channel_id","start_time","end_time","enabled" FROM "symmetricds"."sym_node_group_channel_wnd_" 2015-01-22 16:36:17,114 WARN [SymmetricDS] [JdbcSqlTemplate] [symmetric-engine-startup-0] ERROR: column "start_time" is of type timestamp without time zone but expression is of type time without time zone at character 143. Failed to execute: INSERT INTO "sym_node_group_channel_wnd" ("node_group_id","channel_id","start_time","end_time","enabled") SELECT "node_group_id","channel_id","start_time","end_time","enabled" FROM "symmetricds"."sym_node_group_channel_wnd_" 2015-01-22 16:36:17,116 ERROR [SymmetricDS] [AbstractSymmetricEngine] [symmetric-engine-startup-0] An error occurred while starting SymmetricDS org.jumpmind.db.sql.SqlException: ERROR: column "start_time" is of type timestamp without time zone but expression is of type time without time zone at character 143 at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:288) at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:279) at org.jumpmind.db.sql.JdbcSqlTemplate.execute(JdbcSqlTemplate.java:432) at org.jumpmind.db.sql.JdbcSqlTemplate.update(JdbcSqlTemplate.java:343) at org.jumpmind.db.sql.SqlScript.execute(SqlScript.java:107) at org.jumpmind.symmetric.db.AbstractSymmetricDialect.createOrAlterTablesIfNecessary(AbstractSymmetricDialect.java:427) at org.jumpmind.symmetric.db.AbstractSymmetricDialect.initTablesAndDatabaseObjects(AbstractSymmetricDialect.java:149) at org.jumpmind.symmetric.AbstractSymmetricEngine.setupDatabase(AbstractSymmetricEngine.java:385) at org.jumpmind.symmetric.AbstractSymmetricEngine.setup(AbstractSymmetricEngine.java:376) at org.jumpmind.symmetric.AbstractSymmetricEngine.start(AbstractSymmetricEngine.java:515) at org.jumpmind.symmetric.AbstractSymmetricEngine.start(AbstractSymmetricEngine.java:507) at org.jumpmind.symmetric.ClientSymmetricEngine.start(ClientSymmetricEngine.java:237) at org.jumpmind.symmetric.web.SymmetricEngineHolder$EngineStarter.run(SymmetricEngineHolder.java:444) Caused by: org.postgresql.util.PSQLException: ERROR: column "start_time" is of type timestamp without time zone but expression is of type time without time zone at character 143 at org.postgresql.core.v2.QueryExecutorImpl.receiveErrorMessage(QueryExecutorImpl.java:561) at org.postgresql.core.v2.QueryExecutorImpl.processResults(QueryExecutorImpl.java:484) at org.postgresql.core.v2.QueryExecutorImpl.execute(QueryExecutorImpl.java:364) at org.postgresql.core.v2.QueryExecutorImpl.execute(QueryExecutorImpl.java:258) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:561) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:405) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:397) at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264) at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264) at org.jumpmind.db.sql.JdbcSqlTemplate$6.execute(JdbcSqlTemplate.java:356) at org.jumpmind.db.sql.JdbcSqlTemplate$6.execute(JdbcSqlTemplate.java:343) at org.jumpmind.db.sql.JdbcSqlTemplate.execute(JdbcSqlTemplate.java:430) ... 10 more | ||||
| Tags | data sync, dialect: postgresql | ||||
|
|
I tried this: alter table symmetricds.sym_node_group_channel_wnd alter column start_time type time, alter column end_time type time ; However, on startup SymmetricDS move that table out of the way and built a new one again. |
|
|
Oh, I was upgrading from 3.6.16 not 3.6.12, that was a typo above. Sorry about that confusion. |
|
|
Are you using the sym_node_group_channel_wnd? Can you just drop it? |
|
|
Did you have any data in the table? |
|
|
No, there was not data in the table. Did it exist prior to 3.7? If I drop the table, won't the system just recreate it when it starts up? I could try revoking permissions to it, I suppose... |
|
|
If you drop the table symmetricds will re-create it correctly with timestamp columns |
|
|
I think the problem is that SymmetricDS is trying to put TIME values not TIMESTAMP values into the table, not that the table is creating the wrong column type. From above: org.jumpmind.db.sql.SqlException: ERROR: column "start_time" is of type timestamp without time zone but expression is of type time without time zone at character 143 Column reference: http://www.postgresql.org/docs/9.4/static/datatype-datetime.html I tried forcing the columns to be type "TIME", but symmetricds replace them with type "TIMESTAMP", and then failed again on the same error. |
|
|
I was able to go from 3.6.16 to 3.7.6 by manually dropping the node_group_channel_wnd and node_group_channel_wnd_ tables after the upgrade failed, and then restarting. It still creates the empty tables, but it doesn't try to put any data in them, so the nodes start up ok. |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2015-01-22 21:46 | rotten | New Issue | |
| 2015-01-22 21:52 | rotten | Note Added: 0000651 | |
| 2015-01-22 21:57 | rotten | Note Added: 0000652 | |
| 2015-01-23 00:16 | chenson | Note Added: 0000653 | |
| 2015-01-23 00:17 | chenson | Note Added: 0000654 | |
| 2015-01-23 01:43 | rotten | Note Added: 0000655 | |
| 2015-01-24 21:39 | chenson | Note Added: 0000656 | |
| 2015-01-26 00:47 | rotten | Note Added: 0000657 | |
| 2015-02-17 16:43 | rotten | Note Added: 0000670 | |
| 2019-04-23 16:47 | elong | Tag Attached: data sync | |
| 2019-04-23 16:47 | elong | Tag Attached: postgresql | |
| 2019-04-23 20:50 | admin | Tag Renamed | postgresql => dialect: postgresql |
| 2022-08-02 19:03 | elong | Assigned To | => elong |
| 2022-08-02 19:03 | elong | Status | new => closed |
| 2022-08-02 19:03 | elong | Resolution | open => won't fix |