Violates foreign key constraint when replicating from ZohoCRM to Postgres

  • 22Views
  • Last Post 3 weeks ago
  • Topic Is Solved
0
votes
Craig Cooke posted this 4 weeks ago

Trying to replicate data from ZohoCRM to Postgres on-prem DB.  Initially had some issues with the sync, but it started yesterday.  Took 15 hours(!) to run but was completed this morning.  Unfortunatley I am seeing that over half of my contacts weren't imported, with the following error insert or update on table ""Contacts"" violates foreign key constraint

Most of my other tables also encountered an issue when they were imported, with a similar error message.

Any suggestions on how I can fix this?  I want to ensure all of my records are imported successfully, otherwise what is the point??

Order By: Standard | Newest | Votes
0
votes
Mariia Zaharova posted this 4 weeks ago

Hi Craig! 

Please select "Drop Tables" and "Create Tables" check boxes and run your package. This will re-create all your target tables from the scratch (according to the source tables schema) and re-copy the data.

Also, we recommend you to check that "Unicode" check box (Advanced settings) is selected in your PostgreSQL connection (some failed records are related to encoding issues).

 

Looking forward to your reply with the results.

 

0
votes
Craig Cooke posted this 4 weeks ago

I have tried running again with the changes you mentioned above.  Unfortunately I am still getting a lot of errors.  I do have the Create Foreign Keys option enabled as well, should it be turned off?

What can be done to get this working properly?  I want to ensure all of my records are being exported properly to SQL.

0
votes
Mariia Zaharova posted this 4 weeks ago

Hi Craig! 

 

As I can see, your last run is cancelled, so we cannot even check error files for this run.

 

Please check that "Unicode" check box (Advanced settings) is selected in your PostgreSQL connection (some failed records in previous run are related to encoding issues).

 

Also, yes, you can try to turn-off 'Create Foreign Keys' option. 

After all these changes, please run your package and do not cancel it.

 

 

Looking forward to your reply with the results.

 

Best regards,

Mariia

 

0
votes
Craig Cooke posted this 4 weeks ago

Thanks for the suggestion.  I was able to run this again and am getting much further.  Unforutnately I still have over 22,000 contact records that didn't import.  When I check the error log they all seem to kick out with the error "null value in column ""Title"" violates not-null constraint"

Any suggestions on how we can get these records into the table?

0
votes
Mariia Zaharova posted this 4 weeks ago

Most likely, the issue is the following:

- previously, the Title field was non-required in Zoho CRM and some records have nulls in it;

- later, settings were changed and the Title field became a required one: http://prntscr.com/pbn7x2

- Metadata of your Zoho CRM connection was updated or new connection was created; in this case Skyvia treats this field as non-nullable and creates target PostgreSQL column as not null;

- records that have nulls in this field are failed.

 

We recommend you to check this directly in Zoho CRM: Settings-> Customization-> Modules and Fields-> Contacts -> Fields-> Create & Edit fields. 

NOTE: after making this field non-required, you need to clear the Metadata Cache for your Zoho CRM connection and run your package again with the "Drop Tables" and "Create Tables" check boxes.

 

Looking forward to your reply with the results.

 

 

Best regards,

Mariia

0
votes
Craig Cooke posted this 4 weeks ago

Thank you!!!!

Worked perfectly after those changes were made.

0
votes
Mariia Zaharova posted this 3 weeks ago

Glad to see that the issue is resolved!

 

Feel free to contact us in case of any further issues.

 

Best regards,

Mariia

Close