Hello, I am struggling to no end trying to get the SymmetricDS service working for my project.
I have successfully achieved a working version of the sample provided (corp and store), and even a sample version of my final product. However, when I try to integrate a copy of the master database to provide data to the other sub-databases, I run into two major problems as stated in the subject line.
When attempting the initial load, the dataextractor collects the data from the tables to be copied properly. However, when transferring to the PostgreSQL database it alters the sql statements that I have created to define the tables, and replaces it with syntax that cannot be executed. This is because:
1. I assume to match the auto_increment quality of the mysql column (ID), it changes the syntax to read: DEFAULT nextval('"mytable_id_sec"')
for which the sequence is not created.
2. It seems to randomly select 5 columns from the master database to create indices, all of which are not included in the defined sub-database field nor the included_column_names column in sym_triggers.
Is there something that I am doing clearly wrong?
Relevant segment copied from log file:
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database"><databasename="dataextractor"><tablename="projects"><columnname="ID"primaryKey="true"required="true"type="INTEGER"size="10"autoIncrement="true"><platform-columnname="mysql"type="INT"size="10"/></column><columnname="PNAME"type="VARCHAR"size="255"><platform-columnname="mysql"type="VARCHAR"size="255"/></column><columnname="TYPE"type="INTEGER"size="10"><platform-columnname="mysql"type="INT"size="10"/></column><columnname="ACTIVE"type="INTEGER"size="10"default="0"><platform-columnname="mysql"type="INT"size="10"default="0"/></column><columnname="FID"required="true"type="INTEGER"size="10"><platform-columnname="mysql"type="INT"size="10"/></column><uniquename="ID"><unique-columnname="ID"/></unique><indexname="MANAGER_ID"><index-columnname="MANAGER_ID"/></index><indexname="OGID"><index-columnname="OGID"/></index><indexname="PGID"><index-columnname="PGID"/></index><indexname="SGID"><index-columnname="SGID"/></index><indexname="TYPE"><index-columnname="TYPE"/></index></table></database>
2017-07-25 16:49:43,379 INFO [pi-001] [PostgreSqlDatabasePlatform] [dataloader-lmacs-000-1] Running alter sql:
DROP TABLE "public"."projects_" CASCADE;
CREATE TABLE "public"."projects_"(
"id" INTEGER NOT NULL,
"pname" CHAR(255) DEFAULT 'NULL::bpchar',
"type" INTEGER DEFAULT 0,
"active" INTEGER DEFAULT 0,
"fid" INTEGER NOT NULL,
PRIMARY KEY ("id")
);
INSERT INTO "public"."projects_" ("id","pname","type","active","fid") SELECT "id","pname","type","active","fid" FROM "public"."projects";
DROP TABLE "public"."projects" CASCADE;
CREATE TABLE "projects"(
"id" INTEGER NOT NULL DEFAULT nextval('"projects_id_seq"'),
"pname" VARCHAR(255),
"type" INTEGER,
"active" INTEGER DEFAULT 0,
"fid" INTEGER NOT NULL,
PRIMARY KEY ("id")
);
INSERT INTO "projects" ("id","pname","type","active","fid") SELECT "id","pname","type","active","fid" FROM "public"."projects_";
DROP TABLE "public"."projects_" CASCADE;
CREATE UNIQUE INDEX "id" ON "projects" ("id");
CREATE INDEX "manager_id" ON "projects" ("manager_id");
CREATE INDEX "ogid" ON "projects" ("ogid");
CREATE INDEX "pgid" ON "projects" ("pgid");
CREATE INDEX "sgid" ON "projects" ("sgid");
CREATE INDEX "type" ON "projects" ("type");
2017-07-25 16:49:43,446 WARN [pi-001] [JdbcSqlTemplate] [dataloader-lmacs-000-1] ERROR: relation "projects_id_seq" does not exist. Failed to execute: CREATE TABLE "projects"(
"id" INTEGER NOT NULL DEFAULT nextval('"projects_id_seq"'),
"pname" VARCHAR(255),
"type" INTEGER,
"active" INTEGER DEFAULT 0,
"fid" INTEGER NOT NULL,
PRIMARY KEY ("id")
)
2017-07-25 16:49:43,448 ERROR [pi-001] [DefaultDatabaseWriter] [dataloader-lmacs-000-1] Failed to alter table using the following xml: <?xml version="1.0"?><!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database"><databasename="dataextractor"><tablename="projects"><columnname="ID"primaryKey="true"required="true"type="INTEGER"size="10"autoIncrement="true"><platform-columnname="mysql"type="INT"size="10"/></column><columnname="PNAME"type="VARCHAR"size="255"><platform-columnname="mysql"type="VARCHAR"size="255"/></column><columnname="TYPE"type="INTEGER"size="10"><platform-columnname="mysql"type="INT"size="10"/></column><columnname="ACTIVE"type="INTEGER"size="10"default="0"><platform-columnname="mysql"type="INT"size="10"default="0"/></column><columnname="FID"required="true"type="INTEGER"size="10"><platform-columnname="mysql"type="INT"size="10"/></column><uniquename="ID"><unique-columnname="ID"/></unique><indexname="MANAGER_ID"><index-columnname="MANAGER_ID"/></index><indexname="OGID"><index-columnname="OGID"/></index><indexname="PGID"><index-columnname="PGID"/></index><indexname="SGID"><index-columnname="SGID"/></index><indexname="TYPE"><index-columnname="TYPE"/></index></table></database>
2017-07-25 16:49:43,500 ERROR [pi-001] [DataLoaderService] [dataloader-lmacs-000-1] Failed to load batch 000-9 StackTraceKey.init [SqlException:466256802]
org.jumpmind.db.sql.SqlException: ERROR: relation "projects_id_seq" does not exist
at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:300)
at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:291)
at org.jumpmind.db.sql.JdbcSqlTemplate.execute(JdbcSqlTemplate.java:493)
at org.jumpmind.db.sql.JdbcSqlTemplate.update(JdbcSqlTemplate.java:401)
at org.jumpmind.db.sql.SqlScript.execute(SqlScript.java:107)
at org.jumpmind.db.platform.AbstractDatabasePlatform.alterTables(AbstractDatabasePlatform.java:227)
at org.jumpmind.db.platform.AbstractDatabasePlatform.alterDatabase(AbstractDatabasePlatform.java:200)
at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.create(DefaultDatabaseWriter.java:512)
at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriter.write(AbstractDatabaseWriter.java:190)
at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:64)
at org.jumpmind.symmetric.model.ProcessInfoDataWriter.write(ProcessInfoDataWriter.java:65)
at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:64)
at org.jumpmind.symmetric.io.data.writer.TransformWriter.write(TransformWriter.java:207)
at org.jumpmind.symmetric.io.data.DataProcessor.forEachDataInTable(DataProcessor.java:200)
at org.jumpmind.symmetric.io.data.DataProcessor.forEachTableInBatch(DataProcessor.java:170)
at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:116)
at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$1.call(DataLoaderService.java:974)
at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$1.call(DataLoaderService.java:950)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.postgresql.util.PSQLException: ERROR: relation "projects_id_seq" does not exist
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2155)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:288)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:430)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:356)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:303)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:289)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:266)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:262)
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:416)
at org.jumpmind.db.sql.JdbcSqlTemplate$6.execute(JdbcSqlTemplate.java:401)
at org.jumpmind.db.sql.JdbcSqlTemplate.execute(JdbcSqlTemplate.java:491)
... 19 more
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Chris you are not doing anything wrong. DDL creation between platforms does surface issues occassionally. Are you Postgres to Postgres? Often we see issues in the default values because we simply copy these over as is and sometimes they call functions or sequences that do not exist in the target. I would recommend using the dbexport tool to generate the create sql script which is the same that is sent through this process. However this gives you a chance to look at it and analyze and correct anything. Then you can run against the target or use SDS to send SQL to many target nodes once its all flushed out.
Dbexport is a command line utility in the bin directory of SDS.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I've actually determined the source of the problem, it has to do with defined indices in the master database. The communication is, or rather was, between Postgres and MySQL. I had a creation script left over from a demo of the service and used it to determine the problem (it was almost identical to the Postgres script). When I ran the service as MySQL-MySQL, a similar error appeared except without the use sequences. Somehow I missed this, but the erroneous columns turned out to be indexed.
Now that I know what the problem is, is there a way to ignore indices? I am trying to have the sub-database only carry a subset of the data contained in the master database. The columns that raised the error from the excerpt I shared are unnecessary for my purposes and would prefer if they were excluded.
I have used your suggestion to use the dbexport utility which seems to have solved the syntax errors I was receiving, but I am still unable to leave out the indexed columns.
Thanks for the help! I appreciate the quick response.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
There is a create.table.without.foreign.keys parameter to turn off foreign keys, but not one to turn off indexes during table creation. You can request it in the issue tracker at http://symmetricds.org/issues/
Another approach might be to use the initial.load.before.sql parameter to set your SQL script to create all the tables at the client during the initial load.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hello, I am struggling to no end trying to get the SymmetricDS service working for my project.
I have successfully achieved a working version of the sample provided (corp and store), and even a sample version of my final product. However, when I try to integrate a copy of the master database to provide data to the other sub-databases, I run into two major problems as stated in the subject line.
When attempting the initial load, the dataextractor collects the data from the tables to be copied properly. However, when transferring to the PostgreSQL database it alters the sql statements that I have created to define the tables, and replaces it with syntax that cannot be executed. This is because:
1. I assume to match the auto_increment quality of the mysql column (ID), it changes the syntax to read: DEFAULT nextval('"mytable_id_sec"')
for which the sequence is not created.
2. It seems to randomly select 5 columns from the master database to create indices, all of which are not included in the defined sub-database field nor the included_column_names column in sym_triggers.
Is there something that I am doing clearly wrong?
Relevant segment copied from log file:
Chris you are not doing anything wrong. DDL creation between platforms does surface issues occassionally. Are you Postgres to Postgres? Often we see issues in the default values because we simply copy these over as is and sometimes they call functions or sequences that do not exist in the target. I would recommend using the dbexport tool to generate the create sql script which is the same that is sent through this process. However this gives you a chance to look at it and analyze and correct anything. Then you can run against the target or use SDS to send SQL to many target nodes once its all flushed out.
Dbexport is a command line utility in the bin directory of SDS.
I've actually determined the source of the problem, it has to do with defined indices in the master database. The communication is, or rather was, between Postgres and MySQL. I had a creation script left over from a demo of the service and used it to determine the problem (it was almost identical to the Postgres script). When I ran the service as MySQL-MySQL, a similar error appeared except without the use sequences. Somehow I missed this, but the erroneous columns turned out to be indexed.
Now that I know what the problem is, is there a way to ignore indices? I am trying to have the sub-database only carry a subset of the data contained in the master database. The columns that raised the error from the excerpt I shared are unnecessary for my purposes and would prefer if they were excluded.
I have used your suggestion to use the dbexport utility which seems to have solved the syntax errors I was receiving, but I am still unable to leave out the indexed columns.
Thanks for the help! I appreciate the quick response.
There is a create.table.without.foreign.keys parameter to turn off foreign keys, but not one to turn off indexes during table creation. You can request it in the issue tracker at http://symmetricds.org/issues/
Another approach might be to use the initial.load.before.sql parameter to set your SQL script to create all the tables at the client during the initial load.