View Issue Details

Related ChangesetsJump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0005525SymmetricDSBugpublic2022-10-11 13:002022-11-30 19:40
Reporterclloyd1 Assigned To 
Priorityurgent 
Status closedResolutionfixed 
Product Version3.13.8 
Target Version3.13.9Fixed in Version3.13.9 
Summary0005525: Unsupported Postgresql data types
DescriptionWe have a schema with some Postgresql data types which are unsupported by SymmetricDS. These types are cidr, macaddr and macaddr8

When trying to replicate tables with any of these column types, there will be a resulting error such as:

2022-10-10 08:47:27,224 ERROR [cluster-a] [AcknowledgeService] [cluster-a-push-default-5] The outgoing batch cluster-c-3479 failed: ERROR: column "mac_address" is of type macaddr but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 548

We faced a similar issue in the past with the inet type https://www.symmetricds.org/issues/view.php?id=5381. This looks to be similar but with different types. Is the solution going to be adding further types into the list of castable types, e.g. https://github.com/JumpMind/symmetric-ds/pull/184? If there is any workaround without the code fix I'd be really grateful to know
Steps To ReproduceRun the following sql to create schema and data:
CREATE TABLE table_cidr_type (id varchar NOT NULL, col_cidr cidr, CONSTRAINT table_cidr_type_pkey PRIMARY KEY (id));
CREATE TABLE table_macaddr_type (id varchar NOT NULL, col_macaddr macaddr, CONSTRAINT table_macaddr_type_pkey PRIMARY KEY (id));
CREATE TABLE table_macaddr8_type (id varchar NOT NULL, col_macaddr8 macaddr8, CONSTRAINT table_macaddr8_type_pkey PRIMARY KEY (id));
INSERT INTO table_cidr_type (id, col_cidr) VALUES('1', '2.0.0.0/8'::cidr);
INSERT INTO table_macaddr_type (id, col_macaddr) VALUES('1', '08:00:2b:01:02:04'::macaddr);
INSERT INTO table_macaddr8_type (id, col_macaddr8) VALUES('1', '08:00:2b:ff:fe:01:02:05'::macaddr8);


Setup replication of these 3 tables see replication fail
Additional InformationTested on SymmetricDS versions 3.13.8 and also 3.14.2, same error on both
Postgresql version 13.7-1
Tagsdialect: postgresql

Activities

clloyd1

2022-10-11 13:00

reporter  

cidr_error.txt (3,980 bytes)    
[cluster-b] - IncomingBatchService - Retrying batch cluster-a-253
[cluster-b] - DefaultDatabaseWriter - Failed to process insert event in batch cluster-a-253 on channel 'default'.
Failed sql was: insert into "data2"."table_cidr_type" ("id", "col_cidr") values ('3','2.0.0.0/8') on conflict do nothing
Failed raw sql was: insert into "data2"."table_cidr_type" ("id", "col_cidr") values (?,?) on conflict do nothing
Failed sql parameters: ['3', '2.0.0.0/8']
Failed sql parameters types: [VARCHAR, LONGVARCHAR]
Failed sql state and code: 42804 (0)
Failed row data was: "3","2.0.0.0/8"
org.jumpmind.db.sql.SqlException: ERROR: column "col_cidr" is of type cidr but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 69
        at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:308)
        at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:297)
        at org.jumpmind.db.sql.JdbcSqlTransaction.addRow(JdbcSqlTransaction.java:443)
        at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.execute(DefaultDatabaseWriter.java:1018)
        at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.insert(DefaultDatabaseWriter.java:247)
        at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriter.write(AbstractDatabaseWriter.java:168)
        at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:62)
        at org.jumpmind.symmetric.model.ProcessInfoDataWriter.write(ProcessInfoDataWriter.java:84)
        at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:62)
        at org.jumpmind.symmetric.io.data.writer.TransformWriter.write(TransformWriter.java:190)
        at org.jumpmind.symmetric.io.data.DataProcessor.forEachDataInTable(DataProcessor.java:194)
        at org.jumpmind.symmetric.io.data.DataProcessor.forEachTableInBatch(DataProcessor.java:160)
        at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:108)
        at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1023)
        at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1001)
        at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
        at java.base/java.lang.Thread.run(Thread.java:871)
Caused by: org.postgresql.util.PSQLException: ERROR: column "col_cidr" is of type cidr but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 69
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)
        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:166)
        at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:134)
        at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136)
        at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136)
        at org.jumpmind.db.sql.JdbcSqlTransaction.executePreparedUpdate(JdbcSqlTransaction.java:462)
        at org.jumpmind.db.sql.JdbcSqlTransaction.addRow(JdbcSqlTransaction.java:440)
        ... 16 more
cidr_error.txt (3,980 bytes)   
macaddr_error.txt (3,961 bytes)    
[cluster-b] - DefaultDatabaseWriter - Failed to process insert event in batch cluster-a-254 on channel 'default'.
Failed sql was: insert into "data2"."table_macaddr_type" ("id", "col_macaddr") values ('4','08:00:2b:01:02:04') on conflict do nothing
Failed raw sql was: insert into "data2"."table_macaddr_type" ("id", "col_macaddr") values (?,?) on conflict do nothing
Failed sql parameters: ['4', '08:00:2b:01:02:04']
Failed sql parameters types: [VARCHAR, LONGVARCHAR]
Failed sql state and code: 42804 (0)
Failed row data was: "4","08:00:2b:01:02:04"
org.jumpmind.db.sql.SqlException: ERROR: column "col_macaddr" is of type macaddr but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 75
        at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:308)
        at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:297)
        at org.jumpmind.db.sql.JdbcSqlTransaction.addRow(JdbcSqlTransaction.java:443)
        at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.execute(DefaultDatabaseWriter.java:1018)
        at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.insert(DefaultDatabaseWriter.java:247)
        at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriter.write(AbstractDatabaseWriter.java:168)
        at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:62)
        at org.jumpmind.symmetric.model.ProcessInfoDataWriter.write(ProcessInfoDataWriter.java:84)
        at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:62)
        at org.jumpmind.symmetric.io.data.writer.TransformWriter.write(TransformWriter.java:190)
        at org.jumpmind.symmetric.io.data.DataProcessor.forEachDataInTable(DataProcessor.java:194)
        at org.jumpmind.symmetric.io.data.DataProcessor.forEachTableInBatch(DataProcessor.java:160)
        at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:108)
        at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1023)
        at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1001)
        at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
        at java.base/java.lang.Thread.run(Thread.java:871)
Caused by: org.postgresql.util.PSQLException: ERROR: column "col_macaddr" is of type macaddr but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 75
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)
        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:166)
        at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:134)
        at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136)
        at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136)
        at org.jumpmind.db.sql.JdbcSqlTransaction.executePreparedUpdate(JdbcSqlTransaction.java:462)
        at org.jumpmind.db.sql.JdbcSqlTransaction.addRow(JdbcSqlTransaction.java:440)
        ... 16 more
macaddr_error.txt (3,961 bytes)   
macaddr8_error.txt (3,987 bytes)    
[cluster-b] - DefaultDatabaseWriter - Failed to process insert event in batch cluster-a-255 on channel 'default'.
Failed sql was: insert into "data2"."table_macaddr8_type" ("id", "col_macaddr8") values ('5','08:00:2b:ff:fe:01:02:05') on conflict do nothing
Failed raw sql was: insert into "data2"."table_macaddr8_type" ("id", "col_macaddr8") values (?,?) on conflict do nothing
Failed sql parameters: ['5', '08:00:2b:ff:fe:01:02:05']
Failed sql parameters types: [VARCHAR, LONGVARCHAR]
Failed sql state and code: 42804 (0)
Failed row data was: "5","08:00:2b:ff:fe:01:02:05"
org.jumpmind.db.sql.SqlException: ERROR: column "col_macaddr8" is of type macaddr8 but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 77
        at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:308)
        at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:297)
        at org.jumpmind.db.sql.JdbcSqlTransaction.addRow(JdbcSqlTransaction.java:443)
        at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.execute(DefaultDatabaseWriter.java:1018)
        at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.insert(DefaultDatabaseWriter.java:247)
        at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriter.write(AbstractDatabaseWriter.java:168)
        at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:62)
        at org.jumpmind.symmetric.model.ProcessInfoDataWriter.write(ProcessInfoDataWriter.java:84)
        at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:62)
        at org.jumpmind.symmetric.io.data.writer.TransformWriter.write(TransformWriter.java:190)
        at org.jumpmind.symmetric.io.data.DataProcessor.forEachDataInTable(DataProcessor.java:194)
        at org.jumpmind.symmetric.io.data.DataProcessor.forEachTableInBatch(DataProcessor.java:160)
        at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:108)
        at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1023)
        at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1001)
        at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
        at java.base/java.lang.Thread.run(Thread.java:871)
Caused by: org.postgresql.util.PSQLException: ERROR: column "col_macaddr8" is of type macaddr8 but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 77
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)
        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:166)
        at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:134)
        at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136)
        at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136)
        at org.jumpmind.db.sql.JdbcSqlTransaction.executePreparedUpdate(JdbcSqlTransaction.java:462)
        at org.jumpmind.db.sql.JdbcSqlTransaction.addRow(JdbcSqlTransaction.java:440)
        ... 16 more
macaddr8_error.txt (3,987 bytes)   
schema_and_data.sql (653 bytes)    
CREATE TABLE table_cidr_type (id varchar NOT NULL, col_cidr cidr, CONSTRAINT table_cidr_type_pkey PRIMARY KEY (id));
CREATE TABLE table_macaddr_type (id varchar NOT NULL, col_macaddr macaddr, CONSTRAINT table_macaddr_type_pkey PRIMARY KEY (id));
CREATE TABLE table_macaddr8_type (id varchar NOT NULL, col_macaddr8 macaddr8, CONSTRAINT table_macaddr8_type_pkey PRIMARY KEY (id));

INSERT INTO table_cidr_type (id, col_cidr) VALUES('1', '2.0.0.0/8'::cidr);
INSERT INTO table_macaddr_type (id, col_macaddr) VALUES('1', '08:00:2b:01:02:04'::macaddr);
INSERT INTO table_macaddr8_type (id, col_macaddr8) VALUES('1', '08:00:2b:ff:fe:01:02:05'::macaddr8);
schema_and_data.sql (653 bytes)   

clloyd1

2022-10-14 16:24

reporter   ~0002205

Pull request raised. Please review https://github.com/JumpMind/symmetric-ds/pull/188
Thanks

Related Changesets

SymmetricDS: 3.13 ff0a9bef

2022-10-14 17:49:21

Chris Lloyd


Committer: GitHub Details Diff 		0005525: allow cast to additional types in Postgres (0000188) Affected Issues
0005525
mod - symmetric-db/src/main/java/org/jumpmind/db/model/TypeMap.java Diff File
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/postgresql/PostgreSqlDmlStatement.java Diff File

Issue History

Date Modified Username Field Change
2022-10-11 13:00 clloyd1 New Issue
2022-10-11 13:00 clloyd1 Tag Attached: dialect: postgresql
2022-10-11 13:00 clloyd1 File Added: cidr_error.txt
2022-10-11 13:00 clloyd1 File Added: macaddr_error.txt
2022-10-11 13:00 clloyd1 File Added: macaddr8_error.txt
2022-10-11 13:00 clloyd1 File Added: schema_and_data.sql
2022-10-14 16:24 clloyd1 Note Added: 0002205
2022-10-14 17:53 emiller Status new => resolved
2022-10-14 17:53 emiller Resolution open => fixed
2022-10-14 17:53 emiller Fixed in Version => 3.13.9
2022-10-14 18:00 Changeset attached => SymmetricDS 3.13 ff0a9bef
2022-11-30 19:40 admin Status resolved => closed