View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0003521 | SymmetricDS | Bug | public | 2018-04-18 09:17 | 2019-04-26 19:52 |
| Reporter | ddlg_2 | Assigned To | josh-a-hicks | ||
| Priority | urgent | ||||
| Status | closed | Resolution | fixed | ||
| Product Version | 3.9.6 | ||||
| Target Version | 3.10.0 | Fixed in Version | 3.10.0 | ||
| Summary | 0003521: Create Table First is creating table with all columns as primary key instead of none | ||||
| Description | In 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 Reproduce | source 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 | ||||
| Tags | initial/partial load | ||||
| has duplicate | 0003529 | closed | Symmetric needs to insert successfully null value into target_node_database_tables when initial load firstly | |
| related to | 0002426 | closed | josh-a-hicks | Sym server create Primary Keys for all fields in table without PK |
| related to | 0000755 | closed | elong | When missing primary key, update where clause needs to use "is null" for null values |
|
|
|
|
|
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? |
|
|
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 |
|
|
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 |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2018-04-18 09:17 | ddlg_2 | New Issue | |
| 2018-04-18 09:17 | ddlg_2 | Tag Attached: table_reload_request | |
| 2018-04-18 09:20 | ddlg_2 | File Added: symmetricSource.log | |
| 2018-04-18 09:20 | ddlg_2 | File Added: symmetricTarget.log | |
| 2018-04-18 15:51 | elong | Note Added: 0001164 | |
| 2018-04-19 01:18 | ddlg_2 | Note Added: 0001165 | |
| 2018-10-03 16:56 | hanes | Status | new => feedback |
| 2018-10-03 17:20 | hanes | Product Version | 3.9.6 => 3.10.0 |
| 2018-10-03 17: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 17:20 | hanes | Note Added: 0001252 | |
| 2018-10-03 17:20 | hanes | Status | feedback => confirmed |
| 2018-10-03 17:47 | hanes | Product Version | 3.10.0 => 3.9.6 |
| 2018-10-03 17:47 | hanes | Target Version | => 3.10.0 |
| 2018-10-03 17:47 | hanes | Relationship added | has duplicate 0003529 |
| 2018-10-15 20:30 | josh-a-hicks | Assigned To | => josh-a-hicks |
| 2018-10-15 20:30 | josh-a-hicks | Status | confirmed => assigned |
| 2018-10-15 20:33 | josh-a-hicks | Status | assigned => resolved |
| 2018-10-15 20:33 | josh-a-hicks | Resolution | open => fixed |
| 2018-10-15 20:33 | josh-a-hicks | Fixed in Version | => 3.10.0 |
| 2018-11-28 16:00 | josh-a-hicks | Changeset attached | => SymmetricDS 3.10 42f9e4e9 |
| 2019-03-14 12:48 | admin | Status | resolved => closed |
| 2019-04-12 16:33 | admin | Tag Renamed | table_reload_request => initial/partial load |
| 2019-04-23 20:13 | elong | Relationship added | related to 0002426 |
| 2019-04-26 19:52 | elong | Relationship added | related to 0000755 |