View Issue Details

Related ChangesetsJump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0005381SymmetricDSBugpublic2022-07-28 10:092022-08-30 13:03
Reportershikhar.srivastava Assigned To 
Priorityurgent 
Status closedResolutionfixed 
Product Version3.13.5 
Target Version3.13.7Fixed in Version3.13.7 
Summary0005381: [Postgres Data replication fails] ERROR: column "address" is of type inet but expression is of type character varying
DescriptionPostgres data replication seems to fail when symmetricds finds a column of type `inet.

Failed raw sql was: insert into "ABCD"."IPADDRESS_TABLE" ("id", "created", "last_updated", "address") values (?,cast(? as timestamp with time zone),cast(? as timestamp with time zone),?) on conflict do nothing
Failed sql parameters: [10000001, {ts '2022-07-20 10:01:55.534'}, {ts '2022-07-20 10:01:55.534'}, '10.4.3.23/24']
Failed sql parameters types: [BIGINT, TIMESTAMPTZ, TIMESTAMPTZ, LONGVARCHAR]
Failed sql state and code: 42804 (0)
Failed row data was: "10000001","2022-07-20 10:01:55.534884 -00:00","2022-07-20 10:01:55.534903 -00:00","10.4.3.23/24"
org.jumpmind.db.sql.SqlException: ERROR: column "address" is of type inet but expression is of type character varying
Steps To ReproduceSteps:-
1. 3 clusters with postgres and symmetricds responsible for data replication.
2. Create a table with records as mentioned in the error logs above.
3. Received the error and data replication stops.
4. Works fine with `string`, `bigint` etc.
Tagsdialect: postgresql

Relationships

related to 0005380 closedjvanmeter [Postgres Data replication fails] java.lang.RuntimeException: Could not convert a value of 2022-07-11 10:54:59.860155 -00:00 for 

Activities

emiller

2022-08-01 14:07

developer   ~0002124

I attempted to reproduce this issue and issue 0005380 using the same columns and values from IPADDRESS_TABLE and I did not run into any errors. I used Postgres 13 and ran the following create and insert statements:

create table ipaddress_table (id int primary key, created date, last_updated date, address inet);
insert into ipaddress_table values ('10000001', '2022-07-20 10:01:55.534884 -00:00', '2022-07-20 10:01:55.534903 -00:00', '10.4.3.23/24');

What version of Postgres are you using? Also, can you provide the table definition for IPADDRESS_TABLE?

shikhar.srivastava

2022-08-02 07:40

reporter   ~0002133

We are seeing the issue both on PostgreSQL 11.14 and 13.6. Attaching the table structure:-
ABCD.IPADDRESS_TABLE (3,743 bytes)    
app=# \d ABCD.IPADDRESS_TABLE;
                                                 Table "ABCD.IPADDRESS_TABLE"
         Column          |           Type           | Collation | Nullable |                        Default
-------------------------+--------------------------+-----------+----------+--------------------------------------------------------
 id                      | bigint                   |           | not null | nextval('ABCD.IPADDRESS_TABLE_id_seq'::regclass)
 created                 | timestamp with time zone |           |          |
 last_updated            | timestamp with time zone |           |          |
 address                 | inet                     |           | not null |
 description             | character varying(200)   |           | not null |
 assigned_object_id      | bigint                   |           |          |
 nat_inside_id           | bigint                   |           |          |
 vrf_id                  | bigint                   |           |          |
 tenant_id               | bigint                   |           |          |
 status                  | character varying(50)    |           | not null |
 role                    | character varying(50)    |           | not null |
 dns_name                | character varying(255)   |           | not null |
 assigned_object_type_id | integer                  |           |          |
 custom_field_data       | jsonb                    |           | not null |
 system_tenant_id        | bigint                   |           | not null |
Indexes:
    "IPADDRESS_TABLE_pkey" PRIMARY KEY, btree (id)
    "IPADDRESS_TABLE_assigned_object_type_id_02354370" btree (assigned_object_type_id)
    "IPADDRESS_TABLE_nat_inside_id_key" UNIQUE CONSTRAINT, btree (nat_inside_id)
    "IPADDRESS_TABLE_tenant_id_ac55acfd" btree (tenant_id)
    "IPADDRESS_TABLE_vrf_id_51fcc59b" btree (vrf_id)
Check constraints:
    "IPADDRESS_TABLE_assigned_object_id_cff64474_check" CHECK (assigned_object_id >= 0)
    "IPADDRESS_TABLE_system_tenant_id_check" CHECK (system_tenant_id >= 0)
Foreign-key constraints:
    "IPADDRESS_TABLE_assigned_object_type_02354370_fk_django_co" FOREIGN KEY (assigned_object_type_id) REFERENCES ABCD.django_content_type(id) DEFERRABLE INITIALLY DEFERRED
    "IPADDRESS_TABLE_nat_inside_id_a45fb7c5_fk" FOREIGN KEY (nat_inside_id) REFERENCES ABCD.IPADDRESS_TABLE(id) DEFERRABLE INITIALLY DEFERRED
    "IPADDRESS_TABLE_tenant_id_ac55acfd_fk" FOREIGN KEY (tenant_id) REFERENCES ABCD.tenancy_tenant(id) DEFERRABLE INITIALLY DEFERRED
    "IPADDRESS_TABLE_vrf_id_51fcc59b_fk" FOREIGN KEY (vrf_id) REFERENCES ABCD.ipam_vrf(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "ABCD.TABLE_2" CONSTRAINT "TABLE_2_primary_ip4_id_2ccd943a_fk" FOREIGN KEY (primary_ip4_id) REFERENCES ABCD.IPADDRESS_TABLE(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "ABCD.TABLE_2" CONSTRAINT "TABLE_2_primary_ip6_id_d180fe91_fk" FOREIGN KEY (primary_ip6_id) REFERENCES ABCD.IPADDRESS_TABLE(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "ABCD.IPADDRESS_TABLE" CONSTRAINT "IPADDRESS_TABLE_nat_inside_id_a45fb7c5_fk" FOREIGN KEY (nat_inside_id) REFERENCES ABCD.IPADDRESS_TABLE(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "ABCD.TABLE_3" CONSTRAINT "TABLE_3_ipaddress_id_b4138c6d_fk" FOREIGN KEY (ipaddress_id) REFERENCES ABCD.IPADDRESS_TABLE(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "ABCD.TABLE_4" CONSTRAINT "TABLE_4_primary_ip4_id_942e42ae_fk" FOREIGN KEY (primary_ip4_id) REFERENCES ABCD.IPADDRESS_TABLE(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "ABCD.TABLE_4" CONSTRAINT "TABLE_4_primary_ip6_id_b7904e73_fk" FOREIGN KEY (primary_ip6_id) REFERENCES ABCD.IPADDRESS_TABLE(id) DEFERRABLE INITIALLY DEFERRED
ABCD.IPADDRESS_TABLE (3,743 bytes)   

clloyd1

2022-08-03 14:27

reporter   ~0002140

@emiller This issue is fairly straightforward to reproduce with an inet datatype in Postgres. Just a simple table containing the inet type should fail to replicate. The insert of the data on the target seems to fail with error "column XXX is of type inet but expression is of type character varying" due to a missing cast on the inet column in the prepared statement

I have a suggested fix in https://github.com/JumpMind/symmetric-ds/pull/184 which resolves the issue for me

Related Changesets

SymmetricDS: 3.13 79278b29

2022-08-03 16:37:06

Chris Lloyd


Committer: GitHub Details Diff 		0005381: allow cast to inet type in Postgres (0000184) Affected Issues
0005381
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-07-28 10:09 shikhar.srivastava New Issue
2022-07-28 10:09 shikhar.srivastava Issue generated from: 0005380
2022-07-28 10:09 shikhar.srivastava Relationship added related to 0005380
2022-07-28 10:10 shikhar.srivastava Tag Attached: dialect: postgresql
2022-08-01 14:07 emiller Note Added: 0002124
2022-08-01 14:08 emiller Status new => feedback
2022-08-02 07:40 shikhar.srivastava Note Added: 0002133
2022-08-02 07:40 shikhar.srivastava File Added: ABCD.IPADDRESS_TABLE
2022-08-02 07:40 shikhar.srivastava Status feedback => new
2022-08-03 14:27 clloyd1 Note Added: 0002140
2022-08-03 16:37 emiller Status new => resolved
2022-08-03 16:37 emiller Resolution open => fixed
2022-08-03 16:37 emiller Fixed in Version => 3.13.7
2022-08-03 17:00 Changeset attached => SymmetricDS 3.13 79278b29
2022-08-30 13:03 admin Status resolved => closed