Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Exception - Cannot insert duplicate key #2

Closed
cruscio opened this issue Feb 28, 2018 · 6 comments
Closed

Exception - Cannot insert duplicate key #2

cruscio opened this issue Feb 28, 2018 · 6 comments

Comments

@cruscio
Copy link
Collaborator

cruscio commented Feb 28, 2018

Not sure if there's a bug or a weird data condition; seemed worth reporting though.

01:39:06 INFO [main] Database customer_contact INIT: truncate table mydomain.customer_contact
01:39:06 INFO [main] Database customer_contact PROCESS: truncate table mydomain.customer_contact
01:39:08 INFO [main] RestTableAPI customer_contact PROCESS: getStats query="" count=731579
01:39:08 INFO [main] TableLoader customer_contact INIT: begin load customer_contact (731579 rows)
01:39:13 INFO [main] DatabaseInsertWriter customer_contact PROCESS: loaded 200 / 731579
01:39:18 INFO [main] DatabaseInsertWriter customer_contact PROCESS: loaded 400 / 731579
[...]
02:51:27 INFO [main] DatabaseInsertWriter customer_contact PROCESS: loaded 171000 / 731579
02:51:32 INFO [main] DatabaseInsertWriter customer_contact PROCESS: loaded 171200 / 731579
02:51:37 INFO [main] DatabaseInsertWriter customer_contact PROCESS: loaded 171400 / 731579
02:51:46 INFO [main] DatabaseInsertWriter customer_contact PROCESS: loaded 171600 / 731579
02:51:51 INFO [main] DatabaseInsertWriter customer_contact PROCESS: loaded 171800 / 731579
Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: Violation of PRIMARY KEY constraint 'customer_contact_pk'. Cannot insert duplicate key in object 'mydomain.customer_contact'. The duplicate key value is (3b3b1450db90db00d4b85eea4b96198f).
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1635)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:426)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:372)
        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:6276)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1794)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:184)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:159)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:315)
        at servicenow.datamart.DatabaseInsertStatement.insert(DatabaseInsertStatement.java:39)
        at servicenow.datamart.DatabaseInsertWriter.writeRecord(DatabaseInsertWriter.java:31)
        at servicenow.datamart.DatabaseTableWriter.processRecords(DatabaseTableWriter.java:38)
        at servicenow.api.RestTableReader.call(RestTableReader.java:68)
        at servicenow.api.RestTableReader.call(RestTableReader.java:8)
        at servicenow.datamart.TableLoader.call(TableLoader.java:136)
        at servicenow.datamart.Loader.loadTables(Loader.java:94)
        at servicenow.datamart.Loader.main(Loader.java:59)

For reference, the record in the error was created and last updated days ago (not in the middle of the load)

curl -X GET \
  'https://mydomain.service-now.com/api/now/table/customer_contact/3b3b1450db90db00d4b85eea4b96198f?sysparm_fields=sys_id,sys_created_on,sys_updated_on' \
  -H 'Accept: application/json' \
  -H 'Authorization: Basic *****' \
  -H 'Cache-Control: no-cache' \
  -H 'Content-Type: application/json' \

{"result": {
        "sys_id": "3b3b1450db90db00d4b85eea4b96198f",
        "sys_created_on": "2018-02-11 05:18:13",
        "sys_updated_on": "2018-02-23 18:48:03"
}}

Property file

servicenow.instance=https://mydomain.service-now.com/
servicenow.username=*****
servicenow.password=*****
servicenow.limit=200
servicenow.getkeys_limit=20000
datamart.url=jdbc:sqlserver://*****:1433;database=ServiceNow
datamart.username=*****
datamart.password=*****
datamart.dialect=mssql
datamart.schema=mydomain

Load Configuration

metrics: checkpoint_mydomain.txt

tables:
- {source: change_request, truncate: true}
- {source: cmdb_ci, truncate: true}
- {source: cmdb_ci_service, truncate: true}
- {source: cmn_location, truncate: true}
- {source: cmn_skill, truncate: true}
- {source: core_company, truncate: true}
- {source: customer_account, truncate: true}
- {source: customer_contact, truncate: true}
- {source: incident, truncate: true}
- {source: kb_knowledge, truncate: true}
- {source: problem, truncate: true}
- {source: sc_cat_item_delivery_plan , truncate: true}
- {source: sc_cat_item_delivery_task, truncate: true}
- {source: sc_request, truncate: true}
- {source: sn_customerservice_case, truncate: true}
- {source: sys_user, truncate: true}
- {source: sys_user_group, truncate: true}
- {source: task, truncate: true}
- {source: u_application_instance, truncate: true}
- {source: u_patient_safety, truncate: true}
- {source: cmdb_ci_appl, truncate: true}
- {source: alm_asset, truncate: true}
- {source: csm_consumer, truncate: true}
- {source: service_entitlement, truncate: true}
- {source: sn_app_cs_social_social_profile, truncate: true}

Command

java -ea -jar sndml-3.0.1-b.7-mssql.jar -p property.prpty -y load.yaml &> 20180227.05.log
@gflewis
Copy link
Owner

gflewis commented Feb 28, 2018

I think there may be more than one bug here.

First, the DatabaseInsertWriter class is not catching the primary key violation as it should. Apparently SQL Server is throwing a com.microsoft.sqlserver.jdbc.SQLServerException which is extended from java.sql.SQLException, and not java.sql.SQLIntegrityConstraintViolationException which the code is trying to catch. I can correct for this in the next release. As an interim workaround, use action: update (which should not have this issue).

Second, I am wondering if the truncate is working correctly. You have already confirmed that there is only one record with this sys_id. If the database table is truncated, then it should be empty when the load starts, so the PK violation should not occur. Is it possible for you to verify that truncate: true is working correctly?

@gflewis
Copy link
Owner

gflewis commented Feb 28, 2018

Here is another possibility: I noticed that this job had been running for more than an hour before it failed. Is it possible that a record was inserted or deleted in the customer_contact table while this job was running? (Any record; not necessarily 3b3b1450db90db00d4b85eea4b96198f) If so, it could cause the paging to get messed up and the loader could end up processing a record that had already been processed (which would explain the duplicate key).

If the issue is background inserts, then it should be possible to correct for it by adding created: [void, start], which will cause the loader to ignore any records inserted after it starts running. Background deletes are a bit more of a challenge.

@cruscio
Copy link
Collaborator Author

cruscio commented Mar 1, 2018

Is it possible that a record was inserted or deleted in the customer_contact table while this job was running?

There is insert/update activity occurring at the same time as my extract job is running. Unfortunately, there's not much I can do about that.

wondering if the truncate is working correctly
should be possible to correct for it by adding created: [void, start]

I tried manually truncating the table before running, and adding created: [void, start] to the load directive - still seeing primary key violations on large tables. Different sys_id each time.

Interestingly, the second occurrence of the key is appearing nowhere close to the first - making me think this isn't just a shift in paging from insert/delete causing it to appear in two adjacent intervals.

  • Does the request for records need an order by? (I'm unfamiliar with how the rest API orders by default)
  • Would adding partitioning help this issue?

use action: update

I'll give this a try next

16:51:27 INFO INIT Globals GLOBAL: loadProperties /opt/sndml/conf/mydomain.prpty
16:51:27 INFO INIT Session GLOBAL: instance=https://mydomain.service-now.com/ user=*****
16:51:27 INFO INIT Database GLOBAL: database=jdbc:sqlserver://*****:1433;database=ServiceNow user=***** schema=*****
16:51:28 INFO INIT Generator GLOBAL: dialect=mssql schema=mydomain namecase=LOWER namequotes=SQUARE
16:51:28 DEBUG INIT Globals GLOBAL: setLoaderConfig start=2018-03-01 16:51:28 metrics=null
16:51:28 INFO INIT LoaderConfig GLOBAL:
metrics: /opt/sndml/checkpoint/core_company
tables:
- source: core_company
  truncate: true
  created: [void, start]
16:51:28 DEBUG INIT DateTimeFactory GLOBAL: getDate java.lang.String=void
16:51:28 DEBUG INIT DateTimeFactory GLOBAL: getDate(void)=null
16:51:28 DEBUG INIT DateTimeFactory GLOBAL: getDate java.lang.String=start
16:51:28 DEBUG INIT DateTimeFactory GLOBAL: getDate(start)=2018-03-01 16:51:28
16:51:28 DEBUG INIT Loader GLOBAL: starting loader threads=0
16:51:28 DEBUG INIT TableLoader core_company: call table=core_company action=INSERT
16:51:28 DEBUG INIT Database core_company: tableExists schema=mydomain table=core_company result=true
16:51:28 DEBUG REQUEST XmlRequest core_company.WSDL: https://mydomain.service-now.com/core_company.do?WSDL
16:51:31 DEBUG RESPONSE XmlRequest core_company.WSDL: status="HTTP/1.1 200 OK" contentType=text/xml len=34822
16:51:31 INFO INIT Database core_company: truncate table mydomain.core_company
16:51:31 INFO PROCESS Database core_company: truncate table mydomain.core_company
16:51:31 DEBUG INIT RestTableReader core_company: initialize statsEnabled=true query="sys_created_on<2018-03-01 16:51:28"
16:51:31 DEBUG REQUEST JsonRequest core_company: GET https://mydomain.service-now.com/api/now/stats/core_company?sysparm_query=sys_created_on%3C2018-03-01+16%3A51%3A28&sysparm_count=true
16:51:31 DEBUG RESPONSE JsonRequest core_company: status="HTTP/1.1 200 OK" contentType=application/json;charset=UTF-8 len=38
16:51:31 DEBUG PROCESS RestTableAPI core_company: {"result":{"stats":{"count":"62769"}}}
16:51:31 INFO PROCESS RestTableAPI core_company: getStats query="sys_created_on<2018-03-01 16:51:28" count=62769
16:51:31 DEBUG INIT RestTableReader core_company: expected=62769
16:51:31 INFO INIT TableLoader core_company: begin load core_company (62769 rows)
16:51:31 DEBUG REQUEST JsonRequest core_company: GET https://mydomain.service-now.com/api/now/table/core_company?sysparm_offset=0&sysparm_limit=200&sysparm_exclude_reference_link=true&sysparm_display_value=false&sysparm_query=sys_created_on%3C2018-03-01+16%3A51%3A28
16:51:33 DEBUG RESPONSE JsonRequest core_company: status="HTTP/1.1 200 OK" contentType=application/json;charset=UTF-8 len=213830
16:51:33 DEBUG PROCESS DatabaseInsertWriter core_company: processing c94993314a362312005cfc20c1aa15d2
16:51:33 DEBUG PROCESS DatabaseInsertWriter core_company: processing 0a55c660dbc4d740b273dd0b5e96199a
16:51:33 DEBUG PROCESS DatabaseInsertWriter core_company: processing 17164a24dbc4d740b273dd0b5e9619ba
16:51:33 DEBUG PROCESS DatabaseInsertWriter core_company: processing 2155c660dbc4d740b273dd0b5e961939
16:51:33 DEBUG PROCESS DatabaseInsertWriter core_company: processing 3955c660dbc4d740b273dd0b5e961990
16:51:33 DEBUG PROCESS DatabaseInsertWriter core_company: processing 3b168a24dbc4d740b273dd0b5e9619a1
16:51:33 DEBUG PROCESS DatabaseInsertWriter core_company: processing 3f554a60dbc4d740b273dd0b5e9619f1
16:51:33 DEBUG PROCESS DatabaseInsertWriter core_company: processing 4255c660dbc4d740b273dd0b5e9619ad
16:51:33 DEBUG PROCESS DatabaseInsertWriter core_company: processing 44658a60dbc4d740b273dd0b5e96195a
16:51:33 DEBUG PROCESS DatabaseInsertWriter core_company: processing 4ab58aa0dbc4d740b273dd0b5e96198a
16:51:33 DEBUG PROCESS DatabaseInsertWriter core_company: processing 57554a60dbc4d740b273dd0b5e961911
16:51:33 DEBUG PROCESS DatabaseInsertWriter core_company: processing 5d06cae0dbc4d740b273dd0b5e961940
16:51:33 DEBUG PROCESS DatabaseInsertWriter core_company: processing 5d37de80db268300469538ff9d961952
16:51:33 DEBUG PROCESS DatabaseInsertWriter core_company: processing 67f50ae0dbc4d740b273dd0b5e961982
16:51:33 DEBUG PROCESS DatabaseInsertWriter core_company: processing 6e550a60dbc4d740b273dd0b5e961931
16:51:33 DEBUG PROCESS DatabaseInsertWriter core_company: processing 7955c660dbc4d740b273dd0b5e961969
16:51:33 DEBUG PROCESS DatabaseInsertWriter core_company: processing 7f060224dbc4d740b273dd0b5e9619e8
16:51:33 DEBUG PROCESS DatabaseInsertWriter core_company: processing 81264264dbc4d740b273dd0b5e9619be
16:51:33 DEBUG PROCESS DatabaseInsertWriter core_company: processing 83550a60dbc4d740b273dd0b5e9619dd
16:51:33 DEBUG PROCESS DatabaseInsertWriter core_company: processing 88658a60dbc4d740b273dd0b5e961916
16:51:33 DEBUG PROCESS DatabaseInsertWriter core_company: processing 8eb58aa0dbc4d740b273dd0b5e96197b
16:51:33 DEBUG PROCESS DatabaseInsertWriter core_company: processing 97164a24dbc4d740b273dd0b5e9619e9
16:51:33 DEBUG PROCESS DatabaseInsertWriter core_company: processing 97554a60dbc4d740b273dd0b5e96195a
16:51:33 DEBUG PROCESS DatabaseInsertWriter core_company: processing a4658a60dbc4d740b273dd0b5e9619b2
16:51:33 DEBUG PROCESS DatabaseInsertWriter core_company: processing a6550a60dbc4d740b273dd0b5e961949
16:51:33 DEBUG PROCESS DatabaseInsertWriter core_company: processing af554a60dbc4d740b273dd0b5e9619ab
16:51:33 DEBUG PROCESS DatabaseInsertWriter core_company: processing b6550a60dbc4d740b273dd0b5e961982
16:51:33 DEBUG PROCESS DatabaseInsertWriter core_company: processing cf164a24dbc4d740b273dd0b5e961995
16:51:33 DEBUG PROCESS DatabaseInsertWriter core_company: processing e155c660dbc4d740b273dd0b5e961930
16:51:33 DEBUG PROCESS DatabaseInsertWriter core_company: processing e2550a60dbc4d740b273dd0b5e96195b
16:51:33 DEBUG PROCESS DatabaseInsertWriter core_company: processing e2550a60dbc4d740b273dd0b5e961960
16:51:33 DEBUG PROCESS DatabaseInsertWriter core_company: processing ed558660dbc4d740b273dd0b5e9619e5 <-- First occurrance
16:51:33 DEBUG PROCESS DatabaseInsertWriter core_company: processing f42c4e21db0c5f40b273dd0b5e96192d
16:51:33 DEBUG PROCESS DatabaseInsertWriter core_company: processing fe550a60dbc4d740b273dd0b5e961990
16:51:33 DEBUG PROCESS DatabaseInsertWriter core_company: processing 4f36d53adb61074041d8d7795e9619a5
[...]
16:51:34 DEBUG PROCESS DatabaseInsertWriter core_company: processing 00b4ce03db0c1700720dd2984b961979
16:51:34 DEBUG PROCESS DatabaseInsertWriter core_company: processing 00b58e4fdb0c1700720dd2984b9619c7
16:51:34 DEBUG PROCESS DatabaseInsertWriter core_company: processing 00b5ce4fdb0c1700720dd2984b96191f
16:51:34 DEBUG PROCESS DatabaseInsertWriter core_company: processing 00b6ce8bdb4c1700720dd2984b961917
16:51:34 INFO PROCESS DatabaseInsertWriter core_company: loaded 200 / 62769
16:51:34 DEBUG PROCESS RestTableReader core_company: processed 200 rows
16:51:34 DEBUG REQUEST JsonRequest core_company: GET https://mydomain.service-now.com/api/now/table/core_company?sysparm_offset=200&sysparm_limit=200&sysparm_exclude_reference_link=true&sysparm_display_value=false&sysparm_query=sys_created_on%3C2018-03-01+16%3A51%3A28
16:51:36 DEBUG RESPONSE JsonRequest core_company: status="HTTP/1.1 200 OK" contentType=application/json;charset=UTF-8 len=215906
16:51:36 DEBUG PROCESS DatabaseInsertWriter core_company: processing 00b7ca47db8c1700720dd2984b961938
16:51:36 DEBUG PROCESS DatabaseInsertWriter core_company: processing 00b7ca47db8c1700720dd2984b9619a3
16:51:36 DEBUG PROCESS DatabaseInsertWriter core_company: processing 00c00287db481700720dd2984b96195e
16:51:36 DEBUG PROCESS DatabaseInsertWriter core_company: processing 00c14e8fdb481700720dd2984b9619fb
[...]
17:04:24 DEBUG PROCESS DatabaseInsertWriter core_company: processing ed35ce0bdb0c1700720dd2984b96196e
17:04:24 DEBUG PROCESS DatabaseInsertWriter core_company: processing ed36ca47db4c1700720dd2984b96190b
17:04:24 INFO PROCESS DatabaseInsertWriter core_company: loaded 58200 / 62769
17:04:24 DEBUG PROCESS RestTableReader core_company: processed 58200 rows
17:04:24 DEBUG REQUEST JsonRequest core_company: GET https://mydomain.service-now.com/api/now/table/core_company?sysparm_offset=58200&sysparm_limit=200&sysparm_exclude_reference_link=true&sysparm_display_value=false&sysparm_query=sys_created_on%3C2018-03-01+16%3A51%3A28
17:04:26 DEBUG RESPONSE JsonRequest core_company: status="HTTP/1.1 200 OK" contentType=application/json;charset=UTF-8 len=217370
17:04:26 DEBUG PROCESS DatabaseInsertWriter core_company: processing ed36ca47db4c1700720dd2984b9619a1
17:04:26 DEBUG PROCESS DatabaseInsertWriter core_company: processing ed370ecfdb4c1700720dd2984b961918
17:04:26 DEBUG PROCESS DatabaseInsertWriter core_company: processing ed3829d4db2cdb00a43689584b96191a
17:04:26 DEBUG PROCESS DatabaseInsertWriter core_company: processing ed3829d4db2cdb00a43689584b96191d
17:04:26 DEBUG PROCESS DatabaseInsertWriter core_company: processing ed3829d4db2cdb00a43689584b961922
17:04:26 DEBUG PROCESS DatabaseInsertWriter core_company: processing ed3829d4db2cdb00a43689584b961927
17:04:26 DEBUG PROCESS DatabaseInsertWriter core_company: processing ed3829d4db2cdb00a43689584b96192c
17:04:26 DEBUG PROCESS DatabaseInsertWriter core_company: processing ed3829d4db2cdb00a43689584b961931
17:04:26 DEBUG PROCESS DatabaseInsertWriter core_company: processing ed3829d4db2cdb00a43689584b961936
17:04:26 DEBUG PROCESS DatabaseInsertWriter core_company: processing ed3829d4db2cdb00a43689584b96193b
17:04:26 DEBUG PROCESS DatabaseInsertWriter core_company: processing ed3829d4db2cdb00a43689584b961940
17:04:26 DEBUG PROCESS DatabaseInsertWriter core_company: processing ed3829d4db2cdb00a43689584b961945
17:04:26 DEBUG PROCESS DatabaseInsertWriter core_company: processing ed3829d4db2cdb00a43689584b96194a
17:04:26 DEBUG PROCESS DatabaseInsertWriter core_company: processing ed3829d4db2cdb00a43689584b96194f
17:04:26 DEBUG PROCESS DatabaseInsertWriter core_company: processing ed418e8bdb481700720dd2984b96190d
17:04:26 DEBUG PROCESS DatabaseInsertWriter core_company: processing ed418e8bdb481700720dd2984b96191e
17:04:26 DEBUG PROCESS DatabaseInsertWriter core_company: processing ed434e03dbc81700720dd2984b961964
17:04:26 DEBUG PROCESS DatabaseInsertWriter core_company: processing ed434e03dbc81700720dd2984b961967
17:04:26 DEBUG PROCESS DatabaseInsertWriter core_company: processing ed434e03dbc81700720dd2984b961986
17:04:26 DEBUG PROCESS DatabaseInsertWriter core_company: processing ed434e03dbc81700720dd2984b9619b4
17:04:26 DEBUG PROCESS DatabaseInsertWriter core_company: processing ed44064fdbc81700720dd2984b9619fe
17:04:26 DEBUG PROCESS DatabaseInsertWriter core_company: processing ed45028bdb0c1700720dd2984b9619e9
17:04:26 DEBUG PROCESS DatabaseInsertWriter core_company: processing ed45028bdb0c1700720dd2984b9619ff
17:04:26 DEBUG PROCESS DatabaseInsertWriter core_company: processing ed460e87db4c1700720dd2984b96190c
17:04:26 DEBUG PROCESS DatabaseInsertWriter core_company: processing ed460e87db4c1700720dd2984b96190f
17:04:26 DEBUG PROCESS DatabaseInsertWriter core_company: processing ed464e87db4c1700720dd2984b961913
17:04:26 DEBUG PROCESS DatabaseInsertWriter core_company: processing ed474603db8c1700720dd2984b96190b
17:04:26 DEBUG PROCESS DatabaseInsertWriter core_company: processing ed51c6cbdb481700720dd2984b961991
17:04:26 DEBUG PROCESS DatabaseInsertWriter core_company: processing ed53c643dbc81700720dd2984b9619e3
17:04:26 DEBUG PROCESS DatabaseInsertWriter core_company: processing ed540e4fdbc81700720dd2984b9619e3
17:04:26 DEBUG PROCESS DatabaseInsertWriter core_company: processing ed550e8bdb0c1700720dd2984b9619c6
17:04:26 DEBUG PROCESS DatabaseInsertWriter core_company: processing ed554e8bdb0c1700720dd2984b961908
Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: Violation of PRIMARY KEY constraint 'core_company_pk'. Cannot insert duplicate key in object 'mydomain.core_company'. The duplicate key value is (ed558660dbc4d740b273dd0b5e9619e5).
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1635)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:426)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:372)
        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:6276)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1794)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:184)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:159)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:315)
        at servicenow.datamart.DatabaseInsertStatement.insert(DatabaseInsertStatement.java:39)
        at servicenow.datamart.DatabaseInsertWriter.writeRecord(DatabaseInsertWriter.java:31)
        at servicenow.datamart.DatabaseTableWriter.processRecords(DatabaseTableWriter.java:38)
        at servicenow.api.RestTableReader.call(RestTableReader.java:68)
        at servicenow.api.RestTableReader.call(RestTableReader.java:8)
        at servicenow.datamart.TableLoader.call(TableLoader.java:136)
        at servicenow.datamart.Loader.loadTables(Loader.java:94)
        at servicenow.datamart.Loader.main(Loader.java:59)

@gflewis
Copy link
Owner

gflewis commented Mar 1, 2018

(1) Would partitioning help? Yes. This condition can still occur if you partition, but only if the record gets inserted/deleted in the partition that is currently being processed. Since the partitions are processed in reverse chronological order, the likelihood drops sharply after you get past the first partition.
(2) Does the request need an order by? Maybe. I will investigate the default order. If it is not predictable and consistent then I will add an "order by".

@gflewis
Copy link
Owner

gflewis commented Mar 5, 2018

There are some changes in Beta 8 which may help with this issue. Please see the notes here:

If you need to load a large table that is being concurrently updated, I recommend splitting it into pieces. For example

# companies created before March 1
- name: core_company_before_20180301
  source: core_company
  created: [void,2018-03-01]
  partition: month
  orderby: sysid
# companies with null sys_created_on
- name: core_company_null_created
  source: core_company
  created: [void,void]
  filter: sys_created_onISEMPTY
# companies created or updated since March 1
- name: core_company_after_20180301
  source: core_company
  since: 2018-03-01
  orderby: sysid

Let me know if the orderby helps.
Thanks.

@gflewis
Copy link
Owner

gflewis commented Mar 18, 2018

The latest release uses a default OrderBy of "sys_created_on,sys_id". I have found that this improves the reliability when loading large tables. Thanks for the suggestion.

You may also want to test out the Action: Sync feature available with the latest release.

Thanks again.

@gflewis gflewis closed this as completed Mar 18, 2018
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@gflewis @cruscio