View Issue Details

Related ChangesetsJump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0005854SymmetricDSBugpublic2023-05-23 06:592023-07-05 18:06
Reporterd.jimenez Assigned Tojosh-a-hicks  
Priorityhigh 
Status assignedResolutionopen 
Product Version3.14.7 
Summary0005854: Error creation a table with extra _ because sequence does not exists
DescriptionI am syncronizing a s system of two postgress instances. Rhis Dbs are a bit special because most of the tables are created in upper case and it associated secuences too.
After creating the schema and be able to copy data, it start to report errors like:


INSERT INTO "public"."ISSUE_WA_SENSITIVE_DATA" ("ID","ISSUE_ID","FIELD_OPTION_ID","AIO_SOURCE") SELECT "ID","ISSUE_ID","FIELD_OPTION_ID","AIO_SOURCE" FROM "public"."ISSUE_WA_SENSITIVE_DATA_";
DROP TABLE "public"."ISSUE_WA_SENSITIVE_DATA_" CASCADE;

[replication-001] - JdbcSqlTemplate - ERROR: relation "public.ISSUE_WA_SENSITIVE_DATA__ID_seq" does not exist
  Position: 91. Failed to execute: CREATE TABLE "public"."ISSUE_WA_SENSITIVE_DATA_"(
    "ID" int4 NOT NULL DEFAULT nextval('"public"."ISSUE_WA_SENSITIVE_DATA__ID_seq"'),
    "ISSUE_ID" int4,
    "FIELD_OPTION_ID" int4,
    "AIO_SOURCE" text,
    PRIMARY KEY ("ID")
)
[replication-001] - ManageIncomingBatchListener - Failed to load batch 000-851 org.jumpmind.db.sql.SqlException: ERROR: relation "public.ISSUE_WA_SENSITIVE_DATA__ID_seq" does not exist
  Position: 91
    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.JdbcSqlTemplate.execute(JdbcSqlTemplate.java:494)
    at org.jumpmind.db.sql.JdbcSqlTemplate.update(JdbcSqlTemplate.java:405)
    at org.jumpmind.db.sql.SqlScript.execute(SqlScript.java:113)
    at org.jumpmind.db.platform.AbstractDatabasePlatform.alterTables(AbstractDatabasePlatform.java:254)
    at org.jumpmind.db.platform.AbstractDatabasePlatform.alterDatabase(AbstractDatabasePlatform.java:222)
    at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.create(DefaultDatabaseWriter.java:658)
    at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.create(DefaultDatabaseWriter.java:608)
    at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriter.write(AbstractDatabaseWriter.java:197)
    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:186)
    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:1069)
    at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1045)
    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:829)
Caused by: org.postgresql.util.PSQLException: ERROR: relation "public.ISSUE_WA_SENSITIVE_DATA__ID_seq" does not exist
  Position: 91
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:333)
    at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:319)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:295)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:290)
    at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:193)
    at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:193)
    at org.jumpmind.db.sql.JdbcSqlTemplate$6.execute(JdbcSqlTemplate.java:420)
    at org.jumpmind.db.sql.JdbcSqlTemplate$6.execute(JdbcSqlTemplate.java:405)
    at org.jumpmind.db.sql.JdbcSqlTemplate.execute(JdbcSqlTemplate.java:492)
    ... 20 more

The thing is that the table "public"."ISSUE_WA_SENSITIVE_DATA_" does not exists and when try to delte it associated secuence does not exists to. Seems that the program still working but all console is plenty of errors.

As a temporal fix im creating the sequence it is complaining but they are a lot....
Steps To ReproduceI attach my configuration files and also the configuration I have run:

insert into SYM_NODE_GROUP
        (node_group_id, description)
        values ('master', 'Jira master Node');
        
       
insert into SYM_NODE_GROUP
        (node_group_id, description)
        values ('rep', 'Replication Node');
        
       
       insert into SYM_NODE_GROUP_LINK
(source_node_group_id, target_node_group_id, data_event_action)
      values ('master', 'rep', 'P');
     
 insert into SYM_ROUTER (router_id,
    source_node_group_id, target_node_group_id, create_time,
    last_update_time) values ('master-2-rep','master', 'rep',
    current_timestamp, current_timestamp);

   
   
insert into SYM_TRIGGER (trigger_id, source_table_name,
          channel_id, last_update_time, create_time)
                  values ('all', '*', 'default', current_timestamp, current_timestamp);
                 

insert into SYM_TRIGGER_ROUTER
        (trigger_id, router_id, initial_load_order, create_time,
        last_update_time) values ('all', 'master-2-rep', 1, current_timestamp,
        current_timestamp);
Tagsschema

Activities

d.jimenez

2023-05-23 06:59

reporter  

jiraMaster-000.properties (856 bytes)    
#Server Configuration

# Friendly name to refer to this node from command line
engine.name=jiraMaster

# The class name for the JDBC Driver
db.driver=org.postgresql.Driver

# Node group this node belongs to, which defines what it will sync with who.
# Must match the sym_node_group configuration in database.
group.id=master

# External ID for this node, which is any unique identifier you want to use.
external.id=000

# Sync URL where other nodes can contact this node to push/pull data or register.
sync.url=http://localhost:31415/sync/jiraMaster

# This node will contact the root node's sync.url to register itself.
# Leave blank to indicate this is the root node.
registration.url=


# The JDBC URL used to connect to the database
db.url=xxxxxxxxxxx

db.user=xxxxxx
db.password=xxxxxxxxxxxxxx

auto.registration=true

initial.load.create.first=true
jiraMaster-000.properties (856 bytes)   
replication-001.properties (725 bytes)    
#Replicate Configuration

# Friendly name to refer to this node from command line
engine.name=replication-001

# The class name for the JDBC Driver
db.driver=org.postgresql.Driver

# Node group this node belongs to, which defines what it will sync with who.
# Must match the sym_node_group configuration in database.
group.id=rep

# External ID for this node, which is any unique identifier you want to use.
external.id=001

# This node will contact the root node's sync.url to register itself.
registration.url=http://localhost:31415/sync/jiraMaster

# The JDBC URL used to connect to the database
db.url=xxxxxxxxxxxxxx

db.user=xxxxxxxxx
db.password=xxxxxxxxxxxxxxx


auto.registration=true

initial.load.create.first=true
replication-001.properties (725 bytes)   

d.jimenez

2023-05-23 09:42

reporter   ~0002330

Using Postgress in both DBs

emiller

2023-05-24 17:57

developer   ~0002333

Can you provide the table definitions for the ISSUE_WA_SENSITIVE_DATA table from nodes 000 and 001?

d.jimenez

2023-05-26 07:32

reporter   ~0002336

HI. It happens with all the table but sure I attach that table. Im copying an schema that it is a dump of jira db via plugin: Here you have the full schema https://appfire.atlassian.net/wiki/spaces/ADEFJC/pages/65044633/Getting+started

-- public."ISSUE_WA_SENSITIVE_DATA" definition

-- Drop table

-- DROP TABLE public."ISSUE_WA_SENSITIVE_DATA";

CREATE TABLE public."ISSUE_WA_SENSITIVE_DATA" (
    "ID" serial4 NOT NULL,
    "ISSUE_ID" int4 NULL,
    "FIELD_OPTION_ID" int4 NULL,
    "AIO_SOURCE" varchar NULL,
    CONSTRAINT "ISSUE_WA_SENSITIVE_DATA_pkey" PRIMARY KEY ("ID")
);

Let me know if you need more information

Related Changesets

SymmetricDS: 3.14 67aff1a9

2023-06-27 15:34:07

joshahicks

Details Diff 		0005854: Error creation a table with extra _ because sequence does not
exists		 Affected Issues
0005854
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/AbstractDdlBuilder.java Diff File

Issue History

Date Modified Username Field Change
2023-05-23 06:59 d.jimenez New Issue
2023-05-23 06:59 d.jimenez Tag Attached: schema
2023-05-23 06:59 d.jimenez File Added: jiraMaster-000.properties
2023-05-23 06:59 d.jimenez File Added: replication-001.properties
2023-05-23 09:42 d.jimenez Note Added: 0002330
2023-05-24 17:57 emiller Status new => feedback
2023-05-24 17:57 emiller Note Added: 0002333
2023-05-26 07:32 d.jimenez Note Added: 0002336
2023-05-26 07:32 d.jimenez Status feedback => new
2023-06-30 13:00 Changeset attached => SymmetricDS 3.14 67aff1a9
2023-07-05 18:06 pmarzullo Assigned To => josh-a-hicks
2023-07-05 18:06 pmarzullo Status new => assigned