View Issue Details

IDProjectCategoryView StatusLast Update
0003521SymmetricDSBugpublic2018-11-28 11:00
Reporterddlg_2Assigned Tojosh-a-hicks 
Priorityurgent 
Status resolvedResolutionfixed 
Product Version3.9.6 
Target Version3.10.0Fixed in Version3.10.0 
Summary0003521: Create Table First is creating table with all columns as primary key instead of none
DescriptionIn the case of source_node_database_tables do not have any primary keys and some fields of the source_node_database_tables are empty, target_node_database_tables are created successfully when initial load firstly, but Symmetric can not insert automatically values into target_node_database_tables.

part of target_node symmetric.log:

2018-04-18 15:37:41,006 INFO [server-010] [DefaultDatabaseWriter] [server-010-dataloader-2] Failed to process insert event in batch 001-12 on channel 'reload'.
Failed sql was: insert into `test`.`jc_ckdd` (`ckdd_id`, `ckdd_bh`, `ckdd_dd`, `ckdd_cjsj`, `ckdd_by1`, `id`) values (?,?,?,?,?,?)
Failed sql parameters: ['1', '0021001AD6C3', '??13#???', {ts '2018-01-28 16:09:43.000'}, '7896', null]
Failed sql parameters types: [VARCHAR, VARCHAR, VARCHAR, TIMESTAMP, VARCHAR, BIGINT]
Failed sql state and code: 23000 (1048)
Failed row data was: "1","0021001AD6C3","??13#???","2018-01-28 16:09:43","7896",
 StackTraceKey.init [SqlException:2774797739]
org.jumpmind.db.sql.SqlException: Column 'id' cannot be null
    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.JdbcSqlTransaction.addRow(JdbcSqlTransaction.java:478)
    at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.execute(DefaultDatabaseWriter.java:865)
    at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.insert(DefaultDatabaseWriter.java:202)
    at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriter.write(AbstractDatabaseWriter.java:191)
    at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:64)
    at org.jumpmind.symmetric.model.ProcessInfoDataWriter.write(ProcessInfoDataWriter.java:84)
    at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:64)
    at org.jumpmind.symmetric.io.data.writer.TransformWriter.write(TransformWriter.java:206)
    at org.jumpmind.symmetric.io.data.DataProcessor.forEachDataInTable(DataProcessor.java:210)
    at org.jumpmind.symmetric.io.data.DataProcessor.forEachTableInBatch(DataProcessor.java:177)
    at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:123)
    at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$1.call(DataLoaderService.java:1029)
    at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$1.call(DataLoaderService.java:991)
    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)

part of source_node symmetric.log:

2018-04-18 15:37:36,668 INFO [client1-001] [PushService] [client1-001-push-default-10] Push data sent to server:010:010
2018-04-18 15:37:40,139 INFO [client1-001] [PushService] [client1-001-push-default-10] Pushed data to node server:010:010. 6 data and 6 batches were processed. (sym_node_security, sym_node, jc_lfyhis, jc_ckdd, jc_lfy, jc_ckdw)
2018-04-18 15:37:40,139 INFO [client1-001] [PushService] [client1-001-push-default-10] Pushing to server:010:010 again because the last push contained reload batches
2018-04-18 15:37:40,315 INFO [client1-001] [PushService] [client1-001-push-default-10] Push data sent to server:010:010
2018-04-18 15:37:41,347 ERROR [client1-001] [AcknowledgeService] [client1-001-push-default-10] The outgoing batch 010-12 failed: [23000,1048] Column 'id' cannot be null

I have uploaded the symmetric.log
Steps To Reproducesource node:
1.Oracle database type
2.Tables of needing to synchronzie do not have any primary keys and some fields of the tables are empty in the oracle database

target node:
1.MariaDB/MySQL database type
2.in the MariaDB/MySQL database, it has not the source_node_database_tables of needing to synchronzie

Relationships

has duplicate 0003529 closed Symmetric needs to insert successfully null value into target_node_database_tables when initial load firstly 

Activities

ddlg_2

2018-04-18 05:20

reporter  

symmetricSource.log (607,330 bytes)
symmetricTarget.log (358,785 bytes)

elong

2018-04-18 11:51

developer   ~0001164

You tried to sync a null value to the "id" column, which is defined as not nullable, so the database rejects the row. What did you expect to happen?

ddlg_2

2018-04-18 21:18

reporter   ~0001165

Thanks for the quick response!

In the case of source tables(need to sync) do not have any primary keys and some columns are null, I want to sync a null value to the "id" column or other column,which is defined as nullable, can you reach it?

In the case, I know that symmetric will create primary key for each filed of target tables when the symmetric create target table .

Regards,
ddlg_2

hanes

2018-10-03 13:20

developer   ~0001252

Will change the default behavior in 3.10.

If a table does not have a PK SymmetricDS will try to create a target table with all columns used in the PK. This allows
loads to run multiple times without duplicate data. The first time would insert, additional runs would fall back to updates and
since the target has a PK now it will not duplicate. In your case though you have tables with too large of columns to use all cols
in PK. You can set the following parameter to create the target without PKs as the source. However you will need to use the delete first
on the table reload request to ensure every time data is loaded into the target it is deleted or truncated so that duplicates to not occur.

Make this the default going forward:

create.table.without.pk.if.source.without.pk=true

Related Changesets

SymmetricDS: 3.10 42f9e4e9

2018-10-15 16:32:41

josh-a-hicks

Details Diff
0003521: Create Table First is creating table with all columns as
primary key instead of none

0003521
mod - symmetric-core/src/main/resources/symmetric-default.properties Diff File

Issue History

Date Modified Username Field Change
2018-04-18 05:17 ddlg_2 New Issue
2018-04-18 05:17 ddlg_2 Tag Attached: table_reload_request
2018-04-18 05:20 ddlg_2 File Added: symmetricSource.log
2018-04-18 05:20 ddlg_2 File Added: symmetricTarget.log
2018-04-18 11:51 elong Note Added: 0001164
2018-04-18 21:18 ddlg_2 Note Added: 0001165
2018-10-03 12:56 hanes Status new => feedback
2018-10-03 13:20 hanes Product Version 3.9.6 => 3.10.0
2018-10-03 13:20 hanes Summary Symmetric can not insert automatically values into target_node_database_tables when initial load firstly => Create Table First is creating table with all columns as primary key instead of none
2018-10-03 13:20 hanes Note Added: 0001252
2018-10-03 13:20 hanes Status feedback => confirmed
2018-10-03 13:47 hanes Product Version 3.10.0 => 3.9.6
2018-10-03 13:47 hanes Target Version => 3.10.0
2018-10-03 13:47 hanes Relationship added has duplicate 0003529
2018-10-15 16:30 josh-a-hicks Assigned To => josh-a-hicks
2018-10-15 16:30 josh-a-hicks Status confirmed => assigned
2018-10-15 16:33 josh-a-hicks Status assigned => resolved
2018-10-15 16:33 josh-a-hicks Resolution open => fixed
2018-10-15 16:33 josh-a-hicks Fixed in Version => 3.10.0
2018-11-28 11:00 josh-a-hicks Changeset attached => SymmetricDS 3.10 42f9e4e9