View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0004006SymmetricDSBugpublic2019-06-14 09:472019-08-23 16:46
Reporterklippit Assigned ToJJ_Starrett  
Priorityhigh 
Status closedResolutionfixed 
Product Version3.10.0 
Target Version3.10.4Fixed in Version3.10.4 
Summary0004006: CAST Error on auto.resolve.foreign.key.violation=true
DescriptionCAST Error on finding missing foreign key rows

Error only existst if there is another foreign key after first round of searching for foreignkeys.

[server] - DataService - Unknown exception while processing foreign key for node id: 1 data id 368
org.jumpmind.db.sql.SqlException: ERROR: operator does not exist: bigint = character varying
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 133
        at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:302)
        at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:291)
        at org.jumpmind.db.sql.JdbcSqlTemplate.execute(JdbcSqlTemplate.java:513)
        at org.jumpmind.db.sql.JdbcSqlTemplate.queryForMap(JdbcSqlTemplate.java:274)
        at org.jumpmind.db.platform.AbstractJdbcDdlReader.getImportedForeignTableRows(AbstractJdbcDdlReader.java:1746)
        at org.jumpmind.symmetric.service.impl.DataService.reloadMissingForeignKeyRows(DataService.java:2418)
        at org.jumpmind.symmetric.service.impl.AcknowledgeService.ack(AcknowledgeService.java:145)
        at org.jumpmind.symmetric.web.AckUriHandler.ack(AckUriHandler.java:111)
        at org.jumpmind.symmetric.web.AckUriHandler$2.call(AckUriHandler.java:74)
        at java.util.concurrent.FutureTask.run(FutureTask.java:266)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)
Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: bigint = character varying
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 133
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:143)
        at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:106)
        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$4.execute(JdbcSqlTemplate.java:287)
        at org.jumpmind.db.sql.JdbcSqlTemplate$4.execute(JdbcSqlTemplate.java:274)
        at org.jumpmind.db.sql.JdbcSqlTemplate.execute(JdbcSqlTemplate.java:511)
        ... 10 more

Error fixed for my usage by cutting the code

                                 Row foreignRow = new Row(foreignTable.getColumnCount());

                                /*if (foreignTable.getForeignKeyCount() > 0) {
                                    DmlStatement selectSt = platform.createDmlStatement(DmlType.SELECT, foreignTable, null);
                                    Object[] keys = whereRow.toArray(foreignTable.getPrimaryKeyColumnNames());
                                    Map<String, Object> values = platform.getSqlTemplateDirty().queryForMap(selectSt.getSql(), keys);
                                    if (values == null) {
                                        log.warn(
                                                "Unable to reload rows for missing foreign key data for table '{}', parent data not found. Using sql='{}' with keys '{}'",
                                                foreignTable.getName(), selectSt.getSql(), keys);
                                    } else {
                                        foreignRow.putAll(values);
                                    }
                                }*/

                                TableRow foreignTableRow = new TableRow(foreignTable, foreignRow, whereSql, referenceColumnName, fk.getName());
                                fkDepList.add(foreignTableRow);

Steps To ReproduceCREATE TABLE acct_account
(
  id bigint NOT NULL,
  nr bigint NOT NULL,
  name character varying(64),
  last_journal_nr bigint NOT NULL,
  last_sequence_nr bigint NOT NULL,
  balance numeric(15,4) NOT NULL,
  factor_balance numeric(15,4) NOT NULL,
  account_owner character varying(128) NOT NULL,
  journal_store character varying(128) NOT NULL,
  open_date timestamp without time zone NOT NULL,
  close_date timestamp without time zone,
  options integer,
  locked integer NOT NULL DEFAULT 0,
  CONSTRAINT acct_account_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE acct_account
  OWNER TO postgres;

REATE TABLE comp_shop_account
(
  id bigint NOT NULL,
  shop_id bigint NOT NULL,
  account_id bigint NOT NULL,
  CONSTRAINT comp_shop_account_pkey PRIMARY KEY (id),
  CONSTRAINT fk_shopaccttoaccount FOREIGN KEY (account_id)
      REFERENCES acct_account (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_shopaccttoshop FOREIGN KEY (shop_id)
      REFERENCES comp_shop (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE comp_shop_account
  OWNER TO postgres;

REATE TABLE comp_shop
(
  id bigint NOT NULL,
  nr integer NOT NULL,
  name character varying(255),
  address_id bigint NOT NULL,
  open_date date,
  close_date date,
  manager_id bigint,
  company_id bigint NOT NULL,
  CONSTRAINT comp_shop_pkey PRIMARY KEY (id),
  CONSTRAINT fk_shoptoaddress FOREIGN KEY (address_id)
      REFERENCES crm_address (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_shoptocompany FOREIGN KEY (company_id)
      REFERENCES comp_company (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_shoptomanager FOREIGN KEY (manager_id)
      REFERENCES comp_employee (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE comp_shop
  OWNER TO postgres;


begin transaction;

INSERT INTO public.acct_account(
            id, nr, name, last_journal_nr, last_sequence_nr, balance, factor_balance,
            account_owner, journal_store, open_date, options,
            locked)
    VALUES (70,123,'test',0,0,0.0000,0.0000,'owner','store','2019-06-13 16:45:06.911793',0,0);



INSERT INTO public.comp_shop_account(
            id, shop_id, account_id)
    VALUES (70, 2812497967292879830, 70);


commit;
TagsNo tags attached.

Activities

elong

2019-06-20 20:55

developer   ~0001539

I ran your test case, but it ran okay for me and resolved the foreign keys. I debugged that queryForMap() line and I do see that the column is a BIGINT, but it's passing a string (character varying) for the argument to the SQL query -- it must have performed conversion on it without any trouble. I'm using the postgresql-42.2.5.jar that comes with SymmetricDS 3.10. Can you tell me the versions for your SymmetricDS, Postgres, and Postgres JDBC driver?

klippit

2019-06-25 14:18

reporter   ~0001541

I use Postgressql postgresql-42.2.5 Postgres Server 9.6 and SymmetricDs 3.10.1

Issue History

Date Modified Username Field Change
2019-06-14 09:47 klippit New Issue
2019-06-20 20:55 elong Status new => feedback
2019-06-20 20:55 elong Note Added: 0001539
2019-06-25 14:18 klippit Note Added: 0001541
2019-06-25 14:18 klippit Status feedback => new
2019-07-10 19:49 elong Assigned To => JJ_Starrett
2019-07-10 19:49 elong Status new => assigned
2019-07-10 19:49 elong Target Version => 3.10.4
2019-07-15 18:28 elong Status assigned => resolved
2019-07-15 18:28 elong Resolution open => fixed
2019-07-15 18:28 elong Fixed in Version => 3.10.4
2019-08-23 16:46 admin Status resolved => closed