6

I have a query that I'm using to export data:

SELECT c.Id, c.SystemModstamp, ...
FROM Contact c
WHERE c.SystemModstamp > start
AND c.SystemModstamp < end
AND c.Id > string
ORDER BY c.Id ASC
LIMIT 100000

...where 'start' and 'end' are valid timestamps and 'string' is an 18-digit Id. This query runs multiple times, each time setting the 'string' to the highest-value Id from the previous batch.

Most of the time this is fine. However, for a certain (large) slice of data, this query instead returned 99994 records with 0 errors. I know that it didn't simply reach the end of the filtered list, because when I run the next batch (with a new and higher 'string'), that query returns the full 100000.

What could be causing this? I've been running this query in Data Loader while researching, and the results are the same when using Export or Export All. I've been able to reproduce the 99994 records even with slightly-altered queries. It almost seems like there is somehow 'broken' data on this table that's not returned and doesn't throw an error.

Has anyone seen this before?

UPDATE: After a lot of trial-and-error, I've found that excluding all custom fields from this query returns the expected 100000 records. However, including even one custom field (I tested with a simple checkbox, Inactive__c) causes the query to return 99994 records instead. Unfortunately, I have no idea why that is or what it means.

UPDATE 2: I was able to compare the 100000 records with the 99994 records and found the 6 records that make up the difference. Despite being an Admin, I get the 'Insufficient Privileges' message when trying to access the records in UI. However, I can query them in Dev Console (SELECT Id FROM Contact WHERE Id='x'). What could be wrong with these six records that would cause this sort of behavior?

UPDATE 3: Neither of the posted answers match up with what I'm seeing, but since both have received a score of 2, the bounty will be awarded anyway. Unless a better answer comes along before the bounty expires, I'm abandoning this question.

Improve this question
5
  • Do you have any field in those 6 missing records which has null values? And if yes, what field is that?
    – Jayant Das
    Feb 14, 2019 at 22:45
  • @JayantDas There are many fields on these records that are null, but none that aren't null on some valid record somewhere.
    – iwhitt567
    Feb 15, 2019 at 13:56
  • I was wondering if Indexing with Nulls was somewhat causing this issue.
    – Jayant Das
    Feb 15, 2019 at 14:47
  • In developer console can you run query as SELECT Id, Inactive__c FROM Contact WHERE Id='x' for the problematic records?
    – tugce
    Feb 18, 2019 at 11:54
  • @tugce That is almost exactly the query I detailed above. It returns no results.
    – iwhitt567
    Feb 19, 2019 at 20:33

2 Answers 2

Reset to default
3
+50

From your update 2 , it's pretty obvious that those 6 records are ghost records and thus they won't be part of your export.

What are ghost records? Ghost records are corrupt records copied during Sandbox refresh for a partial copy or full copy sandbox. The records get corrupt when they are being edited during the sandbox copy process to clone them.

Have a look at this related knowledge article, its close .

Solution: Delete those 6 records using workbench and clone it back from production.

Edit: I found a link for this behaviour, you have to reach out to SF support for the fix.

Improve this answer
3
  • Is there any official SF documentation on 'ghost records'? I'll use the steps linked to confirm and get back to you, thank you.
    – iwhitt567
    Feb 12, 2019 at 16:53
  • @iwhitt567 I have updated link for ghost records.
    – Pranay Jaiswal
    Feb 12, 2019 at 17:08
  • I attempted to confirm that my issue matched what you linked using Workbench, but Workbench was not able to find the record despite the fact that I can query for it (as long as I don't query for custom fields). As such, this is not an answer to my issue.
    – iwhitt567
    Feb 14, 2019 at 15:47
1

I would recommend you try changing your WHERE conditions to include equals in the Modstamp criteria. It's possible that you you could be encountering rare conditions where some of the record mod stamps just happen to be equal to either the start or end records.

Try changing to this:

WHERE c.SystemModstamp >= start AND c.SystemModstamp <= end
Improve this answer
4
  • If a record is exactly equal to either timestamp and the record is excluded for that reason, I have no problem with that. The query above should still return 100000 records.
    – iwhitt567
    Feb 8, 2019 at 21:07
  • @iwhitt567 I see the updates you've since posted to your question where you say that including ANY custom field in your query causes the discrepancy to occur. Have you checked the OWD & FLS for your Admin Profile on Contact? What is the record sharing for both Contact and for Account?
    – crmprogdev
    Feb 9, 2019 at 18:33
  • The Admin profile has View All and Modify All on the Contact, as well as Edit Access to the custom field I've been testing with. The OWD for Contact is Controller by Parent, but all six of the problem records have a null AccountId - which is not a problem, so do many other Contact records in this environment.
    – iwhitt567
    Feb 10, 2019 at 0:55
  • Do you happen to have Contacts to Multiple Accounts enabled in your org? If so, deleting a primary Account relationship or having one that isn't populated could potentially create more problems than you may realize. This could possibly cause a sharing problem for you that's the source of the issue with the custom fields.
    – crmprogdev
    Feb 12, 2019 at 16:52

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .